help for loading a datawarehouse

[joe69008]

hi ,

I have recently create a model of datawarehouse (script of table creation) in oracle and i must now load the oltp's data in the warehouse with pl sql. but i don't know how proceed.
can you help me please.
according what i hear , i must firstly load dimension table, after fact table but i don't know how.
must i create a package with pl sql or only one request script per dimension.

thanks,
best regards

 

[sweetleaf]

You'll need to devise 2 different load processes.
1 for the initial conversion. Another for ongoing loads.

You'll also need to decide bw loading table by table or row by row.

Assuming you have already mapped out your update strategies(type 1,2,3..rolling updates), and will load dimension rows first then fact rows, then dim rows then facts etc you can do something like the following:

Create a package with a stored procedure for each dimension and a driver SP to call all the SP's in sequence. If you've got a Customer dimension, you'd make an SP with a FILE_IN parameter where you'd leverage UTL_FILE to read and parse the flat file. Do this type of SP for each dimension, where you'd first treat dimension records, then treat fact records - row by row. PLSQL won't be the most elegant or optimal way to do this but loading row by row will allow you to treat all of the columns(dim & fct) in the flat file in one process. If you load all dimensions first then facts you'll need to take more passes at the flat file.

 

[joe69008]

thanks you for your help

best regards