The Data Warehouse Development Life Cycle
Oracle Data Warehouse Design
POPULATING STAR SCHEMAS WITH DISTRIBUTED SQL
This is a very simple method for achieving the extraction,
normalization, and insertion of the operational data into the STAR
schema. Note that we can even handle computed values such as
total_cost, which is price times quantity_sold. By specifying the
SYSDATE in the WHERE clause, we ensure that only the day’s
transactions are extracted and loaded into the STAR schema
fact_table. Of course, we are still undertaking a very large
five-way table join, but we would hope to run this extraction during
off hours when the retrieval would not impact the production users.
But, what about rows that have been deleted? While
uncommon, we still need to account for the possibility that some
orders may be canceled. We need a mechanism for updating the STAR
schema to reflect these deletions. The most obvious method for
removing deleted orders from the STAR schema is to utilize Oracle
triggers to create a DELETE trigger on the ORDER table of the
operational system. This DELETE trigger will fire off a remote
delete from the trigger to delete all rows from the STAR schema that
are no longer valid, as follows:
CREATE TRIGGER delete_orders
AFTER DELETE ON ORDER
AS
(DELETE FROM fact_table@london
SEE CODE DEPOT FOR FULL SCRIPT
);
We now have a mechanism for keeping our data warehouse in relative
synchronization with the operational database.
