Forum Discussion
SaaS and Multi-Tenancy combined with Direct Query against Oracle in Power BI Embedded report
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
5 Replies
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
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
