by Burleson Consulting
The Data Warehouse Development Life Cycle
Oracle Data Warehouse Design
POPULATING STAR SCHEMAS WITH DISTRIBUTED SQL
Although it is evident at this point that having several copies of
the same database can sometimes be desirable, problems arise with
this dual approach when attempting to keep the STAR schema in sync
with the operational database. Fortunately, Oracle provides several
mechanisms to assist in this synchronization. It is safe to assume
that the STAR schema will be used by executives for long-range trend
analysis, so it is probably not imperative that the STAR schema be
completely up-to-date with the operational database. Consequently,
we can develop an asynchronous method for updating the STAR schema.
If we make this assumption, then a single SQL statement can be used
to extract the data from the operational database and populate the
new rows in the STAR schema. In Listing 4.1, we assume that the STAR
schema resides at our corporate headquarters in London, and we will
use Oracle’s SQL*Net with database links to directly reference the
remote data warehouse directly from our OLTP database.
Listing 4.1 Updating the STAR schema.
INSERT INTO fact_table@london
FROM QUANTITY, ITEM, CUSTOMER, CITY, STATE
SEE CODE DEPOT FOR FULL SCRIPT
order_date = SYSDATE /* get only today’s transactions */
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.