Extraction to text file and Surrogate Key processing

[monsky]

We have Data Stage for our ETL and Sybase 11 for our data warehouse. At present I'm evaluating the ETL performance and somehow not really satisfied with it.

Is it advisable to do extraction of data sources down to a text file first before doing a bulk load into the data warehouse? I know that Data Stage has a means to call BCP as the bulk loader but feedback from our DBA says that performance is not that good.

Second, will it be better to do a separate application developed in Visual Basic to process surrogate keys for the extracted text files or will Data Stage still do a better job? We haven't tried Data Stage for processing surrogate keys or even handling SCDs.

My objective is to improve performance of ETL and surrogate key processing.

Thanks guys.

 

[johnherman]

I can't answer your surrogate key question, but I have some insight into Data Stage and loading. DS is very very good with text file manipulation and loading and will probably be faster using unload and load with bcp rather than SQL. In addition, if you are having severe perf problems and are not on the parallel extender version of DS, you might want to consider upgrading to that version. It uses multiple threads on the server to execute loading in parallel.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[monsky]

Thanks for the reply. I'd like to share more info about the configuration.

We are using a Solaris E4500 box, running on Solaris 6. This box has a dual processor and 2Gb of RAM. RDBMS for DW is Sybase 11.9.2.

We are doing extraction from another Sybase Production Server. I was hoping that extracting about 1.9 million rows from the production and loading it to the DW takes about two days.

I had an experience with another project where I did a manual bulk loading using Oracle SQL*Loader into an Oracle DW and it took only an hour or two. The configuration is even less (single processor, 512Mb RAM)than what we have right now.