Slowly Changing Dimension Type 2 in SSAS DimensionPosted: May 23, 2011
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
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.