Comments

Sunday, October 28, 2012

Data Warehousing Object: Dimension Tables Types

Posted by on Sunday, October 28, 2012 Read our previous post
We had our last post on Fact Tables in Data Warehousing. This post is on Dimension Table Types. Along with Fact Tables there are many types of Dimension Tables which are used in Data Warehousing.

Dimension Tables are those tables which contain attributes that helps in describing the facts of the fact table.

The following are types of Dimension Tables:

* Slowly Changing Dimensions
* Junk Dimensions
* Confirmed Dimensions
* Degenerated Dimensions



Slowly Changing Dimensions (SCD)



Various data in the dimensions undergo changes which is needed to be captured fr analysis. In short, what we need is to maintain a track of changes in the data elements so that we can analyse the changes and come to a conclusion.

SCD is one of the common problems for Data Warehousing.

Example:-

We have so many McDonalds in Mumbai, all have there different Store_ID, Region_ID. And if one of the McDonald changes its location from A to B then that is to be tracked and the change has to be preserved, to maintain a history of that particular McDonald (store_ID) region change.


Degenerated Dimensions



Its a dimension table which is derived from a Fact table and doesn't have its own dimension table.

Degenerated Dimensions are  often used when a fact tables's grainularity  represents transactional level data and it wished to have one of the system, generated flags/identifier, example:- Invoice number, order number etc.


Confirmed Dimensions



Confirmed Dimension is that dimension which is fixed and reusable, therefore it is also called a fixed dimension. Unlike SCD it is a dimension that doesn't gets affected w.r.t. to time.

Example:-, When the name of Girl changes after marriage. The name won't change from Time to Time, once its changed its fixed. This change is permanent.


Junk Dimensions



A dimension where one can store random flags, data, attributes etc that are not related to dimensions and which provide a simple way to find those unrelated attributes.

Example:-

Martial Status: (Yes or No)
Gender : (M or F) etc.

© 2010 Code 2 Learn