Usage Question: Using Variables for SCD2 Date Handling

Feb 12, 2009 at 8:15 PM
fred_mumble asked:

"Is there a way to use variables for Type 2 date handling?"



Yes - this answer applies to v1.3 and higher of the Kimball Method SCD component.

Due to sometimes unavoidable circumstances, dimension table processing does not always occur on the same day as the change took place.  This is always the case when loading a dimension table with historic data, and can often be the case if unforseen problems occur in daily loads that prevent a same-day load.

The Kimball Method SCD component allows specification of a variable to use to identify the "load date" of an SCD2 change.  This variable is then used in a configurable manner to set the "effective date" of New records, and the "expiry date" of SCD2 Expired records.  In order to take advantage of this feature, you must:
1. Ensure that the Existing Dimension table includes your two SCD2 "control" columns (dates).
2. Edit the Kimball Method SCD component through the editor to identify those columns on the Existing Dimension Column Usage tab as "SCD2 Effective Date" and "SCD2 Expiry Date".  (Only date-type columns may be identified with these usages.)
3. Select the "Options" tab in the editor and scroll down to the "Date Handling for SCD Type 2" section.
    a) Pull down the variable list to identify the "variable containing today's date" (your "loading date")
    b) Alter the meaning of this date by selecting one of these three choices:
        i) "Old Ends Yesterday New Starts Today" -> New records will be stamped with the value in the identified variable in their "effective" date column.  SCD2 Expired records will be stamped with a value one day prior to the value in the identified variable in their "expiry" date column.
        ii) "Old Ends Two Days Ago New Starts Yesterday" -> New records will be stamped with a value one day prior to the value in the identified variable in their "effective" date column.  SCD2 Expired records will be stamped with a value two days prior to the value in the identified variable in their "expiry" date column.
        iii) "Old Ends Today New Starts Tomorrow" -> New records will be stamped with a value one day after to the value in the identified variable in their "effective" date column.  SCD2 Expired records will be stamped with the value in the identified variable in their "expiry" date column.
    c) Instruct the component what to set the "first" version of a member's "effective" date to by selecting one of the choices in the "first record's effective date is" list.
    d) Instruct the component what to set the "last" version of a member's "expiry" date to by selecting one of the choices in the "last record's expiry date is" list.

The net result is that you can use a custom date to drive the "effective" and "expiry" dates for SCD2 handling, as well as populate the effective date of your first record or the expiry date of your last record with NULL or a date of your choice.