Managing A Slowly Changing Dimension in SQL Server Integration Services
Managing A Slowly Changing Dimension in SQL Server Integration Services
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 Latest posts by Thomas LeBlanc (see all) Performance tuning – Nested and Merge SQL Loop with Execution Plans - April 2, 2018 Time Intelligence in Analysis Services (SSAS) Tabular Models - March 20, 2018 How to create Intermediate Measures in Analysis Services (SSAS) - February 19, 2018
SQLShack
SQL Server training EspañolManaging A Slowly Changing Dimension in SQL Server Integration Services
October 10, 2017 by Thomas LeBlanc 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. 1234567891011121314151617181920212223 CREATE TABLE [dbo].[DimProduct]( [ProductSKey] [int] IDENTITY(1,1) NOT NULL, [ProductID] [nvarchar](25) NULL, [ProductName] [nvarchar](50) NOT NULL, [Color] [nvarchar](15) NOT NULL, [ListPrice] [money] NULL, [Class] [nchar](2) NULL, [ProductSubcategorySKey] [int] NULL CONSTRAINT [PK_DimProduct_ProductSKey] PRIMARY KEY CLUSTERED ( [ProductSKey] ASC) ON [PRIMARY]) ON [PRIMARY]GO ALTER TABLE [dbo].[DimProduct] WITH CHECK ADD CONSTRAINT [FK_DimProduct_DimProductSubcategory] FOREIGN KEY([ProductSubcategorySKey])REFERENCES [dbo].[DimProductSubcategory] ([ProductSubcategorySKey])GOALTER TABLE [dbo].[DimProduct] CHECK CONSTRAINT [FK_DimProduct_DimProductSubcategory]GO Code Block 1: Product Dimension The following columns will be added to the product dimension table. StartDate (DateTime) – Start date for this product to being active and related to new sales Status (Boolean) – 0 means not active, 1 means active 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. 12345678910 SELECT p.ProductNumber AS ProductID, p.Name AS ProductName , IsNull( p.Color, 'NA') AS Color, IsNull( p.Class, 'NA') AS Class , IsNull( p.ListPrice, 0) AS ListPrice , ISNULL(p.ProductSubcategoryID, -1) AS ProductSubcategoryID , CAST( CAST( GETDATE() AS DATE) AS DATETIME) AS StartDate , DATEADD( SECOND, -1, CAST( CAST( GETDATE() AS DATE) AS DATETIME) ) AS EndDate , 1 AS ProductActive FROM Production.Product p 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. 12345678 UPDATE dbo.DimProduct SET Color = s1.Color, Class = s1.Class FROM dbo.DimProduct dp INNER JOIN StageDW.dbo.StageDimproduct sdp ON sdp.matchProductSKey = dp.ProductSKey Code Block 3: SC1 Product Dimension Update 123456789 UPDATE dbo.DimProduct SET EndDate = s1.EndDate , [Status] = 0 FROM dbo.DimProduct dp INNER JOIN StageDW.dbo.StageProduct_SC2 s2 ON sdp.matchProductSKey = dp.ProductSKey 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
Kimball Group Slowly Changing Dimension Kimball Group Slowly Changing Dimension Part II Conditional Split Author Recent Posts Thomas LeBlancThomas 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 Latest posts by Thomas LeBlanc (see all) Performance tuning – Nested and Merge SQL Loop with Execution Plans - April 2, 2018 Time Intelligence in Analysis Services (SSAS) Tabular Models - March 20, 2018 How to create Intermediate Measures in Analysis Services (SSAS) - February 19, 2018