cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KetanB Frequent Visitor
Frequent Visitor

Cannot change Data Source of Power BI Report built with Direct Query mode

Hello friends,
We have a requirement to redistribute same Power BI Chart (PBIX) to multiple customers (complete different environment with different databases of each customer). Meaning, we can't create PBIX file using 'Import' connectivity mode because we can't let data of development environment be present in PBIX file. So, we are thinking that we can create a master PBIX file using ‘Direct Query’ connection mode which would be calling a SP to get data. And we will distribute that PBIX file to all customers and they can further point that PBIX file to their DB (SQL DB on their Premises).


Meaning, after creating and delivering PBIX files to customer, the customers will still have to change the data source (connection string of Data source) to point it to their DB. And we will like to have a programmatic way to do so. We first checked the Report Portal URL and went to ‘Manage > Data Sources’ option and observed that Connection String was disabled there. Meaning, we couldn’t change it from there. Then, we thought to do it Programmatically using REST APIs of Data sources. But, REST API also couldn’t do that if we are changing Connection string of the Data source (it returns Bad request response). So, we are landing to conclusion that ‘We cannot change the connection string of a data source after the PBIX file is created from Power BI Desktop’.

Can someone please help me out on this? I could figure out two ways to change Data source connection after the PBIX file is created from Power BI Desktop using Direct Query mode.
One is to open the PBIX in Power BI Desktop and navigate to 'Option and Settings > Data Source Settings'; but we have hundreds of PBIX files which has to be dilievered to 8-10 different customers. So, opening all files in PBI Desktop and change data source according to each customer would be time consuming.
Other way is to go and modify data of 'DataModelDataSource' table of Report Server. But this is also not a clean way, because things are saved in encrypted format there.

I would like to know if there are any others ways or not. Also, if anyone have idea if Microsoft is planning to bring/extend API to change data source of a PBIX (at least for Direct Query mode).

2 REPLIES 2
Highlighted
KetanB Frequent Visitor
Frequent Visitor

Re: Cannot change Data Source of Power BI Report built with Direct Query mode

Please also note that, we are using On-Prem Power BI Report Server to deploy the Power BI Reports. Not the Power BI Service/Online.

stpnet Established Member
Established Member

Re: Cannot change Data Source of Power BI Report built with Direct Query mode

Hmmmmm!

 

I'm also getting Bad Request Responses when trying to use Patch to update the connectionstring or indeed change anything about the data source.

 

I can't find anything useful being logged at the PBI-SSRS end to give me a clue as to what the issue is.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 51 members 1,195 guests
Please welcome our newest community members: