topic Re: PowerBI Embedded problem with Direct Query on Azure SQL in Developer

PowerBI Embedded problem with Direct Query on Azure SQL

christiank — Thu, 28 Apr 2016 18:46:49 GMT

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

Re: PowerBI Embedded problem with Direct Query on Azure SQL

Greg_Deckler — Thu, 28 Apr 2016 19:01:17 GMT

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?

Re: PowerBI Embedded problem with Direct Query on Azure SQL

irinag — Fri, 29 Apr 2016 02:46:47 GMT

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

Re: PowerBI Embedded problem with Direct Query on Azure SQL

christiank — Fri, 29 Apr 2016 08:24:32 GMT

Thanks for the hint!

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

Re: PowerBI Embedded problem with Direct Query on Azure SQL

leamx — Tue, 09 Aug 2016 02:27:19 GMT

Hello, I am facing the same issue with direct query and an azure database. Where exactly do you set the Path Gateway Datasource?

Thanks.

Re: PowerBI Embedded problem with Direct Query on Azure SQL

Felix — Mon, 05 Dec 2016 08:51:22 GMT

Hello,

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

Thanks in advance.

Re: PowerBI Embedded problem with Direct Query on Azure SQL

Felix — Mon, 05 Dec 2016 09:51:17 GMT

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.

Re: PowerBI Embedded problem with Direct Query on Azure SQL

vanessa — Fri, 03 Mar 2017 12:59:19 GMT

Where exactly do you set the Path Gateway Datasource?

Re: PowerBI Embedded problem with Direct Query on Azure SQL

Felix — Mon, 06 Mar 2017 11:52:37 GMT

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.

Re: PowerBI Embedded problem with Direct Query on Azure SQL

vanessa — Mon, 06 Mar 2017 12:27:32 GMT

@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?

Re: PowerBI Embedded problem with Direct Query on Azure SQL

Felix — Mon, 06 Mar 2017 13:04:18 GMT

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?

Re: PowerBI Embedded problem with Direct Query on Azure SQL

vanessa — Mon, 06 Mar 2017 13:28:56 GMT

@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.

Re: PowerBI Embedded problem with Direct Query on Azure SQL

Felix — Mon, 06 Mar 2017 14:21:57 GMT

@vanessa

Ok now I understand your problem. Unfortunatly I haven't tried RLS in combination with Power BI Embedded... But it could be possible that there is a problem with this combination. Perhaps opening a support request could solve your problem.

I suppose you have already seen the example Power BI Embedded with RLS?

Re: PowerBI Embedded problem with Direct Query on Azure SQL

irinag — Mon, 06 Mar 2017 16:50:31 GMT

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);

Re: PowerBI Embedded problem with Direct Query on Azure SQL

vanessa — Tue, 07 Mar 2017 05:41:45 GMT

@irinag

Yes. I have done that. RLS worked fine when the source was SQL server table. But the visuals don't show up when the source is Azure SQL Data warehouse.

Re: PowerBI Embedded problem with Direct Query on Azure SQL

ccounotte — Mon, 24 Apr 2017 13:22:52 GMT

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-DirectQuery/td-p/29326, 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.

Re: PowerBI Embedded problem with Direct Query on Azure SQL

TedPattison — Tue, 25 Apr 2017 00:27:59 GMT

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

Re: PowerBI Embedded problem with Direct Query on Azure SQL

ccounotte — Tue, 25 Apr 2017 06:15:04 GMT

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.

Re: PowerBI Embedded problem with Direct Query on Azure SQL

TedPattison — Tue, 25 Apr 2017 13:27:50 GMT

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

Re: PowerBI Embedded problem with Direct Query on Azure SQL

ccounotte — Wed, 26 Apr 2017 05:09:48 GMT

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:

...

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.