DSXchange

yousuff1710 wrote:We require this, as our ETL process is performing bad compared to ELT. We are going to convert ETL jobs to SQL scripts to do transformations.

Or maybe you have badly designed ETL jobs?

If your jobs are just a straightforward lookup to a table in the same DB and schema then it is quite possible that SQL would be faster.

But if you were doing anything more complex than that (ie multiple transformations, conditional changes to data etc) a well designed ETL job should be quicker.

Of course each scenario is different, and a judgement call needs to be made each time. There are many factors which can make an ETL job 'slow' when compare to a purpose built SQL process, (eg network speeds, o/s storage, memory availability, data volumes etc)

But to answer your question - not that I am aware of. There are methodologies out there which do similar to what you suggest, but they rely on the requirement for the SQL design being known before development begins. (ie they are purpose built in SQL).

I'm going to agree with Shane's first question if you feel the need to use SQL to 'do transformations'. Has your company considered bringing in someone to review your job designs and ETL methodologies? Seems like it might be a prudent investment before spending time and money on an alternate plan when you already have an investment in a tool more than capable of doing the job.

Your issue could also be aggravated but running a very old version of the product that upgrading could help with in you really are running an 8x release. And of course hardware and topology play a part here.

I'm not aware of any such methodologies.

Just to be clear, when I mentioned methodologies - I was referring to using SQL to do transformations in conjunction with ETL tools (eg use Datastage to load all your data into canonical models in the same db as your mapping tables, then create view which performs those mappings to use as the inputs into subsequent load DS jobs). I was not referring to a methodology to convert DS jobs to SQL.

Have you thought about looking into Balanced Optimizer?

chulett wrote:Has your company considered bringing in someone to review your job designs and ETL methodologies? Seems like it might be a prudent investment before spending time and money on an alternate plan when you already have an investment in a tool more than capable of doing the job.

I would second Craig's suggestion.

I wrote something like this for BellSouth. They were converting from DataStage to Oracle stored procedures. I wrote it in BASIC. I doubt if it would work now even if I had the code. That was version 6 of server.

I would imagine you could do something similar by exporting your jobs to XML. Then you need to parse your XML. I would name the cursor the same name as the link then formulas in the transforms would work. Any calls to DataStage functions would have to be hand coded. They could not run the code generated but they could read it and understand it enough to take it and write the new Oracle from reading it. Saved lots of time. The jobs back then were not complex and had just a few stages.

So every source stage becomes a cursor with a loop around it. I lookup was a lookup cursor. The transforms were done by just setting variables equal to the derivations. The variables were created from the target table. So you have figure out data types from what is in the XML. I created a lookup. So a 1 got translated into char and a 2 into a varchar as examples.

Good luck. Not a simple task. I would optimize yur jobs before I would do this.