Usage Instructions

Before you start attempting to use the Kimball Method SSIS Slowly Changing Dimension component or the SCD Wizard, please do your background learning on dimensional modeling and what a slowly changing dimension is. Here are some resources: If you happen to be familiar with the SCD Wizard's terminology and not Kimball's - all you need to know is that a "Changing" attribute is a Type 1, and a "Historical" attribute is a Type 2.

Overview

For a quick runthrough of how to use the component, watch the Overview video (on YouTube, as WMV).

Inputs

Existing Dimension

The flow you connect to this input should be from your data warehouse's dimension table. Use whatever connection manager and source component you wish - the input connection is a data flow, not a connection manager. You will want to provide all rows, and likely all columns from your dimension table to the component - especially the "housekeeping" columns, such as the SCD2 effective and expiry dates.

Source System

The flow you connect to this input should be from your source OLTP system. It's likely you will have transformed the information coming from that source somewhat, in order to generate the attributes you'll need to compare and either insert or update in your data warehouse's dimension table. The format of the source system flow should match the data types of the existing dimension flow.

Special Members

This flow is entirely optional, but provides a clean way of specifying and maintaining any possible "special" members in your data warehouse dimension table. In typical dimensions, there is an "unknown" member that you can use to link fact table rows to when the fact table row refers to a NULL or otherwise unknown dimension value. In some cases, you may have several of these artificial special members in your dimension.
It is completely acceptable to preload your dimension table manually with these members, and/or manually add them to your source system on every load. However, this method does result in those special members acting identically to any other source row when you may not want them to behave in that manner. Typically, all changes to special member rows should be treated as Type 1 - fixing a mistake. The reason for this is that your "unknown" member is "special" and always represents "unknown". If an attribute of an "unknown" member needs to be changed, it's highly unlikely that you would want two versions of "unknown". It's much more likely that you are changing the "unknown" member to clear up confusion or improve its behaviour - which means you would want all "versions" of the member to be revised.

Outputs

Unchanged

New

Deleted

SCD1 Updated

Expired SCD2

New SCD2

Invalid Input

Auditing

Last edited Aug 28, 2010 at 8:58 PM by toddmcdermid, version 4

Comments

No comments yet.