DSXchange

Hi all,

I am extracting aggregated data from the warehouse using oracle sql ( invovled joining dimension and fact tables , summing up some measures, some translation of data column and sorting )
Now i need to build the DS job for it.

-should i get better performance if I break the logic down into a series of stages ( like ext, join, xfm, aggre, sort...etc )?
-Or let the Oracle handle all the processing? ( I am pulling data from one source only )

Thanks and Regards,
william

William,

there is no single good answer to that. If your SQL logic includes a big reduction in the amount of data then often it is faster to do it in the database and save I/O. Sometimes the DB machine is more heavily loaded than the DS or ETL machine, so processing can be faster inside DataStage.

It also depeds on where you database resides. If it is costly have carry huge number of rows from database server to DS server, it might be effecient to do the necessay aggregation steps from DS by either stroed procedure or using your SQL logic, and carry the necessay records for transformation to DS server.

Also, you need to always keep in mind, what time, or what part of day or night is your process going to run when it goes live. If during that time, not much database activity is taking place, then i would suggest doing aggregations sorting and joins at the database level.Transformations can be done in datastage.
My 2 centy.