Implement an error handling strategy while demonstrating the usage of a joiner transformation and mapplet.
Use this template as a guide for trapping errors in a mapping based on business requirements, sending errors to an error table so that they can be reviewed and corrected, and reloading fixed errors from the error table into the target system.
This mapping template provides two mappings (which contain a shared mapplet) that, taken together, illustrate a simple approach to utilizing PowerCenter objects in handling known types of errors (based on identified business requirements).
The essential transformation objects shown and utilized are expression transformations that provide error validation, lookup transformations that are used to compare or find prerequisite values, a router transformation that sends valid rows to the warehouse and error rows to an appropriate error table, a joiner transformation for combining the error records back to the original source details, and update strategy transformations to flag records for UPDATE or INSERT into the target tables. To further illustrate the reusability of common mapping logic, a mapplet has been created to promote the reuse of the data validation rules that are common between the two mappings.
The key to using an error table in this example is providing reference back to the original source data so that corrections can be performed at the point of origin. This ensures that the data is consistent across the various systems after corrections are made. The first mapping that runs is m_TT_ERROR_HANDLING_STRATEGY_CUSTOMER_LOAD. For each record that is flagged as an error, a record is loaded into the error table with a corresponding error description and reference to the original source record’s primary key (i.e. CUSTOMER_ID). A subject matter expert can review and correct errors in the original source data as necessary. The second mapping,
m_TT_ERROR_HANDLING_STRATEGY_REPROCESSED_CUSTOMER_LOAD, will reprocess the fixed records and load them into the target system if no additional errors are encountered.
Developers routinely write mapping logic that filters records with data errors out of a data stream. However, capturing those error records so that they can be corrected and re-loaded into a target system can present a challenge. The mappings in this mapping template illustrate a process for trapping error records, and sending the error rows – which includes a reference (i.e. CUSTOMER_ID) to the original record containing the error – on to an error table.
This template can accommodate any number of data errors, and provides maximum flexibility in allowing for error correction
Requires input from an external actor, such as a subject matter expert, to correct errors.
- PowerCenter 9.6.x Mapping XML (See Attachments section below for the file
wf_TT_ERROR_HANDLING_STRATEGY_REPROCESSED_CUSTOMER_LOAD_961.XML and wf_TT_ERROR_HANDLING_STRATEGY_CUSTOMER_LOAD_961.XML)
- PowerCenter 9.1.0 Mapping XML (See Attachments section below for the file wf_TT_ERROR_HANDLING_STRATEGY_REPROCESSED_CUSTOMER_LOAD_910.XML and wf_TT_ERROR_HANDLING_STRATEGY_CUSTOMER_LOAD_910.XML)
- PowerCenter 8.6.1 Mapping XML (See Attachments section below for the files wf_TT_ERROR_HANDLING_STRATEGY_REPROCESSED_CUSTOMER_LOAD_861.XML and wf_TT_ERROR_HANDLING_STRATEGY_CUSTOMER_LOAD_861.XML)
Error handling within these mapping templates address a few identified business requirements for customer data: a valid first name and last name, and ensuring that either a phone number or email address is included with the customer information. There are other areas of data validation that can be implemented in addition to the ones included here such as address validation and phone/email address format and structure validation. Again, this is dependent on requirements provided by the business to ensure a certain level of data quality in the data warehouse.
This mapplet demonstrates reusability of mapping logic that can be shared with multiple mappings. It performs the data validation on customer information coming from the source (i.e. CUSTOMER). The key components of this mapplet are:
- exp_VALIDATE_NAME_CONTACT_DETAILS – This transformation object performs the validation checks on the first name and last name through the ‘v_VALIDATE_NAME’ variable port. It ensures that neither, the first name or last name is NULL, and that these names are greater than 1 character. Otherwise, an error message is generated for this particular rule. In addition, there is a validation check to ensure that a phone number or email address has been provided through the ‘v_VALIDATE_PHONE_EMAIL’ variable port. An error message is only generated if both of these fields are empty or NULL.
- lkp_CUSTOMER_WAREHOUSE – This lookup transformation object checks the current customer record against customer records in the target system to ensure that only new customers are processed. It works in conjunction with the filter transformation object, fil_EXISTING_CUSTOMERS, to get rid of customer records that have been previously inserted into the target system.
- rtr_NEW_CUST_ERROR_REPROCESSED – Based on the various validation checks in the mapplet, this router transformation outputs the necessary data elements to generate records (or update existing error records in the CUSTOMERS_ERRORS table). For example, if the incoming customer record has been flagged as an error record, the router will supply the customer ID, error description and create date that will be used to insert a record into the CUSTOMERS_ERRORS table.
Because the validation logic is coming from the mapplet, mplt_VALIDATE_NEW_CUST_DETAILS, this mapping is simply providing the source data from the CUSTOMERS table to the mapplet. From here, the mapplet returns the details for either creating an error record in the CUSTOMERS_ERRORS table, or a new customer record in the CUSTOMER_WAREHOUSE.
After the identified customer records have been corrected and the FIXED flag has been set to ‘Y’ in the CUSTOMERS_ERRORS table, error records with the FIXED flag and a NULL value for REPROCESSED_DT will be joined with their corresponding customer records in the CUSTOMERS table to be reprocessed and re-validated. The key components of this mapping are:
- jnr_CUSTOMERS_JOIN_CUSTOMERS_ERRORS – This joiner transformation object demonstrates the capability of PowerCenter to join data sets from within the tool where a database join may or may not be possible. To ensure that the least amount of data is being cached by the PowerCenter integration service, the source with the least number of records is identified as the master souce table. In this case, it is the CUSTOMERS_ERRORS table. This table will be joined to the CUSTOMERS table on the CUSTOMER_ID field. Based on the error records within the CUSTOMER_ERRORS table, the corresponding customer details need to be brought into the mapping to be reprocessed and re-validated.
- upd_INSERT_CUSTOMERS_ERRORS – Based on the outcome of the mapplet, customer records that have been flagged with an error message will have a record inserted into the CUSTOMERS_ERRORS table. The error record will be flagged with DD_INSERT.
- upd_INSERT_CUSTOMER_WAREHOUSE – For customer records that have been successfully validated without any error messages, a new customer record will be inserted into the CUSTOMER_WAREHOUSE table. This record will be flagged with DD_INSERT.
- upd_UPDATE_CUSTOMERS_ERRORS – To ensure that fixed errors are not reprocessed multiple times (unless new error records are generated each time this process is run), the existing error record needs to be updated with a REPROCESSED_DT value. Assigning this value to the corresponding error record does not necessarily mean that the incoming customer record is free of errors. A new error record will be created in the event that a customer record being reprocessed fails the validation checks and an error message is generated.
There are many options available for more complex error handling operations besides the method being demonstrated here.
- The Mappings were originally built using PowerCenter Designer. If you are using PowerCenter Express (PCX), all the mappings cannot be imported as PCX includes only the Informatica Developer tool and not PowerCenter Designer tool. For example, Informatica Developer doesn’t support mappings that use “Sequence Generator” transformation.
- The objects are based on “UTF-8” codepage. If you have changed you codepage, XML may need to be edited.