SSAS 2012 - Find Member against person logged in

Hi Experts , 

I have working on SSAS Cube -2012 .
My Cube is ready and is working fine .

I am stuck while creating a Calculate Measure (In writing MDX)

My Requirement is - When a person log in , based on this Role , Calculate Measure should be Visible or Invisible.

I need to use UserName and IIf function to achieve this , but not getting correct MDX to achieve it .

Requirement : Check the ROLE of a person who has logged in using UserName function
If ROLE =1 Then [Measures].[SALES_AMT]

If ROLE =2 Then [Measures].[SALES_AMT]

ELSE NULL

Below is the Screen shot of the Table and Data to give IDEA of the Dimension and Fact Used in the cube 

Emp_ROLE_Fact is Fact Table connected to Employee and Role Dimension in Cube .

Please assist in providing MDX for this Calculated Measure 

August 28th, 2015 2:50am

Hi Rihan8585,

According to your description, you want to limit the accessing of [Measure].[Sales AMT] for Role 3 and 4. Right?

In this scenario, you can deny the measure accessing for Role 3 and 4 under Cell Data tab in Role. Select Enable read permissions.

Apply the expression in Allow reading of cube content :

NOT Measures.CurrentMember IS [Measures].[Sales AMT]

For more information, please see: Grant custom access to cell data (Analysis Services)

Regards,

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 9:33am

Hi Simon , 

I do not want to follow the approach you mentioned , This is what I am trying to avoid and working on to get it work other way. 

As that approach has comes out as a big performance issue .

Can you please help me with the MDX ? to find role of a person who has logged in ??

Please assist !!

August 28th, 2015 10:21am

Hi Experts , 

Can any one help in providing MDX to find the Role of a person who has logged in ? and then a IIF condition to show calculated Meaure ?


Or it can be taken in other way , I need to find the Member which exist against other attribute Member and check its Value and put condition ,as this kind of MDX would help me resolving other issue as well.

Please assist !!


  • Edited by Rihan8585 1 hour 47 minutes ago edit
August 31st, 2015 1:20am

Hi Experts , 

Can any one help in providing MDX to find the Role of a person who has logged in ? and then a IIF condition to show calculated Meaure ?


Or it can be taken in other way , I need to find the Member which exist against other attribute Member and check its Value and put condition ,as this kind of MDX would help me resolving other issue as well.

Please assist !!


  • Edited by Rihan8585 Monday, August 31, 2015 5:20 AM edit
Free Windows Admin Tool Kit Click here and download it now
August 31st, 2015 5:18am

Hi, another solution would be to create the calculated measure as Named Calculation in your DSV and instead creating the calculation use a scope statement with the calculation. Then u can use dimension security instead cell security which performs better.

August 31st, 2015 7:30am

Hi Rihan

Please made rule for them assign them to the rules.it can clearly solve your problem

Free Windows Admin Tool Kit Click here and download it now
August 31st, 2015 12:32pm

Hi Dileep, 

Can you please elaborate your solution .
Any Link to help understand what you mean saying ??

Also my above Image show bit of wrong sample data which I prepared, below is the corrected image with proper data .




  • Edited by Rihan8585 19 hours 58 minutes ago EDITED
September 1st, 2015 7:55am

Hi Rihan

follow below link

https://www.mssqltips.com/sqlservertip/2776/configuring-permissions-for-sql-server-analysis-services/

1.create role 

2.assign rules

3.assign user to role 

finally dont forget to do full processing 

Please mark as answer if you feel helpful to you

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 6:10pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics