How to Identify Data Errors With Mappings -Mapping approach

In this post we will discuss about how to check data errors using mappings and how to handle these errors.In Datawarehouse Mapping we come across different kinds of errors.It depends on us that what kind of errors we want to capture and should we reload or correct these errors and also how to store them.

Data must be checked before loading to target table. One method is to use database Constraints or to use mappings to capture errors.

Using Mapping approach we can identify the data content errors and give the appropriate descriptions and then re route these errors to error table. Error table makes the analysis of captured errors easier and also improves the performance. This can be effective for errors like null fields, incorrect data types or date conversions.

Once we have captured the error, the next step is to separate these errors from the pipeline. We can do this using a Router Transformation which will route the error into a separate Error table (ERR_TBL).This Error table will hold the information about the error like,

• The Mapping name from which the error came.
• The ROW ID of the record
• Descritption about the error

 "We can make this approach in the form of a Mapplet and use it repeatedly in several mappings and hence reduce the complexity and make the error detection easier"

 

Once we capture the data, the Business Team needs to decide whether we need to correct the data and reload it or not. Using the Mapping approach we can capture all the data errors and constraint errors and the business users can analyze the errors more effectively.

Please share your valuable comments......