Forum Discussion
Row Level Security
Hi toddpbi,
We have used SSAS Tabular RLS with Power BI RS On-Prem. Works well, I'm not sure why you think you would still need to publish to the cloud, I might be missing part of your requirement?
Regards
Rob
Anonymous Sorry to ask but have you really been able to use RLS SSAS with Power BI Report Server? How did you do that? How did you get the role been recognized in Power BI?
Hi Anonymous,Yes honest we have, it works well. :)
There are a few pre-requisites
You have to be using windows active directory domain user security, and your PBI users need to be authenticated logged in users.
We are using the Oct 2017 PBI Desktop and PBIRS on-prem, on a server on the domain. Not sure if it works with versions earlier than this.
We have SQL Server 2016 SSAS Tabular supporting our data model. And we use the SSAS Tab DM as the data source for the PBI report. Not a DM in PowerBI. (Direct Query like connection)
The roles are created and managed in SSAS Tabular, PBI is just the consumer.
We do have the SQLserver, SSAS Tab service and PBIRS all running on the same server. I think there maybe some extra setup needed if you distribute these elements.
There are some online resources for this, try Guy In A Cube for example.
Hope that helps
Rob
Thank you Anonymous. I have tried many times these steps and for several weeks now, I am still not able to get Power BI to filter the row according to my windows login. The puzzeling thing is that the Tabular model works well in excel or even SSRS and get it to recognize role A using the *Roles* keyword in the connection string. I'm using the following connection string: Data Source=MyServerName\TABULARSERVER;Initial Catalog=TestRLS;Roles=A;
But such filtering doesn't work in Power BI.
In your SSAS Tabular, if you created several roles - say "role A", "role B" and "role C" - how do you get power bi to filter the report using say "role A"?
Hi Anonymous. I have been working with Rob on the role level security in our organisation.
In our model we have an employees table which is linked to the main data. This has their Active Directory user name stored in a column (say Role A).
In Roles in SSAS Tabular we have then written the following DAX and add appropriate measures.
=ADUserName = USERNAME() || [RoleA] = USERNAME() || [RoleB] = USERNAME() || [RoleC] = USERNAME()
The article that we refered to when setting our role level security is as follows:
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
Also are you connecting to Analysis Services using a Live Connection in Power BI?
I hope this helps.
James
Thanks for your quick response.
How do you share your reports to the end consumers after prepping the data model?
How do the end consumers view these dashboards?
Regards,Ben
Hi todd. Anonymous currently share our reports using Power Bi Report Server on prem.
We are currently not in a position to use the cloud yet.
Does that help?
