Author Topic: About Slowly Changing Dimensions  (Read 155 times)

0 Members and 1 Guest are viewing this topic.

Offline CyborgRepublic

  • Full Member
  • *
  • Posts: 203
  • Karma: +0/-0
    • View Profile
About Slowly Changing Dimensions
« on: February 16, 2016, 07:00:25 AM »
Slowly Changing Dimensions

Slowly Changing Dimensions are dimensions that change slowly over time, rather than changing on regular schedule, time-base. In Data Warehouse there is a need to track changes in dimension attributes in order to report historical data. In other words, implementing one of the SCD types should enable users assigning proper dimension's attribute value for given date. Example of such dimensions could be: customer, geography, employee.

There are many approaches how to deal with SCD. The most popular are:


Type 0 - The passive method
Type 1 - Overwriting the old value
Type 2 - Creating a new additional record
Type 3 - Adding a new column

Type 0 - The passive method. In this method no special action is performed upon dimensional changes. Some dimension data can remain the same as it was first time inserted, others may be overwritten.

Type 1 - Overwriting the old value. In this method no history of dimension changes is kept in the database. The old dimension value is simply overwritten be the new one. This type is easy to maintain and is often use for data which changes are caused by processing corrections(e.g. removal special characters, correcting spelling errors).

Before the change:
Customer_ID   Customer_Name   Customer_Type
1   Cust_1   Corporate


After the change:
Customer_ID   Customer_Name   Customer_Type
1   Cust_1   Retail


Type 2 - Creating a new additional record. In this methodology all history of dimension changes is kept in the database. You capture attribute change by adding a new row with a new surrogate key to the dimension table. Both the prior and new rows contain as attributes the natural key(or other durable identifier). Also 'effective date' and 'current indicator' columns are used in this method. There could be only one record with current indicator set to 'Y'. For 'effective date' columns, i.e. start_date and end_date, the end_date for current record usually is set to value 9999-12-31. Introducing changes to the dimensional model in type 2 could be very expensive database operation so it is not recommended to use it in dimensions where a new attribute could be added in the future.

Before the change:
Customer_ID   Customer_Name   Customer_Type   Start_Date   End_Date   Current_Flag
1                     Cust_1            Corporate           22-07-2010   31-12-9999    Y


After the change:
Customer_ID   Customer_Name   Customer_Type   Start_Date           End_Date   Current_Flag
1                   Cust_1           Corporate           22-07-2010           17-05-2012         N
2                   Cust_1           Retail             18-05-2012            31-12-9999   Y


Type 3 - Adding a new column. In this type usually only the current and previous value of dimension is kept in the database. The new value is loaded into 'current/new' column and the old one into 'old/previous' column. Generally speaking the history is limited to the number of column created for storing historical data. This is the least commonly needed techinque.

Before the change:
Customer_ID   Customer_Name   Current_Type   Previous_Type
1                      Cust_1           Corporate            Corporate


After the change:
Customer_ID   Customer_Name   Current_Type   Previous_Type
1                    Cust_1               Retail            Corporate

Techronnati | where technology never sleeps

About Slowly Changing Dimensions
« on: February 16, 2016, 07:00:25 AM »

Mountain View

Offline Corps

  • Sr. Member
  • *
  • Posts: 276
  • Karma: +0/-0
    • View Profile
Re: About Slowly Changing Dimensions
« Reply #1 on: July 14, 2016, 05:15:11 AM »
When you talk about Slowly Changing Dimensions, ano usually ang mga pwedeng gawing dimension?


Offline Corps

  • Sr. Member
  • *
  • Posts: 276
  • Karma: +0/-0
    • View Profile
Re: About Slowly Changing Dimensions
« Reply #2 on: July 14, 2016, 05:16:14 AM »
Just to add, How would you know if a certain field can be candidate as slowly changing dimension?

 

Related Topics

  Subject / Started by Replies Last post
0 Replies
132 Views
Last post July 28, 2008, 02:08:26 AM
by rpmolecule
0 Replies
56 Views
Last post March 20, 2015, 07:20:32 AM
by JusticeLeague

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.