## 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 ﬁnd 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 diﬀerent time periods.

In most real-world calculations, you use the CurrentMember function to construct tuple or set expressions to ﬁnd 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 ﬁrst is the **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 ﬁnd 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.

**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];

**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];