The Designer provides two mapping wizards to help us create mappings quickly and easily. Both wizards are designed to create mappings for loading and maintaining star schemas, a series of dimensions related to a central fact table.
Note: We do not use them in projects and instead make the mappings manually.
Two wizards are:
1. Getting Started Wizard
2. Slowly Changing Dimensions Wizard
Use the following sources with a mapping wizard:
- Flat file
- Relational
- Application
- Shortcut to a flat file, relational, or Application sources
Getting Started Wizard
It creates mappings to load static fact and dimension tables and slowly growing dimension tables.
The Getting Started Wizard can create two types of mappings:
- Simple Pass Through
- Slowly Growing Target
1. SIMPLE PASS THROUGH
- Loads a static fact or dimension table by inserting all rows.
- Use this mapping when we want to drop all existing data from the table before loading new data.
- Use the truncate target table option in the session properties, or use a pre-session shell command to drop or truncate the target before each session run.
Steps:
1. Open the folder where we want to create the mapping.
2. In the Mapping Designer, click Mappings > Wizards > Getting Started.
3. Enter a mapping name and select Simple Pass Through, and click next.
4. Select a source definition to use in the mapping.
5. Enter a name for the mapping target table and click Finish.
6. To save the mapping, click Repository > Save.
2. SLOWLY GROWING TARGET
- Loads a slowly growing fact or dimension table by inserting new rows.
- Use this mapping to load new data when existing data does not require updates.
- The Slowly Growing Target mapping filters source rows based on user-defined comparisons, and then inserts only those found to be new to the target.
Handling Keys: When we use the Slowly Growing Target option, the Designer creates an additional column in target, PM_PRIMARYKEY. In this column, the Integration Service generates a primary key for each row written to the target, incrementing new key values by 1.
Steps:
1. Open the folder where we want to create the mapping.
2. In the Mapping Designer, click Mappings > Wizards > Getting Started.
3. Enter a mapping name and select Slowly Growing Target, and click next.
4. Select a source definition to be used in the mapping.
5. Enter a name for the mapping target table. Click Next.
6. Select the column or columns from the Target Table Fields list that we want the Integration Service to use to look up data in the target table. Click Add. These columns are used to compare source and target.
· We select EMPNO as it is key column in the source to compare with target.
7. Click Finish.
8. To save the mapping, click Repository > Save.
Note: The Fields to Compare for Changes field is disabled for the Slowly
Growing Targets mapping.
Slowly Growing target example
I’ll show u how to implement the manually Implement the SCD’s rather than the wizard. The reason why we opt this way is once the audience understand the concept driving SCD they can also illustrate things.
No comments:
Post a Comment