Dynamic Exception and Rejection Handling

[suneeth]

Hi All
My requirement is to handle exception and rejection dynamically. After doing bit of reading I have learnt that I can do this using ETL/Business rules table. This table is a repository of all the rules which can be called by any ETL process. I started trying it. Has anyone implemented this before? Can anyone help with some suggestions how to implement it in the real scenario.

cheers

[chulett]

My requirement is to handle exception and rejection dynamically.

Perhaps you could give a little explanation as to what this means to you.

[suneeth]

My requirement is to handle exception and rejection dynamically.

Perhaps you could give a little explanation as to what this means to you.

What I meant was
I have some exception/rejection rules to be applied on a Column A.
1. Value is null - reject(reject means rejected values are captured in a rejections file or table.
2. Check the Not Null values against an look-up table. If match found take it to next layer(table or dataset)
3. If not found default it to "unmapped" and raise an exception(capture the exception records in a table or dataset)

Same to be performed on couple of columns. All the exception and rejetions are captured with some specific codes.

The easy or rather dirty way to do it is hard coding in the datastage jobs. By the rule might change periodically. This will lead to change the code periodically. Which I am trying to avoid.

I am trying to put the 3 rules in a rules table and call it the ETL process.

Hope I explaination is clear. Pls let me know if you have more questions.

[suneeth]

My requirement is to handle exception and rejection dynamically.

Perhaps you could give a little explanation as to what this means to you.

What I meant was
I have some exception/rejection rules to be applied on a Column A.
1. Value is null - reject(reject means rejected values are captured in a rejections file or table.
2. Check the Not Null values against an look-up table. If match found take it to next layer(table or dataset)
3. If not found default it to "unmapped" and raise an exception(capture the exception records in a table or dataset)

Same to be performed on couple of columns. All the exception and rejetions are captured with some specific codes.

The easy or rather dirty way to do it is hard coding in the datastage jobs. By the rule might change periodically. This will lead to change the code periodically. Which I am trying to avoid.

I am trying to put the 3 rules in a rules table and call it the ETL process.

Hope I explaination is clear. Pls let me know if you have more questions.

[vmcburney]

I don't think you can put rules code into a table and call it from DataStage, however you can put rules values into a table to avoid hard coding them in DataStage.

For example: if a flag field can only have the values of Y or N you can have a lookup table with the values of Y and N in it and do a lookup against it to validate the flag field. This is so you don't have to hard code 'Y' and 'N' in a transformer. You can also build IF statements using values in a table. If code = 'A' and status = 'CURRENT' than 'Active' would require hard coding. If you do a lookup with key fields of CODE and STATUS and bring back the field RESULT you would pass in A and CURRENT as values and get back CODE.

This is a good approach as it lets the business change the rule values and the behaviour of the rule without needing to change any code. It is a more predictable and low maintenance rule than a hard coded rule.

This works for business rule validation. It doesn't help you with metadata validation such as valid dates and integers. That has to be handled completely in your transformer or via implicit conversions with reject links.

What you get out of a job as an exception is what I call "Unpleasant Discharge". What you get dropped out of a job unexpectedly is what I call "Row Leakage". I tried to document an approach for exceptions in my data quality firewall blogs though it is still a work in progress for me:
- Data Quality Firewall 1: Unpleasant Discharge
- Data Quality Firewall 2: inside an ETL job

[ray.wurlod]

You can create business rules routines and store their names in a hashed file keyed by some kind of rule ID. You can then create a routine that invokes the named routine using late binding (also known as indirect call).

Code: Select all

SubrName = Arg1
Call @SubrName(Ans, Arg2)