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