Hi,
For the past 20 days or so, I've been following Service principal profiles for multi-tenancy apps in Power BI Embedded to try and understand if there is some way to make Power BI work with our use case (it's a SaaS product with one database per tenant that's hosted in Azure with 1000+ tenants), and at the moment I am stuck. Let me first describe what I've tried.
Approach 1 - Workspace per tenant, each workspace has its own database and single dataset
This is something that would be ideal for our needs. We would create a report with our test database (also hosted in Azure) and publish it to "Master Workspace". So I created a simple report in Power BI Desktop (Azure DB - DirectQuery) and published it to MasterWorkspace. Embedding works fine, and now I want to programmatically add more workspaces. I've been using Rest API to create a new workspace, add permissions, copy reports and datasets to Tenant1 workspace, and then I called UpdateDatasourcesInGroup to change database from test to tenant1. Everything went fine but when I try to display report I get an error that connection credentials for tenant1 are not set and only way to set them is to go to Power BI service and manually enter them (which I can't do 1000+ times). At the moment I am investigating parameters for server and database and I will see if that might work, but I have a feeling that I'll be presented with an identical issue.
Approach 2 - Single Workspace for all tenants, single reporting database
Not that ideal, but could work if we copy data from all tenants into a single database periodically and add tenant ID to rows. I've been using parameters in my queries for reports and everything seems to work fine from within Power BI Desktop (when I update parameter, data is fetched and displayed) but when I call UpdateParametersInGroup and then try to embed a report I am still "served" with data from test database. Calling RefreshDatasetInGroup after parameter update also didn't display data from tenant1 database (I had to wait some 20 minutes until update was actually registered, and new data was displayed). Is there a way to call report with parameters so that change is visible right away? Use case would be that multiple such calls could be done independently for multiple tenants at the same time. So far I was unable to achieve that. (I actually hit a limit on Refresh after a couple of tries)
Any input on how to integrate Power BI into our use case would be appreciated.
