Data Warehouse temptation

This is a question to people, engaged in such activities as modeling of medium-sized (millions of records) data warehouses. I need your opinion or, if you encountered this, your experience with the following:

I have a fairly banal data model in Oracle 7.3.4, split into three tiers:

Loading - where incoming data is cleaned and translated
Storage - where data is split into a relational structure
Marts   - summary objects, mostly denormalized

Data comes in monthly from an increasing number of sources. The tuned data flow from Loading to Marts takes about several hours (single data chunk is about 500K of records, approx. 250MB)

Now, the enterprise comes with an initiative to populate one of the key fields, which is some commodity code, with values, since they are about 40% blank.
Suggested is that we extract unique records from "Storage", where this field is blank, submit them to the third-party source, receive them back with filled codes (they hold a standard for this) and roll the values back in all objects that contain this field in "Storage" and "Marts". This process is supposed to be carried on a regular basis (monthly or more frequently)

I wonder, what are your thoughts or practices, regarding how such processes may affect the system in general.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 02 1999 - 22:10:27 CDT