Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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/
Thanks a lot!
Very useful topics for me! 🙂 I will dig into this!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
25 | |
3 | |
2 | |
2 | |
2 |