The Slowly Changing Dimension transformation in SSIS coordinates the inserting and updating records in data warehouse dimension tables. This transformation supports four types of changes, and in this article we will explain about SSIS Slowly Changing Dimension Type 1 (called as Changing attribute).
SSIS Slowly Changing Dimension Type 1: If you want to update the columns data then mark them as Changing attribute. If your Dimension table members (Columns) are marked as Changing attributes then it will replace the existing records with new records. This type of changing is very useful, For example if we want to update the wrongly typed data then we can mark this column as Changing attribute. Before we step into the example, let us see the data inside our Dimension table.
NOTE: This will update the old records with new values but it doesn’t maintain the historic data.
Following screenshot will show you the data inside our [Employees] table. You can get the same data by selecting TOP 10 records from [DimEmployee] table in [AdventureWorksDW2014] database.
Below image shows the data present in the [EmployeeSource] table and we are going to update or insert these two records into the [Employees] table.
If you observe the above screenshot, Department Name of the EmployeId (253022876) is different from original value (it has changed from ‘Marketing’ to ‘Sales’ and We have a new record with EmployeId 253022870. Our task is to insert the new record and update the Department Name of the second record.
SSIS Slowly Changing Dimension Type 1 example
STEP 1: Open BIDS and Drag and drop the data flow task from the toolbox to control flow and name it as SSIS Slowly Changing Dimension Type 1.
Double click on it will open the data flow tab.
STEP 2: Drag and drop OLE DB Source, Slowly Changing Dimension from SSIS toolbox to data flow region
STEP 3: Double click on OLE DB source in the data flow region will open the connection manager settings and provides space to write our SQL statement. Here we are using already created OLE DB Connection Manger and EmployeSource table present in the [SSIS Source] as the Source table. If you find any difficulty to configure the OLE DB Source then please refer OLE DB Source in SSIS article.
STEP 4: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
In order to use the data inside the OLE DB Source, Drag and drop the blue arrow from OLE DB Source to SCD Transformation.
STEP 5: Double-click the Slowly Changing Dimension transformation. Once you click on it, It will open Slowly Changing Dimension Wizard. First page is welcome page and If you don’t want to see this page again, then Please tick the check box “Do not show this page again”. For now click Next
STEP 6: Select a Dimension Table and Keys: This page is used to configure the Dimensional table information.
Connection Manager: This option is used to provide the connection to the dimension table. Here we are using already created OLE DB Connection Manger which is connected to [SSIS Source] database present in our SQL Server 2014. You can change the connection manager settings by clicking the New button.
Table or View: This option is useful to select the Data Warehouse Dimension table or View on which you want to perform slowly changing operation. Here we are selecting Employees table.
- Input Columns: Columns that are coming from the OLE DB Source.
- Dimension Columns: Columns that are available in Employees Dimension table.
- Key Type: In order to perform Slowly Changing Dimension we need at least one Business key and we all know that the Employee Alternative Key is the key columns. Please change the Key type from Not a Key Column to Business key as we shown below
STEP 7 – Slowly Changing Dimension Columns: This is the main page in this whole wizard. here we have
- Dimension Columns: All the columns that are available in Dimension table will be available in this section. Here you have to select the columns that you want to select for Change Type. From the below screenshot you can observe that, we are selecting the Birth date and Email Address columns as Fixed attributes and Department Name as changing attribute
- Change Type: Select the change type. In this example, we are keeping the default Fixed attribute as change type for Birth date, Email Address columns and Department Name as changing attribute.
TIP: By default the wizard will assign the Fixed attribute as the Change Type but you can change them as we shown above.
STEP 8 – Fixed and Changing Attribute Options: This page has two option and we already explained the first option in our previous article so, Please refer SSIS Slowly Changing Dimension Type 0 article. We will explain the second option in our next article.
STEP 9 – Inferred Dimension Members: We will discuss this option in Slowly Changing Dimension Inferred Dimension article. For now, we are de-selecting this option and click Next button
STEP 10 – Finish the Slowly Changing Dimension Wizard: Click finish button to finish configuring the SSIS Slowly Changing Dimension Type 1
Once you click on the finish button our Data Flow will automatically changed. If you observe the below screenshot, it added the OLE DB Destination to insert new records into the Dimension table and OLE DB Command Transformation to update the existing Old records with new data
(Optional Step): Let us double-click on the OLE DB Destination to check the automatically created destination (table name and connection manager setting and mappings).
(Optional Step): Let us double-click on the OLE DB Command Transformation to check the Update statement that was created automatically.
Click OK to finish our SSIS Slowly Changing Dimension Type 1 package design. Let us run the package
From the above screenshot you can observe that, though there are two rows coming from the OLE DB Source, One row is inserted and other row is updated.
Let’s open the SQL Server Management Studio and check the results. If you observe the below records, our package added New record of Employee Alternative key 253022870 and updated the department name of Employee Alternative key 253022876.
Thank you for Visiting Our Blog