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.


5 Comments on “Slowly Changing Dimension Type 2 in SSAS Dimension”

  1. I visit each day a few web pages and sites to read posts, but this
    web site provides quality based articles.

  2. Rudolph says:

    Howdy! This post couldn’t be written any better! Reading through this post reminds me of my old room mate! He always kept chatting about this. I will forward this write-up to him. Fairly certain he will have a good read. Thanks for sharing!

  3. Brittney says:

    Heya i’m for the first time here. I came across this board and I find It truly useful & it helped me out a lot. I hope to provide one thing again and aid others such as you aided me.

  4. You made some decent points there. I checked on the internet to
    find out more about the issue and found most people will go along with your views on this web site.

  5. friends site says:

    What’s up colleagues, its wonderful paragraph about teachingand entirely explained, keep it up all the time.

What do you think?.. Let me know...

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s