Replicate same design to multiple tenants

[KevinRDynasoft]

Hello there,

The company I work for has a human resources app for D365 Business Central. With the app we usually include a Power BI report.

The thing is, for each client, I have to make a copy of the report layout and change the data source. This works for a few clients but when you have 50+ clients each with multiple companies, it becomes very time consuming.

The report always uses the same web services. The only thing that changes is the tenant and the names of the companies.

My question is,

Is there any way I can make Power BI change the data source of a "master report" to the tenant's? I know it's complicated because eventhough we're using the same web services, customer A could have 5 companies and customer B could have only 2. 

Any tips are greatly appreciated.

Thanks,

Kevin

[collinq · Accepted solution]

Hi @KevinRDynasoft ,

At first glance, I do not see why you couldn't use RLS to do this.  You could have one report with the Roles being set for each company.  Then, in the security just assign the correct user.  The first time setup would take a few minutes but that would still be easier than creating multiple reports.

If you are not familiar with RLS, this is a good starting point:
Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn

[connect · Accepted solution]

Hi there,

I understand your challenge with managing multiple clients and company-specific Power BI reports. There are a few strategies you can consider to streamline this process:

1. Power BI Parameters: Use parameters to dynamically change the data source. You can create parameters for the tenant and company names, and use them in your queries to filter data accordingly. This way, you only need to change the parameter values for each client.

2. Dynamic Data Source via Power Query: Power Query allows you to use parameters in your M code to dynamically change the data source. You can set up a master report with parameters for tenant and company names, and modify the M code to reference these parameters. This makes it easier to switch data sources without duplicating reports.

3. Dataflows: Consider using Power BI Dataflows. Dataflows can act as a central data preparation layer where you can transform and load data once, and then reuse it across multiple reports. Each client can have their dataflow with tenant-specific transformations, and the reports can connect to the appropriate dataflow based on the tenant.

4. Power BI Template Files (.pbit): Create a Power BI Template file with parameters for the data source. When you open a template file, it prompts you to enter values for the parameters. This allows you to quickly generate a report for a new client by simply entering their tenant and company information.

5. Automation with Power BI REST API: If you're comfortable with scripting, you can use the Power BI REST API to automate the process of updating data sources. You can write a script to update the data source of a master report for each client programmatically.

By implementing one or more of these strategies, you should be able to reduce the time and effort required to manage your Power BI reports across multiple clients.

Please let me know if you need any more information. 

[collinq · Accepted solution]

Hi @KevinRDynasoft ,

At first glance, I do not see why you couldn't use RLS to do this.  You could have one report with the Roles being set for each company.  Then, in the security just assign the correct user.  The first time setup would take a few minutes but that would still be easier than creating multiple reports.

If you are not familiar with RLS, this is a good starting point:
Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn