Switch databases

[Anonymous]

Hello All,

We are using Embedded Power bi lisence and we integrated our power bi reports to angular js application.

We have users who are belongs to multiple tenants.

These user may have access to one or more than one tenant access.

When these users login into application, they can select the tenant if they are part of multiple tenants and if they are just having access to one tenant, that one tenant would be selected by default.

Currently we have the data available in azure sql server and for each tenant we have individual databases created.

The tables which we have used to create power bi reports in these multiple tenant databases will have same schema.

We have created 15 reports and these 15 reports template would be same for all the tenants.

As the templates are same, the database table's schema is also same across all the tenants.

If i put it in table structure, then it will looks like below.

I have created Database parameters in power query and i can edit the parameter in power bi service from one db to another db.

But here, i dont want to create 15 reports for each tenant and publish to power bi service which will lead to 15*600(tenants db) = 9000 datasets.

What are the possible and better ways that i can accomplish this in power bi.

I have tried and explored multiple things like Dynamic M Query Parameters which i can bind to m query of tables in power bi by creating a table of databases column. But i do not see this solution working as the user has to select the value from slicer, but even though i dont think this will work at database level.

I have seen copule of blogs to have power bi report url with parameter filters but couldnt get through.

Any help or suggestions please.

Thanks,

Mohan V.

[vannda21]

Hi @Anonymous, based on what I understand your purpose you want to have the same 600 datasets and want to share with different 15 tenants .
Since Power BI just update a new function to share dataset across the tenants so just enable that option in dataset setting, so you can share it with the different tenant without creating the same dataset

[Anonymous]

@vannda21  thanks for the reply.

But unfortunately, that i not my ask.

Let me try to make it more simpler way.

I have built 15 reports by connecting to one database as of now and lets call it as DB1 and this DB1 is having data of related to TENANT1. TENANT means, lets call it as a client but not the tenant we have in power bi.

So for each client we have individual databases been built in my azure sql server.

like CLIENT1 --> DB1, CLIENT2 --> DB2, CLIENT3 --> DB3 .......CLIENT600 --> DB600

All these databases has 4 dim tables and 1 fact table which has same schema.

So now, as i have already built 15 different reports like, REPORT1, REPORT2 ---- REPORT15 with different requirements by connecting to DB1 as source.

I have published these reports to power bi service as well, and all these reports are pointing to DB1 of CLIENT1 from my azure server.

for all the other clients, these 15 reports will be having same template. Means same calculations, visuals, filters, modelling, but it is just the database for that client should change.

I have created Parameter for Database, now i can go to power bi service settings --> Parameters and i can manually change that parameter value from DB1 to DB2 or DB3..etc. But this change of parameter is can be done only through the user or developer who has that access to edit.

lets call USER10 when logged into power bi service, he should see the data of these reports for DB10, and he is just an viewer, that means he cant go to settings and change the parameter to DB10 right.

Now I am looking for a solution where i cam make that parameter change automatically based on RLS or Dynamic M Query parameters or Report url parameters filters.

Because for these 15 reports, already 15 datasets been built in power bi service.

If i have to built reports for other clients with their specific database as source, means i will end up having 600clients*15reports*15datasets.

[Anonymous]

anyone has any thoughts on this.

Thanks,

Mohan V.,