The task in my hand is to individualize the power bi report based on the logged in user i.e., logged in user should view only the data related to them. We are using Azure Analysis Service(AAS) Database as a data source for the report.
We can use Row Level Security(RLS) in power bi to do the above task but we are connecting to the azure analysis service through "connect live" method which disables the RLS option.
On researching, I got a solution to do the RLS at AAS level. So I created two roles in my model (Admin and Login) which will restrict the user in that role group based on a DAX expression.
I have a Customer table which has Name, Email and Username of the users. Also there are two more tables, Product and Sales with
For Admin role. the members doesn't have any restriction. They can see all the data from the customer table.
For Login role, I put a DAX expression on the customer table as ='Customer'[Email] = USERPRINCIPALNAME() to retrieve only the rows based on the user principal name.
With that in place, I used "Analyze in Excel" option to test the roles. When I use Admin role I can see all the rows in the Customer table and when I use Login role, it has returned only one row which has my email. Worked perfectly. So, I deployed my model to my Azure Analysis Service.
As a next step, I connected my model as a data input for a sample power bi report and created a pie chart with the data I have and published it to my workspace in the power bi service. When I view the report on the power bi service, as a logged in user, I need to see only my data but I can actually see all users data.
On debugging, I thought since I put myself in both Admin and Login role group it is not working. So I removed myself from Admin role in my model, redeployed my model and checked again in my report. Still, I got the same result. Then I thought since I created the report, it is not working for me so I shared my report to other users in my customer table. But the result is same, they can also see all users data.
So I searched a solution for this and I found many references (both microsoft and others) but the model used in those references are from on-premises SSAS where they are using a gateway to connect between model and report. And it the solution they mentioned to map Usernames correctly. In the gateway configuration in power bi, we can map usernames with two options one going with "EffectiveUserName" and second with "CustomData". Since, the references state that this solution works on Azure Analysis Services too, I thought this will work. But, we are not using any gateway for AAS since all are in cloud.
Now I got struck on following items
- how I can send the logged in username to the AAS model?
- how I check what format the "Username" is being sent to AAS model when the user views the report?
Also, point me in right direction if I'm doing anything wrong or missing something.
Thanks in advance!
