Wednesday, March 12, 2014

Growing Business Changing Data Changing Dimensions!!!

Hi All,

In the previous blog we discussed about the different type of keys such as Domain, Natural, Surrogate keys which will help us to retrieve data easily and will maintain data in a coherent manner.

The only thing constant in the world of Business is change, thus as Business grows the need to change becomes inevitable. In this Blog we will be discussing about different ways to accommodate change in Data in a DataWarehouse.

This concept is called as Slowly Changing Dimensions, therefore these are as follows:

a) Type 0 - Retain Original
  • Attributes that do not change, Example: Date dimension and Durable Supernatural Key

b) Type 1 - Overwrite
  • Update the dimension table row by overwriting
  • History is lost
  • Need to rebuild OLAP cubes


c) Type 2 – Add new row
  • Most Commonly used technique
  • New Row added to the table
  • Distinguishes using effective date columns
  • Requires use of Surrogate Keys
  • Fact table remains unaffected



d) Type 3 - Add New Attribute
  • Not used Commonly
  • Add new attribute to capture the previous value
  • Not useful for attributes that changes unpredictably
  • Useful when the change affects many rows and constitutes a big events e.g.: major re-organization 




e)  Type 4 - Add Mini-Dimension
  • Very large table with some frequently changing attributes
  • Break off the Frequently Changing dimensions into a separate dimension
  • Type 2 technique will lead to explosion of data in frequently changing dimensions




These are some of the techniques used in order to encounter changing dimensions and data problem.

With this i conclude my Fifth Blog on "Growing Business Changing Data Changing Dimensions!!!". Stay tuned to learn more about basics of Dimensional Modelling and Business Intelligence.

References:
The Data Warehouse Toolkit - Third Edition, Ralph Kimball and Margy Ross
MIS587_K&RChapter_05





No comments:

Post a Comment