MDX Calculations Part 2 – CurrentMember Function

The CurrentMember function

The CurrentMember function is the most important function in MDX, understanding how it works is the key to being able to write all kinds of really useful calculations in MDX, including time intelligence calculations.
What it does is this? when you call it from inside a calculation, it tells you where the calculation is being executed inside the space of the cube. In other words a calculation is executed inside a cell, and a cell has a tuple that points to it, the CurrentMember function allows you to inspect that tuple, and to find which member from any hierarchy on any dimension in the cube is present in that tuple.
The CurrentMember function can only be used with a hierarchy object, and it returns a member object. Using this function is essential while building calculations which include related metrics, such as sales values from different time periods.
In most real-world calculations, you use the CurrentMember function to construct tuple or set expressions to find values elsewhere in the cube, relative to the cell you are currently in.

WITH 
  MEMBER Measures.CurrentYear AS 
    [DimDate].[Calendar].CurrentMember.Name 
SELECT 
  {
    [Measures].[Order Quantity]
   ,[Measures].[CurrentYear]
  } ON COLUMNS
 ,{[DimDate].[Calendar].[Year].MEMBERS} ON ROWS
FROM [BigADWCube];

WITH 
  MEMBER Measures.PrevYearValue AS 
    [DimDate].[Calendar].CurrentMember.PrevMember.Name 
SELECT 
  {
    [Measures].[Order Quantity]
   ,[Measures].[PrevYearValue]
  } ON COLUMNS
 ,{[DimDate].[Calendar].[Year].MEMBERS} ON ROWS
FROM [BigADWCube];

-- Now lets convert simple Year Value to tuple calculation
WITH 
  MEMBER Measures.PrevYearMeasureValue AS 
    (
      [Measures].[Order Quantity]
     ,[DimDate].[Calendar].CurrentMember.PrevMember
    ) 
SELECT 
  {
    [Measures].[Order Quantity]
   ,[Measures].[PrevYearMeasureValue]
  } ON COLUMNS
 ,{[DimDate].[Calendar].[Year].MEMBERS} ON ROWS
FROM [BigADWCube];

Relative TUPLE pattern

There are two popular types of calculations in MDX. The first is the go site relative tuple pattern. In this pattern the CurrentMember function is used to return a member from a hierarchy; this member is then passed to another function such as PrevMember to find another member relative to that member. Subsequently, this new member is then used inside a dynamic tuple expression to return a value from the cube. Frequently used examples of this type of calculation are: previous period growth, and percentage share calculations.
here Previous Period Growth Problem
Are our orders up or down compared to previous year? by how much or what percentage?
We will need a calculated measure to show the absolute or percentage difference
Must work for every possible selection on the Calendar hierarchy

-- Simple Basic Absolute Difference
WITH 
  MEMBER Measures.Growth AS 
      [Measures].[Order Quantity]
    - 
      (
        [Measures].[Order Quantity]
       ,[DimDate].[Calendar].CurrentMember.PrevMember
      ) 
SELECT 
  {
    [Measures].[Order Quantity]
   ,Measures.Growth
  } ON COLUMNS
 ,{[DimDate].[Calendar].[Year].MEMBERS} ON ROWS
FROM [BigADWCube];

-- (Tuple1 - Tuple2)/Tuple1
WITH 
  MEMBER Measures.[Previous Year Growth] AS 
      (
        [Measures].[Order Quantity]
      - 
        (
          [Measures].[Order Quantity]
         ,[DimDate].[Calendar].CurrentMember.PrevMember
        )
      )
    / 
      [Measures].[Order Quantity] 
   ,FORMAT_STRING = 'Percent' 
SELECT 
  {
    [Measures].[Order Quantity]
   ,Measures.[Previous Year Growth]
  } ON COLUMNS
 ,{[DimDate].[Calendar].[Year].MEMBERS} ON ROWS
FROM [BigADWCube];

-- 1.#INF is not something you want to show to your end users. Furthermore, Mosha showed us all a long time ago that when dividing we should always use the pattern 
-- iif(measures.x=0, null, measures.y/measures.x) 
-- to get the best performance. But divide also gives comparable performance
WITH 
  MEMBER Measures.[Previous Year Growth] AS 
    Divide
    (
        [Measures].[Order Quantity]
      - 
        (
          [Measures].[Order Quantity]
         ,[DimDate].[Calendar].CurrentMember.PrevMember
        )
     ,[Measures].[Order Quantity]
    ) 
   ,FORMAT_STRING = 'Percent' 
SELECT 
  {
    [Measures].[Order Quantity]
   ,Measures.[Previous Year Growth]
  } ON COLUMNS
 ,{[DimDate].[Calendar].[Year].MEMBERS} ON ROWS
FROM [BigADWCube];

tastylia strips reviews Parent Share Problem
What percentage of orders is allocated to one particular quarter as compared to yearly orders?
First, get the orders of the particular quarter when placed at quarter level:
Tuple1 = ([Measures].[Order Quantity],[DimDate].[Calendar].CurrentMember)
And now, get the orders of the parent. This can be done by:
Tuple2 = ([Measures].[Order Quantity],[DimDate].[Calendar].Parent)
Calculating the contribution ratio is simply a matter of dividing the first Tuple1 by Tuple2 .

-- Tuple1 / Tuple2 
WITH 
  MEMBER Measures.[Parent Share] AS 
    Divide
    (
      (
        [Measures].[Order Quantity]
       ,[DimDate].[Calendar].CurrentMember
      )
     ,(
        [Measures].[Order Quantity]
       ,[DimDate].[Calendar].Parent
      )
    ) 
   ,FORMAT_STRING = 'Percent' 
SELECT 
  {
    [Measures].[Order Quantity]
   ,Measures.[Parent Share]
  } ON COLUMNS
 ,
    {[DimDate].[Year].[Year].MEMBERS}
  * 
    {[DimDate].[Quarter].[Quarter].MEMBERS} ON ROWS
FROM [BigADWCube];

-- For debugging purpose
WITH MEMBER Measures.[YearMemberToStr]		AS MemberToStr( [DimDate].[Calendar].CurrentMember )
	MEMBER Measures.[QuarterMemeberToStr]	AS MemberToStr( [DimDate].[Calendar].Parent )
SELECT {[Measures].[Order Quantity],Measures.[YearMemberToStr],Measures.[QuarterMemeberToStr] } ON COLUMNS
  ,{[DimDate].[Year].[Year].members} * {[DimDate].[Quarter].[Quarter].members} ON ROWS
FROM [BigADWCube];

Relative SET pattern.

The second common calculation pattern is the relative set pattern. In this pattern the starting point is still the CurrentMember function, but this time it is used to construct a set expression which is then passed to a numeric function such as Sum() or Count(). A running sum calculation is a good example of this pattern.
Running sum by day
Typical and very sensible approach to this problem is to call Sum over all the preceding days. Assuming that our calculation will only ever be called on Date attribute

WITH 
  MEMBER [Measures].RunSales AS 
    Sum
    (
      NULL : [Date].[Date].CurrentMember
     ,[Measures].[Sales Amount]
    ) 
SELECT 
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

This works OK – about 2 seconds execution time, and in block mode. We still want to optimize it. The common optimization technique for running sum is to do summation at higher grains whenever possible. I.e. instead of running over days, we would like to run over months until the current month, and only from there over the remaining days in this month. This approach makes a lot of sense, but the implementation should be done carefully. Let’s see what will happen if we modify the set to be union of months and days:

WITH 
  MEMBER [Measures].RunSales AS 
    Sum
    (
      Union
      (
        NULL : [Date].[Calendar].Parent.PrevMember
       ,
        [Date].[Calendar].FirstSibling : [Date].[Calendar].CurrentMember
      )
     ,[Measures].[Sales Amount]
    ) 
SELECT 
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

The results are disastrous ! The execution time rose above 3 seconds, but more alarmingly, both number of cells calculated and SE queries jumped to thousands. Why is that ? After all, if we read the official documentation, it says that Sum over Union is in fact optimized for block mode, but the results we see here clearly show the opposite. The root cause is a little bit deeper. It is true that Sum(Union()) is optimized, but one of the other conditions for aggregation functions to work optimally, is to have the input set in a good shape, i.e. not arbitrary shaped set. Our Union combines sets from different granularities, so we end up with the mixed grain set, which is classified as arbitrary shape. How can we fix it ? One way is to break single Sum into two Sum’s, each one over the set of uniform single grain, i.e.

WITH 
  MEMBER [Measures].RunSales AS 
      Sum
      (
        NULL : [Date].[Calendar].Parent.PrevMember
       ,[Measures].[Sales Amount]
      )
    + 
      Sum
      (
        [Date].[Calendar].FirstSibling : [Date].[Calendar].CurrentMember
       ,[Measures].[Sales Amount]
      ) 
SELECT 
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

Now the results are great – the runtime is about 400 ms, and other stats look good too. It is possible to optimize it even further using techniques described in the “Take advantage of FE caching to optimize MDX performance” article. We notice that the first component of plus is the same for all days in the month, so we can take advantage of it by caching it at the first day and reusing for all other days as following:

WITH 
  MEMBER [Measures].RunMonthSales AS
      Sum
      (
        NULL : [Date].[Calendar].CurrentMember
       ,[Measures].[Sales Amount]
      )
  MEMBER [Measures].RunSales AS 
      ([Measures].RunMonthSales, [Date].[Calendar].Parent.PrevMember)
    + 
      Sum
      (
        [Date].[Calendar].FirstSibling : [Date].[Calendar].CurrentMember
       ,[Measures].[Sales Amount]
      ) 
SELECT 
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];