Data Vault mapping task template configuration tutorial

Back Next

Step 2. Configure customer-order relationships in a link table

We need to define customer and order relationships and write the business keys to a link table. To do this we use the Data Vault – Link mapping task template.

The following image shows the mapping this template is based on:

When the task runs, it calculates customer and order hash keys, and a primary hash key for the link table. It then writes the keys and the date inserted to the target.

On the

Templates

page, click the

Data Vault – Link

tile and then click

Create a mapping task

On the

General

tab, select the runtime environment to run the task and then click

On the

Sources

tab, configure the following sources:

Hub_Table_1 source. Select the Snowflake connection and H_ORDER hub table.

Hub_Table_2 source. Select the Snowflake connection and H_CUSTOMER hub table.

Link_Table. The template is preconfigured to check if records are already present in the target link table. Select the target Snowflake connection and LNK_ORDER_CUSTOMER link table.

Src_Landing_Table. Select the Snowflake connection and the ORDER landing table.

On the

Targets

tab, select the Snowflake target connection and link table, LNK_ORDER_CUSTOMER.

Select the

Insert

operation.

On the

Input Parameters

tab, resolve the following parameters:

Hub Key Expression 1. Defines the hash key in the ORDER landing table and outputs the value to the Hub_Key_Calculated1 field. You assign the hash key to the POS_TRANSACTIONID field using the following expression:

MD5(TO_CHAR(TRUNC(POS_TRANSACTIONID)))

Hub Key Expression 2. Defines the customer hash key and outputs the value to the Hub_Key_Calculated2 field. You assign the hash key to the POS_CUSTOMER_ID field using the following expression:

MD5(LTRIM(RTRIM(POS_CUSTOMER_ID)))

Hub 1 – Join Condition: Defines the join condition between the H_ORDER hub table and the hub order key we calculated in step 6a. You enter the following expression:

Hub_Key_Calculated1 = Hub1_ORDER_HUB_KEY

Hub 2 – Join Condition. Defines the join condition between the H_CUSTOMER hub table and the hub customer key we calculated in step 6b. You enter the following expression:

Hub_Key_Calculated2 = Hub2_CUSTOMER_HUB_KEY

Join Condition New Record. Joins new records with existing data. You enter the following expression:

Hub_Key_Calculated1 = Link_ORDER_HUB_KEY AND Hub_Key_Calculated2 = Link_CUSTOMER_HUB_KEY

Link Key: Generates the hash key for the link table. Concatenate the customer and order keys with the following expression:

MD5(Concat(Hub1_BK_ORDER_ID,Hub2_BK_CUSTOMER_ID))

Target Field Mapping. Defines the field mapping between incoming fields and target fields. The following table shows how to configure the field mapping: