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.
We are currently evaluating the Power BI embedded platform in the context of a cloud based solution we offered to our clients. Our software is multi-tenant and to achieve this one we use database schema based isolation. This means that many tenants share one database and each tenant has a dedicated schema as you can see in the picture on the left.
For every tenant we have a dedicated workspace inside a single workspace collection. Our workflow is to design the reports against the dbo schema and then upload the pbix file in all the workpspaces in order to make it available for all the tenants. So far we haven't found a way to update the report's datasource to point in the correct schema. As far as I know there is the option to update the connection string via the rest api after you upload the report but there you can't set the schema.
On top of that we also tried to create a database user for each tenant and assing this user to only access this schema. Then when we updated the datasource we set as credentials the one from the database user, hopping that the database would point to the report to the correct schema but this didn't work either.
Does anyone has faced something similar or have any idea how we could bypass this issue?
I was looking at a very similar usecase for myself. I found this link https://docs.microsoft.com/en-us/azure/power-bi-embedded/power-bi-embedded-iframe, which explains how you can change the datasource config after the report is uploaded into the workspace. See the section "Data source connectivity (and multi-tenancy of data)".
Hope this helps.
Thanks for the reply @varshaneelesh
We know about the change datasource functionality but it's limited when it comes to change the schema. For example you can design a report against "dbo" schema and then once uploaded to change the datasource to point to "tenantx" schema.
In any case we chose to use elastic pool and have one database per tenant which makes the entire set up much easier.
Hi all -
has this issue / feature been addressed in the meanwhile? I am trying to find out how to use Power BI Embedded with a single workspace to access a single database using multiple schemas (for multi-tenancy).
Thanks!
Hello @sjungels
I don't think something has changed regarding this one.
As mentioned above we finally used a separate dataase per tenant in the context of an elastic pool which is much easier to understand and maintain.
Hi,
we are facing the exact same issue. We have addressed this to Microsoft.
So far we only received (non relevant) RLS tips. We have rephrased the question and submitted it again a couple of days ago.
No reaction so far. I will post the reaction as soon as we receive one.
Hi @lukaszp, thanks for your reply. The way you describe works indeed, but only when you use written out SQL statements in the data sources without typing the schema name. When a table or view is selected in the data source wizard, its schema name is also included in the data source. When I remove the schema from the source in the advanced editor and I save the report as a pbix file, the schema name seems to remain applied. I notice this in two ways:
When I publish the pbix file in a workspace with a sql user which has access to the specific schema (same tenant), it works. When I publish the pbix file in a wokspace with a sql user which has access to a different schema (another tenant), it doesn't.
The other way is when I open the pbix file in a text editor, I can see the schema name in a number of lines (between lots of unreadable characters).
RLS has nothing to do with this, the question is how can I design a schema independent report on a database with schema based tenant isolation, without using written out SQL statements?
Hi All, We also are facing the same issue, as rightly said by @TvB changing user credentials, removing schema definition from advanced query editor nothing works as of now.
Just a suggestion if this works for you (please check the secuity aspect) you can use sql level filtering on the basis of some parameters while designing the report to see data relevant to a tenant and then disable the filters and visualization options from JavaScript while rendering the embedded report.
Hi,
we are facing the exact same issue. We have addressed this to Microsoft.
So far we only received (non relevant) RLS tips. We have rephrased the question and submitted it again a couple of days ago.
No reaction so far. I will post the reaction as soon as we receive one.
Hi @xabikos,
In this scenario, I would suggest you to create a new schema to put all data into it, and design reports against this schema, then use Row level security with Power BI to restrict user access to particular data within a report or dataset, allowing for multiple different users to use the same report while all seeing different data.
Reference:
Row level security with Power BI Embedded
Row-level security (RLS) with Power BI
Regards
Hello @v-ljerr-msft and thanks a lot for the answer.
Maybe the question was not entirely clear but for our use case Row Level Security is not a option for us, at least for now. Maybe in the end we will be forced to do that but we have strong doubts about this solution, not only for data isolation but for scaling reasons as well.
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.