Forum Discussion
PowerBI Embedded problem with Direct Query on Azure SQL
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
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?
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
Thanks for the hint!
After setting the credentials using the Path Gateway Datasource operation it's working fine.
Hello, I am facing the same issue with direct query and an azure database. Where exactly do you set the Path Gateway Datasource?
Thanks.
Hello,
how do I configure the Path Gateway Datasource. Searched for it but with no luck.
Thanks in advance.
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.
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_NoGatewayWithAllDatasourcesToBindErrorServer xx.xx.xx.xxID de corrélation 2cb3f593-1b0c-302c-6af9-a9d4b2ce2e7bDatabasexxxxxxxxxConnectionType{FieldValue}ID d'activitéba5159c0-00fd-40cb-9fbb-957db84c03b2ID de demandef447e1d1-aef5-4863-eb97-4b4f0987de5eHeureMon Apr 24 2017 15:03:24 GMT+0200 (Paris, Madrid (heure d’été))Version13.0.1700.1899URI du clusterhttps://WABI-UK-SOUTH-redirect.analysis.windows.netFact 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.
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.
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.
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
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""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":}"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.
