Welcome to EMC Consulting Blogs Sign in | Join | Help

Christian Wade's Blog

MDX Script: Current/Relative Period

There are differing approaches out there on how best to get a current period member in MDX.  It is often a requirement to show, for example, [Current Month] and [Current Month - 1] members side by side and then perform comparison calculations between the two.  This allows OLAP reports to dynamically refresh the numbers when a new period arrives rather than depending on the user changing the slicing/members displayed.

Here is a backup of an AS 2005 database that contains all the code in this post.  Just restore it and open using the File > Open > Analysis Services Database option.

Current period is not to be confused with “relative period”.  Relative period tells us the period relative to the one in current context.  For example, if we have the following Sales data by year,

              Sales
2004       100
2005       200
2006       300

We could cross join year with the relative year members to give us the following.

                                                      Sales
2004             Relative Year               100
2004             Relative Year - 1 
2005             Relative Year                200
2005             Relative Year - 1           100
2006             Relative Year                300
2006             Relative Year - 1           200
2007             Relative Year 
2007             Relative Year - 1           300

The terminology of “Relative” vs. “Current” will obviously vary between implementations, but I think this is the clearest way of differentiating the two types of functionality for the purposes of this post.

Current Period, on the other hand, gives us the following functionality.

                             Current Year             Current Year - 1
All Products            600                          500
Bikes                     100                          150
Cars                       200                          150
Caravans                300                           200

This simply saves the user having to change the year member when a new year arrives.  This might not seem like much to ask if it only has to be done once a year, but the same principle applies to Quarter, Month, Week, etc.  Having to change the Week member every Monday morning would be a little annoying.

I chose to take a similar approach to the time intelligence wizard in AS2005.  I am using MDX script against calculated members (so I don’t need to worry about all the possible permutations for aggregation up the hierarchy as this doesn’t apply to calculated members).  I personally think MDX script is very cool.  Granted it is more difficult to get your head round than the traditional approach of defining calculations in the member declaration, but there are often real performance benefits as illustrated by this post.

I have a Named Calculation column called “Date Calculations” in the date dimension table of my DSV and its value is hard coded to “Current Period”.  This gives us an attribute hierarchy in which to place our calculated members for time analysis.

It is a convention to use the Current Period member (which is the DefaultMember) in the MDX script assignments CrossJoined with other members that contribute to the assignment.  Although this is not always necessary, it ensures that we have the required dimensionality and it can avoid infinite recursion.

The current period calculations are quite simple MDX script assignments.  The Members( … ) function comes in useful for external functions (e.g. MDX sprocs) that return a string identifier for a given member.  I opted for the VBA approach as it is easy and works fine for my requirements.

For completeness, I have also included a “Relative Year - 1” calculation.  This is a simple approach that is similar to the AS 2000 implementation described by this article.

For a more sophisticated approach to relative period (and potentially more powerful depending on your requirements), see Chris Webb’s blog entry.  The advantage of doing it the way that Chris describes is that we have an attribute hierarchy for each level of relative period (e.g. year, quarter, month).  We can then group these into a user hierarchy.  This implementation is outside the scope of this post.  For my particular requirements, I chose to place all time calculations under the single [Time Calcs] attribute hierarchy.

 

Here is the code:

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Time].[Time Calcs].[Current Month]

AS Null ;

CREATE MEMBER CURRENTCUBE.[Time].[Time Calcs].[Current Month - 1]

AS Null ;

CREATE MEMBER CURRENTCUBE.[Time].[Time Calcs].[Current Year]

AS Null ;

CREATE MEMBER CURRENTCUBE.[Time].[Time Calcs].[Current Year - 1]

AS Null ;

CREATE MEMBER CURRENTCUBE.[Time].[Time Calcs].[Relative Year - 1]

AS Null ;

-- Current Month

  (

    [Time].[Time Calcs].[Current Month],

    {

        [Measures].[Consequence Count],

        [Measures].[Incident Count]

    }

  ) =

    Sum

    (

        { [Time].[Time Calcs].DefaultMember } *

        Members

        (

            "[Time].[Month].[" +

            VBA!Format(VBA!Now(), "MMM-yy") + "]"

        )

    );

-- Current Month - 1

  (

    [Time].[Time Calcs].[Current Month - 1],

    {

        [Measures].[Consequence Count],

        [Measures].[Incident Count]

    }

  ) =

    Sum

    (

        { [Time].[Time Calcs].DefaultMember } *

        Members

        (

            "[Time].[Month].[" +

            VBA!Format(VBA!DateAdd("m", -1, VBA!Now()), "MMM-yy") + "]"

        )

    );

-- Current Year

  (

    [Time].[Time Calcs].[Current Year],

    {

        [Measures].[Consequence Count],

        [Measures].[Incident Count]

    }

  ) =

    Sum

    (

        { [Time].[Time Calcs].DefaultMember } *

        Members

        (

            "[Time].[Year].[" +

            VBA!CStr(VBA!Year(VBA!Now())) + "]"

        )

    );

-- Current Year - 1

  (

    [Time].[Time Calcs].[Current Year - 1],

    {

        [Measures].[Consequence Count],

        [Measures].[Incident Count]

    }

  ) =

    Sum

    (

        { [Time].[Time Calcs].DefaultMember } *

        Members

        (

            "[Time].[Year].[" +

            VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())) - 1) + "]"

        )

    );

-- Relative Year - 1

  (

    [Time].[Time Calcs].[Relative Year - 1],

    {

        [Measures].[Consequence Count],

        [Measures].[Incident Count]

    }

  ) =

    Sum

    (

        { [Time].[Time Calcs].DefaultMember } *

        ParallelPeriod

        (

            [Time].[Calendar].[Year], 1, [Time].[Calendar].CurrentMember

        )

    );


We can now run the following 2 queries and they should return the same result; assuming it is still 2006 when you reading this post!

SELECT

 {

  ([Time].[Year].&[2006]),

  ([Time].[Year].&[2005])

 } ON 0,

 {

  [Measures].Members

 } ON 1

FROM

 [Current-Relative Date];

SELECT

 {

  ([Time].[Time Calcs].[Current Year]),

  ([Time].[Time Calcs].[Current Year - 1])

 } ON 0,

 {

  [Measures].Members

 } ON 1

FROM

 [Current-Relative Date];

Here is a query that illustrates relative period.

SELECT

 {

  ([Time].[Time Calcs].&[Current Period]),

  ([Time].[Time Calcs].[Relative Year - 1])

 } ON 0,

 {

  [Time].[Calendar].[Year].Members

  //[Time].[Calendar].[Quarter].Members (another option)

 } ON 1

FROM

 [Current-Relative Date];

 

 

Published Friday, June 23, 2006 12:14 PM by christian.wade
Attachment(s): Current-Relative Date.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(optional)
(required) 
Submit
Powered by Community Server (Personal Edition), by Telligent Systems