in

Pragmatic Works

Enabling your business intelligence enterprise.

This Blog

Syndication

Tags

Darren Herbold

To Type 2 or Not Type 2?

If you have ever worked on a Data Warehousing project, specifically with being tasked in developing
and loading (ETL) a dimensional model, you will have no doubt encountered the decision to implement
a dimensional type architecture. Basically, you have 2 choices (3 really, but I won't go there) when
deciding on a dimension type in your warehouse. With Type 1, you basically get no history. 
For example, consider a Customer dimension called DimCustomer. This dimension may contain address
information. With Type 1, you would never know if a particular customer had changed addresses.
Is this important?  Maybe it is and maybe it is not. It's up to your business analysts to decide.
Of course you will always add new customers to the table as they arrive, but no updates, because
that's Type 1.  

Much to your dismay, the business analysts have decided that they want to track the changing
address attribute of your DimCustomer table. This is where Type 2 comes in. It keeps track of
history. Unfortunately, implementing this type of dimension is a bit more work. Since you are now
tracking history for this dimension, you will need to add 2 date attributes that will define a range
of time when the row of data has changed. A good example of the names for these attributes
would be EffectiveDate and ExpiredDate. You will also need another attribute that will "flag" the
row as the most current. This will of course, make your ETL more complicated from a Dimension
and Fact table load.

So, when deciding between these two dimensions types, the answer lies in the business question.
Is it, for example, important to your decision makers to track a customer's address change between
business transactions? If so, use a Type 2, if not, then Type 1.

Comments

No Comments
Copyright Pragmatic Works
Powered by Community Server (Non-Commercial Edition), by Telligent Systems