Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

RLS Rules on two dimensions for one fact table

Hi there PBI community,

I'm currently looking into using RLS to filter a fact table on product flows for customers. The problem I'm currently having is the following:

The fact table (zFlowETMShippingQuery) consists of a sender and a recipient of the products. A customer needs to be able to see both what he receives and sends out. When I implement RLS however, both filters interfere with eachother.


This is the model that I'm using and below are the RLS rules that I'm using.


For context, Users is a copy of Web Portal User, both RecipientActorTo and SenderActorFrom are DISTINCT extractions from the fact table.

If someone could help me out, I'd be very grateful :smileyvery-happy:




Need Help

3 Replies

  • Thejeswar's avatar
    Thejeswar
    Icon for Super User rankSuper User
    8 years ago

    Hi Anonymous,

     

    In this case, if my guess is right, you are filtering on the same column in the same fact table.

     

    Have two separate fact tables, One for ReceivedFrom data and One for SendTo data and join these tables with security table to filter them. When you are using same fact table for two different security, it more like to not work the way they are expected to.

     

    The Reason is when one security filter is applied you data set will get filtered. Then how will the other security filter work properly??? It might not give you the desired result set.

     

    So you should have two fact tables. one for driving Received from and one for driving Send to

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      HI Thejeswar,

      Thanks for your reply. Duplicating the fact table could solve the issue I'm having. There are two things I'm concerned about if I do that:

       

      1. Can I use both fact tables in a single table visual?
      2. How will duplicating the fact table impact the performance/size of the report?

      My fact table can potentially become quite large due to the facts being daily stock flows of every customer.

      Thanks again!

      • Thejeswar's avatar
        Thejeswar
        Icon for Super User rankSuper User
        8 years ago

        Anonymous,

         

        For your 1st Question, No you cannot use the data from both fact tables in the same visual. This will again break your security if at all you are able to use them together

         

        2. Yes, Duplicating the tables might result in size/ performace overhead

         

        How about trying the approach I am stating below

         

        Just have 2 different columns in your fact table on which you are applying security. say ReceivedFrom and SendTo as 2 separate columns in your fact table

        Join each of the column with the corresponding security table.

         

        This should work, but here again using data from two fact tables in the same visual will break your security (might not give the desired result)

         

        Regards,

        Thejeswar