Thomas LeBlanc

Managing A Slowly Changing Dimension in SQL Server Integration Services

October 10, 2017 by

A data warehouse has to be historically correct. This becomes an issue when data like the Product List Price for a previous year needs to be saved historically. Dimensional Modeling methodologies provide a solution for the situation. The Slowly Changing method integrated with components from SQL Server Integration Services solves the issue. This article will look at updating a product dimension table using the Slowly Changing Type 2 Dimension while maintaining the Type 1 columns.

Slowly Changing Type 1 (SC1) refers to columns in a dimension table that are overwritten with new data. Say the color was entered incorrectly. The dimension process will need to update the incorrect value. The historical reporting will change but the business wants this. Slowly Changing Type 2 (SC2) refers to the example of the ListPrice changing from year to year. The reports from the previous year will need to include the List Price for that year. The dimension table will track multiple rows for the products with historical data in the previous rows based on a date range.

The regular product dimension table used in this article appears in Code Block 1.

Code Block 1: Product Dimension

The following columns will be added to the product dimension table.

  1. StartDate (DateTime) – Start date for this product to being active and related to new sales
  2. Status (Boolean) – 0 means not active, 1 means active
  3. Enddate (DateTime) – End date for this row to be inactive but still related to historical sales

The data would look like the following for Slowly Change Type 2 (SC2) products.

Key ID Name Color ListPrice SubcatSKey StartDate EndDate Status
243 FR-R92R-44 HL Road Frame – Red, 44 Red 1431.50 14 2016-07-01 NULL 1
242 FR-R92R-44 HL Road Frame – Red, 44 Red 1263.4598 14 2014-07-01 2015-06-30 0
241 FR-R92R-44 HL Road Frame – Red, 44 Red 1301.3636 14 2015-07-01 2016-06-30 0
246 FR-R92R-48 HL Road Frame – Red, 48 Red 1431.50 14 2016-07-01 NULL 1
245 FR-R92R-48 HL Road Frame – Red, 48 Red 1263.4598 14 2014-07-01 2015-06-30 0
244 FR-R92R-48 HL Road Frame – Red, 48 Red 1301.3636 14 2015-07-01 2016-06-30 0

Table 1: Slowly Changing Type 2 Product Data

In Table 1, the Product ID FR-R92R-44 (HL Road Frame – Red 44) has 3 rows. The data seems to be duplicated in the table, but the StartDate, EndDate, and Status do label each row for when it is valid. The Null in the EndDate along with the Status of 1, shows the Key row 243 to be the active row for this product.

SQL Server Integration Services provides a Slowly Changing Dimension component (it is actually a wizard), but sometimes it is better to build it with other components. This gives the package more flexibility when updating the dimension table with additional columns. The package will look like any dimension table import. Figure 2 shows the Control Flow for the product dimension.

Figure 1: DimProduct SSIS package

The additional Execute SQL Task, named Update Product Changes, allows a set base update on the DimProduct table instead of using an OLEDB Command component in a Data Flow Task. An OLEDB Command used to execute a T-SQL UPDATE might be a performance problem when there are many rows to be updated. This is referred to as “Rows by Agonizing Rows” or RBAR.

Figure 2 shows the Data Flow Task for the Product Dimension. The part that needs to be modified is the Conditional Split. Here, a Multicast needs to be added to insert a new row for the Slowly Changing Type 2 (SC2) data in the Product table plus a pipe to a check for Slowly Changing Type 1 (SC1) changes. The code for SC2 needs to check for ListPrice changes. If the ListPrice changes, then a new row will be added to the product dimension table and the existing current product row needs the EndDate to be updated to the end of yesterday and the Status changed to 0 (Inactive).

Figure 2: Product Dimension Data Flow Task

Figure 3 shows the Change to the Lookup to see if an existing Product is in the Dimension table. The Status = 1 WHERE clause will only return active Products in the Data Warehouse. The columns selected (ListPrice, Color, etc.) will be compared to the existing dimension table for changes – SC1 and SC2.

Figure 3: Lookup Changes for Existing Product Dimension Rows

The Multicast will be placed before the Conditional Split and this Conditional Split (SC1) will not look at ListPrice, only Color, and Class. These 2 columns have been determined to be Slowly Changing Type 1 which means overwrite existing Product Dimension rows whether active or inactive.

An additional Conditional Split (Figure 4) will be added after the Multicast to compare the ListPrice. A Union ALL component (Figure 5) will be added to merge existing new rows from the transactional database and the new SC2 rows from the new Conditional Split. Another Multicast will pipe the new SC2 rows to Union All and a Staging table.

Figure 4: ListPrice Conditional Split

Figure 4 shows the Conditional Split comparison for ListPrice column. Note here that there is no check for Nulls. This Data Warehouse does not allow Nulls and the source query will return NA or Unknown for Null values in the source data like the code in Code Block 2.

Code Block 2: Source Query for Products

The Data Flow Task (Figure 5) is now a little more complicated, but manageable.

Figure 5: Data Flow Task with SC1 and SC2 Updates

Going back to the Control Flow, there are 2 Execute T-SQL Statement components rather than one as seen in Figure 6.

Figure 6: Product Package Control Flow

The Update statements for the SC1 changes are in Table 2 and the SC2 updates are in Code Blocks 3 and 4. They are updated based on a join to the staging table for the changed data.

Code Block 3: SC1 Product Dimension Update

Code Block 4: SC2 Product Dimension Update

SQL Server Integration Services can handle almost any data import situation that is given it. There are so many options to accomplish things like Slowly Changing Dimensions. Starting with an opportunity like this helps individuals dig deeper into the functionality of SQL Server. As newer releases become available, it is noticeable that Microsoft is improving the tools that make Data Management more attainable.

Useful links

Thomas LeBlanc
Integration Services (SSIS)

About Thomas LeBlanc

Thomas LeBlanc is a Data Warehouse Architect in Baton Rouge, LA. Today, he works with designing Dimensional Models in the financial area while using Integration (SSIS) and Analysis Services (SSAS) for development and SSRS & Power BI for reporting. Starting as a developer in COBOL while at LSU, he has been a developer, tester, project manager, team lead as well as a software trainer writing documentation. Involvement in the SQL Server community includes speaking at SQLPASS.org Summits and SQLSaturday since 2011 and has been a speaker at IT/Dev Connections and Live! 360. Currently, he is the Chair of the PASS Excel Business Intelligence Virtual Chapter and worked on the Nomination Committee for PASS Board of Directors for 2016. View all posts by Thomas LeBlanc

168 Views