Data reconciliation using relations reconciliation-relations
The following example demonstrates a workflow that updates the database using the purchasing data in a file. The purchasing data contains data referencing elements from other dimensions, such as the client emails and product codes.
The workflow is made up of the following activities:
-
A Load file activity, which loads and detects the data of the file to import. The imported file contains the following data:
- Transaction date
- Client email address
- Code of product purchased
code language-none date;client;product 2015-05-19 09:00:00;mail1@email.com;ZZ1 2015-05-19 09:01:00;mail2@email.com;ZZ2 2015-05-19 09:01:01;mail3@email.com;ZZ2 2015-05-19 09:01:02;mail4@email.com;ZZ2 2015-05-19 09:02:00;mail5@email.com;ZZ3 2015-05-19 09:03:00;mail6@email.com;ZZ4 2015-05-19 09:04:00;mail7@email.com;ZZ5 2015-05-19 09:05:00;mail8@email.com;ZZ7 2015-05-19 09:06:00;mail9@email.com;ZZ6 -
A Reconciliation activity to bind purchasing data to database profiles as well as products. It is therefore necessary to define a relation between the file data and the profile table as well as the product table. This configuration is carried out in the activity’s Relations tab:
- Relation with the Profiles: the file’s client column is linked to the email field of the Profiles dimension.
- Relation with the Products: the file’s product column is linked to the productCode field of the Profiles dimension.
Columns are added to the inbound data in order to reference the foreign keys of the linked dimensions.
-
An Update data activity allows you to define the database fields to update using the imported data. As the data was already identified as belonging to the Transactions dimension in the previous activity, here you can use the Directly using the targeting dimension identification option.
By using the option that automatically detects fields to update, the links configured in the previous activity (to profiles and products) are added to the list of Fields to update. You must also make sure that the field that corresponds to the transaction date is correctly added to this list.
