RLS with Power BI Embedded and SSAS Multidimensional

[rebecka]

Hi,

We're trying to do a POC with an web-app that will embed Power BI reports. When a user signs in, we want them to only be able to view the data that belongs to their organization. As a source we have a cube built with SQL Server Analysis Services Multidimensional. Embedding the reports are no problem. The problem is passing which user/organization is accessing the embedded report down to SSAS. I've found some posts about how to solve this with Tabular, but can't solve it for Multidimensional och everywhere I read I just see that this should be solved in the source, but not how.

Can someone explain this to me?

[rebecka · Accepted solution]

Found the solution by myself at last...

If someone else is intrerested:

Setup a role in the Multidimensional database in SSAS. Choose 'Read definition' and 'Read' access on the cube of interest. Under dimension data chose the CUBE DIMENSION which you want to filter on. Pick the attribute of interest and under 'Advanced' and 'Allowed member set': type the following expression:

STRTOMEMBER('[Organization].[Organization Id].&[{'+CUSTOMDATA()+'}]')

and of course replace Organization and Organization Id with your dimension attributes.

And chose Enable Visual Totals if you don't want each organization to know the total in the measures.

[devbv]

Have you seen this blog post - https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-embedded-rls/ ? It might help.

[rebecka]

Hi,

Yes, I've seen that. But if I understand it correctly it means that I should put up one role per customer? And I have around 1000 potential organizations as customers, and setting up one role per organization doesn't make sense. Isn't it possible to this dynamical in some way?

[devbv]

Its not quite my area of expertise, so can't be precise. However, if you explore solutions to implementing cell level security on SSAS, they may help. I think the idea is that you would run your queries against SSAS in the context of the user accessing the reports (using an on-premise data gateway and Live Analysis Services Connection option). Within SSAS you will pick up the current user (from the context) and map to the appropriate set of data. There will require that there is already some sort of bootstrapping in place, i.e., the users are already AD users in the SSAS domain.