Using an Error Bitmap

When running any mapping many things can go wrong and this usually results in several attempts to run a mapping with gradually improving source data. For instance, the first run some character conversion fails and the record is written to the error file. This is noticed and the source data is adjusted. When running the mapping again another error is detected and the record is again written to the error file. It is much more convenient to derive all the possible errors in one run, with an error bitmap.

The idea behind the error bitmap is that one number shows which errors have occurred for that particular row. By doing this only one run is required to see all the errors which have been checked for that particular mapping and record. The result of the error bit expression is stored in an extra audit attribute (ERROR_CODE, numeric)

The basic principle is as follows:
Possible error situation                              Bit position
1                                                                  1
2                                                                  2
3                                                                  4
4                                                                  8
5                                                                  16
6                                                                  32
7                                                                  64
8                                                                  128

For each possible error the expression checks if this is valid for the current records. If this is true the corresponding bit position (a multiplier of 2) will be selected. The eventual error code is the sum of these bit positions.

For example, if the errors 2 and 6 indeed occur, the error code will be 34 (2 + 32).

This concept/example can also be shown as (hence the bitmap):
00100010 (read from right to left)

This shows us that error situation 2 and error 6 have occurred for that particular record. 

The implementation of the error bitmap concept is done by creating a (reusable) expression, procedure or code with inputsfor each possible error situation (0 or 1). Each situation is tested separately in the mapping expressions and the error bits are linked to the reusable error handling expression. Within this expression each bit is multiplied with the bit position thus resulting in an error bitmap. This process is displayed in the following two pictures (examples in Informatica Powercenter)

Inside the error handling expression. 

Inside the Error Bitmap

After storing the error codes the corresponding error description can be combined from a reference table which contains the possible error codes. This reference table contains the bitmap values (numbers!) with the corresponding error description.

By joining the error record from the mapping with the error code as stored in the reference table (bitwise join) you can show what errors have been detected for the record. The query to be used is different for each database system, providing the RDBMS provides this option. I know that Oracle, DB2 and SQL Server have this option. An example is: 

SELECT
   reject.*, 
   error_table.description
FROM
   <reject_tablename>        reject,
   ERROR_CODE_REFERENCE      error_table
WHERE
   BITAND(reject.error_code,error_table.bit_mask_value) = error_table.bit_mask_value

 
Roelant Vos

Roelant Vos

You may also like...

2 Responses

  1. Sjoerd Evers says:

    Nice article and a smart way of errorhandling by using errorbitmap. As far as now i have never used the bitwise join. Seems this a good way to get full use of it. The next time there’s going to be a discussion how to implement error handling in the DWH i’ll consider this above the old fashioned way.

    In the beginning of your article you refer to previous articles about error handling but i can not seem to find them. Do you have a link towards these?

     
  2. Roelant Vos Roelant Vos says:

    Hey Sjoerd!

    Thanks, well it seems there aren’t any prior posts about error handling. I meant to create a design pattern about it which I will do very soon 🙂 I have updated this post accordingly.

    Greetings,
    Roelant

     

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.