Power BI Embedded and multitenant support

[Ermin]

Hi all,

We’re are developing a multitenant application with a set of Power BI reports where our clients would be able to see only their data and KPIs relevant to them. Additionally they would be able to compare they KPIs to the industry average KPIs for all other companies. i.e. How my company’s margin is doing in comparison to the one measure which is average margin for all others similar companies. We  calculate average of my company data and then in a Power BI visual compare it to a measure which is calculating an average of average for all companies. (so the measure calculation should be able to see all the data not only RLS limitted data)

Initially my approach was row level security (RLS) but that didn’t work because the measures can see only my company data and it doesn’t bring back the average of all other companies. I have stuck now with trying to create a slicer which will be hidden and set to a default value depending on the user logging. Which is still not working and it doesn’t sound as the right approach.

If any of you had a similar problem and managed to solve it or has a good idea how to resolve this, I would really appreciate

Thanks, in Advance!

[Greg_Deckler · Accepted solution]

What I would recommend would be to do another import of your data but summarize the data in Power Query such that all of the details are removed and then base your measure calculation (if needed) on that table. So, for example, you could do a "Group By" in Power Query to remove row level detail. Then, give everyone access to that table in RLS, or portions of the table that are relevant. For example, if you summarize by industry and only want them to see the measure calculated by their particular industry.

[Anonymous]

Thanks for this suggestion @Greg_Deckler. I'd got to the point of realizing that RLS "pre"-filters the data before the ALL function in my DAX expressions even applies, but didn't have a solution. Your idea worked fine for my use case: RLS roles ensure that our users only see performance data for their specific states and sites (using on Security Groups and UserPrincipalName()), but we want to show "program-wide" values so they can compare performance in their area to program-wide averages. Obviously ALL wasn't working because the rest of the program was (rightly) filtered out for them.

[Ermin]

Hi guys,

 A short update from my side. Based on the solution proposal from @smoupre I think I have a good enough implementation.

I ended up creating an additional sub model of pre-aggregated data. The Power BI “GROUP BY”   functionality in Query Editor helped a lot.

The solution for me was:

1. Use RLS to limit the access only to the user company data
2. Determine measures/values that a user would need to see aggregated for all other companies (tenants)
3. Determine dimensions (slicer values or categories in the visual) we need to see for all companies
4. Use “Group By “ function to create additional data sources containing aggregations of measures across dimensions calculated below.
5. At the end you get a sub-model of tables that are not collected to your main power bi model
6. Enjoy playing with a safe way to compare your company data against aggregated others J

Note: Depending on the amount of data the “Group by” functionality tends to be extremely slow. I ended up creating views/ queries in the database which delivers aggregated data so that I don’t need to use Group By. Group BY in power BI is useful to test the approach or if you don’t have access to the database or if your data source is not a database.

@smoupre, thanks for the idea.

[Anonymous]

Thanks for your update @Ermin. I will try the same approach, but the hard thing for us is that almost every visual has a comparison version. Instead of aggregating, I might have to copy the whole model twice and add RLS to one, while the copy will be used for comparison.

[Ermin]

@Anonymous,

this may be even an easier approach than the one I 'have chosen, since you just need to duplicate your data sources. However, I didn’t go that way because it seemed to be such a waste of storage to me.  

Additionally, I would expect to gain on performance when dealing with the pre-aggregated values.  

However, approach you selected is easier to implement and more flexible in case you need to add new comparison measure or dimension later.

It would be interesting to get your feedback after you gain some expirience with your approach. (Issues you had, workarounds, do and don’ts, pitfalls to be aware of, etc…)

[Ermin]

First of all thanks on a fast response @Greg_Deckler.

This is an interesting approach. I need to figure out how this may work in my case, since I’m not talking about one data source but multiple sources. However, there is definitely a potential to do something in that direction.

I was in the meantime testing another approach. I didn’t not use RLS but rather a hidden slicer which will then make sure that a user sees only his data. The measures would still work the across the  complete data set.  

The problem there is:

1. How to hide a slicer in power BI?
2. How to set it to a default company depending on the user using the dashboard?

Don’t forget we’re talking about application containing power bi embedded.

It’s hard to believe that no one out there had a similar problem. Multi—tenancy should be a quite common scenario.  

[Anonymous]

I just posted the same question HERE

We are having the same issue. 

@Greg_Deckler I think we need a more general solution to this. @Ermin you can use visual level filter instead of slicer AND instead of row level security, but I am not sure if this is completely secure. @Greg_Deckler is it possible to change the visual level filter thourgh the JavaScript API? If it is, then anyone can get the isntance of the embedded component and change the visual filter. Or any other filter. We have very strict data access policy. (BTW the export data from the embedded report is another issue for us). 

I think row level security is the only secure approach.