Change reports data source connection from PROD to UAT based on User

[johnelmasry77]

Dear All,

I have 100+ dashboards within my power bi service, they are all connected to Production DB.

The requirement is to have all these reports connected also to UAT DB, and the change from PROD to UAT should happen dynamically according to the user viewing the dashboards.

For example User A will Access all this 100 dashboards with PROD DB

While User B will Access all this 100 dashbaords with UAT DB

P.S: it will be impossible to replicate all this 100 dashboards again with a different connection.

P.S.: I have read about making the data source as parameter to change from Power BI Service, but I couldn't find a way to change it dynamically based on the user.

So, Is there any workaround for this challenge?

Regards,

[ibarrau]

Hi. I'm sorry but there is no solution exactly like you are thinking it. AS I'm seeing the issue there are two alternatives for this:

- Replicating workspaces. For each environment keep a workspace, if  you have a Sales Production workspace, then you need a Sales Production UAT. That way you can distribute the reports for the correct users. Then you can use deployment pipelines or power bi rest api to orchestrate the flow in a way that make sense for you.

- Load data from both databases in a single data model (only possible if it's not a lot of data). If you have a customers table. You could append both databases data in a single table with a column "prod" and "uat". Then RLS can help you filter rows depending on the viewer of the report. This would be dynamic, but it can be confusing for an admin view that has both the tables appended together when seeing data. Remember that it won't work if it's a ton of data (hundred of millions).

The parameter you have seen will let you change something before the refresh of a dataset. It can help as a development perspective. For example, thinking in the first scenario, you only keep one power bi desktop file with parameter as datasource. When you publish to UAT you write the connection for UAT and then refresh. Once it's validated you could publish to PROD, then open the service parameter to write the prod connection.

The parameters won't be dynamic depending on the user asking for the report. It doesn't work that way.

I hope that helps,

[johnelmasry77]

Thank you so much for your response,

I already investigated this two options, none of them will be sufficient for our requirement because

In First Solution: we will have to replicate 100+ dashboards, and if for change in each we will have to change twice

In second Soltuion: dataset is huge around 700 MB, so doubling it is not an option for a pro user.

I was working on an option but still didn't make it, which is IF condition evaluating the user accessing in the powerquery, and based on it, writing M query to choose the corresponding DB connection.

Regards,

John