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
Dynamic Exception and Rejection Handling
Moderators: chulett, rschirm, roy
Re: Dynamic Exception and Rejection Handling
Perhaps you could give a little explanation as to what this means to you.suneeth wrote:My requirement is to handle exception and rejection dynamically.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Dynamic Exception and Rejection Handling
What I meant waschulett wrote:Perhaps you could give a little explanation as to what this means to you.suneeth wrote:My requirement is to handle exception and rejection dynamically.
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.
Re: Dynamic Exception and Rejection Handling
What I meant waschulett wrote:Perhaps you could give a little explanation as to what this means to you.suneeth wrote:My requirement is to handle exception and rejection dynamically.
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.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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
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
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.