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

[mStreetAdvisory]

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

[Greg_Deckler]

@mStreetAdvisory - Well, best case is that you would have a single repository for everyone and use RLS to control who can see what. 

In your case, perhaps you might look into Azure DevOps or Power BI Pipelines

[mStreetAdvisory]

@Greg_Deckler  - I should have added that our contracts require us to keep each client on a seperate database instance, we can't commingle the data.

[Greg_Deckler]

@mStreetAdvisory - Yep, I completely understand that. Only thing I can think of then is to build some kind of dev ops pipeline where you can automatically set this depending upon who you are deploying it for. I get the contracts issue but it just makes things very inelegant overall.

[FinDataAnalyst]

Hey @Greg_Deckler , 

I agree and please vote for this. 
However, your suggestion alone will not solve the issue, as if you had 1 repport on several datasets,  as a small change to a dataset will still unfortionately require all report datasets to be changed. And normally in Power BI the dataset changes when the report changes a bit more. 

Good workarounds:
I run a similar setup for ca. 500 separate client companies, all on separate instances so I know what you are going through. 

We have solved this by having a looping script that goes through all clients and: 
1. For small changes (no measure or dataset changes)
--> Push in the new report, bind the dataset to it, validate and then start using the new report.
2. For large changes (to a measure or larger dataset change)
--> Push in new report and new dataset, bind these URL:s to your production enviroment.

This is Better than a single template app for some schenarios, as we allow users to edit the repors via power bi embeded, and those edits need to be programatically found and replicated in newer versions. 

So we only have 1 template and over night we systematically update that to all clients.

Lessons: 
1. Have them all in the same workspace and have 1 set of credentials that works for all your instances, will make orchestration easier. Does increase data leak risk though.
2. Only remove old after a short waiting period as power bi api can be quite buggy some times. 
3.  Don't try to do it all at once, power BI api has some thresholding limitations
4. Your power bi capacity cannot handel too much refresh load at once, spread your refreshes out durring the day.