PowerBI Embedded problem with Direct Query on Azure SQL

[christiank]

Hi,

I have a Power BI Embedded report with an SQL Azure data source using the Direct Query method.

First I created a PBIX file using Power BI Desktop and then uploaded it using the Power BI Embedded sample app.

When embedding the report on a web page (using the JavaScript SDK), all visualizations of the report fail to load.

If I use the Import method instead of Direct Query on the same dataset, the visualizations show up fine.

Any ideas why Direct Query isn't working on SQL Azure datasources?

Do I have to provide the credentials for the data source somehow after uploading the PBIX file?

Thanks

[Greg_Deckler]

I wouldn't be surprised if Direct Query isn't support with Power BI Embedded. Quick question, if you use the Direct Query version and upload to the Power BI Service (not Azure Workspace) does it work OK? If it works OK, do you have an Enterprise Gateway configured? 

[irinag · Accepted solution]

---

Direct Query is supported for the Embeded and you would need to configure the datasource after the import, here is how: https://msdn.microsoft.com/library/mt711498.aspx

https://azure.microsoft.com/en-us/documentation/articles/power-bi-embedded-get-started/

Hope this helps,

Irina

---

[ccounotte]

Hello, I don't understand how this can be the answer.

I embedded a Direct Query report in Azure, however I can't see any data, keeps complaining my personnal gateway is offline (which is not true).

Looking at how to configure a datasource, I have no gateway ID nor a datasource id whatsoever. All I get from the imports is a dataset id. So there's no way I can configure the datasource.

Similarly I embedded a Import-mode report (at least it shows the data), but again, no datasource or gateway ID.

All I can use is the REST API SetAllConnections to configure the connection strings, but actual info on its syntax is not well documented, and it doesn't help in any ways either.

Can anybody shed some light on what I could be missing?

I tried all guides on embedding powerbi reports I could find, using the ProvisionSample or using direct REST calls, no difference.

Thanks.

EDIT: If I use the BoundGatewayDataSource on any of the datasets I've embedded, it just reports an empty list at best, an NotFound or other errors.

[TedPattison]

You should not have to change the data source connection string. You can just use the connection string that is embedded in the data source definition inside the PBIX file you are uploading to PBIE. If it works for you while you are designing a report in Power BI Desktop, the same connection string should work once you have uploaded the PBIX file to PBIE. The only thing you MUST do after the PBIX upload is to set the credentials (e.i. user name and password) using the Power BI .NET API. It seems like this is where you are having problems.

If you post the code you are using, I would be happy to look at it and if I can help to troubleshoot the problem. I would also encourage you to look at the code I referred you to in my post yesterday which covers all aspects of uploading a PBIX file to Power BI Embedded and updating the data source credentials. Here is the link one more time - Program.cs

[ccounotte]

Thanks a lot for your reply.

When it calls GetGatewayDatasourcesAsync(), it returns no data sources, so I receive no gateway id or data source id. Is it because the report is in direct-query mode? Or am I missing something?

I've embedded a few direct-query reports and they all have the same issue. The same report works fine in PowerBI Desktop and fine when I publish it to the web.

Here is a partial output of the Get Imports on my workspace. It shows a dataset, but no datasource or gateway ids.

{

"id": "865b0a38-3107-42ce-a382-847aa20fcec4",

"importState": "Succeeded",

"createdDateTime": "2017-04-21T13:09:20.637",

"updatedDateTime": "2017-04-21T13:09:20.637",

"reports": {

"id": "e5098782-dec3-473d-b740-2499becc4a61",

"modelId": 598557,

"name": "TestReport",

"webUrl": "https://app.powerbi.com/reports/e5098782-dec3-473d-b740-2499becc4a61",

"embedUrl": "https://app.powerbi.com/appTokenReportEmbed?reportId=e5098782-dec3-473d-b740-2499becc4a61",

"isOriginalPbixReport": true,

"pages": null

}

"datasets": {

"id": "5177b668-e586-499b-98bd-0a6b7140c5f8",

"name": "TestReport",

"tables": [],

"relationships": [],

"webUrl": "https://app.powerbi.com/datasets/5177b668-e586-499b-98bd-0a6b7140c5f8"

} 

"name": "TestReport"

}

,

This is the javascript I use to show the report:

<div id="reportContainer2" style="width: 100%; height: 330px;"></div>

...

var embedConfiguration1 = {
type: 'report',
accessToken: 'eyJ0e...3Q',
id: 'e5098782-dec3-473d-b740-2499becc4a61',
embedUrl: 'https://embedded.powerbi.com/appTokenReportEmbed',
settings: { filterPaneEnabled: false, navContentPaneEnabled: false },
filters: {
$schema: "http://powerbi.com/product/schema#basic",
"target": {
"table": "Circuits",
"column": "id_circuit"
},
"operator": "In",
"values": [257795,256022,256023]
}
};

var $reportContainer = $('#reportContainer1');
var report = powerbi.embed($reportContainer.get(0), embedConfiguration1);

Note that the filter does not work at all so far, be it with a direct-query or imported report.

[TedPattison]

Here is the code I have used successfully.

static void UpdateAzureSqlDataSource(string workspaceCollectionName, string workspaceId, string datasetId) {
      using (var client = CreatePowerBIClient()) {
        IList<Dataset> datasets = client.Datasets.GetDatasetsAsync(workspaceCollectionName, workspaceId).Result.Value;
        foreach (Dataset dataset in datasets) {
          if (dataset.Name == datasetId) {
            var datasources = client.Datasets.GetGatewayDatasourcesAsync(workspaceCollectionName, workspaceId, dataset.Id).Result;
            // Reset your connection credentials
            var delta = new GatewayDatasource {
              CredentialType = "Basic",
              BasicCredentials = new BasicCredentials {
                Username = azureSqlUser,
                Password = azureSqlPassword
              }
            };
            // Update the datasource with the specified credentials
            client.Gateways.PatchDatasourceAsync(workspaceCollectionName, 
                                                 workspaceId, 
                                                 datasources.Value[0].GatewayId, 
                                                 datasources.Value[0].Id,
                                                 delta).Wait();
          }
        }
      }
    }

Here is the flow when you already know the datset name dataset:

1. Call client.Datasets.GetDatasetsAsync and get all datsets in workspace
2. Enumerate through datsets and find dataset and dataset ID of dataset with matching name
3. Call client.Datasets.GetGatewayDatasourcesAsync and pass dataset ID to get datasource ID you need to update
4. Call to GetGatewayDatasourcesAsync to get datasource
5. Note that datasource is accessible through datasources.Value[0].
6. The datasources.Value[0] object has an ID property for datasource ID
7. The datasources.Value[0] object has an GatewayID property for gateway ID
8. Create GatewayDatasource object to servie as input parameter with credentials
9. Pass GatewayDatasource object in call to client.Gateways.PatchDatasourceAsync

[ccounotte]

Thanks a lot for your time and suggestions.

At this point, the call to client.Datasets.GetGatewayDatasourcesAsync(workspaceCollectionName, workspaceId, datasetId); always returns a datasources with Value.Count = 0, on any dataset IDs I've already embedded.

So I can't retrieve get any required ID to call the patch datasource REST API.

I tried to locate those IDs in 'manage gateways' on the web, no ID are reported there. Tried in PowerBI Desktop, same thing, no ID reported.

[sherylmeg]

I'm having a similar problem that started once I moved my SQL Server to an Azure VM from SQL Azure. When my database was hosted on SQL Azure I was able to upload a pbix that uses direct query and update the connection settings. I have since started using a SQL DB on an Azure VM as the data source and am unable to update the connection settings. When running client.Datasets.GetGatewayDatasourcesAsync(workspaceCollectionName, workspaceId, datasetId) the datasets always return datasources with Value.Count = 0.

This post seems to discuss the problem and mentions that there might be a solution soon.

https://github.com/Microsoft/PowerBI-Cli/issues/29

"wbreza commented on Aug 18, 2016
After following up with @julianmbs offline the scenario ended up being a SQL DB on an Azure VM and not pure SQL Azure. This scenario is technically treated the same as "on-premise" support which will be supported sometime in Fall 2016."

Any news regarding this?

Thanks

[ccounotte]

Wished I knew about this issue before I wasted so many days trying to make it work. We actually dropped PBI because of that.

[ccounotte]

That can't be the solution. When importing a direct query mode PowerBI document, it only creates a datasets, and there's no gateway ID or datasource ID whatsoever.

I've installed the private and entreprise gateways, but none of them are providing a gateway ID.

As for the datasource ID, I have no clue where to get it in the process. Maybe if I switch to import, however I'm definitely not interested in using import-mode.

I've following those guides:

    http://radacad.com/bring-the-power-into-your-application-power-bi-embedded

    https://docs.microsoft.com/en-us/azure/power-bi-embedded/power-bi-embedded-iframe

The SQL Server is hosted on Azure, PowerBI Web/Desktop have no issue what soever, it's only the embedded reports that show nothing.

No matter what I do, I only get errors for any elements of the reports, similar to those:

Your private gateway is offline, but other gateway are available with the data (translated from french)

    Code d'erreurDMTS_NoGatewayWithAllDatasourcesToBindError

    Server xx.xx.xx.xx

    ID de corrélation 2cb3f593-1b0c-302c-6af9-a9d4b2ce2e7b

    Databasexxxxxxxxx

    ConnectionType{FieldValue}

    ID d'activitéba5159c0-00fd-40cb-9fbb-957db84c03b2

    ID de demandef447e1d1-aef5-4863-eb97-4b4f0987de5e

    HeureMon Apr 24 2017 15:03:24 GMT+0200 (Paris, Madrid (heure d’été))

    Version13.0.1700.1899

    URI du clusterhttps://WABI-UK-SOUTH-redirect.analysis.windows.net

Fact is, this is an utter non-sense as my gateway is up-and-running and can be used to refresh import-mode reports!

I've also followed this solution: https://community.powerbi.com/t5/Developer/Connection-String-for-Embedded-report-which-uses-DirectQu..., but it doesn't help either.

Any help would be greatly appreciated, I've wasted several days reinstalling everything, including the gateways, but none of the error message makes any sense.

[TedPattison]

It's possible the confusion is caused by the Power BI REST API which makes you retrive a data source ID and a gateway ID in order to set credentals for the data source used by a DirectQuery-mode dataset in Power BI Embedded. This is true even when there is no Power BI gateway invovled as in the case of using Azure SQL to create a DirectQuery-mode dataset for Power BI Embedded. I have written a sample C# console app that uses the Power BI .NET API to upload PBIX files to Power BI Embedded. If you go to this GitHub repo:

https://github.com/CriticalPathTraining/PowerBiEmbedded

And look at the Visual Studio project named PBIEmbeddedDemo_Provisioning which contains a C# course files named Program.cs. This source file has a method named UpdateAzureSqlDataSource which shows you the code you need to patch credentials in Power BI embedded.

You can also look at the following video I published tpo youtube. Go to the 55:45 minute mark to see a walkthrough of this code.

https://www.youtube.com/watch?v=AE6j2y_F2CA

[christiank]

Thanks for the hint!

After setting the credentials using the Path Gateway Datasource operation it's working fine.

[Felix]

Hello,

how do I configure the Path Gateway Datasource. Searched for it but with no luck.

Thanks in advance.

[Felix]

Hello,

I solved it myself. My problem was that I had to update my credentials for my existing dataset. Just select point 7 at the Provision Sample located at the Power BI Embedded sample and enter your credentials for the SQL Server. Updating the connection string was not necessary so I skipped it.

[vanessa]

Where exactly do you set the Path Gateway Datasource?

[Felix]

Hello,

I don't know if I configured the Path Gatewy Datasource. But if you want to upload your .pbix-report to your web application you can use the Provision-Example (Provision-Sample). At first you have to upload your example-report (Provision-Sample: Nr. 5). After that you have to update the connection string, because it seems that the credentials you have entered before are not applied to the report uploaded to your web-app. So just select Nr. 7 of the Provision-Smaple and enter your credetials for your database. Then the report should show the data from the database.

[vanessa]

@Felix

I have configured RLS on the PowerBI desktop file and when I embed it into the application, the visuals dont show up. Is there any configuration needed for that?

[Felix]

Unfortunatly I don't know exactly if you have a problem with embedding your report into your web-app or with your credentials to load the data from your database into the report. Could you show me a screenshot of your embedded report?

Futhermore did you follow this sample:  Get started with Power BI Embedded sample?

[vanessa]

@Felix

When I embed a report using Direct Query into the app without any RLS configured, it works fine.

But when I configure RLS in the same pbix file and embed that into the application, the report opens up but the visuals dont.

So, I dont think its an issue with the credentials. It has something to do with the RLS part. 

When I use Import option and embed this report with RLS into the app, it all works fine. Looks like some issue with Direct Query+RLS.

[irinag]

Have you modified the EmbedToken on the embedded report to contain RLS role and user name?

 string[] roles = { "NAMEOFTHEROLE" };

PowerBIToken.CreateReportEmbedToken(this.workspaceCollection, this.workspaceId, report.Id, USERNAME, roles);