none
How to create a calculated member based on two measures and the hierarchy level of the queried dimension's current member?

    Question

  • I have a cube which has

    • two measure members: [Measures].[Value] (integer) and [Measures].[EffectiveBelowLevel] (integer).
    • a dimension called [DimParentChild] with a user hierarchy called [ParentChildHierarchy].

    I would like to create a calculated member on the measures dimension ([Measures].[EffectiveValue]) based on [Measures].[Value] which when queried along [DimParentChild] and [ParentChildHierarchy] behaves as follows:

    • [Measures].[Value] is used if the hierarchy level of [DimParentChild].[ParentChildHierarchy].CURRENTMEMBER > [Measures].[EffectiveBelowLevel].
    • 0 is used if the hierarchy level of [DimParentChild].[ParentChildHierarchy].CURRENTMEMBER <= [Measures].[EffectiveBelowLevel].

    Is it possible to achieve this functionaly with a calcuated member on the measures dimension?

    If yes then what the formula would look like?

    If not then what other way would there be? I am very interested in any other kind of solution as well (e.g. an mdx query, etc.)

    As an example:

    [Measures]
    [Value] [EffectiveBelowLevel] ParentChildAssociation
    10  1      GrandChild1
    20  2      GrandChild2
    
    [DimParentChild].[ParentChildHierarchy]
    Member  HierarchyLevel Description
    Parent    1    - 
    Child    2    first child of Parent 
    GrandChild1 3    first child of Child
    GrandChild2 3    second child of Child
    

    With this data [Measures].[EffectiveValue] should look like this

    ParentChild EffectiveValue
    Parent     0
    Child     10
    GrandChild1  10
    GrandChild2  20
    
    Friday, April 01, 2011 8:56 AM

Answers

  • Hi Zoltan,

    You are right then.

    What you could do is add a slight change to the previous calculation.

    I'll try to provide an example using Product Category that will serve to simulate the EffectiveBellowLevel dimension. So lets change the EffectiveBellowMeasure to dynamically determine the level based on hard coded Joe Pack employee and 'Product Category' member key. As there are 4 product categories with member keys 1-4, rows associated with each of product categories and Joe Pack should be eliminated based on the level of "current" employee and product category. So "Accessories" with member key 4 should not aggregate to Joe Pack's parent Amy E. Alberts (level 3). Accessories (4) and Clothing (3) categories related to Joe Pack should be eliminated for Brian S. Welcker (level 2). And so on, All employees member (level 0) should have all categories (1-4) related to Joe Pack eliminated from it's aggregation.

    WITH
    
    MEMBER [Measures].[P] AS Int([Product].[Product Categories].CURRENTMEMBER.MEMBER_KEY)
    
    MEMBER [Measures].[EffectiveBelowLevel] AS
    CASE [Employee].[Employees].CURRENTMEMBER
    --	WHEN [Employee].[Employees].&[291] THEN 2 /* Jae B. Pak - Level 4 */
    	WHEN [Employee].[Employees].&[291] THEN [Measures].[P] 
    	ELSE 0 /* all other employees */
    END
    
    MEMBER [Measures].[Employee Level] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL
    
    member [Measures].[Eliminate Desc] as
    Generate
    (
    	Filter
    	(
    		existing [Product].[Product Categories].[Category],
    		[Measures].[P] > [Measures].[Employee Level]
    	)
    	,[Product].[Product Categories].CurrentMember.NAME
    	,','
    )
    
    MEMBER [Measures].[Reseller Sales Amount Calc] AS
    SUM({[Employee].[Employees].CURRENTMEMBER} as CM, null)
    +
    SUM
    (
    	Filter
    	(	
    		{Descendants( [Employee].[Employees].CURRENTMEMBER) - [Employee].[Employees].[All Employees]}
    		*
    		existing [Product].[Product Categories].[Category]
    		,[Measures].[EffectiveBelowLevel] <= (CM.Item(0).Level.Ordinal)
    	)
    	,
    	(
    		[Employee].[Employees].CURRENTMEMBER.DataMember, 
    		[Product].[Product Categories].CurrentMember, 
    		[Measures].[Reseller Sales Amount]
    	)
    ), FORMAT_STRING = "Currency"
    
    SELECT
    {
    	[Measures].[P],
    	[Measures].[Eliminate Desc],
    	[Measures].[Employee Level],
    	[Measures].[EffectiveBelowLevel],
    	[Measures].[Reseller Sales Amount],
    	[Measures].[Reseller Sales Amount Calc]
    	
    } ON COLUMNS,
    Hierarchize
    (
    	ASCENDANTS([Employee].[Employees].&[291]) 
    	+[Employee].[Employees].&[290].CHILDREN
    )
    --Descendants([Product].[Product Categories],1,SELF_AND_BEFORE)
    ON ROWS
    FROM [Adventure Works]
    
    

    I hope this is more appropriate to your situation.

    HTH,

    Hrvoje Piasevoli

    • Proposed as answer by Jerry NeeModerator Tuesday, April 05, 2011 8:48 AM
    • Marked as answer by hadadiz Wednesday, April 06, 2011 11:35 AM
    Monday, April 04, 2011 7:11 AM

All replies

  • Have you tried CASE statement to accomplish this?

    Check CASE statement in MDX here...

    http://msdn.microsoft.com/en-us/library/ms144841.aspx


    Please visit my Blog for some easy and often used t-sql scripts
    Friday, April 01, 2011 9:15 AM
  • Hi,

    Here is an example on Adventure Works cube:

    Note that I'm creating a calculated measure here [Measures].[EffectiveBelowLevel] to simulate a situation where 3 employees from the [EmployeeSet] have the first case (level.Ordinal > [EffectiveBellowLevel]). If the condition has been satisfied the calculated measure [Measures].[Reseller Sales Amount Calc] will return the value of the actual measure [Measures].[Reseller Sales Amount]. Since you already have a measure group for that neither the set not that calculation would be needed.

    with
    set [EmployeeSet] as
    {
    	[Employee].[Employees].&[291], -- Jae B. Pak	4
    	[Employee].[Employees].&[295],	-- Rachel B. Valdez 4
    	[Employee].[Employees].&[272] --Stephen Y. Jiang	3	
    }
    member [Measures].[EffectiveBelowLevel] as
    Iif
    (
    	Intersect
    	(
    		[Employee].[Employees].CurrentMember,
    		{EmployeeSet}
    	).Count > 0
    	,[Employee].[Employees].CurrentMember.Level.Ordinal - 1
    	,NULL
    )
    
    member [Measures].[Employee Level] as [Employee].[Employees].CurrentMember.Level.Ordinal
    member [Measures].[UniqueName] as [Employee].[Employees].CurrentMember.UniqueName
    member [Measures].[Reseller Sales Amount Calc] as
    Iif
    (
    	NOT IsEmpty([Measures].[EffectiveBelowLevel]) 
    	AND [Measures].[Employee Level] > [Measures].[EffectiveBelowLevel]
    	,[Measures].[Reseller Sales Amount]
    	,0
    ), Format_String = "Currency"
    
    select
    {
    	[Measures].[Employee Level],
    --	[Measures].[UniqueName],
    	[Measures].[EffectiveBelowLevel],
    	[Measures].[Reseller Sales Amount],
    	[Measures].[Reseller Sales Amount Calc]
    } on 0,
    [Employee].[Employees].MEMBERS
    on 1
    from
    [Adventure Works]
    
    

    HTH,

    Hrvoje Piasevoli

    Friday, April 01, 2011 10:34 AM
  • Hello,

    Thanks for your suggestion, but I am afraid it does not do exactly what I would like to have.

    I modified your query a little bit so I can explain maybe a bit better exactly what I would like to accomplish.

    WITH
    MEMBER [Measures].[EffectiveBelowLevel] AS
    CASE [Employee].[Employees].CURRENTMEMBER
    	WHEN [Employee].[Employees].&[291] THEN 2 /* Jae B. Pak - Level 4 */
    	ELSE 0 /* all other employees */
    END
    MEMBER [Measures].[Employee Level] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL
    MEMBER [Measures].[Reseller Sales Amount Calc] AS
    IIF
    (
    	NOT ISEMPTY([Measures].[EffectiveBelowLevel]) 
    	AND [Measures].[Employee Level] > [Measures].[EffectiveBelowLevel]
    	,[Measures].[Reseller Sales Amount]
    	,0
    ), FORMAT_STRING = "Currency"
    SELECT
    {
    	[Measures].[Employee Level],
    	[Measures].[EffectiveBelowLevel],
    	[Measures].[Reseller Sales Amount],
    	[Measures].[Reseller Sales Amount Calc]
    } ON COLUMNS,
    ASCENDANTS([Employee].[Employees].&[291]) ON ROWS
    FROM [Adventure Works]
    

     The result of the query (only a specific employee and all of it's ascendants are included from employees):

    		Employee Level	EffectiveBelowLevel	Reseller Sales Amount	Reseller Sales Amount Calc
    Jae B. Pak		4		2		$8,503,338.65		$8,503,338.65
    Amy E. Alberts	3		0		$15,535,946.26		$15,535,946.26
    Brian S. Welcker	2		0		$80,450,596.98		$80,450,596.98
    Ken J. Sánchez	1		0		$80,450,596.98		$80,450,596.98
    All Employees	0		0		$80,450,596.98		$0.00
    

    What I would like to achieve:

    		Employee Level	EffectiveBelowLevel	Reseller Sales Amount	Reseller Sales Amount Calc
    Jae B. Pak		4		2		$8,503,338.65		$8,503,338.65
    Amy E. Alberts	3		0		$15,535,946.26		$15,535,946.26
    Brian S. Welcker	2		0		$80,450,596.98		$71,947,258.33
    Ken J. Sánchez	1		0		$80,450,596.98		71,947,258.33
    All Employees	0		0		$80,450,596.98		$0.00
    

    The [Reseller Sales Amount] and [Reseller Sales Amount Calc] for [Brian S. Welcker] and [Ken J. Sánchez] should be different.

    They should be different as the calculated value from [Jae B. Pak] should not be included in the aggregation any more as it is told by [EffectiveBelowLevel] value 2 for [Jae B. Pak].

    And there is a slight problem with having the [EffectiveBelowLevel] as a calculated member as this way it is possible to define for each already aggregated value. On the other hand when I have the [EffectiveBelowLevel] values on the measures they will be aggregated (summed up) when used in the query at this point.

     

    Thank You really much for Your help!

    -Zoltan

    Friday, April 01, 2011 1:50 PM
  • Hello Atif-ullah Sheikh,

    Thank you for your suggestion, I tried using IIF and CASE would give me similar functionality.

    Unfortunately the problem problem seems to have complexities which can not be solved easily by any of these statements.

     

    Kind Regards,

    Zoltan

    Friday, April 01, 2011 2:00 PM
  • Hi,

    OK it seems I've misunderstood your rules but I understand now you need to eliminate those mombers from the parent aggregation according to parent's level.

    Here is a modified query where the amounts are eliminated but it has some tricks to reconstruct the sum as this is a parent child dimension where non leaf members have data.

    I'l just paste the query and can explain it latter if it works for you as I have to go now.

    Query

    WITH
    MEMBER [Measures].[EffectiveBelowLevel] AS
    CASE [Employee].[Employees].CURRENTMEMBER
    	WHEN [Employee].[Employees].&[291] THEN 2 /* Jae B. Pak - Level 4 */
    	ELSE 0 /* all other employees */
    END
    MEMBER [Measures].[Employee Level] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL
    
    MEMBER [Measures].[Reseller Sales Amount Calc] AS
    SUM({[Employee].[Employees].CURRENTMEMBER} as CM, null)
    +
    SUM
    (
    	Filter
    	(	
    		Descendants( [Employee].[Employees].CURRENTMEMBER) - [Employee].[Employees].[All Employees]
    		,[Measures].[EffectiveBelowLevel] <= (CM.Item(0).Level.Ordinal)
    	)
    	,([Employee].[Employees].CURRENTMEMBER.DataMember, [Measures].[Reseller Sales Amount])
    ), FORMAT_STRING = "Currency"
    
    SELECT
    {
    	[Measures].[Employee Level],
    	[Measures].[EffectiveBelowLevel],
    	[Measures].[Reseller Sales Amount],
    	[Measures].[Reseller Sales Amount Calc]
    } ON COLUMNS,
    Hierarchize
    (
    	ASCENDANTS([Employee].[Employees].&[291]) 
    	+[Employee].[Employees].&[290].CHILDREN
    )
    ON ROWS
    FROM [Adventure Works]
     
    

    HTH,

    Hrvoje Piasevoli

    Friday, April 01, 2011 2:59 PM
  • And there is a slight problem with having the [EffectiveBelowLevel] as a calculated member as this way it is possible to define for each already aggregated value. On the other hand when I have the [EffectiveBelowLevel] values on the measures they will be aggregated (summed up) when used in the query at this point.

    Hi, just a quick comment on the above. So [EffectiveBelowLevel] is a member property right? It should be simple to replace the expression in query with something like [Employee].[Employees].CurrentMember.Properties("EffectiveBelowLevel", TYPED)

    Regards,

    Hrvoje Piasevoli

    Friday, April 01, 2011 3:15 PM
  • Hello,

    Thanks for the summing up mdx, it does what I wanted, except where [EffectiveBelowLevel] is taken from.

    Regarding the "slight problem" above, in my actual cube I have two options for getting [EffectiveBelowLevel]:

    • Initially I have [EffectiveBelowLevel] as a member property in an other dimension than the one I use for projecting (in case of above mdx-es not in the [Employee] dimension). The question for the final solution would be if it is possible to use such a dimension member property in this kind of calculations?
    • If the previous way is not viable, I have the possibility to store the [EffectiveBelowLevel] as a Measure in the same measure group as the value which has to be summed up (just fill it along the etl). But in this case how can I use it? - as it is aggregated for each of the leaf members of the [Employee] dimension.

     

    Kind Regards,

    Zoltan

    Friday, April 01, 2011 4:13 PM
  • Hi,

    First I am glad that the aggregation is working now.

    Regarding the [EffectiveBelowLevel] - I guess it would be possible to use it off of an another dimension but I'm not sure how the two are related so would need more info on that.

    If there is a reason you can not have it as a member property of the parent-child dimension maybe it would be the easiest to have that value in a (small) separate measure group linked to the parent-child dimension.

    HTH,

    Hrvoje Piasevoli

    Saturday, April 02, 2011 6:38 PM
  • Hello,

    I try to describe the situation with EffectiveBelowLevel as I have in my real cube, using the initial example.

    The star schema the cube is based on is made up of the following tables:

    • DimParentChild table - defines the dimension with the parent-child hierarchy.
    • DimEffectiveBelowLevel - defines the dimension for the effective below level values.
    • FactValues - contains the Value column and FK columns pointing to DimParentChild and DimEffectiveBelowLevel.

    There is no one-to-one association between DimEffectiveBelowLevel and DimParentChild entries.
    The parent-child and the effective-below-level dimensions are related only through the entries in FactValues table.
    There may be some [Measures].[Value] which belong to [DimParentChild].[ParentChildHierarchy].[NodeX] and have corresponding [DimEffectiveBelowLevel].[EffectiveBelowLevelHierarchy].[2] and [DimEffectiveBelowLevel].[EffectiveBelowLevelHierarchy].[5] respectively.

    I hope that this cleared up the circumstances.

    Regarding the "... separate measure group ..." I do not see how this would work in this case.

    It is possible that there is a problem already with the way I have the star schema - but in this case what would be the good way to model this situation?

    Thanks for bearing with me ;)
    -Zoltan

    Monday, April 04, 2011 1:36 AM
  • Hi Zoltan,

    You are right then.

    What you could do is add a slight change to the previous calculation.

    I'll try to provide an example using Product Category that will serve to simulate the EffectiveBellowLevel dimension. So lets change the EffectiveBellowMeasure to dynamically determine the level based on hard coded Joe Pack employee and 'Product Category' member key. As there are 4 product categories with member keys 1-4, rows associated with each of product categories and Joe Pack should be eliminated based on the level of "current" employee and product category. So "Accessories" with member key 4 should not aggregate to Joe Pack's parent Amy E. Alberts (level 3). Accessories (4) and Clothing (3) categories related to Joe Pack should be eliminated for Brian S. Welcker (level 2). And so on, All employees member (level 0) should have all categories (1-4) related to Joe Pack eliminated from it's aggregation.

    WITH
    
    MEMBER [Measures].[P] AS Int([Product].[Product Categories].CURRENTMEMBER.MEMBER_KEY)
    
    MEMBER [Measures].[EffectiveBelowLevel] AS
    CASE [Employee].[Employees].CURRENTMEMBER
    --	WHEN [Employee].[Employees].&[291] THEN 2 /* Jae B. Pak - Level 4 */
    	WHEN [Employee].[Employees].&[291] THEN [Measures].[P] 
    	ELSE 0 /* all other employees */
    END
    
    MEMBER [Measures].[Employee Level] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL
    
    member [Measures].[Eliminate Desc] as
    Generate
    (
    	Filter
    	(
    		existing [Product].[Product Categories].[Category],
    		[Measures].[P] > [Measures].[Employee Level]
    	)
    	,[Product].[Product Categories].CurrentMember.NAME
    	,','
    )
    
    MEMBER [Measures].[Reseller Sales Amount Calc] AS
    SUM({[Employee].[Employees].CURRENTMEMBER} as CM, null)
    +
    SUM
    (
    	Filter
    	(	
    		{Descendants( [Employee].[Employees].CURRENTMEMBER) - [Employee].[Employees].[All Employees]}
    		*
    		existing [Product].[Product Categories].[Category]
    		,[Measures].[EffectiveBelowLevel] <= (CM.Item(0).Level.Ordinal)
    	)
    	,
    	(
    		[Employee].[Employees].CURRENTMEMBER.DataMember, 
    		[Product].[Product Categories].CurrentMember, 
    		[Measures].[Reseller Sales Amount]
    	)
    ), FORMAT_STRING = "Currency"
    
    SELECT
    {
    	[Measures].[P],
    	[Measures].[Eliminate Desc],
    	[Measures].[Employee Level],
    	[Measures].[EffectiveBelowLevel],
    	[Measures].[Reseller Sales Amount],
    	[Measures].[Reseller Sales Amount Calc]
    	
    } ON COLUMNS,
    Hierarchize
    (
    	ASCENDANTS([Employee].[Employees].&[291]) 
    	+[Employee].[Employees].&[290].CHILDREN
    )
    --Descendants([Product].[Product Categories],1,SELF_AND_BEFORE)
    ON ROWS
    FROM [Adventure Works]
    
    

    I hope this is more appropriate to your situation.

    HTH,

    Hrvoje Piasevoli

    • Proposed as answer by Jerry NeeModerator Tuesday, April 05, 2011 8:48 AM
    • Marked as answer by hadadiz Wednesday, April 06, 2011 11:35 AM
    Monday, April 04, 2011 7:11 AM
  • Hello,

    Even if it took some time to adapt the query to my cube finally I got it working and it does exactly what I wanted at the beginning.

    Thank You really much!
    -Zoltan

    Wednesday, April 06, 2011 12:54 PM
  • Hi Zoltan,

    It's great to hear that :) Your feedback and clear explanations have certainly contributed a lot.

    PS:

    Unfortunately the problem problem seems to have complexities which can not be solved easily by any of these statements.

    Says who? ;)

    Regards,

    Hrvoje Piasevoli

    Thursday, April 07, 2011 12:22 PM