Related Records

The Add Related Records feature enables the insertion of records from one table into another table based on a specified set of conditions. This feature is commonly used to create related records in a dimension table using the fact table as input. It serves as a solution for handling Early Arriving Facts, which refers to situations where facts are available in the fact table before the related dimensions are present in a data warehouse. Additionally, Add Related Records can be utilized to consolidate disparate data by inserting records from one table into another when a key value is not yet present.

Early Arriving Facts

In a live working environment, it is possible that transactional data may contain values that have not yet been added to the source database in the corresponding dimension table. An example of this could be a Sales Invoice that has a Salesperson Code where the Salesperson Code does not yet exist in the Salesperson table. When the Prepare instance and Deliver instance are executed, the values for this salesperson will not exist in the Salesperson dimension. 

In TimeXtender Data Integration, it is easy to handle these Early Arriving Facts to show at least partial information until the data source is properly updated with all of the normal dimension information.

Prerequisites

A relation between the source table and destination table is required to add related records. For more information see:

• Table relations Tutorial

Add Related Records

The example below uses the following setup between a fact table and a dimension table.

1. Select Add Related Records under Advanced on the destination table

   

2. Provide a Name for the Related Records transformation.

   

3. Select the table to create records from in the Create Records from table dropdown.
4. Select the record condition as Exists or Not Exists. The Exists option will add records returned by the condition, whereas Not Exists will return records that fall outside the condition as defined in Step 7 below.
5. Select the Data Destination Table as Raw or Valid. The Valid option will insert the records after the data cleansing procedure, ensuring the values in any conditional lookup fields are overwritten by the related records insert.
6. Select the field mappings from the dropdowns in the Mapping area by either selecting a field from the source table or by selecting Fixed Value or None. Select Allow Default Value and specify a Default Value in order to populate the value of a mapped field when the field is empty. 
7. Select Add near the Conditions pane to add conditions, and then select a field from the source table in the first dropdown and a field from the destination table in the second dropdown. Select an operator to compare the two fields. Click OK
8. Deploy and execute the destination table with the Related Records transformation. Notice that new records have been added from the source table.