Introduction
The Add Related Records feature is a powerful table transformation that enables you to generate records in one table using data from another table. This functionality is particularly useful when creating records in dimension tables based on fact table data.
How It Works
The transformation allows you to establish relationships between tables and automatically generate new records based on specified conditions and mappings.
Configuration Steps
1. Right click on the Dimension table, select Advanced, then Add Related Records
2. Configure the Add Related Records dialog items.
- Provide a name for the Table Transformation.
- Set up the necessary conditions and mappings that will be used to create the new related record.
- Configure the "Allow Default Value" and "Default Value" options to handle empty field scenarios.
Note that a Condition must be set relating the two tables in the Add Related Records dialog, even if a relation already exists between the two tables in the data warehouse.
Example
In this example, we have a dimension table and a fact table:
The Fact Table contains 3 rows of data:
The Dimension Table contains 2 rows of data:
Select Add Related Records on the dimension table:
Assign a name to the Table Transformation and setup the mappings and conditions needed to complete the Add Related Records dialog.
The “Allow Default Value” and “Default Value” can be used to supply values for mapped fields when they are empty.
The Table Transformation will appear on the Dimension Table as follows:
The Dimension table with the added related record is shown below:
The dimension record with Key = C was created from the fact table based the value of the DimKey field. The DimDescription field was also set to the DimKey field value, and the DimAttribute field was set to the text of "Auto Generated Entry" as configured by the fixed value mapping for this field.
