Implementation of Type 2 with SSIS
Let’s look at the same example which we used for the Type 1 SCDs in the previous article. Like we did before we will create a SSIS project and create new SSIS package.
We will then add a Data flow task to the control flow and double click the data flow task. We will then drag and drop an OLE Database Source and the following is what should enter for the SQL Command.
SELECT cus.CustomerID, cus.FirstName, cus.LastName, cus.MaterialStatus, reg.RegionName, cat.CategoryName
FROM staging.tblCategory AS cat INNER JOIN staging.tblcustomer AS cus
ON cat.ID = cus.CategoryID
INNER JOIN staging.tblRegion AS reg
ON cus.RegionID = reg.ID
Next, drag and drop a Slowly Changing Dimension control and let’s configure it.
As in the Type1 SCD, the first screen will ask you to map the source and target columns and select the business key which is CustomerID.
The next step is to manage the changes to column data in the slowly changing dimensions by settings the change type for dimension columns.
From the above image, you can see that the CategaryName, FirstName, LastName and MaritalStatus are configured as changing attributes which means that if there is a change in these columns a new record will not be updated. Instead, the existing record will be updates. However, for the RegionName the change type is set to Historical attribute which means that historical data will be kept for RegionName.
The next option is to set whether to change all the matching records, including outdated record when changes are detected. This option is useful to update things like names. If a customers name has changed it could be because ofa typing error. Therefore it is better to change all og the historical records as well.
Next is to configure the historical attribute option. As we discussed before, we need to identify what is the current record of the customer. For example, if customer has changed from Region A to Region B, we will have two records. When loading the fact table we need to assign current customers. For that we need to know what the current customer record is. As discussed before, there are two ways of doing this.
1. Assign attribute (True/False or Current/Expired) to a dimension field.
In the above configuration, you need to first assign the column to indicate the current record. In this example it is IsCurrentRecord which has a bit data type. In the above configuration, True is configured to the current value and false is configured to the Expiration value.
There is another default configuration which is Current/Expired. Unlike True/False which can be assigned to Bit/Int/Varchar data types, Current/Expired can be allocated only to a varchar.
You can have your own configuration for this field of your choice. My favourite values are Active/Historical.