MDX: Order of Named Sets and Calculated Members

Just before the end of 2013, I started a new project doing some heavy MDX development. During a recent code-review session with the rest of the team, one of the developers brought up an issue with one of the queries where a named set was referencing another named set before the second named set had been defined in the code.

Here’s an example using AdventureWorks:

WITH
    MEMBER CustomerAge AS
        DATEDIFF(
             "yyyy"
            ,[Customer].[Customer].CurrentMember.Properties( "Birth Date" )
            ,CDate(Now())
        )

    SET Customers_Female35to45 AS
        FILTER(
              Customers_35to45
             ,[Customer].[Gender].CurrentMember IS [Customer].[Gender].&[F]
        )

    SET Customers_35to45 AS
        FILTER(
              [Customer].[Customer].[Customer]
             ,CustomerAge >= 35 AND CustomerAge <= 45
        )

    MEMBER NumberOfCustomers AS
        [Customer].[Customer].[Customer].Count

    MEMBER NumberOfCustomers_35to45 AS
        Customers_35to45.Count

    MEMBER NumberOfCustomers_Female35to45 AS
        Customers_Female35to45.Count

SELECT     
    {
         NumberOfCustomers
        ,NumberOfCustomers_35to45
        ,NumberOfCustomers_Female35to45
    } ON 0
FROM    
    [Adventure Works]

Question

In the snippet above, Customers_Female35to45 is defined first and Customers_35to45 is defined second. It was suggested that this was a bug and that Customers_35to45 should appear first in the code since it was being referenced by Customers_Female35to45. However, the code passed QA.

How can this be? Doesn’t Customers_35to45 need to come first since it is referenced by Customers_Female35to45? Maybe QA is broken

Answer

When it comes to query scoped named sets, the order in which the appear in the code does not matter – precedence is determined by the SSAS engine at execution time.

The same logic holds true for calculated members. However, with calculated members we can use the SOLVE_ORDER property to control the precedence of overlapping calculations.

Update: see the first comment from Chris Webb pointing out that “forward references” can affect performance.

Take Away

The following 3 queries are equivalent.

WITH

    MEMBER CustomerAge AS
        DATEDIFF(
             "yyyy"
            ,[Customer].[Customer].CurrentMember.Properties( "Birth Date" )
            ,CDate(Now())
        )

    SET Customers_35to45 AS
        FILTER(
              [Customer].[Customer].[Customer]
             ,CustomerAge >= 35 AND CustomerAge <= 45
        )

    SET Customers_Female35to45 AS
        FILTER(
              Customers_35to45
             ,[Customer].[Gender].CurrentMember IS [Customer].[Gender].&[F]
        )

    MEMBER NumberOfCustomers AS
        [Customer].[Customer].[Customer].Count

    MEMBER NumberOfCustomers_35to45 AS
        Customers_35to45.Count

    MEMBER NumberOfCustomers_Female35to45 AS
        Customers_Female35to45.Count

SELECT     
    {
         NumberOfCustomers
        ,NumberOfCustomers_35to45
        ,NumberOfCustomers_Female35to45
    } ON 0
FROM    
    [Adventure Works]

WITH
    MEMBER CustomerAge AS
        DATEDIFF(
             "yyyy"
            ,[Customer].[Customer].CurrentMember.Properties( "Birth Date" )
            ,CDate(Now())
        )

    SET Customers_Female35to45 AS
        FILTER(
              Customers_35to45
             ,[Customer].[Gender].CurrentMember IS [Customer].[Gender].&[F]
        )

    SET Customers_35to45 AS
        FILTER(
              [Customer].[Customer].[Customer]
             ,CustomerAge >= 35 AND CustomerAge <= 45
        )

    MEMBER NumberOfCustomers AS
        [Customer].[Customer].[Customer].Count

    MEMBER NumberOfCustomers_35to45 AS
        Customers_35to45.Count

    MEMBER NumberOfCustomers_Female35to45 AS
        Customers_Female35to45.Count

SELECT     
    {
         NumberOfCustomers
        ,NumberOfCustomers_35to45
        ,NumberOfCustomers_Female35to45
    } ON 0
FROM    
    [Adventure Works]

 

Notice in this last one (below), the calculated member (CustomerAge) used to define the Customer_35to45 set appears after both named sets have been defined. Yep – still works 😉

WITH
    SET Customers_Female35to45 AS
        FILTER(
              Customers_35to45
             ,[Customer].[Gender].CurrentMember IS [Customer].[Gender].&[F]
        )

    SET Customers_35to45 AS
        FILTER(
              [Customer].[Customer].[Customer]
             ,CustomerAge >= 35 AND CustomerAge <= 45
        )

    MEMBER CustomerAge AS
        DATEDIFF(
             "yyyy"
            ,[Customer].[Customer].CurrentMember.Properties( "Birth Date" )
            ,CDate(Now())
        )

    MEMBER NumberOfCustomers AS
        [Customer].[Customer].[Customer].Count

    MEMBER NumberOfCustomers_35to45 AS
        Customers_35to45.Count

    MEMBER NumberOfCustomers_Female35to45 AS
        Customers_Female35to45.Count

SELECT     
    {
         NumberOfCustomers
        ,NumberOfCustomers_35to45
        ,NumberOfCustomers_Female35to45
    } ON 0
FROM    
    [Adventure Works]

For the life of me I cannot recall where I learned this – but I’m guessing it was some where in MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase