DSXchange

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

Have you looked into the Data Rules stage, seen if that can be leveraged to do what you need?

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

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

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.

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