TDM Reference Processes

The list of Reference tables available for TDM tasks is populated in the trnRefList translation object. TDM Extract tasks store the selected Reference data in the Cassandra DB and the TDM Load tasks select the Reference tables from Cassandra and load them into the target.

TDM LU - tdmCopyRefTablesForTDM Job

This job is executed on each Reference table by the main task execution process of the Extract task and populates the Reference table in the Cassandra DB. It selects the data of the Reference table from the source DB. The source DB Interface Name and Schema Name settings are taken from trnRefList. The job creates the Reference Cassandra table - in case it does not exist - and then selects the data from the source table and populates it into the Cassandra table.

The job updates the status of the processed Reference table in the task_ref_exe_stats TDM DB table:

  • If the copy succeeds, it sets the status to completed.
  • If the copy fails, it sets the status to failed and populates the error_msg field with the error message.

Reference Cassandra Table

TDM Extract tasks can either extract data from different source environments or create different versions of a selected Reference table. As a result, each Cassandra table, which is created for a Reference table, must store different versions of the Reference table. Each such Cassandra table contains the following columns with the purpose of storing Reference data for different source environments and different versions:

  • SOURCE_ENV_NAME - populated by the source environment.
  • TASK_EXECUTION_ID - populated - by default - by ALL. When running a TDM Extract task in Data Versioning mode, this column is populated by the task_execution_id of the task execution.
  • TDM_REC_ID - an internal sequence set on each record and added to the PK of the Cassandra table.
  • RED_DATA - a text field, which contains a JSON with the selected source record.

The PK (primary key) of each Cassandra table consists of the following columns:

  • SOURCE_ENV_NAME
  • TASK_EXECUTION_ID
  • TDM_REC_ID

Example:

  • CUSTOMER_TYPE Reference table. This table has 3 fields: CUSTOMER_TYPE, CUSTOMER_SUB_TYPE and DESCRIPTION.

  • CUSTOMER_TYPE is populated in ENV1 as follows:

CUSTOMER_TYPE CUSTOMER_SUB_TYPE DESCRIPTION
I P Private customer
B S Small business
  • CUSTOMER_TYPE is populated in ENV2 as follows:

CUSTOMER_TYPE CUSTOMER_SUB_TYPE DESCRIPTION
I P Private customer
B S Small business
B M Medium business
B C Corporate customer
  • Creating and executing an Extract task with a regular mode (Data Versioning setting is cleared) to extract CUSTOMER_TYPE Reference table from ENV1. The Cassandra table is populated as follows:

SOURCE_ENV_NAME TASK_EXECUTION_ID TDM_REC_ID REC_DATA
ENV1 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 ALL 2 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
  • Creating an Extract Data Versioning task for CUSTOMER_TYPE Reference table on ENV1. The records of the created version are added to the Cassandra table:

SOURCE_ENV_NAME TASK_EXECUTION_ID TDM_REC_ID REC_DATA
ENV1 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 ALL 2 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV1 1234 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 1234 2 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
  • Create an Extract Task with a regular mode for CUSTOMER_TYPE on ENV2:

SOURCE_ENV_NAME TASK_EXECUTION_ID TDM_REC_ID REC_DATA
ENV1 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 ALL 2 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV1 1234 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 1234 1 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV2 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV2 ALL {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV2 ALL 3 {"customer_type":"B","customer_sub_type":"M","description":"Medium business"}
ENV2 ALL {"customer_type":"B","customer_sub_type":"C","description":"Corporate customer"}
  • The source data of ENV1 is updated and a new record is added to CUSTOMER_TYPE table for a Government customer type.

  • Execute again the regular Extract Task for CUSTOMER_TYPE on ENV1 again:

SOURCE_ENV_NAME TASK_EXECUTION_ID TDM_REC_ID REC_DATA
ENV1 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 ALL 2 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV1 ALL 3 {"customer_type":"B","customer_sub_type":"G","description":"Government"}
ENV1 1234 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 1234 1 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV2 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV2 ALL {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV2 ALL 3 {"customer_type":"B","customer_sub_type":"M","description":"Medium business"}
ENV2 ALL {"customer_type":"B","customer_sub_type":"C","description":"Corporate customer"}
  • The table is re-populated by the data of ENV1 and ALL version. The records of the specific version of ENV1 - 1234, remain unchanged.

    Previous

TDM Reference Processes

The list of Reference tables available for TDM tasks is populated in the trnRefList translation object. TDM Extract tasks store the selected Reference data in the Cassandra DB and the TDM Load tasks select the Reference tables from Cassandra and load them into the target.

TDM LU - tdmCopyRefTablesForTDM Job

This job is executed on each Reference table by the main task execution process of the Extract task and populates the Reference table in the Cassandra DB. It selects the data of the Reference table from the source DB. The source DB Interface Name and Schema Name settings are taken from trnRefList. The job creates the Reference Cassandra table - in case it does not exist - and then selects the data from the source table and populates it into the Cassandra table.

The job updates the status of the processed Reference table in the task_ref_exe_stats TDM DB table:

  • If the copy succeeds, it sets the status to completed.
  • If the copy fails, it sets the status to failed and populates the error_msg field with the error message.

Reference Cassandra Table

TDM Extract tasks can either extract data from different source environments or create different versions of a selected Reference table. As a result, each Cassandra table, which is created for a Reference table, must store different versions of the Reference table. Each such Cassandra table contains the following columns with the purpose of storing Reference data for different source environments and different versions:

  • SOURCE_ENV_NAME - populated by the source environment.
  • TASK_EXECUTION_ID - populated - by default - by ALL. When running a TDM Extract task in Data Versioning mode, this column is populated by the task_execution_id of the task execution.
  • TDM_REC_ID - an internal sequence set on each record and added to the PK of the Cassandra table.
  • RED_DATA - a text field, which contains a JSON with the selected source record.

The PK (primary key) of each Cassandra table consists of the following columns:

  • SOURCE_ENV_NAME
  • TASK_EXECUTION_ID
  • TDM_REC_ID

Example:

  • CUSTOMER_TYPE Reference table. This table has 3 fields: CUSTOMER_TYPE, CUSTOMER_SUB_TYPE and DESCRIPTION.

  • CUSTOMER_TYPE is populated in ENV1 as follows:

CUSTOMER_TYPE CUSTOMER_SUB_TYPE DESCRIPTION
I P Private customer
B S Small business
  • CUSTOMER_TYPE is populated in ENV2 as follows:

CUSTOMER_TYPE CUSTOMER_SUB_TYPE DESCRIPTION
I P Private customer
B S Small business
B M Medium business
B C Corporate customer
  • Creating and executing an Extract task with a regular mode (Data Versioning setting is cleared) to extract CUSTOMER_TYPE Reference table from ENV1. The Cassandra table is populated as follows:

SOURCE_ENV_NAME TASK_EXECUTION_ID TDM_REC_ID REC_DATA
ENV1 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 ALL 2 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
  • Creating an Extract Data Versioning task for CUSTOMER_TYPE Reference table on ENV1. The records of the created version are added to the Cassandra table:

SOURCE_ENV_NAME TASK_EXECUTION_ID TDM_REC_ID REC_DATA
ENV1 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 ALL 2 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV1 1234 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 1234 2 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
  • Create an Extract Task with a regular mode for CUSTOMER_TYPE on ENV2:

SOURCE_ENV_NAME TASK_EXECUTION_ID TDM_REC_ID REC_DATA
ENV1 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 ALL 2 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV1 1234 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 1234 1 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV2 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV2 ALL {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV2 ALL 3 {"customer_type":"B","customer_sub_type":"M","description":"Medium business"}
ENV2 ALL {"customer_type":"B","customer_sub_type":"C","description":"Corporate customer"}
  • The source data of ENV1 is updated and a new record is added to CUSTOMER_TYPE table for a Government customer type.

  • Execute again the regular Extract Task for CUSTOMER_TYPE on ENV1 again:

SOURCE_ENV_NAME TASK_EXECUTION_ID TDM_REC_ID REC_DATA
ENV1 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 ALL 2 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV1 ALL 3 {"customer_type":"B","customer_sub_type":"G","description":"Government"}
ENV1 1234 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV1 1234 1 {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV2 ALL 1 {"customer_type":"I","customer_sub_type":"P","description":"Private customer"}
ENV2 ALL {"customer_type":"B","customer_sub_type":"S","description":"Small business"}
ENV2 ALL 3 {"customer_type":"B","customer_sub_type":"M","description":"Medium business"}
ENV2 ALL {"customer_type":"B","customer_sub_type":"C","description":"Corporate customer"}
  • The table is re-populated by the data of ENV1 and ALL version. The records of the specific version of ENV1 - 1234, remain unchanged.

    Previous