{"id":1295,"date":"2012-08-21T11:45:46","date_gmt":"2012-08-21T11:45:46","guid":{"rendered":"http:\/\/bicortex.com\/?p=1295"},"modified":"2013-11-15T00:41:35","modified_gmt":"2013-11-15T00:41:35","slug":"how-to-implement-slowly-changing-dimensions-part-2-using-checksum-transformation-ssis-component-to-load-dimension-data","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/how-to-implement-slowly-changing-dimensions-part-2-using-checksum-transformation-ssis-component-to-load-dimension-data\/","title":{"rendered":"How to implement Slowly Changing Dimensions \u2013 Part 2. Using Checksum Transformation SSIS component to load dimension data"},"content":{"rendered":"

In the previous post I briefly outlined the methodology and steps behind updating a dimension table using a default SCD component in Microsoft’s SQL Server Data Tools environment. This post also deals with SCD updates but this time I want to introduce a different, open-sourced, third party component which anyone can use to process SCD \u2013 the checksum transformation (you can download it HERE<\/a>). SQL Server Checksum function returns the checksum value computed over a row of a table or over a list of expressions. Checksum is intended for use in building hash indexes but in this context it will become valuable to update dimension tables based on the values the \u2018Checksum Transformation’ component produces.<\/p>\n

If you want to continue hands-on with this tutorial I suggest you create all the objects and data I will be using throughout this post by executing the following SQL code.<\/p>\n

\r\n--CREATE RELATIONAL TABLES AND CONSTRAINTS. POPULATE TABLES WITH SOME DUMMY DATA\r\nCREATE TABLE Customer(\r\nCustomerID int IDENTITY (1,1),\r\nFirst_Name varchar (50) NULL,\r\nMiddle_Name varchar (50),\r\nLast_Name varchar (50) NULL,\r\nEmail_Address varchar (100) NULL,\r\nPhone_Number varchar (50) NULL,\r\nCreated_Date datetime NULL,\r\nModified_Date datetime NULL,\r\nCONSTRAINT pk_CustID PRIMARY KEY (CustomerID))\r\n\r\nCREATE TABLE Address(\r\nAddressID int IDENTITY (1,1) NOT NULL,\r\nAddress1 varchar (50) NULL,\r\nAddress2 varchar (50) NULL,\r\nCity varchar (50)  NULL,\r\nPost_Code char (4) NULL,\r\nIs_Current_Flag char(1) NULL,\r\nCONSTRAINT pk_AddressID PRIMARY KEY (AddressID))\r\n\r\nCREATE TABLE Customer_Address_Bridge(\r\nCustomerID int NOT NULL,\r\nAddressID int NOT NULL)\r\nGO\r\n\r\nINSERT INTO Customer\r\n(First_Name, Middle_Name, Last_Name,Email_Address,Phone_Number,Created_Date,Modified_Date)\r\nSELECT 'Mary', 'Joeanne', 'Black', 'mary0120@yahoo.com.au', '03-8573-9455', '2012-01-01', NULL UNION ALL\r\nSELECT 'John', 'Lorance', 'Moore', 'johnnym@awol.com', '03-3423-1155', '2012-01-01', '2012-05-30' UNION ALL\r\nSELECT 'Martin', NULL, 'Laser', 'mlaser91@aol.com', '03-2355-1109', '2012-01-01', '2012-05-12' UNION ALL\r\nSELECT 'Spencer', 'Chris', 'McEvans', 'spencerdude@hotmail.com', '03-1122-0007', '2012-01-01', '2012-05-30' UNION ALL\r\nSELECT 'Mark', NULL, 'King', 'mk038722@gmail.com', '03-3423-1155', '2012-01-01', '2012-05-30' UNION ALL\r\nSELECT 'Mary', 'Susan', 'Grey', 'mmgrey@gmail.com', '03-1299-3859', '2012-01-01', NULL UNION ALL\r\nSELECT 'Luis', 'Blake', 'Shimaro', 'shimarolou@yahoo.com.au', '03-0385-3999', '2012-01-01', NULL UNION ALL\r\nSELECT 'Natalie', 'G', 'Chin', 'nataliechin@mediasmarts.com.au', '03-3759-1001', '2012-01-01', NULL UNION ALL\r\nSELECT 'Marian', NULL, 'McErin', 'marianmcerin@gmail.com', '03-3400-3331', '2012-01-01', '2012-05-01' UNION ALL\r\nSELECT 'Rick', 'Tony', 'Webster', 'rikky69@gmail.com', '03-9459-1112', '2012-01-01', NULL\r\n\r\nINSERT INTO Address\r\n(Address1, Address2, City, Post_Code, Is_Current_Flag)\r\nSELECT '6 Agave Street', 'Apartment 4A', 'Launceston', '7250', 'Y' UNION ALL\r\nSELECT '88 Dodge Street', NULL, 'Sunshine', '3020', 'Y' UNION ALL\r\nSELECT '3 McKenzie Court', 'Level 9', 'Perthville', '2795', 'Y' UNION ALL\r\nSELECT '5 Spencer Drive', 'Unit 9D', 'Melbourne', '3002', 'Y' UNION ALL\r\nSELECT '8 Sunny Avenue', NULL, 'Sydney', '2000', 'Y' UNION ALL\r\nSELECT '83 Mara Drive', NULL, 'Echuca', '3563', 'Y' UNION ALL\r\nSELECT '1038 Mustang Street', NULL, 'Brisbane', '4000', 'Y' UNION ALL\r\nSELECT '1 Bradman Street', NULL, 'Bendigo', '3550', 'Y' UNION ALL\r\nSELECT '12 Cruger Drive', 'Block C', 'Cairns', '4870', 'Y' UNION ALL\r\nSELECT '124 Lasting Court', NULL, 'Adelaide', '5000', 'Y'\r\n\r\nINSERT INTO Customer_Address_Bridge\r\n(CustomerID, AddressID)\r\nSELECT 1, 1 UNION ALL\r\nSELECT 2,2 UNION ALL\r\nSELECT 3,3 UNION ALL\r\nSELECT 4,4 UNION ALL\r\nSELECT 5,5 UNION ALL\r\nSELECT 6,6 UNION ALL\r\nSELECT 7,7 UNION ALL\r\nSELECT 8,8 UNION ALL\r\nSELECT 9,9 UNION ALL\r\nSELECT 10, 10\r\n\r\n--CREATE CONSTRAINTS\r\nALTER TABLE Customer_Address_Bridge\r\nADD FOREIGN KEY (AddressID) REFERENCES Address(AddressID)\r\nALTER TABLE Customer_Address_Bridge\r\nADD FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)\r\nGO\r\n\r\n--CREATE DIMENSION 'DimCustomer' TABLE\r\nCREATE TABLE DimCustomer(\r\nCustomerSK int IDENTITY (1,1) NOT NULL,\r\nCustomerID int NULL,\r\nFirst_Name varchar (50) NULL,\r\nLast_Name varchar (50) NULL,\r\nEmail_Address varchar (100) NULL,\r\nCity varchar (50) NULL,\r\nDT_Valid_From datetime NULL,\r\nDT_Valid_To datetime NULL,\r\nIs_Current bit NULL,\r\nDimChecksum bigint NULL\r\nCONSTRAINT PK_DimCustomer PRIMARY KEY CLUSTERED (CustomerSK asc))\r\n\r\nALTER TABLE [dbo].[DimCustomer] ADD  CONSTRAINT [DF_DimCustomer_RowChecksum]  DEFAULT ((0)) FOR [DimChecksum]\r\nGO\r\n\r\n--CREATE TEMPORARY TABLE TO HOLD TYPE 1 SCD DATA\r\nCREATE TABLE Temp_Customer_Updates(\r\nChecksum bigint NULL,\r\nDimChecksum bigint NULL,\r\nCustomerID int NULL,\r\nFirst_Name varchar(50) NULL,\r\nLast_Name varchar(50) NULL,\r\nCity varchar (50) NULL,\r\nEmail_Address varchar (100) NULL,\r\nCustomerSK int NULL)\r\n<\/pre>\n
This should form the foundations for our SCD package. As with the previous post to this series (Part 1), attributes such as \u2018First_Name\u2019, \u2018Last_Name\u2019 and \u2018City\u2019 will be treated as Type 2 SCD whereas \u2018Email_Address\u2019 column will be marked as Type 1 SCD. Also, notice that this code is essentially the same as the code I created for the previous post in this series (Part 1) with two differences \u2013 firstly, our \u2018DimCustomer\u2019 dimension table contains an additional attribute (line such and such) called \u2018DimChecksum\u2019 (bigint datatype) to cater for our checksum transformation and we have added \u2018Temp_Customer_Updates\u2019 table as a temporary storage for Type 1 SCD (all explained later).<\/p>\n
Now, onto the main part – creating an SSIS package. Fire up SQL Server Data Tools development environment and create a new SSIS project. Next, on the \u2018Control Flow\u2019 pane drop three \u2018Execute SQL Task\u2019 and one \u2018Data Flow\u2019 transformations and link them together as per image below – ‘Truncate Temp Table’ (Execute SQL Task 1) –> ‘Process SCD’ (Data Flow Task) –> ‘Update DimCustomer from Temp Table’ (Execute SQL Task 2) –> ‘Update Is_Current Flag’ (Execute SQL Task 3).<\/p>\n
\"\"<\/a><\/p>\n
Next, create a database connection in \u2018Truncate Temp Table\u2019 component\u00a0 and enter the following SQL in \u2018SQLStatement\u2019 property:<\/p>\n
\r\nTRUNCATE TABLE Temp_Customer_Updates\r\n<\/pre>\n
This will empty our temporary table at the start of package execution. Following the green precedence lines, double-click on \u2018Process SCD\u2019 transform to go to \u2018Data Flow\u2019 pane. Here, drop the \u2018OLE DB Source\u2019 transformations onto the development surface and using the previously created database connection, input the following query into its \u2018SQL command text:\u2019 textbox.<\/p>\n
\r\nSELECT cu.CustomerID,\r\ncu.First_Name,\r\ncu.Last_Name,\r\nad.City,\r\ncu.Email_Address\r\nFROM dbo.Address AS ad INNER JOIN\r\ndbo.Customer_Address_Bridge AS br ON ad.AddressID = br.AddressID INNER JOIN\r\ndbo.Customer AS cu ON br.CustomerID = cu.CustomerID\r\n<\/pre>\n
This query\u2019s output will form our dimension table data structure. Next, drop the \u2018Checksum Transformation\u2019, link it to our data source and within the transform, on \u2018Input Columns\u2019 tab select all attributes from \u2018Available Input Columns\u2018 except for the business key as per below.<\/p>\n
\"\"<\/a><\/p>\n
This means that only the columns selected will have a checksum values created for each row for SCD comparison. Now it is time to take care of the \u2018Lookup\u2019 transform. Open it up and adjust the properties for each property group i.e. General, Connection, Columns, Advanced and Error Output as per below.<\/p>\n