cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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.

3 REPLIES 3
Highlighted
Microsoft
Microsoft

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

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

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

Sandeep,

 

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

Highlighted
Helper I
Helper I

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

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors