Dynamically link template PBIX file to client specific data sources, leveraging App Workspace

Hello,

I have searched for an answer on our scenario, but have yet to find one that matches our exact need.

We are leveraging PowerBI Embedded inside our custom application (as an ISV) and deploying a series of identical PBIX reports to every customer.  We have created a reporting database for each customer, which gets updates from the application database every morning.  For PowerBI reporting (as well as a couple other items), each customer has their own server/database combo, with the database schema being fairly consistent across all customers.  Each customer has their own App Workspaces for Production and another one for UAT.

Currently, whenever we make an update to the ‘template’ file, we manually update the server and database for each client and then publish to their unique app workspace.  Additionally, we are leveraging the same query (SQL View via SSMS) as the source and need to update that View on each customers server/database combo (via SSMS) whenever we make updates or add/remove fields.

We are leveraging Import method instead of DirectQuery.  This is due to multiple factors, one of which is that our reporting database only updates each morning via an overnight process - so no data is truly real time.  We also had issues with the reports on DirectQuery once we updated credentials to utilize multi-factor authentication.  We utilize scheduled refreshes each morning at 6:AM for every report.

Here is an example of the scenario:

PBIX Report:  Regional Sales Production.pbix

Data Source:  SQL Server (Azure)

Client 1 Data Source Settings:

Server: RedSox_azuredb.database.windows.net

Database: RedSox_Warehouse_PROD (each client also has a UAT version on the same server)

Dataset: VW_PowerBI_SalesProduction

Client 2:

Server: Yankees_azuredb.database.windows.net

Database: Yankees _Warehouse_PROD (each client also has a UAT version on the same server)

Dataset: VW_PowerBI_SalesProduction

I feel like it should be easier, and exist as standard functionality in the PowerBI Embedded product to utilize a single .pbix file and dynamically assign the server name and database when the refreshes happen every morning.  It also feels like having to update the SQL Views via SSMS for each client, every time we make an update, should also have a more automated solution.  I know there is functionality define the SQL in the “Advanced” data source settings, however we’ve experienced issues trying to use that functionality.

I’m by no means an expert, and have long subscribed to the “if you can think it, you can build it” mentality but we just haven’t been able to crack the code for dynamically updating data source settings.

I was thinking maybe there is a way to do it by passing parameters, but we need the user experience to be such that no choices are made, it should somehow know that because the file is in Client1’s Workspace that it should point to RedSox_ azuredb.database.windows.net and RedSox_Warehouse_PROD when in the RedSox app workspace for Production.  Are these parameters something we can store in each workspace somehow? And have the .pbix or scheduled refresh reference/set them based on that?  Setting them within the report and having to choose wouldn’t save too much time, and still leaves room for the human error part of deploying the report to the wrong workspace.

Appreciate any and every piece of advice an expert on this can provide so we can learn some more, and further automate our environment!

Thanks!

mStreetAdvisory