cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Ctijsseling Regular Visitor
Regular Visitor

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:

  1. 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.
  2. 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.

5 REPLIES 5
Microsoft v-yulgu-msft
Microsoft

Re: How to use SSAS with Dynamic RLS as a data source for PBRS?

Hi @Ctijsseling,

 

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".

1.PNG

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ctijsseling Regular Visitor
Regular Visitor

Re: How to use SSAS with Dynamic RLS as a data source for PBRS?

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?

vmn0620 Frequent Visitor
Frequent Visitor

Re: How to use SSAS with Dynamic RLS as a data source for PBRS?

@Ctijsseling did you find a solution for this?

Ctijsseling Regular Visitor
Regular Visitor

Re: How to use SSAS with Dynamic RLS as a data source for PBRS?

@vmn0620: Nope. I have contacted directly several members of Microsoft and noone knows to achieve that. They assume that Report Server Power BI recognises the roles created in SSAS when actually it doesn't. Quite frustrating to be frank.Smiley Frustrated You can get a SSRS report on a PBRS to recognise the roles (using the connection string keyword "role") or even Excel to recognise it, but not Power BI. 

BIrs Frequent Visitor
Frequent Visitor

Re: How to use SSAS with Dynamic RLS as a data source for PBRS?

Has this resolved in the new version of PowerBI? Can PowerBI recognise the SSAS Roles?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!