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.


Slowly Changing Dimension Type 2 in SSAS Dimension

In the world of BI, everybody must be familiar with slowly changing dimension. There are three types of changing dimensions namely Type 1 (where the attributes are overwritten), Type 2 (history is preserved) and Type 3(limited history is preserved in additional columns).

And when it comes to creating a SSAS dimension, we need to take care of few things like

  • attribute relationships and
  • how the hierarchies would behave if there are some
  • Performance

I will only discuss about SCD – II here in this blog.

Let’s look at the data in customer dimension. We have a SCD – II here.

We are tracking history by marital status. And for the sake of brevity, we will allow change in value only once.

Thus for any change in the value of “Married” column, would require a new record to be inserted with new value in ScdStartDate and ScdState would be marked as “C” for current. The previous record would be marked as “H” in the column ScdState column and end date would be stamped in ScdEndDate. See the following screenshot. Notice that Customer 6 has two records


Now, first we will see, how to set up the attribute relationships in this case. Following is the screenshot of how we setup the attribute relationship:


Here are the attribute which may change, are made related attributes of Dim Customer (Key Attribute). All other attributes which would not change are made related attributes of Customer business key. We are assuming, birth place of a customer would not change.

Let’s create a hierarchy State – City – Customer:


All is well so far. Let’s browse this dimension. We see following:


We see that customer 6 is repeating under the city MUMBAI. This is not what we want. As you have guessed, the solution is pretty simple and change the hierarchy level from Dim Customers (which would repeat for a record with change in marital status) to COD Customer (business key).

Here we go. We change the


Let’s browse once to see if every thing is perfect:


Yes everything is perfect. We are almost done with our dimension design. No wait !!!.

If you have noticed, in this design, we have create a un-natural user hierarchy.


This would affect the querying performance. If you consider real world examples, some dimensions are very huge. For instance, customer dimension for a store like Walmart.

Now let’s see how to fix this.

As a first step, we need to create an intermediate attribute between city and COD Customer. This can be done in DSV with a named calculation as follows:


After that bring in this attribute in the dimension and create a level in the hierarchy with this attribute between City and COD customer:


Let’s make this attribute invisible in the dimension. We do not want this level to show up in the hierarchy as well while browsing. So we need to make Customer – City level invisible. How do we do that, as there is no direct property available for that. To do that, change the name column of this attribute to “City” and use “HideMemberIf” property to “ParentName” as follows:


Let’s see how this comes up while browsing:


Now we can say that our job is done.