Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Leaves() Gotcha in Analysis Services 2005 SP2: Be aware of the number of tuples that the MDX script is going to affect. The other day, I was trying to define a calculation for a member at the leaf level of all the dimension. After defining the formula and deploying the MDX script, the results were not displayed in the query in SS Management Studio 2005. When I ran the same query in MDX Sample Application (Analysis Services 2000 utility to run MDX queries), it displayed the error as below: It seems that the MDX script that I had setup was affecting more than 4.3 billion tuples in the set. The cube I used had 8 dimension with varying levels. I tried to search if this is documented, but could not find it.

Leaves() is an interesting MDX functions introduced in SQL 2005. It is intended to be used only inside MDX Scripts to define the scope of the calculations (i.e. in the left-hand side of the assignments).

There are many situations where in we wish to perform the calculation at the leaf level of dimension and then aggregate or roll it up like any other member.

Mosha, in his blog explained the use of leaf level for achieving the above objective.

This article explains how we can implement Leaves() in both parent child as well as regular hierarchies using AdventureWorks cube.

Leaves() function use with parent child hierarchy:

My objective was to create a measure 'BudAmount' which is equal to 'Amount' at the leaf level and then it needs to be aggregated like 'Amount' measure across any hierarchy in AdventureWorks' "Financial Reporting" measuregroup .

I read a post in MSDN forum which was similar to my need. In the post, it was needed to define a calculated measure at leaf level and then aggregate the calculated measure like any normal measure.

Chris Webb, in the answer to the post, suggested that:

"...It's certainly a fairly common problem, but Mosha has indeed blogged about the best way to handle this:

http://www.sqljunkies.com/WebLog/mosha/archive/2005/02/13/7784.aspx

Create a new real (ie not calculated) measure in your cube from a column that is always null in your fact table, and then assign the calculated value to the leaf level of all dimensions. Something like:

(Measures.CM, Leaves()) = iif(Measures.M>0.9, 1, null);"

Based on this newfound knowledge, I started to setup my BudAmount measure.

Below are the steps:

1. In the DSV, changed the FactFiance table to a named query and added a column named  'BudAmount' with value as 0 (zero).

2. In cube, added the measure 'BudAmount' alongside of 'Amount'

3. In mdx script, added the below script:

SCOPE (Leaves(),[Measures].[BudAmount]); 
  this = [Measures].[Amount];
END SCOPE;

4. One important thing which I had missed earlier and the Leandro Tubia pointed it out at the MSDN Analysis Services forum to setup the AggregatFunction of BudAmount to be same as Amount i.e. "ByAccount". Otherwise, for balance sheet account types, the amounts would be summed up rather than the last month values for higher levels of Time Dimension.

5. Full processed the cube (I am yet to understand the appropriate processing to be used, so full processing may not be needed,  but just to be on safer side, using full processing)

6.  Now, when you browse the cube, the value of BudAmount and Amount is same as desired.

Leaves() function use with with regular hierarchies:

In Adventureworks, I created a measure (per Chris Webb, it needs to be a real measure, not a calculated one) called 'SalesAmount1' on adventureworks cube which provides the same data as 'SalesAmount' measure. I mean if I query both the measure anyway, both should behave in same way and return the same amount at any level of other dimension.

Below are the steps:

1. In the DSV, in 'FactSalesSummary' query, add additional column as 'SalesAmount1' with value as 0.

2. Under 'Sales Summary' measure group, add the 'SalesAmount1' measure. (look for any white space in the name).

3. In each of Sales Summary partition, add the new 'SalesAmount1' column:

4. In the MDX script, define the calculation:

SCOPE (Leaves(),[MEASURES].[SalesAmount1]);
  this = [Measures].[Sales Amount];
END SCOPE;

5. Check if the SalesAmount1 measure is same as [Sales Amount]. Yes, it is same.

SELECT
    NON EMPTY
    [Product].[Product Categories].[Category].MEMBERS
    ON ROWS ,
    {   [Measures].[Sales Amount], [Measures].[SalesAmount1]
    }
    ON COLUMNS
    FROM [Adventure Works]

6. Even if the cube is browsed in management studio, the value of both the measure is same. So, Leaves() is working in case of regular hierarchies.

Source Click Here.