Reply
Frequent Visitor
Posts: 3
Registered: ‎03-19-2017
Accepted Solution

Import single PBIX to multiple workspace/datasets using different Azure SQL server hostnames

I have a Report.pbix file which connects to Azure SQL using DirectQuery. All the tables in this PBIX connect as follows:

let Source = Sql.Database("mycustomer1.database.windows.net", "reportdb"),...

 

I am able to import this exact PBIX to all of my customers workspaces and have used powerbi update-connection (Post SetAllConnections) to set the correct datasource for each customer#:

 

{ connectionString: 'data source=[mycustomer#].database.windows.net;initial catalog=reportdb;persist security info=True;encrypt=True;trustservercertificate=False' }

 

In other words, the PBIX is set to a single customer's Azure SQL hostname and I use SetAllConnections to specify a different Azure SQL hostname for each workspace/dataset that I upload this PBIX to. I also set the correct dataset credentials right after each PBIX is uploaded.

 

However, the embed only works for the customer whose SQL server was originally hardcoded into the PBIX file. For everyone else, I get "See details" and the XHR JSON for querydata shows:

 

...{"Id":"DS0","odata.error":{"code":"QuerySystemError","source":"PowerBI","message":{"lang":"en-US","value":"Failed to execute the DAX query."}...

 

Instead of hardcoding the Azure hostname, I also tried using a query parameter and I get the same results.

 

1) Is there a way to change the query parameter (which contains the SQL server hostname) either during import PBIX via command-line or when embedding it on to a webpage?

 

2) It is even possible to change the SQL server hostname used by all the tables in a PBIX file via REST API (i.e. without using PowerBI Desktop)?

 

3) If I have 100 customers and each of them needs the exact same PBIX but pointing to their own SQL server, do I need 100 copies of the PBIX file with the query parameter set to each customer's SQL server? This seems insane to maintain but after hours of searching online, I'm beginning to feel there is no alternative.

 

Note: We need to keep each customer's data on separate SQL server (not just separate DB) for regulatory/legal reasons.

 

Thanks for your assistance in advance.


Accepted Solutions
Frequent Visitor
Posts: 3
Registered: ‎03-19-2017

Re: Import single PBIX to multiple workspace/datasets using different Azure SQL server hostnames

Opened a ticket with Microsoft and figured out the issue. Answering my own questions below in case anyone else has the same issue.

 

> 1) Is there a way to change the query parameter (which contains the SQL server hostname) either during import PBIX via command-line or when embedding it on to a webpage?

 

No. But don't have to because:

 

> 2) It is even possible to change the SQL server hostname used by all the tables in a PBIX file via REST API (i.e. without using PowerBI Desktop)?

 

Yes. This is possible via powerbi update-connection script (API Post SetAllConnections). And it works well as long as certain conditions are met.

 

3) If I have 100 customers and each of them needs the exact same PBIX but pointing to their own SQL server, do I need 100 copies of the PBIX file with the query parameter set to each customer's SQL server? This seems insane to maintain but after hours of searching online, I'm beginning to feel there is no alternative.

 

Thankfully, don't have to maintain 100 PBIX files. Can successfully deploy the same PBIX to any number of hosts. And it works well as long as certain conditions are met.

 

NECESSARY CONDITIONS for deploying the same PBIX to different workspaces that connect to different servers: Tables used in the PBIX must have the exact same structure in all the databases/SQL servers. Structure of tables not used in PBIX does not matter.

 

If the table structure is different, it is still possible to deploy same PBIX if you create a custom SELECT statement with only the common fields. E.g.:

 

let Source = Sql.Database(SQL_Host, SQL_DB, [Query="SELECT Field1, Field2 FROM Table"]) in Source

View solution in original post


All Replies
Moderator
Posts: 2,161
Registered: ‎03-06-2016

Re: Import single PBIX to multiple workspace/datasets using different Azure SQL server hostnames


eMobileBI wrote:


However, the embed only works for the customer whose SQL server was originally hardcoded into the PBIX file. For everyone else, I get "See details" and the XHR JSON for querydata shows:

 

...{"Id":"DS0","odata.error":{"code":"QuerySystemError","source":"PowerBI","message":{"lang":"en-US","value":"Failed to execute the DAX query."}...

 

Instead of hardcoding the Azure hostname, I also tried using a query parameter and I get the same results.

 

1) Is there a way to change the query parameter (which contains the SQL server hostname) either during import PBIX via command-line or when embedding it on to a webpage?

 

2) It is even possible to change the SQL server hostname used by all the tables in a PBIX file via REST API (i.e. without using PowerBI Desktop)?

 

3) If I have 100 customers and each of them needs the exact same PBIX but pointing to their own SQL server, do I need 100 copies of the PBIX file with the query parameter set to each customer's SQL server? This seems insane to maintain but after hours of searching online, I'm beginning to feel there is no alternative.


Supposing you're talking about the Power BI service, not Power BI Embedded.

 

 

What do you mean "hardcoded"? I don't quite get this. AFAIK, I think it is not possible to use one report but pointing to the indivisual datasource according to Who's viewing it in Power BI Service.

 

For maintainance flexibility, you can develop/update one pbix and import it to Power BI Serivce. I guess you may use Group to isolate the customers? so import one pbix to indivisual groups and update connection string and credential accordingly, all in a programmatical way.

 

For Power BI Embedded, you can try to update the connection string and credential every time according to the viewer before they view the report. It looks promising as you can have only one report, however I would have concern about the performance.

Check update connection string in Power BI Embedded.

 

All in all, IMHO, the most reasonable way is using Row Level Security, you can gather all the data in one place and use one pbix. People would only see what they're allowed to see. However it seems that you're trapped by regulatory/legal reasons.

 

 

Frequent Visitor
Posts: 3
Registered: ‎03-19-2017

Re: Import single PBIX to multiple workspace/datasets using different Azure SQL server hostnames

> Supposing you're talking about the Power BI service, not Power BI Embedded.

 

Sorry for being unclear, I am talking about Power BI Embedded only.

 

> What do you mean "hardcoded"? I don't quite get this. AFAIK, I think it is not possible to use one report but pointing to the indivisual datasource according to Who's viewing it in Power BI Service.

 

This is how I setup the DirectQuery tables in the Edit Query screen in PowerBI Desktop for my PBIX file:

 

let Source = Sql.Database("mycustomer1.database.windows.net", "reportdb"),...

 

The hostname is hardcoded in there (as I can see in Advanced Editor) so I can make the charts/tables in PBIX with dummy/test data since I don't import anything (and want to use DirectQuery).

 

> For Power BI Embedded, you can try to update the connection string and credential every time according to the viewer before they view the report. It looks promising as you can have only one report, however I would have concern about the performance.

 

This is what I do once per report per customer using https://github.com/Microsoft/PowerBI-Node

 

When I check the connection string, it shows the updated/correct string for every workspace/dataset. However, the PBIX only works for the originally hardcoded hostname and not for the updated hostnames.

 

I also use RLS for restricting which users for each customer see which rows and it is working well. But of course, only for the hostname that was originally harcdcoded in the PBIX.

Frequent Visitor
Posts: 3
Registered: ‎03-19-2017

Re: Import single PBIX to multiple workspace/datasets using different Azure SQL server hostnames

Opened a ticket with Microsoft and figured out the issue. Answering my own questions below in case anyone else has the same issue.

 

> 1) Is there a way to change the query parameter (which contains the SQL server hostname) either during import PBIX via command-line or when embedding it on to a webpage?

 

No. But don't have to because:

 

> 2) It is even possible to change the SQL server hostname used by all the tables in a PBIX file via REST API (i.e. without using PowerBI Desktop)?

 

Yes. This is possible via powerbi update-connection script (API Post SetAllConnections). And it works well as long as certain conditions are met.

 

3) If I have 100 customers and each of them needs the exact same PBIX but pointing to their own SQL server, do I need 100 copies of the PBIX file with the query parameter set to each customer's SQL server? This seems insane to maintain but after hours of searching online, I'm beginning to feel there is no alternative.

 

Thankfully, don't have to maintain 100 PBIX files. Can successfully deploy the same PBIX to any number of hosts. And it works well as long as certain conditions are met.

 

NECESSARY CONDITIONS for deploying the same PBIX to different workspaces that connect to different servers: Tables used in the PBIX must have the exact same structure in all the databases/SQL servers. Structure of tables not used in PBIX does not matter.

 

If the table structure is different, it is still possible to deploy same PBIX if you create a custom SELECT statement with only the common fields. E.g.:

 

let Source = Sql.Database(SQL_Host, SQL_DB, [Query="SELECT Field1, Field2 FROM Table"]) in Source