Administering Core Business Intelligence Services - Microsoft SQL Server 2008 R2 Administration - page 167

This recipe presents a number of suggestions relating to high volume ETL throughput, laid
out specifically using for the use of SQL Server Integration Services. Aspects of both ETL
performance and management are described in such a way, that it will help users to manage
the overall ETL process efficiently. In this recipe, we will focus on the components that will
address the advanced ETL specific processes such as Slowly Changing Dimensions (SCD),
and Fuzzy lookup transform. The features within SQL Server 2008 R2 provide the Integration
Services beyond simple ETL operations with an advanced and extended set of components
that are for enabling various lookup efficiencies, including the Lookup transform and Lookup
Cache transform.
How to do it...
Managing the ETL processes efficiently and providing the high volume ETL throughput requires
us to consider the best practices and high performance techniques for ETL. The following
steps are defined on the aspects of both ETL efficiency and management:
1. Create a new Integration Services project. Click the package in the new solution.
2. Drop a Data Flow Task onto the workflow. Switch to the Data Flow tab.
3. Drop an OLE DB Source Adapter and double-click it.
4. Click the New button to create a new OLE DB connection manager. Connect to the
Adventure Works database.
5. Select Table or View for the Data Access mode, which is the default selection.
6. Select [HumanResources].[Employee] for the name of the table. Click the Columns
node. Click the OK button to close the OLE DB Source Adapter dialog box.
7. Drop a Slowly Changing Dimension transform onto the Data Flow designer.
8. Drag the output from the OLE DB Source Adapter to the SCD transform.
9. Double-click the Slowly Changing Dimension transform to open the Slowly Changing
Dimension Wizard.
10. Within SCD wizard, there are general steps that are involved in SCD for defining
additional data flow and runtime behavior of SCD transform.
11. Select a Dimension Table and Keys option to choose which dimension table you want
to modify and the keys for matching the source records with dimension table records.
12. Select the Slowly Changing Dimension Columns to choose the dimension attributes
and the change type for each attribute.
13. Select the Fixed and Changing Attribute Option to set the SCD transform to fail if a
fixed attribute has changed, and update all records when a change is detected in a
changing attribute.
14. Select the Historical Attribute Option to set the SCD Wizard to detect current and
expired records.