Dynamic transformation/lookup at a columnar level (Rules Eg)

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
hishamzz
Participant
Posts: 12
Joined: Tue Feb 12, 2008 3:08 am

Dynamic transformation/lookup at a columnar level (Rules Eg)

Post by hishamzz »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
hishamzz
Participant
Posts: 12
Joined: Tue Feb 12, 2008 3:08 am

Post by hishamzz »

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
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

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.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
hishamzz
Participant
Posts: 12
Joined: Tue Feb 12, 2008 3:08 am

Post by hishamzz »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
hishamzz
Participant
Posts: 12
Joined: Tue Feb 12, 2008 3:08 am

Post by hishamzz »

ray.wurlod wrote: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 ...
Premium content :(
Post Reply