Forum Discussion
How to use SSAS with Dynamic RLS as a data source for PBRS?
Please could you tell me how to use SSAS Tabular 2016 with Dynamic RLS as a data source for PBRS?
I have more than a thousand of users with their logins and respective security level in a table. Each users have acess to a specific numbers of files in the database and are not allowed to see each others case files (GDPR/Data protection rules). In the meantime we also have people that manage other people and that can look at their subordinate cases.
In the past, we solved this problem by using SSRS: we would use SQL statement that would include the Userlogin as a parameter to filter a security table.
We would like to reproduce this model using Power BI reporting server.
I have tried several solutions:
- Direct Query with a SQL statement linking to my security table and a where clause (WHERE 'RJW_AD\'+ADUserName =SYSTEM_USER). The issue is that I have too much data and refreshing the data is going to take too much time.
- SSAS Tabular + RLS. The issue is two fold: 1) I don't see myself spending my days just amending the Tabular model to add/remove AD users, 2) I don't know how to connect to SSAS while using the roles in PBRS.
The last solution, would be my prefered if the 2 issues described above could be avoided.
6 Replies
v-yulgu-msftMicrosoft Employee
Hi Anonymous,
Please refer to jtarquino's reply in this thread. After configuring RLS on resource side, and publishing reports to Power BI report server, set the data source credential to "As the user viewing the report".
Regards,
Yuliana Gu
Hi v-yulgu-msft,
Thank you for answering me. I have already done these steps. I wrote before, Direct Query to a SQL statement is not a viable solution because of the amount of the data. I always use the "As the user viewing the report". But for SSAS, this option doesn't filters the rows according to the role included into SSAS Tabular. I was wondering if there is a keyword that I need to use in the connection string to let the SSAS Tabular knows that I want to use the role that I have created?
Lets say that you have created a SSAS Tabular (let's call it a "cube") with Role1 that filter using a certain logic and Role2 taht filters using a different logic. How would you tell Power BI to select Role1 for example? Am I misunderstanding something?
Anonymous did you find a solution for this?
