SSAS Live Connection - Filter for Current User

[Anonymous]

Hi

Hoping for a bity of advice, we've got an on prem SSAS MDX Cube that were looking at producing some Power BI reports against using Live Connection.

One requirement is that we produce an employee specific report/dashboard where the data will be filtered for the currently logged in user viewing the report.  I don't seem to be able to find a way to do this using a live connection, is my understanding correct that this isn't possible in Power Bi by providing a suitable filter?

I realise we can implement Row Level Security RLS on the cube itself  but the problem is that workers may perform multiple roles in the company so they might want to look at their specific performance report where the report is tailored/filtered to them as an individual, or if they are also a team leader/dept manager they will want to view  a different report where its filtered based on their team or dept and therefore different filters would need applying.

Is the only way to do it to use different roles and perspectives directly defined in the cube itself?

Thanks

[parry2k]

@Anonymous that is how we are currently doing it? Using row level security at cube level which hide the rows from user depending on their role.

[Anonymous]

Thanks for the reply @parry2k so do you have the scenario where a person can have multiple roles? 

If they exist in multiple roles (eg Employee & Dept Manager) and those roles effectively have different filters (eg employee filtered for just the employee, & Dept Manager filtered for their DeptCode) does it not compound the filters and therefore if you browse the department manager report you're still just going to get the data filtered for that individual employee because the cube has applied both sets of filtering? 

[parry2k]

yes theoratically it will do that, basically whatever permsision you have on cube, it will get those permission or you need to use powerbi row level security

[Anonymous]

hmm i dont think that will work for us then in that case 😞

Looks like we might have to pull the data from the DWH dim & fact tables into a model in the PBIX itself and then define an appropriate role in the report (eg if its the employee centric report to just filter for the current employee)

[Anonymous]

@Anonymous,

Please check my reply in the following similar thread.

http://community.powerbi.com/t5/Integrations-with-Files-and/Row-Level-Security-in-Power-BI-multidimensional-SSAS/td-p/118908

Regards,
Lydia

[Anonymous]

@Anonymous tbh I'm not sure how that helps my sceanrio

let say you have 2 roles defined on the cube

1 Employee - where all data is filtered belonging a single employee via the employee Dimension (ie the person logged in)

2 Department Manager - where data is filtered by by it beloning to a particular department

I we have a user who is in both roles then the cube is going to allow them to see all their transactions and all transactions belonging to anyone in that department.

Therefore if they if they look at the report geared towards an individuals perfomances its going to returen datat belonging to all people in that department becasue there doen't appear to be anyway to dynamically filter it for just that users.

When they want to look at the department level perfmnce report yes they would want to see all the data broken down by different employees.

Using a LIve connection there just doesnt seem to be any way to dynamically filter that one persons data if they are allowed to see more than tat in the cube. If i've mis understood please could you procide an example of how it would work.

regards