Passing user parameter from dashboard to SQL server through SSAS

[rohan_misra]

Hi,

We have created a Power BI dashboard hosted on an On-Premise Report Server. We are using the June 2017 GA version of the Report Server and hence can only use a Live SSAS connection for publishing the dashboard, with the SSAS model connected to an SQL server with the DirectQuery mode on.

Next, we have to filter the incoming data from the SQL server for the user that has logged-in to view the report. Now since the user-list is relatively large and ever changing, we would ideally want to stay away from using RLS as manual addition of each user into a member group would be a big challenge to maintain moving forward.

Question: Is there a way we can pass the user login info with the USERNAME() function over to the query fetching the data from the SQL server into the SSAS model - something like SELECT * FROM [dbo].[Table] WHERE [User]=USERNAME()? Any other suggestions to help achieve the same?

Thanks in advance.

[malagari]

You can use roles in the SSAS Tabular Model to achieve this.  You'll need to create a role with a DAX Table Filter that uses the USERNAME function.  Then, you'll associate any users that need to be filtered with this role.

When a user logs in, they'll first be checked against the roles in SSAS.  If they are a member of the role, the data they see is filtered based on the DAX filter definitions in that role.  In your case, it'll check the Power BI username against the [User] column in the table.

Check out the tutorial here to get a better understanding: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-tutorial-row-level-security-onprem...

[rohan_misra]

Thanks for the reply @malagari. However, in this case I will have to add each user individually to the role that is created. I actually want to get around that functionality as the user list is large and ever changing. Any thoughts?

[malagari]

I completely understand - I've worked on a project with similar constraints.  

In my case, I was working with a client that used Office 365 as their main directory; I was able to create an Office 365 Group and assign it to the role, and then add users to the Office 365 Group when they needed access to Power BI.  This still required bulk adding users to a group, but the management could be done by anyone with access to the Office 365 Portal.  You may be able to do something similar if you're in an on-prem Active Directory as well.

Good luck - I'll follow this thread for other suggestions.