Archive

Archive for March, 2013

MDX #28– Time functions will not work with subquery

March 22, 2013 Leave a comment

I had a post last month, MDX #25 – Slicer or Sub-Cube?, verifying that the query context did not change with the subquery.

This can pose problems if you want to use the Time-series functions in MDX. Two functions come into mind, YTD() and PeriodsToDate().

Both functions need to know which member on the Date dimension we are operating on.

PeriodsToDate and YTD function will not work with a subquery

Let’s try this query, which tests the PeriodsToDate function with a subquery. It will not work with the created member Reseller Sales YTD. Created member [x] is here to show what the current member on the Datedimension is.

–PeriodsToDate will not work with the subquery
WITH
MEMBER [Measures].[Reseller Sales YTD] AS
   Aggregate(
        PeriodsToDate(
                [Date].[Calendar].[Calendar Year],
                [Date].[Calendar].CurrentMember
                ),
              [Measures].[Reseller Sales Amount]
            )
MEMBER [Measures].[x] AS
    membertostr([Date].[Calendar].CurrentMember)
SELECT
   { [Measures].[Reseller Sales Amount],
     [Measures].[Reseller Sales YTD],
     [Measures].[x] } ON 0
FROM
   ( select   
        { [Date].[Calendar].[Month].[March 2008]:
          [Date].[Calendar].[Month].[April 2008]
        } on 0
     from    [Adventure Works]
    )

image

PeriodsToDate and YTD function will work with modification

The fault is not the subquery, rather it’s because we did not provide the query context.

We can modify the above query to provide the context by adding a member expression [Date].[Calendar].[Month].MEMBERS to the ROWS axis.

— PeriodsToDate works with [Date].[Calendar].[Month].MEMBERS on the ROWS axis
WITH
MEMBER [Measures].[Reseller Sales YTD] AS
   Aggregate(
        PeriodsToDate(
                [Date].[Calendar].[Calendar Year],
                [Date].[Calendar].CurrentMember
                ),
              [Measures].[Reseller Sales Amount]
            )
MEMBER [Measures].[x] AS
    membertostr([Date].[Calendar].CurrentMember)
SELECT
   { [Measures].[Reseller Sales Amount],
     [Measures].[Reseller Sales YTD],
     [Measures].[x] } ON 0,
   { [Date].[Calendar].[Month].MEMBERS } ON 1
FROM
   ( select   
        { [Date].[Month Name].[Month Name].[March 2008]:
          [Date].[Month Name].[Month Name].[April 2008]
        } on 0
     from    [Adventure Works]
    )

Our result is showing both months on the ROWS axis, and with the correct YTD reseller sales for each month.

image

MDX #27–Who are Ascendants?

March 2, 2013 Leave a comment

Many function names in MDX are very family-friendly. Children, parent, ancestors, descendants, ascendants, are all terms we often use in telling family stories. Except ascendants. I never really understand what this word means until I ran into this word in MDX.

Ancestors VS. Ascendants

I checked the www.thefreedictionary.com, and here are the definitions:

ascendant – someone from whom you are descended (but usually more remote than a grandparent).
ancestor – a person from whom another is directly descended, esp. someone more distant than a grandparent.

These two definitions are very close. Nothing really stands out for me to tell the difference.

Fortunately, in MDX, the difference between them is very easy to tell and also easy to demonstrate.

Ascendants() – returns all of the ancestors of a member in a set

That is, from the member itself up to the top of the member’s hierarchy. This is easy to see in this query.

SELECT
   Measures.[Reseller Order Count] ON COLUMNS,
   Ascendants(
        [Sales Territory].[Sales Territory].[Northwest]
   ) ON ROWS
FROM
   [Adventure Works]

In the [Sales Territory] dimension, Northwest is part of United States, which is part of North America. [All Sales Territories] is the root member and sits on top of all other members.  

image

With one Ascendants() function, we get all the ancestors, including the member [Northwest]itself and the most top level member, the [All Sales Territories].

Plural S returns set; singular returns member

Notice that the above query returns a set back. This set contains all the members in [Northwest]‘s family tree.

With the same logic, we can make a good guess that the function Ancestors() will return a set too, while the function Ancestor() will only return a member.

This logical thinking actually applies to other functions as well. Children() will return a set, while FirstChild() returns a member.

Ancestor() VS. Ancestors()

The word Ancestors is a plural, so you might think that it will return all the upward members in the hierarchy. It is true, sort of. It can return all the upward members in the hierarchy, but only one at a time.     

The following query can demonstrate this:

SELECT
   Measures.[Reseller Order Count] ON COLUMNS,
   Ancestors(
        [Sales Territory].[Sales Territory].[Northwest],
        1
   ) ON ROWS
FROM
   [Adventure Works]

We can only see United States because Ancestors() function can only return one specific ancestor at the specified level.

image

If you change the level number to 2, you should see only North America.

Again, the plural only means that it is returning a set; a one-member only set in this case.

Ancestor() and Ancestors() – they return one specific ascendant member at a time, at a specific level

If you change the above query to use the singular Ancestor() function, you will get the same result, only that it is a member, not a set.

%d bloggers like this: