SSAS Server based Time Dimension

For any SSAS implementation, time dimension plays an important role. Different architects take different approach on this. Some of them decide to create their own physical time dimension table and populate it accordingly. Some architects use the SSAS Server based time dimension. SSAS again provides two options namely:

  • A table for time dimension is created in the data source
  • A table for time dimension is created on the server

Let’s see the impact of using server based time dimension and then you would be able to make a call about which way to go. Following are the points that need consideration:

  1. Relational database contains all fact tables and dimension tables, except a time dimension table, which is implemented in an OLAP layer. So the data model is split between data source and OLAP layers. If relational data warehouse database needs to be exposed to other applications for some reporting, custom rollup by time periods would be difficult to implement.
  2. With server based time dimension, the keys at different levels of a hierarchy are not unique, which present different problems while querying the AS database. Let’s quickly look at this scenario.

    This is the fact table, where reservation date is the data the booking is made and check_in_date is the date when the quest arrived. The column sales is used as a measure.


    The requirement is to create a report which displays the sales for all months, where the month of reservation date and month of check in date are same.
    I have populated the table as follows:


    If we were to use the SQL, then following SQL would give us the required results:


    With MDX, this was again a simple query. In my cube, I have used server based time dimension. Let’s see the results:

    WITH

    MEMBER [MEASURES].[SALES FOR SAME MONTH RESERVATION] AS

    (LINKMEMBER([TIME].[YQMD].CURRENTMEMBER,[CHECK IN].[YQMD]),[MEASURES].[SALES])

    // Displaying the check in date member name to ensure, correct linking

    MEMBER [MEASURES].[CHECK IN DATE] AS

    LINKMEMBER([TIME].[YQMD].CURRENTMEMBER,[CHECK IN].[YQMD]).MEMBER_NAME

    SELECT

    {MEASURES.[CHECK IN DATE],[MEASURES].[SALES FOR SAME MONTH RESERVATION]}

    ON
    COLUMNS,

    YTD([TIME].[YQMD].[MONTH].&[2011-04-01T00:00:00])

    ON
    ROWS

    FROM [TEST CUBE]


    Notice that for January, 2011 the check in date member is at returned as Calendar Year 2011. Also for April, it returned member at Quarter level. Why is this so?

    The reason for this is server based time dimension, uses the same key at different levels as shown below. Thus link member links it to wrong member.

Member

Key

Calendar 2011

1-Jan-2011

Quarter 1, 2011

1-Jan-2011

January 2011

1-Jan-2011

1-Jan-2011

1-Jan-2011

To fix it, we need to change the MDX a little as follows:

WITH

MEMBER [MEASURES].[SALES FOR SAME MONTH RESERVATION] AS

(iif (

    linkmember([TIME].[YQMD].CURRENTMEMBER

    ,[CHECK IN].[YQMD]).level.ordinal = 1,

        linkmember([TIME].[YQMD].CURRENTMEMBER

        ,[CHECK IN].[YQMD]).firstchild.firstchild,

        iif (

            linkmember([TIME].[YQMD].CURRENTMEMBER

            ,[CHECK IN].[YQMD]).level.ordinal = 2,

                linkmember([TIME].[YQMD].CURRENTMEMBER

                ,[CHECK IN].[YQMD]).firstchild,

                linkmember([TIME].[YQMD].CURRENTMEMBER

                ,[CHECK IN].[YQMD])

            )

    ),[MEASURES].[SALES])

MEMBER [MEASURES].[CHECK IN DATE] AS

iif (

    linkmember([TIME].[YQMD].CURRENTMEMBER

    ,[CHECK IN].[YQMD]).level.ordinal = 1,

        linkmember([TIME].[YQMD].CURRENTMEMBER

        ,[CHECK IN].[YQMD]).firstchild.firstchild,

        iif (

            linkmember([TIME].[YQMD].CURRENTMEMBER

            ,[CHECK IN].[YQMD]).level.ordinal = 2,

                linkmember([TIME].[YQMD].CURRENTMEMBER

                ,[CHECK IN].[YQMD]).firstchild,

                linkmember([TIME].[YQMD].CURRENTMEMBER

                ,[CHECK IN].[YQMD])

            )

    ).MEMBER_NAME

SELECT

{MEASURES.[CHECK IN DATE],[MEASURES].[SALES FOR SAME MONTH RESERVATION]} ON
COLUMNS,

YTD([TIME].[YQMD].[MONTH].&[2011-04-01T00:00:00]) ON
ROWS

      FROM [TEST CUBE]

3. If you notice, the month names are being displayed as “January 2011”. Usually the month names are displayed as Jan-11 in reports. If that              is required, you have to change the label expression in all those reports probably as follows:

      =Left(Fields!Month.Value,3)+”-“+Right(Fields!Month.Value,2)

The bottom line is, we do not have much control on server based time dimension and we cannot customize it.

With all these issues (if you may call it), I would think, server based time dimension in SSAS can be used in following scenarios:

  • We have no control on the data sources at all.
  • We are already in production, and we cannot do much about it.
  • Or by the virtue of UDM, your data source is some OLTP system. For our data warehouse database, we would not be allowed to change the OLTP system in anyway.

Please feel free to provide feedback.