Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

How to show firmwide data when RLS is in place

  • 1.  How to show firmwide data when RLS is in place

    Posted Jan 15, 2021 12:24 PM
    I have RLS on our data, where sales people can see data related only to their own sales. The RLS is set up with a UPN table that is joined to sales data.

    But we also want to show them firmwide data - e.g. we want a salesman to see that he sold six pairs of shoes compared to 600 pairs of shoes across the entire firm. And we want it on the same report, so they aren't toggling between reports.

    I thought maybe the way to get around this is to have a second duplicate sales table that isn't joined to anything, and thus should be able to show all the numbers. Is this the right approach? Or is there a better approach?

    Thanks!

    ------------------------------
    Caite
    ------------------------------


  • 2.  RE: How to show firmwide data when RLS is in place

    Posted Jan 15, 2021 05:12 PM
    duplicate fact tables are not ideal, but I've had a similar scenario where it worked. If your users will also use the dataset as a source for other PBI or Excel analysis I recommend building a separate measure table for those company-wide measures and hiding the second fact table so users cannot access the details of the underlying data.

    ------------------------------
    Matt Buczynski
    Senior Consultant
    ------------------------------



  • 3.  RE: How to show firmwide data when RLS is in place

    Top Contributor
    Posted Jan 18, 2021 06:30 AM
    Edited by Vishesh Jain Jan 18, 2021 06:30 AM
    Hi @Caite Stevens,

    I agree with @Matt Buczynski, that building separate fact tables​​ in the same model is not ideal.

    However, in order to save space, what you can do is use the Groupby function in Power Query.

    Create a new query by Referencing the original table, DO NOT duplicate it as it will increase memory consumption.
    ***Can someone please confirm this for me, that RLS works on the data model after ETL and not in Power Query.***
    If RLS works on Power Query as well, then you may duplicate the table and then do a group by.
    In this new table, Group all the transactions by date or territory etc., which basically removes the salesman from the sales table and gives you an aggregate of it.

    Now on this new table, there won't be any RLS configured and neither will it have any salesman information, so you can write new measures for this table and show your company-wide totals.

    I hope this helps.

    Thank you,

    ------------------------------
    Vishesh Jain
    MCSA Power BI and Excel
    Emerald Award Dynamic Communities 2019
    Owner
    VR Construction
    ------------------------------



  • 4.  RE: How to show firmwide data when RLS is in place

    Posted Jan 18, 2021 09:34 AM
    Ref: Create a new query by Referencing the original table, DO NOT duplicate it as it will increase memory consumption.
    ***Can someone please confirm this for me, that RLS works on the data model after ETL and not in Power Query.***
    If RLS works on Power Query as well, then you may duplicate the table and then do a group by.

    Referencing or duplicating does NOT increase memory consumption: if you reference, PQ will run the COMPLETE script, including the referenced table: it does not save the referenced table in memory. Anyway, what PQ does to produce is generate a SCRIPT (no data). PowerBI will load the data following the SCRIPT for each PQ Query, and produce a TABLE (Dataset) which will be stores in memory, using its compressed model (VertiPaq):

    RLS does not reach PQ. All data will be in the model. The application that shows the report/dashboard is responsible to filter the data following RLS roles for the user.


    ------------------------------
    Roberto Mirelman -
    roberto@mirelman.com - United Kingdom
    ------------------------------



  • 5.  RE: How to show firmwide data when RLS is in place

    Posted Jan 18, 2021 09:18 AM

    Caite:  The whole purpose of RLS is RESTRICTING data to useres. If you are not concerned of sharing ALL data to ALL users, you should not use RLS, and use measures with filters based on the username() value

    Hope this helps



    ------------------------------
    Roberto Mirelman -
    roberto@mirelman.com - United Kingdom
    ------------------------------



  • 6.  RE: How to show firmwide data when RLS is in place

    Posted Jan 19, 2021 02:21 PM
    Thanks everyone, you've given me a lot to think about.

    I am wondering though - if I were to duplicate a fact table to achieve this, and also restrict the export of underlying data, would that also be a good way to avoid users being able to see what they shouldn't? Or is a summary table really the only way to go? I figured out how to get the summary table working, but some of the results aren't as expected (calculating averages, for example, doesn't work well).

    ------------------------------
    Caite
    ------------------------------