Seeking Guidance on Power BI Dashboard Development and Distribution for a Multi-Customer Environment

[Kengaroo]

I am a Software Developer at a company with a clientele of over 100 customers, many of whom operate multiple databases across various regions. My current responsibility involves the initial development of a Power BI Dashboard, intended for customer access. This Dashboard will feature a range of reports focused on sales and inventory metrics.

The challenge I'm facing is not in the creation or publication of the reports/dashboard within a personal or Organizational workspace. Instead, my query revolves around the optimal method for distributing this dashboard to our extensive customer base.

Our Objective:

• Development of a centralized Power BI Dashboard incorporating multiple reports.
• Distribution of this dashboard to over 100 customers, likely through a published App.
• Data for these reports will be sourced from SQL Server (version 2016 or later). We are preparing a server view to consolidate databases from each customer. These databases share an identical schema, differing primarily in regional data.
• Our intention is to provide customers with a self-service model where they can connect their data sources to the dashboard or app without granting us access to their data.
• We aim for seamless update capabilities, allowing any modifications to the dashboard to reflect automatically for all customers. Custom dashboard requests will be addressed separately.

Challenges:

• The intricacies of App publishing and distribution are where my uncertainties lie. Options like Marketplace publishing, hosting in a Workspace with customer access, or distribution through a SharePoint site are being considered.

I possess a foundational understanding of the process but am seeking advice on the finer details, particularly regarding the most effective distribution strategy for our unique use case.

Any insights or recommendations from this knowledgeable community would be immensely valuable and appreciated.

[GilbertQ]

Hi @Kengaroo 

As far as I can understand, with your requirements, you will have access to their data in a sql server database. If all the databases are the same, what you could then do is consolidate all the data into a single database and then you. use power BI row level security to only show the customer their own data that will then allow you to create a single report in a single app. As well as align the customers to see their own data.

[Kengaroo]

I don't think this would fly with Security. How would I consolidate all the databases into one? There are over 100 customers. Each of these customers has their own SQL Server that's on-prem. Someone from my company will go in and install a data gateway on all 100+ customer servers. Yes the databases are all structured exactly the same. They even have the same name. But each customer might have more than one database. So now these 100+ customers become 200+ for example. It's just a lot of data. 

What I'm planning on doing is the following.

1. Build the reports and publish them to my companies workspace.
2. Bundle the reports/dashboards into a Power BI App. 

3. The IT guy will then log in to the customers Power BI Service account, either through a guest account or the IT guy would use his companies Power BI Service account were the customer has provided access to their own service.

4. The IT guy will install the app, go to the dataset and change the 'servername' and 'databasename' parameters to the correct connection strings.

5. The IT guy will test that the app is now using the customers data.

6. The customer enjoys the app.

The above seems annoying and tedious, but I will build a tool that uses the Power BI Api to allow the IT guy to adjust these settings. This will automate the process as much as possible.

That's the best solution I can think of. I was considering Template Apps, but I don't want to allow the customer to have control of the app. Our customers aren't all tech savvy, so it would be easier if they didn't have access to anything that would break the app.