SSAS Multidimensional Dynamic Security Row Level - Power BI.com LIVE Connection

[Sandeep]

Dear All,

We are facing one small issue with SSAS Multidimensional Dynamic Security - Row Level access for Power BI reports in Desktop as well as in powerbi.com.

Problem Statement: Trying to filter out Cubes data based on the User Login to Power Bi.com using SSAS Multidimensional Dynamic Security Row Level - Power BI.com LIVE Connection

Please Note: While, we are testing with Excel and SSMS using specific ID’s, it is filter out “data correctly” and shows measure information correct. We are not sure, what is going wrong with Power BI Desktop and Power BI.com although we know we are missing some steps.

Giving some background here is what we followed from Microsoft documentations

1. Created a Security dimension and connected to Relative facts and Dims to ensure it filters out the rows based on the user’s access.

Details: Created a User Security Table, Then Mapped to Product table, then the Sales fact is connected to Product table. Based on Users, Product will filter, then based on Product the Sales data will be filtered.

Note: The User Security Table is having a column called “UserName” where it is storing the UserName as “ABC\testuser”, In the format of “DOMAIN\UserName”. Which is passing from Excel/SSMS to filter the SSAS User Security Dimension.

1. We have used USERNAME () function in SSAS multi-dim (in Roles) for User Security table to filter Product and based on the Username/ID, Sales data should be filtered based on User based access to Product data.

1. It is working fine in Excel when we pass a specific user name in the connection properties and in SSMS by changing the roles to specific users as DOMAIN\USERNAME

WHAT WE HAVE DONE IN POWER BI.COM

1. As we are passing an email id while logging to Power BI, and the SSAS Multi-dimensional Cubes is having Users added in AD format, we mapped User name to Effective User Name in Data Source Settings to ensure it is mapping correctly like AD ID.

Details: let's say, we are logging in Power BI.com as testuser@SSAScubes.com however the AD id which is mapped to SSAS Cubes is "ABC\Someone". So, we mapped in User Mapping, someone@SSAScubes.com to ABC\testuser. however when testuser@SSAScubes.com using as login id to power bi but the data is not filtering out.

What are we missing here?

Appreciate your quick response. Thank you.

[BIrs]

Sandeep,

Did you get this work?is Power BI recognising SSAS Roles?

[Sandeep]

Dear Blrs,

Yes. It worked fine with Excel (on-premise) and PowerBI.com. For Power Bi desktop, it somehow giving issue as it connects SSAS Cubes with static user credentials. However, once you deploy the PBI files in powerbi.com, then check with user mapping details, once that is correct. It should show user level data.

Hope this will help!

Best Regards,

Sandeep

[v-yuezhe-msft]

@Sandeep,

In your scenario, I would recommend you check the following things.

1. Make sure that you have correctly created row level security in ssas multidimensional , you can refer to this blog to get more details.

2. Use correct User Mapping path, both Replace and With textbox require Email address. you can review this article.

Regards,
Lydia