Dynamic Data Source based on dynamic parameter from login user session

[malikirfan28]

Hi All,

We have mulitple clients with separate copy of exactly same database schema on same SQL Server. I know how to create parameterized query within Power BI Desktop and then build separate copy of dashboard for each client using their database and upload all pbix files to power bi service (app.powerbi.com).

Please note, we are using these dashboards in our custom application (as embedded) so we show specific dashboards only to respective clients/users.

However as clients are increasing so it is hectic to keep uploading multiple files even dashboard is exactly same (using exact same visuals) and if we have to change any visual then we will have to update (re-build) all copies of dashboards and re-upload.

So is there anyway that we upload only single Dashboard (pbix) file to Power BI Service which will be using direct query via Gateway and then pass those Query Parameters (like Database name) dynamcially from Custom application to Gateway (based on user session) and Gateway fetch data from given database name (instead of static connection string)?

Please need an expert openion.

[GilbertQ]

Hi there

What I would suggest doing is to have all the data in one SQL Server table or ideally build an SSAS or AAS tabular model. You can then have all the reports accessing this tabular model.

You can then configure Row Level Security and based on which user logs in, you can then only show them their data.
This will allow you to only create 1 dashboard and 1 report.

[malikirfan28]

Hi @GilbertQ ,

Thank you for your message. How can we use this RLS with custom application? As mentioned before, we have App Workspace in Power BI Service through which we are embedding dashboards into our Custom Applications (ASP.Net MVC). So how can we pass this Role information from Custom Application to Dashboard?

2nd: I believe, RLS feature will work if Dashboard is using Direct Query. Right? 

3rd: These dashboards will be readonly (we manage it through our custom application) however our App Workspace is configure to allow end users for Edit/Update dashboards (not these but other type of dashboards). So will it have any implication?

Thanks 

[GilbertQ · Accepted solution]

Hi there

Please find below a link on how to use it with Power BI Embedded
https://docs.microsoft.com/en-us/power-bi/developer/embedded-row-level-security

2. It could work with DirectQuery if you are passing it down to a data source which supports single Sign On

3. Currently if a user is a member of an App Workspace RLS does not apply.

[malikirfan28]

Hi @GilbertQ ,

Thank you for your support.

However I couldn't understand your point 3 reply. Can you please give some detail. 

Thank you.

[GilbertQ]

Hi there

Currently people who are a member of an App Workspace do not have RLS applied to them when they view the reports or dashboards.

[malikirfan28]

Ok, that's not our case because our custom application users are not added in App Workspace. Thank you for clarificaton. 

I have also implemented your suggested solution and it seems working fine. Just one last point, if we pass Roles information under GenerateToken Request for a dashboard that doesn't enabled RLS, then this throw error 

Exception Details: Microsoft.Rest.HttpOperationException: Operation returned an invalid status code 'BadRequest'

Source Error:

Line 81:                 generateTokenRequestParameters = new GenerateTokenRequest("View", null, identities: new List<EffectiveIdentity> { new EffectiveIdentity(username: rlfUserId, roles: new List<string> { "RLS" }, datasets: new List<string> { datasetId }) });
Line 82:             
Line 83:             var token = await client.Reports.GenerateTokenInGroupAsync(wsId, repId, generateTokenRequestParameters);

So does it mean we can only pass "identities" if dashboard has enabled RLS? If yes then is there any better way to find this, before loading dashboard? We can used hard-coded dashboard names in condition at our side which have RLS enabled and then create token but it will become problem to update application everytime whenever we will have new RLS based dashbord. So is there any (automatic) bettter & professional way? 

Thank you

[GilbertQ]

Hi there

I am not sure how it would be passed through from your custom application. Unfortunately I do not have the experience with power BI embedded.

Hopefully this document will assist you: https://docs.microsoft.com/en-us/power-bi/developer/embedded-row-level-security