| Oracle® Warehouse Builder User's Guide 11g Release 1 (11.1) Part Number B31278-01 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 11g Release 1 (11.1) Part Number B31278-01 |
|
|
View PDF |
Scenario
A movie rental company periodically updates the customer rental activity in its CUST_RENTAL_ACTIVITY table, where it stores the rental sales and overdue charges data for each customer. This table is used for different mailing campaigns. For example, in their latest mailing campaign, customers with high overdue charges are offered the company's new pay-per-view service.Currently, the movie rental company uses a PL/SQL package to consolidate their data. The existing PL/SQL package needs to be maintained manually by accessing the database. This code runs on an Oracle 8i database.
CREATE OR REPLACE PACKAGE RENTAL_ACTIVITY AS
PROCEDURE REFRESH_ACTIVITY(SNAPSHOT_START_DATE IN DATE);
END RENTAL_ACTIVITY;
/
CREATE OR REPLACE PACKAGE BODY RENTAL_ACTIVITY AS
PROCEDURE REFRESH_ACTIVITY(SNAPSHOT_START_DATE IN DATE) IS
CURSOR C_ACTIVITY IS
SELECT
CUST.CUSTOMER_NUMBER CUSTOMER_NUMBER,
CUST.CUSTOMER_FIRST_NAME CUSTOMER_FIRST_NAME,
CUST.CUSTOMER_LAST_NAME CUSTOMER_LAST_NAME,
CUST.CUSTOMER_ADDRESS CUSTOMER_ADDRESS,
CUST.CUSTOMER_CITY CUSTOMER_CITY,
CUST.CUSTOMER_STATE CUSTOMER_STATE,
CUST.CUSTOMER_ZIP_CODE CUSTOMER_ZIP_CODE,
SUM(SALE.RENTAL_SALES) RENTAL_SALES,
SUM(SALE.OVERDUE_FEES) OVERDUE_FEES
FROM CUSTOMER CUST, MOVIE_RENTAL_RECORD SALE
WHERE SALE.CUSTOMER_NUMBER = CUST.CUSTOMER_NUMBER AND
SALE.RENTAL_RECORD_DATE >= SNAPSHOT_START_DATE
GROUP BY
CUST.CUSTOMER_NUMBER,
CUST.CUSTOMER_FIRST_NAME,
CUST.CUSTOMER_LAST_NAME,
CUST.CUSTOMER_ADDRESS,
CUST.CUSTOMER_CITY,
CUST.CUSTOMER_STATE,
CUST.CUSTOMER_ZIP_CODE;
V_CUSTOMER_NUMBER NUMBER;
V_CUSTOMER_FIRST_NAME VARCHAR2(20);
V_CUSTOMER_LAST_NAME VARCHAR2(20);
V_CUSTOMER_ADDRESS VARCHAR(50);
V_CUSTOMER_CITY VARCHAR2(20);
V_CUSTOMER_STATE VARCHAR2(20);
V_CUSTOMER_ZIP_CODE VARCHAR(10);
V_RENTAL_SALES NUMBER;
V_OVERDUE_FEES NUMBER;
BEGIN
OPEN C_ACTIVITY;
LOOP
EXIT WHEN C_ACTIVITY%NOTFOUND;
FETCH
C_ACTIVITY
INTO
V_CUSTOMER_NUMBER,
V_CUSTOMER_FIRST_NAME,
V_CUSTOMER_LAST_NAME,
V_CUSTOMER_ADDRESS,
V_CUSTOMER_CITY,
V_CUSTOMER_STATE,
V_CUSTOMER_ZIP_CODE,
V_RENTAL_SALES,
V_OVERDUE_FEES;
UPDATE CUST_ACTIVITY_SNAPSHOT
SET
CUSTOMER_FIRST_NAME = V_CUSTOMER_FIRST_NAME,
CUSTOMER_LAST_NAME = V_CUSTOMER_LAST_NAME,
CUSTOMER_ADDRESS = V_CUSTOMER_ADDRESS,
CUSTOMER_CITY = V_CUSTOMER_CITY,
CUSTOMER_STATE = V_CUSTOMER_STATE,
CUSTOMER_ZIP_CODE = V_CUSTOMER_ZIP_CODE,
RENTAL_SALES = V_RENTAL_SALES,
OVERDUE_FEES = V_OVERDUE_FEES,
STATUS_UPDATE_DATE = SYSDATE
WHERE
CUSTOMER_NUMBER = V_CUSTOMER_NUMBER;
IF SQL%NOTFOUND THEN
INSERT INTO CUST_ACTIVITY_SNAPSHOT
( CUSTOMER_NUMBER,
CUSTOMER_FIRST_NAME,
CUSTOMER_LAST_NAME,
CUSTOMER_ADDRESS,
CUSTOMER_CITY,
CUSTOMER_STATE,
CUSTOMER_ZIP_CODE,
RENTAL_SALES,
OVERDUE_FEES,
STATUS_UPDATE_DATE )
VALUES
( V_CUSTOMER_NUMBER,
V_CUSTOMER_FIRST_NAME,
V_CUSTOMER_LAST_NAME,
V_CUSTOMER_ADDRESS,
V_CUSTOMER_CITY,
V_CUSTOMER_STATE,
V_CUSTOMER_ZIP_CODE,
V_RENTAL_SALES,
V_OVERDUE_FEES,
SYSDATE );
END IF;
END LOOP;
END REFRESH_ACTIVITY;
END RENTAL_ACTIVITY;
/
Solution
This case study highlights the benefits of importing an existing custom PL/SQL package into Warehouse Builder and using its functionality to automatically maintain, update, and regenerate the PL/SQL code. Warehouse Builder enables you to automatically take advantage of new database features and upgrades by generating code that is optimized for new database versions. For example, if the customer has a PL/SQL package for Oracle 8i, then by importing it into Warehouse Builder they can generate code for both Oracle 8i and Oracle 9i. Also, by importing a custom package and re-creating its operations through a Warehouse Builder mapping, you can transparently run and monitor the operations. Otherwise, you must manually access the database to verify and update the code. Warehouse Builder also enables you to perform lineage and impact analysis on all ETL operations while the Runtime Audit Browser monitors the running of the code and logs errors.
Case Study
You can migrate the PL/SQL code into Warehouse Builder by taking these steps:
Step 2: Create a 'Black Box' Mapping by using a custom transformation in a Warehouse Builder mapping.
Step 3: Migrate Custom Code into a Mapping by migrating the legacy PL/SQL code functionality into a new Warehouse Builder mapping and phase out the custom package.
Follow these steps to handle a custom PL/SQL package in Warehouse Builder.
Step 1: Import the Custom PL/SQL Package
In the Project Explorer, expand the Transformations node under the Oracle module into which you want to import the PL/SQL package refresh_activity(DATE). Use the Import Metadata Wizard to import the package by right-clicking Transformations and selecting Import. On the Filter Information page of this wizard, indicate that you are importing a PL/SQL Transformation.
After you finish the import, the package refresh_activity(DATE) appears under the Packages node of the Transformations folder.
Step 2: Create a 'Black Box' Mapping
You can use the refresh_activity(DATE) procedure directly in a mapping without making any changes to it. In the mapping, you add a Post-Mapping Process operator to the mapping, with the package refresh_activity(DATE) selected.
In this example, you can immediately take advantage of the existing custom code. The learning curve and investment on resources is minimal. You may decide to maintain all the existing and developed PL/SQL code in this manner, using Warehouse Builder only to develop new processing units. Warehouse Builder enables you to use mappings that use the legacy code along with the new mappings you create. In such a case, although you can generate code for these mappings in Warehouse Builder, they cannot use Warehouse Builder features to maintain, update, or audit the code.
Because the legacy code is used as a 'black box' that is not transparent to Warehouse Builder, you still need to maintain the legacy code manually. Thus, you cannot take advantage of the Warehouse Builder features, such as runtime audit browser, lineage and impact analysis, and optimized code generation, that rely on infrastructure code and metadata available for Warehouse Builder generated mappings.
Follow the next steps to take advantage of these features in Warehouse Builder and to automatically maintain, monitor, and generate your PL/SQL code.
Step 3: Migrate Custom Code into a Mapping
To take advantage of the code generation, maintenance, and auditing features, you can gradually migrate the legacy PL/SQL code functionality into a mapping and phase out the custom 'black box' package. The mapping created to provide the PL/SQL code functionality is called Rental_Activity.
The recommended method is to test out this new mapping by running it side by side with the 'black box' mapping. If the testing is successful and the new mapping can perform all the operations included in the custom code, the 'black box' mappings can be phased out. Warehouse Builder enables you to maintain, update, and generate code from a mapping without performing manual updates in the database. Figure 17–1 shows a sample of code generated from the Rental_Activity mapping that replicates the operations of the custom PL/SQL package for the movie rental company.
Step 4: Generate Code for Oracle 9i
If you upgrade to Oracle 9i version of the database, you only need to re-deploy the Rental_Activity mapping created in Step 3. Warehouse Builder generates code optimized for the new database version. Figure 17-2 shows the MERGE statement from a sample of code generated for the same mapping for Oracle 9i.
No manual steps are required to maintain and generate the new code. Also, you can transparently monitor and maintain their ETL operations. Warehouse Builder enables them to perform lineage and impact analysis on their mappings and the Runtime Audit Browser enables them to track and log errors when running the mappings.
