TDM - Handling Parameters

TDM Task - Selecting Entities Based on Parameters

A TDM task enables you to select a subset of entities based on a predefined list of parameters. For example, copy 10 business customers that belong to Billing Cycle 1 and that are located in NY.
The parameters that are available for the task are attached to the LUs of the task's Business Entity. Parameters are defined at an LU level.

TDM Parameter Tables

When synced, the LUIs create and update the Parameters table in the TDM database. A separate parameters table is created for each LU. The naming convention of the parameters tables is <LU Name>_params.

Parameter tables are used for the following:

• Getting the list of available parameters per task.
• Getting the number of matching entities for the selected parameters of the task.
• Creating the entity list for the task if the task's selection method is based on parameters.
• Creating the entity list for the task if a random selection of entities is used whereby the entities are randomly selected from the parameters table in the task's root LU.

TDM Parameters - Implementation Guidelines

1. Verify that the LU_PARAMS is attached to the LU Schema.

2. Edit the COMBO_MAX_COUNT shared Global imported from the TDM Library, if needed. By default, the Global is populated with 49 and is checked when creating a TDM task using a parameters selection method. If the number of possible values in the TDM Parameters tables is less than or equals to the COMBO_MAX_COUNT value, the parameter is handled as a combo parameter and a list of all possible values for this parameter is displayed. If a value is not selected from the list, the parameter has more values than the threshold defined in COMBO_MAX_COUNT and you must enter the value in the parameter.

3. Note that if the COMBO_MAX_COUNT Global is updated after executing Extract tasks, it is required to repopulate the tdm_params_distinct_values TDM DB table:

• Verify that the COMBO_MAX_COUNT Global is defined as Final.

• Redeploy the TDM LU to Fabric.

• Run the UpgradeDistinctValues flow (imported from the TDM Library). This flow truncates and repopulates the tdm_params_distinct_values table based on the updated COMBO_MAX_COUNT value.

  Add Parameters for the Logical Unit - Optional

1. Add the LU's parameters to the LuParams MTable (located under the References in the project tree).

   Note that from TDM 8.1 onwards, the previous translation object - trnLuParams - is replaced with the LuParams MTable. Deploy all LUs to the debug server and run the RunTDMDBUpgradeScripts flow. This flow runs the convertLuTranslations flow to convert old TDM translations to the equivalent TDM MTables. Each execution of the convertLuTranslations flow deletes and re-populates the related MTables.

2. The LuParams has the following fields:

   • lu_name
   • column_name
   • sql

3. LuParams example:

   lu_name	column_name	sql
   CRM	first_name	Select first_name  From customer
   CRM	last_name	Select last_name  From customer
   CRM	line_number	Select contract.associated_line As line_number From contract
   CRM	num_of_open_cases	Select Count(*) As num_of_open_cases From cases Where Upper(cases.status) != 'CLOSED'
   CRM	open_case_date	Select case_date As open_case_date From cases Where Upper(cases.status) != 'CLOSED'
   CRM	num_of_subscribers	Select Count(*) As num_of_subscribers From contract
   CRM	state	Select state From address
   Billing	total_balance_amount	Select Sum(ifNull(Billing.balance.available_amount, 0)) As total_balance_amount From Billing.balance
   Billing	num_of_open_invoices	Select Count(*) As num_of_open_invoices From Billing.invoice Where Upper(Billing.invoice.status) = 'OPEN'
   Billing	total_payment_amount	Select Sum(ifNull(Billing.payment.amount, 0)) As total_payment_amount From Billing.payment
   Billing	vip_status	Select Distinct vip_status From Billing.subscriber
   Billing	subscriber_type	Select Distinct subscriber_type From Billing.subscriber
   Asset	transaction_duartion	Select Distinct duration From Asset.asset_transaction
   Asset	transaction_city	Select Distinct transactioncity From Asset.asset_transaction

4. The LU_PARAMS' population flow runs the fnEnrichmentLuParams function. This function runs the LU's SQL queries in the LuParams, creates the LU parameters table in the TDM DB if needed, and populates the LU parameters table in the TDM DB. Each parameter's column holds a JSON file that contains the values of the parameter. Each parameter can hold several values that are separated by a comma. For example:

   • Line number = {"(722) 404-4222","+1 (372) 682-2450,"+1 (799) 979-1233","883-486-7523","1394031132"}

Notes:

• The LU_PARAMS' population runs the SQL queries to retrieve the LU tables' data. Therefore, it has an execution order 999 to run after the remaining LU tables' population.
• Do not include spaces or special characters in parameter names.
• Even if parameters do not need to be defined for an LU, the LU_PARAMS table must be added to the LU Schema to create the <LU Name>_params table in the TDM DB. The <LU Name>_params table is needed by both entities selection methods of a TDM task: Parameters and Random Selection.
• The PARAMS_JSON field of the LU_PARAMS table contains the list of LU parameters and their values to enable the debugging of a given entity.
• Click here for more information about parameters handling.