Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
KetanB
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
Anonymous
Not applicable

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.

KetanB
Frequent Visitor

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Kudoed Authors