Dynamic Dashboard Template for Multiple Users - Same Structure, Different Databases

[Frank_SP]

Hello Power BI Community,

I'm currently working on a project where we host multiple databases on a server for different clients. These databases have identical structures (same tables and columns) but contain unique data for each client. My goal is to create a dynamic dashboard template in Power BI that can be used by each client to view their own data without mixing information between clients.

Here's the challenge: I want to design a single dashboard template that, upon a user's login, automatically loads and displays data from their specific database. This means that while the dashboard structure remains the same, the underlying data should change based on who is accessing the dashboard.

I'm looking for advice on how to achieve this functionality. Is it possible to set up such a dynamic connection within Power BI? If so, what would be the best approach to ensure that each user only sees their database's information in the dashboard without any crossover?

I've considered using Row Level Security (RLS), but I'm not sure if it's the right solution since the data sources themselves are entirely separate databases rather than different views within a single database.

Any guidance, insights, or suggestions on how to implement this would be greatly appreciated. I'm eager to learn from your experiences and find the most efficient way to provide personalized dashboards to our users.

Thank you for your help!

Best regards,

[Anonymous]

Hi @Frank_SP ,

You can follow the steps below to get it:

1. Create a user dimension table as below

2. Connect to different data source with same data structure in Power BI Desktop

3. Add a custom column [Group] for every data source

4. Append the queries from different data sources

Append queries - Power Query | Microsoft Learn

5. Create a relationship between the user dimension table and appened table base on the field [Group]

6. Apply the dynimac RLS

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

Dynamic Row Level Security with Power BI Made Simple - RADACAD

Dynamic Row Level Security with Profiles and Users in Power BI : Many-to-Many Relationship - RADACAD

[Email]=UserPrincipalName()

Best Regards

[Frank_SP]

Hi @Anonymous  and Community Support,

First off, thank you very much, Rena, for your detailed response to my query to allow each user to visualize their specific database on a shared template dashboard. I appreciate the time you took to outline the steps and resources.

I have a couple of follow-up questions and concerns regarding the implementation:

1. Efficiency with Multiple Databases: Our setup involves more than 100 databases, each significant in size. Would the approach of connecting to all these data sources within Power BI Desktop and appending queries not lead to substantial memory resource consumption? Is there a more scalable solution that could handle such a volume efficiently?

2. Simultaneous Access by Multiple Users: If two or more users access the dashboard at the same time, could there be any issues with data overlap or performance? Given each user should only see data from their database, how does Power BI manage this concurrency to ensure data integrity and security?

Given these points, I'm looking for guidance on how to best implement a solution that addresses these challenges. Is there a way to dynamically connect a user to their specific database upon login, without pre-loading all databases into Power BI Desktop?

Thank you for your support. Any further advice or alternative solutions you can provide would be greatly appreciated.
Best regards,
Frank

[Anonymous]

Hi @Frank_SP ,

Regarding your first question, connecting to all these data sources within Power BI Desktop and appending queries could lead to substantial memory resource consumption. A more scalable solution that could handle such a volume efficiently is to use DirectQuery or Live Connection. DirectQuery allows you to connect to a data source and query it in real-time, while Live Connection allows you to connect to a data source and use its data in real-time without importing it into Power BI. Both of these options can help you avoid the memory resource consumption issue. You can find more information about DirectQuery and Live Connection in the following link:

DirectQuery in Power BI - Power BI | Microsoft Learn

What's the difference between live connections and DirectQuery? - Power BI | Microsoft LearnRegarding your second question, Power BI manages concurrency to ensure data integrity and security by using row-level security (RLS). RLS allows you to restrict data access at the row level based on user roles and permissions. This means that each user can only see data from their database, and there will be no issues with data overlap or performance.

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

Best Regards