Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rohan_misra
Regular Visitor

Passing user parameter from dashboard to SQL server through SSAS

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.

3 REPLIES 3
malagari
Responsive Resident
Responsive Resident

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...

Dan Malagari
Consultant at Headspring

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?

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.

Dan Malagari
Consultant at Headspring

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.