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_IDCustomer_NameCustomer_Type
    1Cust_1Corporate


    After the change: 
    Customer_IDCustomer_NameCustomer_Type
    1Cust_1Retail


    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_IDCustomer_NameCustomer_TypeStart_DateEnd_DateCurrent_Flag
    1Cust_1Corporate22-07-201031-12-9999Y


    After the change: 
    Customer_IDCustomer_NameCustomer_TypeStart_DateEnd_DateCurrent_Flag
    1Cust_1Corporate22-07-201017-05-2012N
    2Cust_1Retail18-05-201231-12-9999Y


    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_IDCustomer_NameCurrent_TypePrevious_Type
    1Cust_1CorporateCorporate


    After the change: 
    Customer_IDCustomer_NameCurrent_TypePrevious_Type
    1Cust_1RetailCorporate

    Comments

    Post a Comment

    Popular posts from this blog

    Increase Java Memory For Pentaho Data Integration

    Pentaho runs inside a Java virtual machine, and hence is bound by the properties of that VM. These optimisations can apply to just about any Java application, including the Pentaho BI Server and GUI tools. Method to increase memory allocation: Open the file spoon.sh or spoon.bat in a text editor Look for a section that looks like this: # ****************************************************************** # ** Set java runtime options                                     ** # ** Change 256m to higher values in case you run out of memory.  ** # ****************************************************************** OPT="-Xmx256m -cp $CLASSPATH -Djava.library.path=$LIBPATH -DKETTLE_HOME=$KETTLE_HOME -DKETTLE_REPOSITORY=$KETTLE_REPOSITORY -DKETTLE_USER=$KETTLE_USER -DKETTLE_PASSWORD=$KETTLE_PASSWORD -DKETTLE_PLUGIN_PACKAGES=$KETTLE_PLUGIN_PACKAGES" Change the -Xmx parameter to alter the maximum heap size, e.g. -Xmx1024m
    Read more

    Different Match Models in tMap with example

    Unique Match:     (Also called Last match) If several matches are found in the Inner Join, i.e. the last row matching the explicit join as well as the filter will be added to the output flow. First Match: If several matches are found in the Inner Join, i.e. the first row matching the explicit join as well as the filter will be added to the output flow. All Match: If several matches are found in the Inner Join, i.e. rows matching the explicit join as well as the filter, all of them will be added to the output flow Example: deptid   sales 1           1000 2           2000 3           3000 2           5000 3           6000 Outputs: 1. Unique match deptid   sales 1           1000 2           5000 3           6000 2. All matches deptid   sales 1           1000 2           2000 3           3000 2           5000 3           6000 3. First match deptid   sales 1           1000 2           2000 3           3000
    Read more

    Simple Transformation from a csv to Excel Output

    Image
    Lets start with a basic transformation to convert your comma seperated values file input to excel output Step 1: Launch kettle 4.1 Step 2: select file>new>transformation Step 3: drag input>csv input & output>csv output on the workspace Step 4: Arrange them one after another & create a hop/connection between them by holding the shift key & dragging the arrow from center of csv input step to excel output step Step 5 : double click the csv input step & configure as shown in the figure above. Choose the filenae location & select get fields button to auto retrieve fields. Step 6: double click the excel output step & give the desired path location of the new excel file which is to be created, click the fields tab & select the get fields button to retrieve all the fields currently present in the stream. Step 7: Click the run button above the workspace else Select --> Action>Run from the menu. Your file is automatically crea
    Read more