Hi,
We have a report that we deploy to our Power BI embedded service. We deploy a report for each customer. That report has the same underlying data model. The report leverages direct query to access data.
Our reporting database is a multi-tenant database. We use row level security in the SQL Server. On the SQL Database the row level security works exactly as I expect it to.
The issue: In Power BI we setup a report for each customer that connects to this report database however each customer has it's own SQL User and password. The SQL username and password is set in the dataset. In Power BI, when I deploy a PBIX for Acme Co, I see their data on their report. When I deploy the PBIX for Fabricam, I see Acme Co's data in Fabricam's report.
Based on my troubleshooting, what I think is happenening is this. Even thought the PBIX files are separte, and they even have a seperate dataset, Power BI is sharing the data cache due to the database server and database being the same, even though the SQL username and password are different.
Has anyone else encountered this, and if so how did you work around it?
We are looking to implement Azure AD however that won't go live for us for at least a half year. Once we have that implemented we'll shift the row level security to either SSAS or Power BI which will eliminate the need for a PBIX file for each customer.
