Switch dynamically between the databases depending on user credentials

[Anonymous]

Hello everyone,

I have 2 databases in SQL Server with exact same schema. I have a report in Power BI that will be shared online with 2 users (with power BI licenses). Each database relates to a user. How can I make the report switch dynamically between the databases depending on which user is logged in?

I've explored RLS but it is not feasible to combine tables from the two databases since they have millions of rows. I would prefer to keep them independent.  I've also explored the option of using parameters that defines the database name  but that still implies manual action of switching to the relevant parameter for each data source.

Any ideas would be much appreciated.

 

Adrian

[v-yiruan-msft]

Hi @Anonymous ,

Why not create two separate reports based on these two databases? For example, if you have databases ds1 and ds2, you can first connect to ds1 to create report r1, then copy the r1 report file, just change the data source information (because you mentioned that the two data sources have the same schema) and then save it as r2. Then upload reports r1 and r2 to the service and share them to user 1 and user 2 respectively.

Best Regards

[Anonymous]

Hi @v-yiruan-msft,
I would like to have only one report because the number of users will increase overtime and having a duplicate report+data source is not feasible. I guess this is not something that can be done in power bi but perhaps azure?

Anyway thank you for looking at this.

Best

Adrian

[Anonymous]

Hi @Daryl-Lynch-Bzy 
The users are not technical. They are just accessing/reading the report and the reason I'm looking for a dynamic solution is because the number of users will increase over time, thus need to look for a scalable solution where I don't have to duplicate the same report for each user. Is there any other way I can look at this?

[Daryl-Lynch-Bzy]

Hi @Anonymous , I don't think this is possible.  Power BI would be able to select between the two databases based on current user.  This sounds like a Direct Query scenario.  Please consider the following:

• If there are only two users, why not just give them access to the Server SQL directly.  They can fill their boots and even use Power BI if they want to.
• Consider creating a Template that you can publish to respective user and have them enter the Connection Details and credentials to access the Data Model.

Otherwise, you need manage 2 workspaces, 2 datasets and 2 reports.