TDM LU Implementation - Hierarchy Support

TDM Relationship Tables

To support LU hierarchy and extracting or copying selected business entities and their related data, TDM must identify which child entities are related to each parent entity.

For example, to copy Customer 1 from the Production environment, TDM must identify the Billing Accounts and the Orders of Customer 1.

TDM relationship tables hold the links between the parent ID and their children IDs. There are two TDM relationship tables in the TDM DB:

TDM_LU_TYPE_RELATION_EID

This table holds the link between the parent-child source IDs. The relationship is saved per source environment. In addition, each Data versioning extract task creates separate records in the TDM_LU_TYPE_RELATION_EID table with the version name, date and the time of the extracted version of entities.

This table is used for the following:

Which Process Populates the TDM_LU_TYPE_RELATION_EID?

The TDM_LU_TYPE_RELATION_EID is populated by carrying out a sync on the parent LUI. The fnEnrichmentChildLink enrichment function is attached to the root table of each parent LU. It populates the TDM_LU_TYPE_RELATION_EID table with the parent-child link IDs except for delete only tasks or reserve only tasks where no data is extracted from the data sources. This function runs on the parent LU. The SQL queries are populated in the trnChildLink translation to get the child IDs of the task's child LUs. The related child IDs are populated on each parent LUI.

TDM_LU_TYPE_RELATION_EID Structure

Column Name Description
source_env Name of the source environment of the TDM extract task.
lu_type_1 Name of the parent LU. For example, Customer.
lu_type_2 Name of the child LU. For example, Order.
lu_type1_eid Entity ID (EID) of the parent LU. For example, 1.
lu_type2_eid Entity ID (EID) of the child LU. For example, 12.
creation_date Creation date of the record.
version_name Populated by the task name (version name) of DataFlux extract tasks. During regular extract tasks, this column is populated by an empty string.
version_datetime Populated by the execution time (version DateTime) of the DataFlux extract tasks. During regular extract tasks, this column is populated by a default value: 1/1/1970.

Example 1:

Customer 1 has orders 10, 12, and 13 in the Production environment. The LUI of Customer 1 is Production_1. The Sync of the Production_1 LUI of the Customer LU populates the TDM_LU_TYPE_RELATION_EID with the following records:

source_env lu_type_1 lu_type_2 lu_type1_eid lu_type2_eid creation_date version_name version_datetime
Production Customer Order 1 10 1/8/2021 13:31:00   1/1/1970 00:00:00
Production Customer Order 1 12 1/8/2021 13:31:00   1/1/1970 00:00:00
Production Customer Order 1 13 1/8/2021 13:31:00   1/1/1970 00:00:00

Example 2:

Customer 1 has orders 10, 12 and 13 in the Production environment. The user creates and executes a TDM extract DataFlux task to save a version of Customer 1 in Production. The execution date and time is 8-Jan-2021 at 14:15:30 PM. The LUI of Customer 1 is Production_1_saveCust1_20210108141530. The sync of the Production_1_saveCust1_20210108141530 LUI of the Customer LU populates the TDM_LU_TYPE_RELATION_EID with the following records:

source_env lu_type_1 lu_type_2 lu_type1_eid lu_type2_eid creation_date version_name version_datetime
Production Customer Order 1 10 1/8/2021 14:15:30 saveCust1 1/8/2021 14:15:30
Production Customer Order 1 12 1/8/2021 14:15:30 saveCust1 1/8/2021 14:15:30
Production Customer Order 1 13 1/8/2021 14:15:30 saveCust1 1/8/2021 14:15:30

TDM_LU_TYPE_REL_TAR_EID

This table holds the link between the parent-child target IDs. The relationship is maintained per target environment. The table is used to build a list of entities of child LUs when executing a delete or Data Versioning load task.

Which Process Populates the TDM_LU_TYPE_REL_TAR_EID?

The TDM_LU_TYPE_REL_TAR_EID is populated by the sync of the parent LU which populates the related child IDs on each parent entity before deleting the parent entity from the target environment. The fnEnrichmentChildLink enrichment function is attached to the root table of each parent LU and populates the TDM_LU_TYPE_REL_TAR_EID table only if the TDM task deletes entities from the target environment. The SQL queries are populated in the trnChildLink translation and are run on the parent LU to get the target child IDs of the task's child LUs. The related child IDs are populated on each parent LUI.

TDM_LU_TYPE_RELATION_EID Structure

Column Name Column Name
target_env Name of the target environment of the TDM task.
lu_type_1 Name of the parent LU. For example, Customer.
lu_type_2 Name of the child LU. For example, Order.
lu_type1_eid Target Entity ID (EID) of the parent LU. For example, 30.
lu_type2_eid Target Entity ID (EID) of the child LU. For example, 101.
creation_date Creation date of the record.

Parent LU - Implementation Guidelines

Although Business Entities are defined in the TDM GUI, the following guidelines must be implemented to support parent-child LU hierarchy:

  • Populate the trnChildLink translation object. Note that a parent LU can have several child LUs. Populate a separate record for each child LU with the SQL queries to select the source and the target child IDs.
  • Add the fnEnrichmentChildLink function as an enrichment function to the FABRIC_TDM_ROOT root LU table. The enrichment function runs the SQL queries populated in the trnChildLink translation on the LU data and populates the TDM_LU_TYPE_RELATION_EID table in the TDM DB using the link of the parent IID to its children IIDs.

Previous

TDM LU Implementation - Hierarchy Support

TDM Relationship Tables

To support LU hierarchy and extracting or copying selected business entities and their related data, TDM must identify which child entities are related to each parent entity.

For example, to copy Customer 1 from the Production environment, TDM must identify the Billing Accounts and the Orders of Customer 1.

TDM relationship tables hold the links between the parent ID and their children IDs. There are two TDM relationship tables in the TDM DB:

TDM_LU_TYPE_RELATION_EID

This table holds the link between the parent-child source IDs. The relationship is saved per source environment. In addition, each Data versioning extract task creates separate records in the TDM_LU_TYPE_RELATION_EID table with the version name, date and the time of the extracted version of entities.

This table is used for the following:

Which Process Populates the TDM_LU_TYPE_RELATION_EID?

The TDM_LU_TYPE_RELATION_EID is populated by carrying out a sync on the parent LUI. The fnEnrichmentChildLink enrichment function is attached to the root table of each parent LU. It populates the TDM_LU_TYPE_RELATION_EID table with the parent-child link IDs except for delete only tasks or reserve only tasks where no data is extracted from the data sources. This function runs on the parent LU. The SQL queries are populated in the trnChildLink translation to get the child IDs of the task's child LUs. The related child IDs are populated on each parent LUI.

TDM_LU_TYPE_RELATION_EID Structure

Column Name Description
source_env Name of the source environment of the TDM extract task.
lu_type_1 Name of the parent LU. For example, Customer.
lu_type_2 Name of the child LU. For example, Order.
lu_type1_eid Entity ID (EID) of the parent LU. For example, 1.
lu_type2_eid Entity ID (EID) of the child LU. For example, 12.
creation_date Creation date of the record.
version_name Populated by the task name (version name) of DataFlux extract tasks. During regular extract tasks, this column is populated by an empty string.
version_datetime Populated by the execution time (version DateTime) of the DataFlux extract tasks. During regular extract tasks, this column is populated by a default value: 1/1/1970.

Example 1:

Customer 1 has orders 10, 12, and 13 in the Production environment. The LUI of Customer 1 is Production_1. The Sync of the Production_1 LUI of the Customer LU populates the TDM_LU_TYPE_RELATION_EID with the following records:

source_env lu_type_1 lu_type_2 lu_type1_eid lu_type2_eid creation_date version_name version_datetime
Production Customer Order 1 10 1/8/2021 13:31:00   1/1/1970 00:00:00
Production Customer Order 1 12 1/8/2021 13:31:00   1/1/1970 00:00:00
Production Customer Order 1 13 1/8/2021 13:31:00   1/1/1970 00:00:00

Example 2:

Customer 1 has orders 10, 12 and 13 in the Production environment. The user creates and executes a TDM extract DataFlux task to save a version of Customer 1 in Production. The execution date and time is 8-Jan-2021 at 14:15:30 PM. The LUI of Customer 1 is Production_1_saveCust1_20210108141530. The sync of the Production_1_saveCust1_20210108141530 LUI of the Customer LU populates the TDM_LU_TYPE_RELATION_EID with the following records:

source_env lu_type_1 lu_type_2 lu_type1_eid lu_type2_eid creation_date version_name version_datetime
Production Customer Order 1 10 1/8/2021 14:15:30 saveCust1 1/8/2021 14:15:30
Production Customer Order 1 12 1/8/2021 14:15:30 saveCust1 1/8/2021 14:15:30
Production Customer Order 1 13 1/8/2021 14:15:30 saveCust1 1/8/2021 14:15:30

TDM_LU_TYPE_REL_TAR_EID

This table holds the link between the parent-child target IDs. The relationship is maintained per target environment. The table is used to build a list of entities of child LUs when executing a delete or Data Versioning load task.

Which Process Populates the TDM_LU_TYPE_REL_TAR_EID?

The TDM_LU_TYPE_REL_TAR_EID is populated by the sync of the parent LU which populates the related child IDs on each parent entity before deleting the parent entity from the target environment. The fnEnrichmentChildLink enrichment function is attached to the root table of each parent LU and populates the TDM_LU_TYPE_REL_TAR_EID table only if the TDM task deletes entities from the target environment. The SQL queries are populated in the trnChildLink translation and are run on the parent LU to get the target child IDs of the task's child LUs. The related child IDs are populated on each parent LUI.

TDM_LU_TYPE_RELATION_EID Structure

Column Name Column Name
target_env Name of the target environment of the TDM task.
lu_type_1 Name of the parent LU. For example, Customer.
lu_type_2 Name of the child LU. For example, Order.
lu_type1_eid Target Entity ID (EID) of the parent LU. For example, 30.
lu_type2_eid Target Entity ID (EID) of the child LU. For example, 101.
creation_date Creation date of the record.

Parent LU - Implementation Guidelines

Although Business Entities are defined in the TDM GUI, the following guidelines must be implemented to support parent-child LU hierarchy:

  • Populate the trnChildLink translation object. Note that a parent LU can have several child LUs. Populate a separate record for each child LU with the SQL queries to select the source and the target child IDs.
  • Add the fnEnrichmentChildLink function as an enrichment function to the FABRIC_TDM_ROOT root LU table. The enrichment function runs the SQL queries populated in the trnChildLink translation on the LU data and populates the TDM_LU_TYPE_RELATION_EID table in the TDM DB using the link of the parent IID to its children IIDs.

Previous