ETL methods for data marts

[mdauer]

I am currently working on extracting data from a warehouse and loading it into a data mart. The volume of data is going to be in the millions of rows. We are using Oracle 8i on a unix platform. I would like to know if anyone has any ideas of extraction and load methods to process so many rows in a reasonably, timely fashion. Before I start coding I would like to get some ideas from the Oracle community about how this might be accomplished. any suggestions would be greatly appreciated

[santoshym]

Hi Michael,

We had the following databases

IBM DB2
SQL Server
Oracle 8i

We were creating a datawarehouse on HP Ux 11.0 Platform using Oracle 8.1.7

In the begining we were extracting flat file from DB2 and SQL Server, ftp them to a NT box and load the Flat files into Operational data store using SQL Loader and then the rest of the process of loading the data into Facts and dimensions thru Oracle stored procedure.

Then we realized there was lot of manual work which had to be performed in the above case so we decided to automate all the task within the oracle database

We allready had a Gateway for the oracle database on the mainframe, we got SQL Server transparent Gateway setup on the SQL Server Box

We created a DB Link to the IBM DB2, SQL Server & Oracle database and setup procedures to directly pull the data from all the database and stored them in the Operational data store in Oracle 8.1.7.

Then execute the rest of the procedures to load data into Facts and dimensions.

We created a main procedure to execute using built in DBMS_JOB utility every night which in turn executed all the above procedures sequentially.

The same thing can also be done using some ETL tool like Informatica, Oracle Warehouse builder etc.

Performance is allways an issue when extracting,loading and transforming data you just have to find a way in which you can load the data faster.

Testing is going to play a vital role to test performance of the whole process and in fact we found that this was the stage which took most of our time.

I Hope this was helpfull

Regards,
Santosh