performance comparison of custom SQL vs ADS stage's SQL
Posted: Tue Sep 04, 2007 9:48 am
Hi Gurus,
I have just started using ADS for ETL, before I used to do all the ETL scripting in SQL/PLSQL, Shell scripting etc.
I have a pipe separated ascii file of about 546 MB that needs to be ETLed to our data warehouse for recharges subject area.
There are two approaches to it
1. write a custom script; such as pl/sql scripts in stored procedures to load the data form file to staging table. call this script in ADS processing stage. then write another custom script using pure SQL in oracle stored procedures to apply the transformations and insert the load ready delta into final production table. this second script shall also be called by an ADS processing stage by looking at the return result of first processing stage.
2. second approach is that I purely rely on ADs stages to do all the ETL for me like:
a. using a transformation stage i load data from sequential file to staging ODBC/Oracle table. then using transformation stage again I do required transformations and insert/update the results in Load Ready delta table; from where I finally apply insert/update using another transformation stage.
Now, My question is that which approach is more practical one while using ADS and Why?
I hope to have comprehensive views from all ADs gurus over here!
Thanks and Regards,
I have just started using ADS for ETL, before I used to do all the ETL scripting in SQL/PLSQL, Shell scripting etc.
I have a pipe separated ascii file of about 546 MB that needs to be ETLed to our data warehouse for recharges subject area.
There are two approaches to it
1. write a custom script; such as pl/sql scripts in stored procedures to load the data form file to staging table. call this script in ADS processing stage. then write another custom script using pure SQL in oracle stored procedures to apply the transformations and insert the load ready delta into final production table. this second script shall also be called by an ADS processing stage by looking at the return result of first processing stage.
2. second approach is that I purely rely on ADs stages to do all the ETL for me like:
a. using a transformation stage i load data from sequential file to staging ODBC/Oracle table. then using transformation stage again I do required transformations and insert/update the results in Load Ready delta table; from where I finally apply insert/update using another transformation stage.
Now, My question is that which approach is more practical one while using ADS and Why?
I hope to have comprehensive views from all ADs gurus over here!
Thanks and Regards,