Loading a Dimension Table using the Slowly Changing Dimension Wizard
When designing SSIS packages used to build and maintain data warehouses or other application databases that require accurate referential integrity, the sequencing of the control flow of the package is critical. Dimension data processing should always be performed before fact table data, as a rule. When maintaining data for fact tables and dimension tables, the order is important because your most often don’t want to load fact data that has dimension relationships with members that might not already exist in the dimension tables.
Both dimension and fact tables need to reflect the incoming data accurately. Oftentimes, you might need to configure how to detect new data from existing data already stored in the dimension tables. In this example any source changes to any of the two columns will be updated in the dimension table. These columns are referred to as changing dimension attributes, because the values can change.
1. To begin creating the ETL for the DimCustomer Table. Right-click SSIS Packages and choose New SSIS Package. Right-click the new package and rename it DW_DimCustomer.dtsx.
2. Drag a Data flow task from the Toolbox window onto the Control Flow workspace. Right-Click the task and rename it Find New Dim Members.
3. Double-click the Data Flow task to activate the Data Flow tab. From the Data Flow Sources group in the toolbox, drag an OLE DB Source object on the designer.
4. Right-click the new OLE DB source and rename it Stage Customer Table.
5. Double-click Stage Customer Table and select New to Create a new OLE DB connection.
6. Select [dbo].[DimStageCustomer] from the drop-down list of Name OF The Table Or the View. Click OK.
7. Your Screen should look like this:
Add a lookup task process
1. Drag a Lookup task from the Data Flow Transformations group in the Toolbox onto the Data Flow workspace and connect it to the Stage Customer Table data source with the green arrow.
2. Double –click the new lookup task and select localhost.SSIS DW from the OLE DB Connection Manager.
3. Select the [dbo].[DimCustomer] table as Reference table from the Use A table Or A View drop-down list.
4. Click the Columns tab and drag CustomerKey from the Available Input Columns list to the matching column in the Available Lookup Columns list. Then select the checkbox next to the CustomerKey column in the same column list. Set Lookup Operation to . Set Output Alias to LookupValue.
Your screen should look like this:
5. Click OK in the Lookup Transformation Editor to save your changes.
Add a Slowly Changing Dimension
1. Drag a Slowly Changing Dimension Transformation from the Toolbox onto the Data Flow and connect the green data path output from the Lookup onto the Slowly Changing Dimension Component. When you drop the path onto the SCD, you will be prompted to select the output of the lookup. Choose the Lookup Match Output from the drop-down and then click OK.
2. To invoke the SCD wizard, double –click the transformation, which will open up a splash screen for the wizard. Proceed to the second screen by Clicking Next.
3. The first input of the wizard is to identify the dimension table that the source data relates to. Therefore choose the Data Warehouse as the Connection Manager and then choose [dbo].[DimCustomer] as the table or view, which will automatically display the dimension table’s columns in the list as shown in Figure. For the CustomerKey, change the Key Type to Business key.
This screen has two purposes. To match columns from the source to attributes in the dimension table. Notice that the columns are automatically matched between the source input and the destination dimension because they have the same name and data type. On other scenarios, you may have to manually perform the match.
The other purpose is to identify the candidate key (or business key) from the dimension table and what it matches with from the input. This will be used to identify row matches between the source and destination.
4. On the next screen of the wizard, you will need to identify what type of change each matching column is identified as. It has already been mentioned that all the columns are changing attributes for the DimCustomer dimension; therefore, select all the columns and chose the “Changing attribute” Change Type from drop-down lists.
5. The next screen prompts you to choose which records you want to update when a source value changes. The “Fixed attributes” option is grayed out because no Fixed attributes were selected on the prior screen.
6. The inferred Dimension Members Screen is about handling placeholder records that were added during the fact table load. Clear the Enable inferred Member support check box to disable it.
7. Click Next , and then click Finish.
The Slowly Changing Dimension Wizard will create two branches in the pipeline from the Slowly Changing Dimension transformation: one to process new records, and one to process changing attribute records