Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Need Advise on applying Dynamic RLS on Power BI dataset which is converted from SSAS tabular cube

Hi Community,

 

Backgroud : 

Currently in our organization we are moving our reporting solution of SSAS tabular cubes  to Power BI datasets. 

This SSAS cube has a complex dynamic RLS with different security roles on multiple dimension tables like region etc.. and already using the Dyamic RLS method with Login ID (username()) fuction.

 

1st Part of the question: 

For this we have taken below actions:

                 1. Converted our existing SSAS tabular cubes to a power BI dataset using the Tabular editor tool and also deployed the Role members data.

                 2. After the putting this dataset on cloud with multiple users login in, we learnt that we require

USERPRINCIPALNAME() data as well in our dataset to have RLS to work with POWER BI and SSAS server and we are now in process to validate it. 
               3. We will validate and make sure the Cloud settings are in sync with RLS requirement for Power BI service. 
 
Question on this, will our RLS work ? or the approach we have followed is incorrect? If any correction please let me know which can be done differently ?
 
2nd Part to this question:- 
             1. There's another way to do the what we had in the 1st part of the question :
A. From Power BI desktop connect to the data source and import the data and create fresh RLS roles and Publish the empty report to have Power BI data set created
However, the challenge with this method that the data we have in the Cube is 11GB and We cannot Import this volume of Data in Power BI desktop and also, we can do a Direct Query but cannot apply Security roles as this field is disabled while direct Query
 
Question: What method we should use to apply RLS on data with higher volume data and get it working in Power BI service 
 
I understand it would be a broad or vague question, but a summarized steps to achieve our goal would be greatly appreciated. 
 
Thank you 🙂 
 
 
 

 

 

Need Help

2 Replies

  • jaweher899's avatar
    jaweher899
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    1st Part of the Question:

    It sounds like you're taking the right steps to convert your existing SSAS tabular cubes to a Power BI dataset, and including the USERPRINCIPALNAME() data is indeed a requirement for RLS to work in Power BI. However, it's possible that there may be other steps that need to be taken in order to ensure that the RLS rules are working correctly in Power BI. I would recommend double-checking that the Cloud settings are correctly set up for RLS in Power BI, and also reviewing the documentation for setting up RLS in Power BI to ensure that all necessary steps are taken.

    2nd Part of the Question:

    Regarding the challenge of applying RLS to a large volume of data, there are a few potential solutions that you can consider:

    1. Data Sampling: You can import a sample of the data into Power BI Desktop and apply RLS on it, then test the security roles in the Power BI Service. If the RLS rules are working as expected, you can then apply the same roles to the full dataset.

    2. DirectQuery with Parametrized RLS: Another option is to use DirectQuery and parametrized RLS. This allows you to pass the user information to the data source to perform the RLS calculation, instead of performing the calculation within Power BI.

    3. Power BI Premium: If you have a large volume of data and need to apply RLS, you may consider using Power BI Premium. Power BI Premium provides more resources for data processing and allows for DirectQuery with RLS on large datasets.

    I hope these suggestions help you achieve your goal of applying RLS to your Power BI dataset.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks jaweher899 for the prompt response. 

       

      For the 1st part of the question you mentioned "Cloud settings are correctly set up for RLS in Power BI" is there any link,blog or video so that I can go over once ?

       

      Also, for the 2nd part of the question, 1. Data Sampling and Direct Query with Parametrized RLS looks promising by reading through- for these items is there "How to do" documents,blog or videos that you can recommend ?

       

      This will be really helpful

       

      Thank you for your advice and thoughts