SaaS and Multi-Tenancy combined with Direct Query against Oracle in Power BI Embedded report

[Anonymous]

Hi, 

We are developing a large SaaS application with the multi tenant, single database pattern (Oracle). We have a tenant-id column in all database tables, which are separating the tenants.

How is it possible to filter data based on the tenant id in the reports in an embedded Power BI report using Direct Query mode? Can I dynamicly combine RLS with the tenant-id in some way?

Regards from Norway

[v-micsh-msft]

Could you please explain a bit for the tenant ID you used here?

If it is the domain name, then it should be much easier. We could take use of the Username() function and get the domain suffix, then use it to filter the table.

Take a look at the following article:

Use Row-level security with Power BI embeded content

(Please pay more attention to the Considerations and limitations part.)

If it is the GUID of the tenant, then you may need to do some mapping (tenant ID to Domain name ), in order to get the RLS to work.

Regards,

Michael

[Anonymous]

Hi, and thanks for the respond!

Unfortunately the tenant-id is a unique number. Technically it is's comming from an Oracle Sequense that gives all the primary keys (tenant-id) a unique value in the rows (and the foreign keys, which will be the filter columns).

We have over 1000 tenants in the database. With RLS we then need to create a role for each tenant? It seems that it will be much manuel work to maintain.

Is this the only way to filter rows?

Reagrds

Aslak

[GraemeSMiller]

This video from Reza Rad covers some advanced RLS topics that will probably help you https://www.youtube.com/watch?v=LcjIU0oOg0s&feature=youtu.be 

You would probably need to create Role of Tenant - then dynamically assign users to that role. So the Roel can see only data from one tenant - and that filters down to all your tables.  You would then need a way of mapping some user to some tenant. It would be similar to the example of assigning RLS based on their manager - in an org chart. Take user and look up tenant id

You can use USERPRINCIPALNAME() to access the currently viewing username and then filter https://powerbi.microsoft.com/en-us/blog/using-username-in-dax-with-row-level-security/

[Anonymous · Accepted solution]

Thanks a lot! 

Very useful topics for me! 🙂 I will dig into this!

[v-micsh-msft]

Thanks for the explanation, specially for the tenant part.

>>With RLS we then need to create a role for each tenant?

If you would like to filter rows by the tenant ID, then yes, you may need to create a role for each tenant.

>>Is this the only way to filter rows?

That mostly depends on what you would like to filter with.

You may also take use of Power BI Javascript SetFilter function:

See:

Filters

https://github.com/Microsoft/PowerBI-JavaScript/wiki/Filters

Regards,

Michael