Database per customer for embedded.

[oakenpride]

Hey everyone,

I'm working on trying to use Power BI embedded into our app but running into a little roadblock dealing with multi-tenancy. The way our SQL db is set up is a database per customer style. So we have one server and in it we'll have multiple databases each pertaining to a customer/client of ours. I've tried all possible ways to handle multi-tenancy with DirectQuery in PowerBI Embedded and these are the conclusions I've came up with. 

1) PowerBI Row Level Security - I do not believe that this can be applied in my case since the data resides in multiple databases. The dataset cannot span across multiple dbs as far as I know. I haven't seen anything that states otherwise. 

2) Using SetAllConnections - During my attempt to use this method I've notice it doesn't always update or refresh the connectionstring. This also seems to update the underlying dataset in PowerBI which may possibly trigger race conditions. For example if Session A updates DataSet A and Session B updates DataSet A and both sessions ran at the same time, whoever updates last will be the only session that is garanteed to have the right report. This was my observation during my demo. Please let me know if I'm totally wrong.

3) Dataset per Customer - This is the only one I'm getting consistent results for. Essentially I create a dataset for every customer and point the underlying datasource/connection string to the customer's database. The only problem I ran into with this is the creation of datasets. There seems to be no easy underlying way to create a new dataset. I had to manually upload a pbix for each customer in the demo to create the dataset.

#3 seems to be yielding the best results so far. Are there better ways to approach this? and am I going about this the right way. Any help here would really be appreciated. 

Thanks,

-Oak

[TedPattison · Accepted solution]

From what you said, I assume you are using Power BI Embedded and not using embedded reports in PowerBI.com. I also assume that database schema will be the same across all the different customer databases.

Yes, I would use the one PBIX project per customer approach. If the only things that changes between custom projects is the connection string and login info, you might be able to automated the whole process of adding a new customer.

1) Create a new Power BI workspace in your workspace collection in Azure

2) Upload a copy of the master PBIX file to this new Power BI workspace Azure 

3) Patch the database connection string and the data source credentials

These steps can be automated using either C# or PowerShell. You will also have to figure out how to get the web app with embedded reports to load the correct reports for the current customer. This will be done by tracking the GUID for the Power BI workspace that is specific to that customer.

[oakenpride]

Got it. Glad to know I'm on the right track. You seem to be suggestion a workspace per customer. Are there advantages going this route? I was originally going to do dataset per customer and just wanted to know pros and cons here. 

[TedPattison]

Probably not that big a deal - but if you go with a single workspace for all customers, you must come up with a unique string name for each customer dataset. If you go with a seperate workspace per customer, you have to create a new workspace for each new customer but you can use the same dataset name in each workspace.

Seems like 36 of one vs 6 half dozen of the other.