2. Assign start date and end date for the dimension record. For the current record the end date is null.
In this configuration, you can assign the start date and end date column. Both columns should have datetime or smalldatetime data types. In this configuration, you have to configure value to set data values. In this you will be given a list of available date time variables in the package. Out of these there are two system variables you can assign. System::StartTime is the start time of the package and System::ContainerStartTime is the start time of the container.
Ok, that’s all you have to do for the Type 2 SCD and the following is what you get:
Though you don’t need to know what these components do, it will be better to know how it works.
Changing Attribute Updates Output will update changing attributes like the firstname columns as in this example. The Historical Attribute Inserts Output path introduces a new variable depending on the selection you made in historical attribute option. If you selected to use date time value it will be @[System::ContainerStartTime] or if select the option historical it will be value you need to update the column will be derived to a new column and it will be updated to say that record is expired.
After the update Historical Attribute Inserts Output and New Inserts paths are unioned. Then derive a column is used to derive the current and lastly it will insert records in to the table.
Inferred Member Updates
Inferred members are also another important fact when it comes to dimensions. Sometimes, though the dimension record is inserted, it is not the final user. For example, if a broker buys house for another person, obviously he is not the final user. Thus it will give you wrong information if you filled a dimension record with broker’s data. However, you need to insert some dimension record which will be updated when the broker sells the house to the final user.
Implementation of Inferred Member Updates in SSIS
In the above SCD configuration, there is another screen to configure inferred member updates.
After enabling the inferred member support, you need to select either of the options available here. If you select the first option, it will treat all columns with a change type with null as inferred member. The second option, you can include new Boolean column to indicate whether the current record is an inferred member. Second option is the most correct way of doing it.
After configuring this the following is the layout of the SCD.
After configuring the inferred member updates, there is an additional path for Inferred Member Updates Output which will have OLEDB Command.
Other Types of SCD
There are several other SCD types which are hardly used. For the completeness and academic purposes, you can read them in Wikipedia. Because of the fact that they are hardly used, the SSIS SCD control does not support these SCD types. If you really have a requirement to implement these SCD types, you have to use other data flow tasks.
Sample Packages
We have discussed Type2 SCD in this article and hope this will give you an introduction to the SCD including the previous article. If you need the sample package and the sample table schema scripts, drop me an email to dineshasanka@dbfriend.net.
]]>