Thursday, January 5, 2012

INFA: How to convert binary fields to series of 1s and 0s

There are some cases that require to convert a binary field to a sequence of bits. 
Lets say that we have a source system that stores a series of flags in just one field by using a binary, data type. This is a common way of storing flags in ERP systems since you can later add more fields without altering the DB schema, by just utilizing unused bits. During ETL we want to convert this binary datatype to a string so we can check the value that a position has.

e.g convert to 01000000 and then get the value at position 2, at this example 1 denotes that the customer is active.


An easy way to achieve this, is by using the ENC_BASE64 encoding function (introduced at Infa 8.x).
Base64 is an encoding scheme that represents binary data in ASCII characters. It is used mainly for binary data transfer through emails. Lets see some basics about base64 Encoding.

Base64 Primer
  1. "=" at base 64 encoding stands for filler char that has zero value.
  2. Encoding and decoding of base64 algorithm is based in sets of six bits thus the filler charachters when appropriate.
  3. Every ASCII Char at the BASE64 sequence has a coresponding value that stands for (see Char Sequence table below.)


Building the reusable transformation

The whole process consists of two distinct steps:
  1. Encode the incoming Bynary data to BASE64 format
  2. Decode the emerged data to a string of bits
The above steps are going to be encapsulated at a reusable transformation. The aforementrioned expression transformation accepts as input a binary field (binary(4) at this example) and returns a varchar(32) that contains the bit series. As a plus i added an optional input port. That if feeded with 'Y' reverses the bit sequence.


Now lets start with the theory behind the implementation. You can download the transformation at the end of the post.


All the above steps are being encapsulated at a reusable transformation that you can download and try it Here.

No comments:

Post a Comment