How Do I Update the Target Schema? 8-1
8
How Do I Update the Target Schema?
Scenario
You are in charge of managing a data warehouse that has been in production for a few
months. The data warehouse was originally created using two source schemas,
Human Resources (HR) and Order Entry (OE) and was loaded into the Warehouse
(WH) target schema. Recently you were made aware of two changes to tables in the
HR and OE schemas. The WH schema must be updated to reflect these changes.
■ Change #1: The first change was made to the HR schema as show in Figure 8–1.
The length of the REGION_NAME column in the REGIONS table was extended to
100 characters.
Figure 8–1 Changed REGIONS Table
■ Change #2: The second change was made to the OE schema as shown in
Figure 8–2. A column called LOT_SIZE_NUMBER was added to the
ORDER_ITEMS table with a precision of 8 and scale of 0.
8-2 Oracle Warehouse Builder Case Book
Figure 8–2 Changed ORDER_ITEMS Table
Solution
In order to update the WH schema, you need to first determine the impact of these
changes and then create and execute a plan for updating the target schema. The
following steps provide an outline for what you need to do:
■
Step 1: Identify Changed Source Objects
■
Step 2: Determine the Impact of the Changes
■
Step 3: Re-import Changed Objects
■
Step 4: Update Objects in the Data Flow
■
Step 5: Re-design your Target Schema
■
Step 6: Re-Deploy Scripts
■
Step 7: Test the New ETL Logic
■
Step 8: Update Your Discoverer EUL
■
Step 9: Execute the ETL Logic
Case Study
Step 1: Identify Changed Source Objects
The first step in rolling out changes to your data warehouse is to identify changes in
source objects. In order to do this, you must have a procedure or system in place that
can notify you when changes are made to source objects.
In our scenario, you were made aware by the group managing the HR and OE
schemas that some objects had been changed. There were two changes, the first was
made to the HR schema. The REGION_NAME column was extended from 25 to 100
characters to