Hi Folks,
I know this has been asked before but I had not seen a proper response and uncertain as to whether my question actually falls in line with the questions posed by the rest of the members.
Question :-
(a) We have a table which has 10 columns
(b) We have to perform transformations/lookups on 5 of those columns based on whether the values within those columns fulfill the rules applicable on that column
(c) Each column can have 3-5 such rules and could be either a data transformation and/or lookup included.
Any thoughts on how this can be achieved via DataStage?
The idea is to create some sort of a plug and play rules engine.
Based on my limited knowledge i do not see this as being feasible natively on a DataStage front.
Please advice.
Regards,
Hisham
Dynamic transformation/lookup at a columnar level (Rules Eg)
Moderators: chulett, rschirm, roy
Have you looked into the Data Rules stage, seen if that can be leveraged to do what you need?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks for your reply chulett. Will have a look at it!
Skimming through though it would seem that this stage is ideal more on the data validation front rather than transformation/lookups.
To bring more clarity to my question.
Assume we have column A and the following are the rules and their transformation/lookup requirements
Rule 1 - If value of Column A = 'Area1' Then map Column C
Rule 2 - If value of Column A = 'Area2' Then lookup AREA_DIM where AREA_DIM.AREA=Column C and pull AREA_DIM.AREACODE
Something like the above.
Hope you get the gist of what I am saying.
Will read more on the Data Rules stage to see of any possiiblity.
Regards,
Hisham
Skimming through though it would seem that this stage is ideal more on the data validation front rather than transformation/lookups.
To bring more clarity to my question.
Assume we have column A and the following are the rules and their transformation/lookup requirements
Rule 1 - If value of Column A = 'Area1' Then map Column C
Rule 2 - If value of Column A = 'Area2' Then lookup AREA_DIM where AREA_DIM.AREA=Column C and pull AREA_DIM.AREACODE
Something like the above.
Hope you get the gist of what I am saying.
Will read more on the Data Rules stage to see of any possiiblity.
Regards,
Hisham
You could design a transformer to check each rule independently and then trigger look-ups via separate links with constraints so they only execute when the required condition is true. The data would then have to be re-joined to the main feed as required. Not the most performant of solutions due to the number of "fork joins" in the design, but it would work.
Thanks for your reply asorrell.
Yes, this is the only approach which i had in mind too but there are quite a number of other columns which have such rules which would make the job design a mess.
From what i have read so far there is no feasible way of having this implemented in DataStage natively in a plug and play fashion. Was wondering if anyone had going down the route of coming up with an approach or methodology for this.
Regards,
Hisham
Yes, this is the only approach which i had in mind too but there are quite a number of other columns which have such rules which would make the job design a mess.
From what i have read so far there is no feasible way of having this implemented in DataStage natively in a plug and play fashion. Was wondering if anyone had going down the route of coming up with an approach or methodology for this.
Regards,
Hisham
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Many moons ago I built such a dynamic rules engine using server jobs and server routines, using a feature of the server engine called "indirect call" where the name of the routine does not need to be provided till runtime. The only restriction really was that every routine needed the same number of arguments; however, since these could be dynamic arrays if necessary that wasn't really an issue.
If you're interested, check out the CALL @subrname syntax in the DataStage BASIC documentation.
If you're interested, check out the CALL @subrname syntax in the DataStage BASIC documentation.
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.