Zahid's blog on Data Engineering and BI

 

This is a simple example of SCD (Type-2) in OLAP cube.  We have a Dimension table for Employee and their Departments. This dimension is an SCD. One employee worked in different department over the course of time. EmpID is business key and SID is surrogate key.

DimEmpolyee table

We have another dimension called DimTime. It’s a very simple Month Dimension table.

Fact table looks like this:

Both business key (EmpID) and Surrogate key (EmpSID) is present in Fact table, but we will use only EmpSID key here.

Let’s have a look at the cube now. First, look at the Dimension called [Dim Employee]

And it’s attribute relationship…..

See carefully that I did not use SID in hierarchy. I used employee name (attribute called ‘Employee’) for reporting purpose. Its key column and name column both are set to Employee column of the physical dimension table.

We are almost done. Let’s process the cube and write an MDX to see result:

 

You can clearly see that an employee named David sold different amounts in different months. David sold under Product Development Department for up to Aug-2013 and then he started selling under Android Apps department from Sep-2013.

Hope you will like it.