Forum Discussion

Frank_SP's avatar
Frank_SP
Regular Visitor
2 years ago

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

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) b...
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Frank_SP
    Regular Visitor
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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