{"id":1343,"date":"2012-08-23T13:19:00","date_gmt":"2012-08-23T13:19:00","guid":{"rendered":"http:\/\/bicortex.com\/?p=1343"},"modified":"2012-08-26T12:58:30","modified_gmt":"2012-08-26T12:58:30","slug":"how-to-implement-slowly-changing-dimensions-part-3-using-ssis-dimension-merge-scd-component-to-load-dimension-data","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/how-to-implement-slowly-changing-dimensions-part-3-using-ssis-dimension-merge-scd-component-to-load-dimension-data\/","title":{"rendered":"How to implement Slowly Changing Dimensions \u2013 Part 3. Using SSIS Dimension Merge SCD Component to load dimension data"},"content":{"rendered":"

In the first post<\/a>\u00a0to the series I explained how SSIS default component for handling Slowly Changing Dimensions can be used when incorporated into a package. This method was followed by a second post<\/a> depicting managing SCD via \u2018Checksum Transformation\u2019 third party add-in into Integration Services. In this post to the series I will not be leaving SSIS environment just yet as I still have not exhausted the options for additional transformations which can potentially be used for SCD processing. Before I explain how Merge T-SQL function can be utilised outside SSIS (see subsequent post), I would like to explore one more add-in and outline SSIS Dimension Merge SCD Component (from here on called DMSC). The component can be downloaded from HERE<\/a>. Unfortunately, at the time of writing this post there is no version released for SQL Server 2012 hence all screen captures are based on BIDS environment and not SQL Server Data Tools.<\/p>\n

Again, if you want to get your hands dirty, after component installation, execute THIS SQL script to create all database objects I will be using throughout this outline and create a new SSIS project in BIDS.<\/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\nCONSTRAINT PK_DimCustomer PRIMARY KEY CLUSTERED (CustomerSK asc))\r\n<\/pre>\n
Now that we have the groundwork laid out let\u2019s see what the query and the query output for constructing our dimension table looks like \u2013 we will only be selecting a limited amount of columns needed to populate our dimension. Executing the following SQL code will give you an idea of what attributes we will be using to populate out \u2018DimCustomer\u2019 table.<\/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
Now the fun part. Let\u2019s create a simple SSIS solution in BIDS. Drop Data Flow Task onto the Control Flow surface and double-click it to enter the Data Flow development pane. Here, drop 2 OLE DB Source containers (here called \u2018Merge Input Source\u2019 and \u2018Merge Input Target\u2019), 1 Dimension Merge Slowly Changing Dimension transformation, 2 OLE DB Command transformations (here called \u2018Update SCD Type 1 Records\u2019 and \u2018Expire SCD Type 2 Records\u2018) and 2 OLE DB Destination containers (here called \u2018Insert New Records\u2019 and \u2018Insert SCD Type 2 Records\u2019) onto the surface as per below.<\/p>\n
\"\"<\/a><\/p>\n
Let\u2019s go to each individual \u2018source\u2019 components to adjust their properties. First, in \u2018Merge Input Source\u2019 OLE DB Source create a database connection, change the \u2018Data Access Mode\u2019 to \u2018SQL Command\u2019 and input the same query as we used before to construct dimensional data from our source tables.<\/p>\n
\"\"<\/a><\/p>\n
In the second OLD DB Source which we called \u2018Merge Input Target\u2019, choose \u2018Table or View\u2019 data access mode and select \u2018DimCustomer\u2019 table as our target object. Finally, link the two OLE DB Sources to DMSC transform ensuring that the one with the query is linked as \u2018Source System\u2019 and the one with the dimension destination table is marked as \u2018Existing Dimension\u2019. Don\u2019t connect the remaining components just yet.<\/p>\n
These are our source tables\/query configured. Now, let\u2019s tweak the core transformation which will do all the heavy-duty lifting for us in this package \u2013 our DMSC transformation. Follow the screenshots below to adjust individual tab properties leaving Row Change Detection, Surrogate Key Handling, Inferred Member Behaviour, Auditing, Logging and Performance tabs on default settings. The remaining tab properties adjustments are depicted below with the key sections to pay attention to highlighted in red.<\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
If you look just at the screenshot above, you will notice that in the ‘Output Column Selection’ we have removed ‘Deleted’ output from the options provided. This is because we will not be deleting any records out of the existing dimension but without excluding it from this section, the package would not allow us to go any further, asking for explicitly specifying the records for deletion. Don\u2019t worry about the error message when you click \u2018Finish\u2019, this should disappear when we configure the outputs for this DMSC. To do this, first, link DMSC transformation to \u2018Insert New Records\u2019 OLE DB Source selecting \u2018New\u2019 from the \u2018Input Output Selection\u2019. Go into the newly linked OLE DB Source, select our dimension table and create the mapping ensuring that surrogate keys are omitted. We don\u2019t want those populated by the package as \u2018CustomerSK\u2019 is an identity column and should increment automatically. For our second OLE DB Source, link DMSC transformation selecting \u2018New SCD 2\u2019 option from the \u2018Input Output Selection\u2019. As per previous OLE DB Source configuration, create columns mappings, leaving out surrogate keys. All we should have left unlinked in our Data Flow pane are the two OLE DB Commands. Link DMSC to the first one – \u2018Update SCD Type 1 Records\u2019 OLE DB Command, select \u2018Updated SCD1\u2019 from the option dropdown and proceed to adjusting its properties. Select our previously created database connection from the \u2018Connection Managers\u2019 tab, go to \u2018Component Properties\u2019 tab and in its \u2018SQLCommand\u2019 property input the following query.<\/p>\n
\r\nUPDATE DimCustomer\r\nSET\r\nEmail_Address = ?,\r\nDT_Valid_From = ?\r\nWHERE CustomerSK = ?\r\nAND Is_Current = 1\r\n<\/pre>\n
Go to the next tab and map the parameters and columns accordingly as per image below.<\/p>\n
\"\"<\/a><\/p>\n
Lastly, configure the \u2018Expire SCD Type 2 Records\u2019 OLE DB Command. Link it to DMSC selecting \u2018Expired SCD 2 with SCD 1 Changes\u2019 from the selection available, input the database connection and in \u2018Component Properties\u2019 input the following string value.<\/p>\n
\r\nUPDATE DimCustomer\r\nSET\r\nFirst_Name = ?,\r\nLast_Name = ?,\r\nCity = ?,\r\nDT_Valid_From = ?,\r\nDT_Valid_To = ?,\r\nIs_Current = ?\r\nWHERE CustomerSK = ?\r\nAND Is_Current = 1\r\n<\/pre>\n
Complete component adjustments by mapping columns to their corresponding parameters as per below.<\/p>\n
\"\"<\/a><\/p>\n
That is our SSIS package finished. Go ahead and run it. All it should do at this stage is simply populate the \u2018DimCustomer\u2019 table with new values, inserting exactly 10 rows as per below.<\/p>\n
\"\"<\/a><\/p>\n
Now let\u2019s try and see how the package handles new, SCD Type 1 and SCD Type 2 records when we commit an update to our source tables by executing the following SQL script.<\/p>\n
\r\n--INSERT NEW VALUES INTO CUSTOMER\r\nINSERT INTO Customer\r\n(First_Name, Middle_Name, Last_Name, Email_Address, Phone_Number, Created_Date, Modified_Date)\r\nSELECT '11_xxx', NULL, '11_yyy', '11_some_email_address', NULL, GetDate(), NULL UNION ALL\r\nSELECT '12_xxx', NULL, '12_yyy', '12_some_email_address', NULL, GetDate(), NULL UNION ALL\r\nSELECT '13_xxx', NULL, '13_yyy', '13_some_email_address', NULL, GetDate(), NULL\r\nGO\r\n\r\n--INSERT NEW VALUES INTO ADDRESS\r\nINSERT INTO Address\r\n(City, Is_Current_Flag)\r\nSELECT '11_some_city', 'Y' UNION ALL\r\nSELECT '12_some_city', 'Y' UNION ALL\r\nSELECT '13_some_city', 'Y'\r\nGO\r\n\r\n--UPDATE TYPE 1 SCD RECORDS. DON'T WORRY ABOUT UPDATING 'MODIFIED_DATE' COLUMN\r\nUPDATE Customer\r\nSET Email_Address = '1_some_email_address' WHERE CustomerID = 1\r\nUPDATE Customer\r\nSET Email_Address = '2_some_email_address' WHERE CustomerID = 2\r\nUPDATE Customer\r\nSET Email_Address = '3_some_email_address' WHERE CustomerID = 3\r\nGO\r\n\r\n--UPDATE TYPE 2 SCD RECORDS. DON'T WORRY ABOUT UPDATING 'MODIFIED_DATE' COLUMN\r\nUPDATE Customer\r\nSET First_Name = '4_some_first_Name', Last_Name = '4_some_last_Name' WHERE CustomerID = 4\r\nUPDATE Customer\r\nSET First_Name = '5_some_first_Name', Last_Name = '5_some_last_Name' WHERE CustomerID = 5\r\nUPDATE Customer\r\nSET First_Name = '6_some_first_Name', Last_Name = '6_some_last_Name' WHERE CustomerID = 6\r\n\r\n--UPDATE BRIDGING TABLE\r\nINSERT INTO Customer_Address_Bridge\r\n(CustomerID, AddressID)\r\nVALUES (11,11)\r\nINSERT INTO Customer_Address_Bridge\r\n(CustomerID, AddressID)\r\nVALUES (12,12)\r\nINSERT INTO Customer_Address_Bridge\r\n(CustomerID, AddressID)\r\nVALUES (13,13)\r\n<\/pre>\n
Try running the package again and your output should be identical to the one below.<\/p>\n
\"\"<\/a><\/p>\n
What we have done here is made the DMSC to recognize that 3 records needed to be inserted (new data), 3 records required a Type 1 update and finally 3 records had to be expired due to Type 2 SCD insert of 3 more rows. This means that a total of 6 records needed to be inserted into our dimension table \u2013 3 new ones and 3 changed ones (Type 2), adjusting the total row number to 16. This should be synonymous with what I have done in Part 1, 2 and 4, the only exception using Dimension Merge Slowly Changing Dimension transformation rather than T-SQL Merge function, checksum transformation or a default SSIS component. The output to this second execution is as per image below (click on it to enlarge).<\/p>\n
\"\"<\/a><\/p>\n
What you can observe here is that records 1, 2 and 3 (blue rectangle) were updated according to SCD Type 1 i.e. no new rows were created, records 4, 5 and 6 were added as new rows which previously did not exist in this table and finally rows 7, 8 and 9 were created as SCD 2 update to existing records i.e. new entries were created for rows which already exist in the table but were expired due to changes to Type 2 attributes i.e. ‘First_Name’ and ‘Last_Name’.<\/p>\n
Next up, the final part to this series, using T-SQL Merge function<\/a> to update dimensional data with Type 1 and Type 2 SCD.<\/p>\n","protected":false},"excerpt":{"rendered":"
In the first post\u00a0to the series I explained how SSIS default component for handling Slowly Changing Dimensions can be used when incorporated into a package. This method was followed by a second post depicting managing SCD via \u2018Checksum Transformation\u2019 third party add-in into Integration Services. In this post to the series I will not be […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,5,50],"tags":[12,49,13],"class_list":["post-1343","post","type-post","status-publish","format-standard","hentry","category-how-tos","category-sql","category-ssis","tag-code","tag-sql","tag-ssis"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1343","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=1343"}],"version-history":[{"count":26,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1343\/revisions"}],"predecessor-version":[{"id":1380,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1343\/revisions\/1380"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=1343"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=1343"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=1343"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}