MDX Puzzle #2 Solution

This puzzle was a little more challenging than the first, but it was definitely fun and a good learning experience.  It introduced a new method that allows you to create session scoped calculation that can be used in your MDX query.  You can actually take the code used in the CREATE MEMBER statement (that will be explained in later puzzles) and add it to your cube as a CALCULATED MEMBER.

First, let’s address the simple parts of the requirements.  The following query satisfies the following:

1. Columns:  Internet Sales
2. Rows:  Calendar Years
3. Filter:  Only Show United States Bike Sales

Now, above this statement in your query window you can use the CREATE MEMBER or WITH MEMBER statement to create a calculated member to be used in the query.  In this puzzle I will be using the CREATE MEMBER statement.  I will use the WITH MEMBER statement in later puzzles.  The CREATE MEMBER statement defines a calculated member that is available throughout the session and can be used by multiple queries within the session.  This statement will be used to create the YearlyGrowth calculated member, which will be added to the above query.  Here is the statement:

You should note that a CASE statement is used in the calculation.  There are three conditions in the case.  The first condition will ensure that the query avoids a divide-by-zero error by checking the previous years sales.  If the previous years sales is empty then the calculation is not performed and the value retuned is NULL.  The second condition checks to see if the sales for the current year is empty.  As with the previous year, if the current year is empty a NULL value is returned.  Finally, if neither condition is met the calculation is performed. 

There are also a few new functions that must be explained.  The first is IsEmpty.  The IsEmpty function evaluates whether or not a cell value is empty.  The next function is PrevMember.  The PrevMember function returns the previous member in the level that contains the specified member.  In the example, I use it to return the previous years sales.  Finally, FORMAT_STRING, which not a function, but an option for the CREATE MEMBER statement, is used to specify how the returned value should be formatted.

If you couple these two statements in the same query window, running the create member statement first then the query, you will completely satisfy the requirements of the query.  Here is the solution:

CREATE MEMBER [Adventure Works].Measures.YearlyGrowth 

AS 

'

CASE 

When IsEmpty

(

    ([Internet Sales Amount], [Date].[Calendar Year].PrevMember)

)

Then Null

When IsEmpty

(

    [Internet Sales Amount]

)

Then Null

 ELSE

(

    (

        ([Internet Sales Amount])-([Internet Sales Amount], [Date].[Calendar Year].PrevMember)

    )

    /([Internet Sales Amount], [Date].[Calendar Year].PrevMember)

)

END',

FORMAT_STRING='Percent';

SELECT 

    NON EMPTY{[Measures].[Internet Sales Amount], YearlyGrowth

    } ON COLUMNS,

    NON EMPTY(

         [Date].[Calendar].[Calendar Year].Members) ON ROWS

FROM [Adventure Works]

WHERE

    (

        [Sales Territory].[Sales Territory].[Country].&[United States],

        [Product].[Category].[Bikes]

    );

In the above query I added the calculated member, Yearly Growth, to the list of columns.  If you need to update the calculated member, simply change the CREATE to an UPDATE.  Then run the statement and finally rerun your query.  Unlike T-SQL, the two MDX statements must be run separately.  Stay tuned for Puzzle #3. 

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.