MDX #28– Time functions will not work with subquery

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]

    )

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.

MDX #27–Who are Ascendants?

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.  

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.

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.

MDX #26 – SSN can only be referenced as a member property in MDX

I’ve blogged about the Properties()  function before.

MDX #11 – How to get number of cars each customer owned using Properties() function?

MDX #12 – Do not forget the TYPED flag in the Properties() function 

The Properties()  function is used to explore the attribute relationships in a dimension.

If an attribute is also enabled as an attribute hierarchy, then of cause, we can also reference it as Attribute Hierarchy in MDX.

AttributeHierarchyEnabled = False

In the Employee dimension in the Adventure Works cube, employees’ SSN numbers is not enabled as an Attribute Hierarchy. Its property AttributeHierarchyEnabled is set to False.

Here are what will happen in SSAS, in MDX, and in client applications when AttributeHierarchyEnabled is set to False.

In SSAS: A disabled attribute hierarchy cannot be be used as a level in a user defined hierarchy

However, it can still be used to order the members of another attribute.

In MDX: 1) A disabled attribute hierarchy cannot be referenced as Attribute Hierarchy

This query referenced the SSN as attribute hierarchy, and no result is returned.

In MDX: 2) A disabled attribute hierarchy can only be referenced as a member property

This query referenced SSN as the property of current member of the employee. It shows the correct SSN for each employee.

In Client App: 1) Will not be used for browsing in client application

In Client App: 2) Will only be visible to client applications as a member property

In SSAS: still makes sense to order and optimize the disabled attribute

Because the members of these attribute hierarchies are still used for dimensioning fact data, ordering and optimizing the members of these attribute hierarchies can still improve performance.

Therefore, the following properties of these attributes can still be enabled.

• AttributeHierarchyVisible = True: (the attribute will be visible to the attribute hierarchies list in client applications)
• IsAggregatable = True: (it will still have the All top level member)
• AttributeHierarchyOptimizedState = FullyOptimized
• AttributeHierarchyOrdered = True
• GroupingBehavior = EncourageGrouping

MDX #25 – Slicer or Sub-Cube?

Slicer, Axes and Calculations Can All Filter Data in MDX

Every developer with SQL background knows how the WHERE clause works. You put some condition in the WHERE clause, such as TheDate = Yesterday (pseudo code), and it will only return data for yesterday.

Not so straightforward in MDX. We should expect more complex behaviors in MDX because of the multi-dimensional nature of the cubes.

But how different it can be.

There are many topics to explore, including why we prefer to call the WHERE clause slicer in MDX, how tuple is constructed, when default member is used, how slicer (WHERE clause if you insist) can be used to limit data, and how axes can be used, how the MDX engine decides when to use slicer, axes, and calculation formula to limit the data, etc..

Sub-Select Can Filter Data Too

In this blog, I’ll explore just one aspect of how we can use both the slicer and a sub-cube to limit the data, and where they are the same, and where they might give you different results.

Both Slicer and Sub-Select Produces the Same Result

Run these two queries (separating by GO), you get the same Internet Sales Amount from both queries, for July 1, 2008.
So our conclusion so far is that slicer and sub-select should give same results.

If you make such statement to some MDX experts, they will tell you that results from using a member in the slicer can be different from using the same member in a sub-select.

So what can be different?

While Sub-Select does not change the query context, the slicer does

Run this query pair. We are using the currentmember function to show what date we are currently at. In MDX’s term, we are checking the current member of the Date hierarchy in the query context (quite mouthful).

It turned out that the “current date” from the two queries is not the same. The first one with slicer says we are currently at just one day, July 1, 2008. The second one with the sub-select says we are actually currently at All Periods (all days in the entire Date dimension; the root member of the Date dimension).

Now we know that the query context (where we currently at) is different, depending on where we are putting our member, in the slicer or in the sub-select. The sub-select does nothing to change the query context, while the slicer changes the context according to the tuple (in the above example we only have one member in the tuple) we put in the slicer.

You might already figure out that we need to be careful now about the context.

When Sub-Select and Slicer Might Give Different Results

Here is an example where your filtered result might not be exactly what you want if you are using the sub-select.

When using the currentmember as a filter, You should expect the sub-select will give you the Internet Sales Amount for the entire date range in your Date dimension, not just from one day, July 1, 2008.

Confusing? Yes, it is. But no complaining?
 
For my other MDX blogs, visit http://bisherryli.wordpress.com/category/mdx-challenges/

MDX #24 – Leave out a dimension member using Except() Function

Functions such as NonEmpty(), Filter() and Exists() must use a numeric expression

Many of the MDX functions, such as NonEmpty(), Filter() and Exists(), seem to be easy enough to use, but they present quite a challenge for people with T-SQL background. The common theme among them is that they all claim to return dimension members, such as a list (or a set of tuples in MDX lingo) of countries, but they must use a fact measure (a numeric expression in another word) as the condition, such as the sales amount must be more than $10K. We are just not used to using fact measures.

Check out this post for an example of the NonEmpty() function.

MDX #1 – Why should we always provide set_expression2 in NonEmpty() function

Use Except() function to leave out a dimension member

In many situations, we just simply want to leave out a dimension member, such as one of the Product Categories, no matter what the sales amount is.

Run this simple query, and you will get the Order Quantity for all the Product Categories for each month.

SELECT    [Date].[Month of Year].Children  ON COLUMNS,
               [Product].[Product Categories].[All].Children ON ROWS
FROM      [Adventure Works]
WHERE   ([Measures].[Order Quantity])

What if I simply just want to leave out the Components in my query? Components are not the “final” products. Clothing, Bikes and Accessories are. So I have quite a good reason to leave it out of the query.

Now run this query with the help of the Except() function. Components is no longer in the result.

SELECT    [Date].[Month of Year].Children  ON COLUMNS,
       Except(
            [Product].[Product Categories].[All].Children ,
            [Product].[Product Categories].[Components]
        ) ON ROWS
FROM    [Adventure Works]
WHERE   ([Measures].[Order Quantity])

The two sets must be from the same hierarchy and at the same level

The basic syntax of the Except() function is:

Except(Set_Expression1, Set_Expression2)

It returns SET 1, minus the SET 2. Please make sure that both sets are:

1) from the same hierarchy, and

2) at the same level

If you want to leave out Brakes, the following query will not work, because the two sets in the Except() function is not at the same level.

(The following would not work.)

SELECT    [Date].[Month of Year].Children  ON COLUMNS,
        Except(
           [Product].[Product Categories].[All].Children ,
            [Product].[Product Categories].[Subcategory].[Brakes]
        ) ON ROWS
FROM    [Adventure Works]
WHERE   ([Measures].[Order Quantity])

This query should work because both sets are at the Subcategory level.

SELECT    [Date].[Month of Year].Children  ON COLUMNS,
        Except(
            [Product].[Product Categories].[Subcategory].members ,
           [Product].[Product Categories].[Subcategory].[Brakes]
        ) ON ROWS
FROM    [Adventure Works]
WHERE   ([Measures].[Order Quantity])

The Except() function does not involve any numeric expressions

So hopefully, it’s easier for people to understand with T-SQL background.

SSRS #73 – Use Calculated Field to Dynamically Set Default for Parameters

Calculated Field has existed in Reporting Services since version 2005. SSRS 2005 did not have the most attractive user interface design, so this pretty useful feature stayed hidden from me until version 2008.

Even in SSRS 2008, I did not pay much attention to it until I started to write reports in MDX queries on SSAS cubes.

Once I started using them, I find myself like it more and more.

One of the challenges in SSRS reports is to set parameter defaults dynamically. It’s an even bigger challenge if you want to dynamically set parameter defaults to multiple values, such as (Select All). Don’t read this statement wrong. Setting parameter defaults to multiple values, such as (Select All), is not hard. What is hard is to do it dynamically, meaning the situation where you want to set the default or not depends on the selection of another parameter.

I recently ran into this issue when working on a Data-Driven Subscription project. The idea is that we’d use just one SSRS report for both online ad-hoc reporting, and for a daily data extraction that can be sent to users via E-Mail.

For the online ad-hoc reporting, we certainly do not want to default all parameters to (Select All). For the daily data extraction though, that’s exactly what we’d like to do.

Since I am using Data-Driven Subscription and a SSIS package to trigger the subscription, you would think that I can manipulate the parameter defaults in either SQL code and/or in SSIS scripting. But I highly recommend against that if your goals are

1) To use just one SSRS report for both online ad-hoc reporting, and for a daily data extraction

2) Not to replicate all the queries you already have in the SSRS report

3) Not to waste your valuable time to wrestle with Reporting Services. When it comes to parameters with defaults or cascading parameters, Reporting Services is not very forgiving in terms of dynamic settings. If you have tried that before, you probably know what I meant.

Using Calculated Field feature in SSRS 2005 and above is what I’d recommend.

Suppose you have a hidden parameter, Subscription.

Parameter Subscription has two values, 1 and 0.

Its default is 0 (not the 1 shown).

When Subscription is 1, we want to set the default of parameter Geography to (Select All).

We’d create a Calculated Field for DataSet_Geography. Call it GeographyKey_Dynamic.

In the Expression Editor for GeographyKey_Dynamic, enter an IIF() expression. This expression will basically use the GeographyKey value if the Subscription is 1, otherwise, it will be Nothing.

Now we are ready to use this new Calculated Field GeographyKey_Dynamic as the default value for parameter Geography.

DataSet_Geography is the same dataset for Available Values. The only difference is that in Available Values, we’d always use GeographyKey, which is a SQL column. For the Default Values, we’d use GeographyKey_Dynamic instead.

Notes:

1) Watch out when you like the Calculated Fields too much and use them excessively. SSRS reports will typically go through Retrieval, Processing and Rendering phases during run time. If you have queried a Report Server database, you will find that the Processing and Rendering phases take more time than you’d like. Having too many Calculated Fields will no doubt increase the Processing time. So think about distributing the burden across queries on servers and calculations in memories.

2) For data-driven subscription, see my blog at

SSRS #60 – Steps to implement a data-driven subscription

MDX #23 – “Hello World!” Lesson in MDX

Almost every tool we learned has some sort of “Hello World!” tutorial lesson.

So here comes the “Hello World!” lesson in MDX.

Putting “Hello World!” directly on the X axis doesn’t work

You would think this query would work, by putting “Hello World!” directly on the X axis.

Unfortunately it doesn’t.

[Measures].[x] is a perfectly legitimate and unique name for a tuple

The X axis expects a tuple/set expression. Or put it simply that it expects something like this:

[A Dimension].[A Hierarchy].[A Member]

To save some typing, I am going straight to the one special dimension in any SSAS cube, that is, the [Measures] dimension. This special dimension also has only one hierarchy, which happens to have the same name, [Measures]. This save me time to type it twice.

To further save myself typing, I am going to use one letter x for the member. 

[Measures].[x] is a perfectly legitimate and unique name for a tuple. It’s a very rare chance that any cube designer would name any of the measures [x].

Do not use any existing member

If you try to use a measure that has already existed in the cube to represent “Hello World!”, you will get an error.

 

MDX for arithmetic calculation

If you are like me who writes so much SQL code every day that I never use the Calculator software on my PC for arithmetic calculation. SELECT 9999999 + 1 is a perfectly fine query and will return 10000000.

A bit more typing than in TSQL, but this works in MDX.

 

Even better, if you want to format it, go ahead use the format_string.

 

We can create a member on any dimension

[Measures] is not the only choice for a perfect “Hello World!” lesson. As a matter of fact, you can use any dimension in the cube, but it requires a bit more typing.

Let’s pick the Date dimension.

 

  

But don’t forget to pick a hierarchy

It didn’t work, because unlike the special [Measures] dimension, the [Date] has many hierarchies, including a dozen or so attribute hierarchies and a few user-defined hierarchies.

Let’s pick the user-defined hierarchy [Calendar].

 

 

If you want to summarize the “Hello World!” tutorial lesson in MDX, go right ahead!

For my other MDX blogs, visit http://bisherryli.wordpress.com/.