Forum Discussion
Ignore RLS for huge dataset
Hello,
I have Premium License P2.
In my scenario we have 20gb dataset with RLS. Basicly every country has it's own role plus one global role for managers.
I need to build new report based on this dataset which ignores RLS.
Just totals that everyone will be able to see.
Curently there is no way to create aggregated colums in a near future as it would be too complex that's why I need a workaround.
Copying dataset and deleting RLS is also out of question as we don't have remaining 20gb in our capacity.
What could I do?
Is there any way to ignore RLS by removing it in a specific report or creating new measures that ignore them?
My wild idea is to copy a column that our RLS is based on and build new report using this column instead but I'm not sure if it's reliable solution to keep heatlhly security.
Thank you,
Jakub
Hi Cyferki ,
The solution of lbendlin can work for your case , but if you dont want to change the connection to Direct query you can try creating DAX measures to remove RLS.
Steps:
You can first use live connection to import semantic model(data set) from power bi service
Now you can create a DAX measure like below,
TotalSales_AllCountries = CALCULATE([TotalSales], REMOVEFILTERS('Country')).
This will remove filters on country table and calculates total sales .
Now you can publish the report again to the service without RLS.
Alternate Solution:
There is another solution where you can create PAGINATED REPORTS (since you have P2 license) which you can use to bypass RLS and other filters. If you are interested in this solution please reach out.
Hope the above solutions work for you.If it does please Kudos and accept as solution.
Thanks,
Vinay.
4 Replies
Here's a crazy idea, not sure if it will work
- connect to the sematic model
- force a connection change to Direct Query
- remove the RLS reference table from the data model
Give that a try.
lbendlin could you elaborate more?
- connect to the sematic model
do you mean to create new semantic model that is not hosted in PBI? We are using postgresql and import data and we haven't worked on another hosting solution- force a connection change to Direct Query
this one could be and option, I've heard that it's not easy to setup directquery for our postgres but i will try- remove the RLS reference table from the data model
What do you mean? RLS refence table you mean Dim country?- connect to the sematic model
- force a connection change to Direct Query
This is done with the above semantic model, not with your original source
- remove the RLS reference table from the data model
yes, whichever table holds your RLS rules
Hi Cyferki ,
The solution of lbendlin can work for your case , but if you dont want to change the connection to Direct query you can try creating DAX measures to remove RLS.
Steps:
You can first use live connection to import semantic model(data set) from power bi service
Now you can create a DAX measure like below,
TotalSales_AllCountries = CALCULATE([TotalSales], REMOVEFILTERS('Country')).
This will remove filters on country table and calculates total sales .
Now you can publish the report again to the service without RLS.
Alternate Solution:
There is another solution where you can create PAGINATED REPORTS (since you have P2 license) which you can use to bypass RLS and other filters. If you are interested in this solution please reach out.
Hope the above solutions work for you.If it does please Kudos and accept as solution.
Thanks,
Vinay.
