How to stop sharing datasources across app workspaces

[michaelb]

We are migrating from the old PowerBI Embedded to the new Power BI Pro.

We have the data in a SQL Azure database and then the data is exposed based on the user that is logged in.

Previously we accomplished this by creating a workspace collection for each user uploading a copy of the pbix file and setting the connection parameters to the appropriate user. This has been working fine.

Our understanding is that in the new PowerBI Pro, we should be creating a group, or App Workspace, for each user. These should then be isolated from each other. Our experience has been that when we set the username and password on the connection it impacts the report in all workspaces.

After looking at the reports in each of the groups, the datasets are different for each report but the datasource and gateway datasource are shared across the workspaces for the uploaded reports.

The question is, how do we make a new datasource/gateway datasource for each app workspace?

Thanks.

[Anonymous]

HI @michaelb,

I'm not so clarify for your description, can you explain more detail?

For group/app workspace, it means one group per user or one group multiple users?

>>how do we make a new datasource/gateway datasource for each app workspace?

If it means one group one user, you can remove your gateway sharing, then user need to configh this datasource on his gateway.

If it means one group with many users, the report will use report owner's gateway to handling refresh operation.(for this scenario, you can't use multiple gateways to manage one report)

Regards,

Xiaoxin Sheng

[michaelb]

Hi Xiaoxin.

Something I forgot to mention is that the report is embedded in a web front end that is presented to our users, but depending on which group the users belong to I want the contents of the report to be appropriate for them.

From what I have been able to work out, I need an app workspace for each group of users with the same report deployed to each workspace with the different database credentials set for the report in each workspace.

Then I need to display the report from the applicable workspace.

I am uploading the pbix file as the one user to the various workspaces, is that why they all have the same gateway?

Thanks,

Michael

[Anonymous · Accepted solution]

Hi @michaelb,

Current power bi not contains function to get group id and use them as filter parameter.

For your scenario, I think dynamic RLS based on username will suitable for your requirement.

You only need to add one table to mapping username and group and confirm these group has relationship to other table to enable filter effect.

RLS with UserName()

After you finish above steps, publish to web service setting roles and add users to gateway, RLS filter will base on username and related group to filter on records.

Regards,

Xiaoxin Sheng

[michaelb]

Hi Xiaoxin,

Thank you for you reply. We looked into the RLS a while back and found the performance to be a issue as the report loads all the data then filters it in the report. We got better performance by doing it at the database level. That is, we get the report to log in as the separate users and only get it to return the appropriate rows.

We are investigating other options.

Happy to leave this as the accepted solution.

Regards,

Michael