In this article, we will explain SSIS Slowly Changing Dimension Type 0 (called a Fixed attribute). If you want to restrict the columns to be unchanged, then mark them as a Fixed attribute. If your Dimension table members (Columns) are marked as Fixed attributes, then it will not allow any changes to those columns (Updating Data) but, you can insert new records. This type of change is useful.
For example, we can mark the Employee’s Last Name, date of birth, and Company Login details as fixed attributed because once we store those values, they won’t change for any employee. Before we step into the SSIS Slowly Changing Dimension Type 0 example, let us see the data inside our Dimension table.
The following screenshot will show you the data inside our [DimCustomer] table. You can get the same data by selecting the TOP 10 records from the [DimCustomer] table in the [AdventureWorksDW2014] database.
The below image shows the data present in the [CustomerSource] table, and we are going to insert or update these two records into the [DimCustomer] table.
If you observe the above screenshot, the Last name of the Customer Alternative Key (AW00011011) has changed from ‘Yang’ to ‘Lue’, and We have a new record with Customer Alternative Key (AW00011011). Our task is to insert the new record and try to update the Last name of the first record.
SSIS Slowly Changing Dimension Type 0 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 0.
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 the OLE DB source in the data flow region will open the connection manager settings and provide space to write our SQL statement. Here we are using the already created OLE DB Connection Manager and CustomerSource table as the Source table. Please refer OLE DB Source article.
STEP 4: Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.
Drag and drop the blue arrow from OLE DB Source to Slowly Changing Dimension Transformation to connect the data.
STEP 5: Double-click the Slowly Changing Dimension transform to open the editor. Once you click on it, It will open Slowly Changing Dimension Wizard. The first page is the welcome page, and If you don’t want to see this page again, please tick the checkbox “Do not show this page again”. For now, click Next
STEP 6: Select a Dimension Table and Keys: This page is useful for configuring the Dimensional table information.
Connection Manager: This option is useful to provide a connection to the dimension table. Here we are using the already created OLE DB Connection Manager and DimCustomer table present as the dimension table. You always have the luxury to change the connection 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 a slowly changing operation. Here we are selecting the DimCustomer table.
- Input Columns: Columns that are coming from the OLE DB Source Or column that you want to insert or update.
- Dimension Columns: Columns that are available in the Dimension table.
- Key Type: To perform Slowly Changing Dimension, we need at least one Business key, and we all know that the Customer Alternative Key is the key column. Please change the Key type from Not a Key Column to Business key
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 the Dimension table will be available in this section. Here you have to select the columns that you want to mark as a fixed attribute. From the below screenshot, you can observe that we are choosing the Last Name, Birthdate, and Email Address columns
- Change Type: Select the change type. In this example, we are discussing SCD Type 0. So, we are keeping the default Fixed attribute as the change type.
By default, the wizard will assign the Fixed attribute as the Change Type. But you can change them.
STEP 8 – Fixed and Changing Attribute Options: This page has two options, and the second option is grayed out for SCD Type 0. If there are any changes in the data (data coming from the input column is different from Dimension table data) then:
- If you want to Fail the package, then checkmark the Fail transformation if changes are detected in a fixed attribute option.
- If you don’t want to fail the package, uncheck this option. Note, that unchecking this option doesn’t allow you to update the existing records but it will not fail the package
STEP 9 – Inferred Dimension Members: We will discuss this option in the Slowly Changing Dimension Inferred Dimension article. For now, we are de-selecting this option and clicking the Next button..
STEP 10 – Finish the Slowly Changing Dimension Wizard: Click the finish button to finish configuring the SSIS Slowly Changing Dimension Type 0
Once you click on the finish button, our Data Flow will automatically change. If you observe the below screenshot, it added the OLE DB Destination to insert new records into the Dimension table
(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).
Click OK to finish our SSIS Slowly Changing Dimension Type 0 package design. Let us run the package
From the above screenshot, you can observe that, though two rows are coming from the OLE DB Source. Only one row was inserted (No update performed) here.
Let’s open the Management Studio and check the results. If you observe the below records, our package added a New record of customer Alternative key AW00011011. But it didn’t update the Last name of AW00011001.
Let us see what will happen when we uncheck the Fail transformation if changes are detected in a fixed attribute option.
Click OK to finish the SSIS Slowly Changing Dimension Type 0 package design and let us run the package. From the below screenshot, you can observe that the package has failed at the Slowly Changing Dimension transformation.
Comments are closed.