Creating Consolidation Gold Tables
The Multiple Gold model adheres to the same overall premise as the Consolidation model. Configuration changes may be required to implement the model correctly, including configuring the Customer Consolidation model. The following changes should be considered while creating the Gold Consolidation tables.
1. Create the Gold objects, Gold_Customer and Gold_Address, to store the customer attributes and address attributes respectively.
a. The Gold_Address object contains the customer address. Link the address object to the Gold_Customer object with a foreign key.
b. On the foreign key relationship, select those customer attributes in Nova related description that are used in the matching mapping between the Consolidation (Staging) object and Gold view (see step 6 below). Typically, this is the customer name.
2. Create a conceptual object with Gold_Customer as the root object. This can be used for the Consolidation Object to Gold Object Interchange Mapping in Step 4.
3. Create a denormalized view based on a join between the Gold_Customer and the Gold_Address objects. While importing the view into OneData using the Update Schema function, set the base object as the Gold_Address table. The primary key of the view inherits the primary key of the Gold_Address table.
The view provides a consolidated view of customer and address information for the matching project. The view must contain all the columns from both the Gold_Customer and the Gold_Address tables, excluding the audit columns of the Gold_Customer tables.
Ensure that the following conditions are met when you create the view:
There are no columns with same name in Gold_Customer (considered the MASTER) and Gold_Address (considered the DETAIL) tables.
The column names in the view are identical to those in the underlying Gold tables.
Include the primary key columns of both the MASTER and DETAIL tables in the view.
Include all columns associated with the Matching and Survivorship configuration.
If using the Default System Based survivorship, include in the view the columns in the Gold objects associated with
Column Qualifier, Survivorship Detail.
Note:
You cannot insert data into the underlying tables through the view. This view is for read only purposes.
4. Create an Interchange Mapping for Consolidation (Staging) object to Gold object:
a. On the Menu toolbar, click Data Interchange > Configuration > Interchange Mapping. Click to create an Interchange Mapping for Consolidation (Staging) object to Gold object.
b. Click Add New Mapping.
c. Create the Interchange Mapping using the Gold conceptual object created in Step 2.
5. Create the Cleansing configuration.
a. Create Cleansing Project for Consolidation (Staging) object.
Note:
Ideally, one window key each should be defined based on the attributes of the customer (master) and address (detail) information, respectively. The window key generation logic for a particular window key should also, ideally, depend entirely upon the attributes of either the customer or address, and not a combination of both.
b. Create cleansing mappings for the Gold objects (Customer Gold and Address Gold) which are used when data is directly entered into the gold model.
In most cases, the cleansing project for the Customer Gold table must be created, and if the Cleansing project for the Consolidation object handles only address cleansing, the same cleansing project can be used to associate the cleansing mappings for the Address Gold as well. The Cleansing project for the Customer Gold table must be created only if the customer information (for example, name) requires cleansing.
6. Create the Matching configuration.
a. Create the matching project that must accept both the customer (Master) and address (Detail) attributes as input for the matching process.
b. Select the Gold View created between Gold_Customer table and Gold_Address table as the Gold object for matching mapping of the Consolidation object.
This selection ensures that both customer information and address information are part of the matching criterion.
If a match is found, one of the following actions is executed depending upon the value of the pattern:
MASTER. The MASTER (Customer_Gold) attributes have generated a match and a new entry is added into the DETAIL (Address_Gold) table. The MASTER table is updated according to the survivorship rules configured.
DETAIL or BOTH. A match is found on both the MASTER (Customer_Gold) and the DETAIL (Address_Gold) tables. So, both the tables are updated according to the survivorship rules configured.
Not Returned. If a pattern is not returned, but the score is above the match threshold, then the pattern is defaulted to BOTH.
Important:
Avoid this scenario. You should ensure that the data quality server always returns a pattern as part of the matching result. When usingwebMethods Integration Server as the data quality server, value of the pattern is in the output structure of matcher service.
7. Survivorship rules can be configured for both MASTER and DETAIL Gold objects.
a. To use the Default System Based and Historical survivorship, configure survivorship attributes on both MASTER and DETAIL Gold objects as explained in
Defining Default Survivorship Rules. b. For the Default System Based survivorship, add columns associated with the Column Qualifier, Survivorship Detail, in MASTER or DETAIL Gold objects.
8. On the Consolidation (Staging) object, the base object property on the column that stores the link to the gold object has to be set to the denormalized gold view with base column being set to the primary key of the view, which is the address_id column.