When i first came across using the Normalizer, all i was thinking about is just passing multiple values in one row of several columns to multiplly/normalise the source to several rows. Say i have a table source tracking down each Customer’s Instalment on a monthly basis as like, And now my requirement to put these down as different transactions to the target, then as we normalise it through this transformation which comes in handy here.

All we might know

So this was an assumption on what can be the limit of this transformation is just to Normalise the de normalised data, but then this was something i got to know that we can make a big use of some more features of this transformation.The Normalizer tab in the transformation where we define the Ports which are to pass through and doing so we also define the
‘Occurence’ and this is where we define the Column which has to be Normalized from multiple column to rows. As in our instance i define the Monthly amount Column, where i shall have the Occurence given as 12.

Understanding the GCID and GK ports –

Now my Normalizer shall have 12 inputs and the output port for these shall be 12 rows with different Amount values and other Column being of same value. Also i will need to have a track on the months and would like to track one extra column which i can define as Month_Name which has to give the Month. So how i do this, quite simple – as we can see in the port tab along with the 12 Ports of Month_Amount i have a new port auto created as’GCID_(the reoccuring_Column_name)’. This represents a Integer value (Generated COlumn ID). This will be repetitive values of 1 to 12 for each Amount with respect to the month.

Now all we have to do is pull this port also to the next Expression transformation and have your expression to define the Month name depending upon the GCID value. Also.. also we have the another port created by default in the Normalizer for the respective multiple occuring Column GK_{column_name}. This is the Generated
Key column which is a Key column can be used if required for a unique key value.

VSAM and the Pipeline Type:

Also the Normalizer i have used as a transformation was only the Pipeline transformation as what we just discussed in the above example instance. There als is a type known as VSAM Normalizer, which is just a Saource Qualifier like transformation for a COBOL source (COBOL VSAM source). Here the VSAM COBOL source type does gives a de normalized data which are then Normalized through the VSAM Normalizer.The VSAM Normalizer does not allows to edit the ports and the the Normalizer tab is just read only. A VSAM Normalizer transformation has one input port for a multiple-occurring column unlike the Pipeline Normalizer which has multiple input ports for the multiple occuring value.

Have not got a chance to wet my hands with VSAM type, hope to do some day soon and shall update on many more such experiences. Untill then take care and a happy learning 🙂

Advertisements

A little light on Update Over ride and its use:

We have a an Update strategy transformation to perform any kind of update on the target table, then what is it all abot the usage of Update over ride in a target.

Before we can jump into our topic, for all those who are new to Update startegy -> Update startegy transformation is something that can help you in updating your target table with the new values on the basis of the Primary key match.
Making it simple,”UPDATE tableA set ColumnA = New_Val Where Primary_Key matches”

So now lets take up the use of Update Over ride option, as i was going through this particular scenario i had to Update my Target table with the new values, but not for the Primary Key Column. Rather i was in a position where i had to update the table on the equation of non Primary key column.

As an instance, i had to update the salary of Employees of country Australia. WHere as in my table i do not have the Country column as a Primary key (Employee_ID shall be a Prmary key here). We got to update this!! but how?

Then comes the use of having Update over ride, where i have to lookinto the Properties of my targe table. There we have the “Update Override”. SImple on we have the expression or the Update query to be written as one below,

UPDATE TableA
SET Employee_ID = :TU.Employee_ID, Country = :TU.Country, Salary = :TU.Salary
–Here is where you add condition of check on basis of Non Primary key
WHERE
Country = ‘AUSTRALIA’

And this shall update the records with the new records (changed salary) that have the country = ‘Australia’. SO this is where the Update Override comes as a help in hand.

Thanks for reading, please share. sharing makes us feel good 🙂

%d bloggers like this: