Using Custom Parameters

The row level processing task that we have created uses an SQL query to identify the department that should be copied. At this point, the department name is hard-coded into the query. In practice, it is often desirable to allow the end users to specify an arbitrary department name when executing the copy process. This can be achieved by creating a custom parameter for the department name, and modifying the SQL query in the task template so that it contains a placeholder.

The actual value for this custom parameter can be specified at the task level. This means that multiple tasks can be derived from the same task template, and they can copy different departments by specifying different department names as the value for this custom parameter.

Creating a custom parameter

  1. Select the menu category XDM Configuration and click the menu item Custom Parameters. Then click + Create.

  2. In the panel New Custom Parameter, enter the following information:

    Name

    Department Name

    Description

    Please enter the exact name of the department

    Variable

    departmentName [1]

    1. This is the internal name of the parameter. When referencing the parameter in code - for example in an SQL query - use this name. The name is case-sensitive.

    Parameter Type

    Select String from the drop-down list

    Default Value

    (leave empty)

    Requirement Level

    Select RUNTIME from the drop-down list

    Validation Type

    Select None from the drop-down list

    Validation Expression

    (leave empty)
    Screenshot of the custom parameter settings

  3. Click the button Save Changes.

We can now use this custom parameter in task templates.

Using a custom parameter in a task template

  1. In the left sidebar, click the menu category Tasks to expand the tasks menu and select the menu item Task Templates.

  2. In the tab Row Level Processing, click the button Button with an arrow pointing to the right-hand direction on the left side of Row Level Processor Task Templates to expand the list. Locate the task template Copy employees by department name and click the button Button with an arrow pointing to the right-hand direction next to the templates name.

  3. Click the button Edit. Then, in the top panel, select the tab Custom Parameter and click the button Configure Custom Parameters.

  4. In the dialog window Custom Parameter Activation, search for the name of your custom parameter in the search bar and select Department Name to add the custom parameter to the task template. Click the button Apply.

  5. In the Department Name text field, which is now visible, enter "Customer Service"

    Whenever a row level processor task template already contains a task, any custom parameter specified in the task template with requirement level set to RUNTIME must have a value assigned, otherwise the custom parameter cannot be saved.

  6. Click the button Save Changes.

The task template now has a custom parameter with the desired department name. All tasks that are derived from this template require the department name as a value for this parameter, otherwise the tasks cannot run. If no value is assigned at the task level, then the task template value is used.

We now need to use the custom parameter in the SQL query in our start condition.

  1. In the top panel, select the tab Start Conditions. You will see the text field with the SQL query that currently still contains a constant value for the department name.

  2. Click the button Edit and change the contents of the field to the following:

    SELECT ${uniqueRowIdentifier}
FROM "${startTableSchema}"."${startTableName}" "T"
WHERE "T".dept_name = '${departmentName}'

    The expression ${departmentName} at the end of the query is a placeholder for the value of a parameter. Placeholders always start with a dollar symbol, followed by the internal name of the desired parameter in braces. In this case, the actual value of the parameter Department Name, whose internal variable name is departmentName, will be inserted when the task is executed.

    Make sure to retain the apostrophes around the expression ${departmentName}. The department name is later inserted via textual replacement. If the apostrophes are missing, the resulting query will not be valid.

  3. Click the button Save Changes.

Supplying a value for a custom parameter in a task

A value for the custom parameter can now be set at task level. We previously copied the department "Customer Service" and its employees to the qa2 environment. We now want to copy the department "Research" and its employees into the same target tables so the data from both copy processes are merged.

  1. In the left sidebar, click the menu category Tasks to expand the tasks menu and select the menu item Task Templates.

  2. In the tab Row Level Processing, click the button Button with an arrow pointing to the right-hand direction on the left side of Row Level Processor Task Templates to expand the list. Select Copy employees by department name by clicking the button Button with an arrow pointing to the right-hand direction next to the template’s name. Locate the task Copy employees by department name to qa2 in the tab Tasks in the expandable panel Tasks and click the button Button with an arrow pointing to the right-hand direction on the left side of the task name.

  3. Click the button Edit and select the tab Properties to overwrite in the top panel. Click the button Add Property and choose the property Department name. You will be presented with a text field for the custom parameter value. Enter "Research" into this text field and click the button Apply.

    The query is case-sensitive, so make sure to enter the value "Research" exactly as it is shown here. Otherwise, the query will match no rows. Any extra characters will also result in no matches.

  4. Click the button Save Changes

Now the task template introduces the custom parameter and the task provides the value.

The task is now ready to run.

Executing the row level processor task

To execute your second row level processor task:

  1. On the panel to the right, click the button Execute or while in your task template, locate the task in the bottom panel under the tab Tasks and click the execute button (Button with a play icon) on the right.

    1. Alternatively: In the left sidebar, click the menu category Tasks to expand the tasks menu and select the menu item Task Templates.

    2. Click the button Button with an arrow pointing to the right-hand direction on the left side of Row Level Processor Task Templates to expand the list. Select Copy employees by department name by clicking the button Button with an arrow pointing to the right-hand direction next to the templates name. Locate the task Copy employees by department name to qa2 in the tab Tasks in the expandable panel Tasks and click the button Button with an arrow pointing to the right-hand direction on the left side of the task name.

    3. In the right sidebar, click the button Execute.

  2. A dialog window opens. Leave Interrupt execution unchecked and click the button Execute and view. This will schedule the task for immediate execution and switch the main view of Executed tasks.

  3. The task runs asynchronously in the background. Every five seconds, the status is refreshed automatically. Within a few minutes the task should finish.

  4. In the top left corner, the task status should show as COMPLETE.

After the task finishes, the schema qa2 contains data from two departments: Customer Service, which was copied earlier, and Research. The number of rows that were inserted into the target tables by this row level processor task can be verified as follows:

  1. Find the row Stage6 - Apply data, click the button with the three dots in the rightmost column of the table. Then select the button Apply row operations.

    Screenshot of the executed steps of a row level processor task

  2. A panel will appear that shows the number of apply, delete and discard operations that were executed against each of the participating tables. You should not see any deletes or discards, and you should see the following number of apply operations:

    Table Applied

    departments

    1

    dept_emp

    21126

    dept_manager

    2

    employees

    21126

    salaries

    200615

    titles

    31461

Now that two row level processor tasks have been executed, the total number of rows in the tables in the qa2 schema should be as follows:

Table Number of rows

departments

2

dept_emp

44706

dept_manager

6

employees

44706

salaries

424259

titles

66536

Previous section: Row level processing tasks | Next section: Data Shop