Skip Headers
Oracle® Retail Data Model Reference
Release 11.3.2

Part Number E20361-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 ETL for the Oracle Retail Data Model

This chapter includes the following sections:

Introduction to Oracle Retail Data Model ETL

In the Oracle Retail Data Model relational model, reference and lookup tables store master, reference, and dimensional data; while base, derived, and aggregate tables store transaction and fact data at different granularities. Base tables store the transaction data at the lowest level of granularity, while derived and aggregate tables store consolidated and summary transaction data.

As with any data warehouse, you use Extract, Transform, and Load (ETL) operations to populate an Oracle Retail Data Model data warehouse. You perform ETL operations as three separate steps using three different types of ETL:

  1. Source-ETL process that extracts data from the source On-Line Transaction Processing (OLTP) system, transform that data, and loads the reference, lookup, and base tables Oracle Retail Data Model warehouse. Source-ETL is not provided with Oracle Retail Data Model. You must write source-ETL processes yourself.

  2. Intra-ETL processes that populate the remaining Oracle Retail Data Model warehouse relational data structures. Intra-ETL does not access the OLTP data at all. All of the data that it extracts and transforms is located within the Oracle Retail Data Model warehouse. Intra-ETL processes are provided with the Oracle Retail Data Model.

  3. SQL scripts that populate the OLAP cubes provided with Oracle Retail Data Model. These scripts define the OLAP cubes and populate these cubes with data extracted from the Oracle Retail Data Model relational tables and views. See Chapter 7, "Oracle Retail Data Model OLAP ETL" for information on the Data Flow between fact tables and dimension tables of Oracle Retail Data Model relational objects to OLAP Analytical Workspace containing the OLAP dimensions and cubes.

See:

For instructions on populating an Oracle Retail Data Model data warehouse, see Oracle Retail Data Model Implementation and Operations Guide.

There are two categories of Intra-ETL operations (scripts):

Derived tables are implemented using Oracle tables, while the Aggregate tables are implemented using Materialized Views.

Note:

Do not make changes to the ETL as such changes are not supported.

You can execute the intra-ETL packages provided with Oracle Retail Data Model in the following ways.

Using the ORDM_INTRA_ETL_FLW Workflow with Oracle Warehouse Builder process flow is faster than using PL/SQL package. The Oracle Warehouse Builder process flow lets you easily understand the data flow. How fast this runs depends on the degree of parallelism on the server.

For more information, see Oracle Retail Data Model Implementation and Operations Guide.

Intra-ETL Packages for Populating Derived Tables

Table 6-1 shows the Intra-ETL packages for populating derived tables. These packages are in the following directory in the Oracle Retail Data Model installation:

ORACLE_HOME/ordm/pdm/relational/sql_scripts/intra_etl

Table 6-1 lists the Intra-ETL packages for populating tables and provides links to more detailed information about each package. The Derived tables are listed in "Derived Tables".

PKG_DWD_ACTVTY_RQST_DAY

The Intra-ETL Package for the population of ACTIVITY REQUEST DAY DERIVED.

Source Tables


DWB_PRTY_INTRACN_CALL_EVT
DWB_PRTY_INTRACN_EML_EVT
DWB_PRTY_INTRACN_LTTR_EVT
DWB_PRTY_INTRACN_THRD
DWB_PRTY_INTRACN_THRD_EVT_ASGN
DWB_PRTY_INTRACN_THRD_HIST
DWB_PRTY_INTRACN_VST_EVT

Target Table

DWD_ACTVTY_RQST_DAY

PKG_DWD_CARRIER_CMPLNC_DAY

The Intra-ETL Package for the population of CARRIER COMPLIANCE DAY DERIVED.

Source Tables


DWB_INV_CNTRL_DOC
DWB_INV_CNTRL_DOC_ASSN

Target Table

DWD_CARRIER_CMPLNC_DAY

PKG_DWD_COST_DAY

The Intra-ETL Package for the population of COST DAY DERIVED.

Source Table


DWB_COST

Target Table

DWD_COST_DAY

PKG_DWD_CRTFCT_ACTVTY_TRX

The Intra-ETL Package for the population of CERTIFICATE ACTIVITY TRANSACTION DERIVED.

Source Table


DWB_CRTFCT_LI
DWB_RTL_TNDR_LI
DWR_CRTFCT
DWR_CRTFCT_AGE_BND

Target Table

DWD_CRTFCT_ACTVTY_TRX

PKG_DWD_CUST_EMP_RLTNSHP_DAY

The Intra-ETL Package for the population of CUSTOMER EMPLOYEE RELATIONSHIP DAY DERIVED.

Source Table

DWB_RTL_SLS_RETRN_LINE_ITEM

Target Table

DWD_CUST_EMP_RLTNSHP_DAY

PKG_DWD_CUST_ORDR_LI_STATE

The Intra-ETL Package for the population of CUSTOMER ORDER LINE ITEM STATE DERIVED.

Source Tables


DWB_CUST_ORDR_LI
DWB_CUST_ORDR_LI_STATE_ASSIGN

Target Table

DWD_CUST_ORDR_LI_STATE

PKG_DWD_CUST_RFMP_SCR

The Intra-ETL Package for the population of CUSTOMER RFMP SCORE.

Source Tables


DWR_CUST

Target Table

DWD_CUST_RFMP_SCR

PKG_DWD_CUST_SKU_SL_RETRN_DAY

The Intra-ETL Package for the population of CUSTOMER SKU SALE RETURN DAY DERIVED.

Source Tables


DWB_RTL_SL_RETRN_LINE_ITEM
DWB_DISC_LI
DWR_CUST

Target Table

DWD_CUST_SKU_SL_RETRN_DAY

PKG_DWD_CUST_TYP_ORDR_ITEM_DAY

The Intra-ETL Package for the population of CUSTOMER TYPE ORDER ITEM DAY DERIVED.

Source Tables


Target Table

DWD_CUST_TYP_ORDR_ITEM_DAY

PKG_DWD_EMP_LBR

The Intra-ETL Package for the population of EMPLOYEE LABOR DERIVED.

Source Tables


DWB_EMP_ACT_LBR_HRLY
DWB_EMP_ACT_LBR_SAL
DWR_EMP
DWR_BSNS_UNIT_SHFT

Target Table

DWD_EMP_LBR

PKG_DWD_EMP_WG_PYMNT_DAY

The Intra-ETL Package for the population of EMPLOYEE WAGE PAYMENT DAY DERIVED.

Source Tables


DWB_PAY_DTL
DWB_EMP_ACT_LBR_HRLY
DWB_EMP_ACT_LBR_SAL
DWR_BSNS_UNIT_SHFT

Target Table

DWD_EMP_WG_PYMT_DAY

PKG_DWD_INV_ADJ_ITEM_DAY

The Intra-ETL Package for the population of INVENTORY ADJUSTMENT ITEM DAY DERIVED.

Source Table


DWB_INV_ADJ_DOC_LI

Target Table

DWD_INV_ADJ_ITEM_DAY

PKG_DWD_INV_POSN_ITEM_DAY

The Intra-ETL Package for the population of INVENTORY POSITION ITEM DAY DERIVED.

Source Tables


DWB_INV_ITEM_STATE
DWR_ DAY
DWB_INV_CNTRL_DOC_LI
DWB_INV_CNTRL_DOC
DWR_SKU_ITEM
DWR_SKU_ITEM_SLNG_PRICE

Target Table

DWD_INV_POSN_ITEM_DAY

PKG_DWD_INV_RCPT_ITEM_DAY

The Intra-ETL Package for the population of INVENTORY RECEIPT ITEM DAY DERIVED.

Source Tables


DWB_INV_CNTRL_DOC
DWB_INV_CNTRL_DOC_LI
DWR_DAY

Target Table

DWD_INV_RCPT_ITEM_DAY

PKG_DWD_INV_UNAVL_ITEM_DAY

Intra-ETL Package for the population of INVENTORY UNAVAILABLE ITEM DAY DERIVED.

Source Tables


DWB_INV_ITEM_STATE

Target Table

DWD_INV_UNAVL_ITEM_DAY

PKG_DWD_INV_XFER_ITEM_DAY

Intra-ETL Package for the population of INVENTORY TRANSFER ITEM DAY DERIVED.

Source Tables


DWB_INV_CNTRL_DOC
DWB_INV_CNTRL_DOC_LI
DWR_DAY

Target Table

DWD_INV_XFER_ITEM_DAY

PKG_DWD_ORG_BSNS_UNT_TRFC_DAY

Intra-ETL Package for the population of ORGANIZATION BUSINESS UNIT TRAFFIC DAY DERIVED.

Source Tables


DWB_ORG_BSNS_UNIT_TRFC
DWR_DAY
DWR_ORG_BSNS_UNIT

Target Table

DWD_ORG_BSNS_UNT_TRFC_DAY

PKG_DWD_POS_CNTRL

The Intra-ETL Package for the population of POS CONTROL.

Source Tables


DWB_TILL_HIST
DWB_RTL_TRX
DWR_EMP
DWB_TILL_TNDR_HIST

Target Table

DWD_POS_CNTRL

PKG_DWD_POS_RTL

The Intra-ETL Package for the population of POS RETAIL.

Source Tables


DWB_TILL_HIST
DWB_RTL_TRX,DWR_EMP

Target Table

DWD_POS_RTL

PKG_DWD_POS_STORE_FINCL

The Intra-ETL Package for the population of POS STORE FINANCIAL.

Source Tables


DWB_TNDR_CNTRL_TRX

Target Table

DWD_POS_STORE_FINCL

PKG_DWD_POS_TNDR_FLOW

The Intra-ETL Package for the population of POS TENDER FLOW.

Source Tables


DWB_RTL_TNDR_LI

Target Table

DWD_POS_TNDR_FLOW

PKG_DWD_RTL_SL_RETRN_ITEM_DAY

The Intra-ETL Package for the population of RETAIL SALE RETURN ITEM DAY DERIVED.

Source Tables


DWB_RTL_SLS_RETRN_LINE_ITEM
DWB_DISC_LI

Target Table

DWD_RTL_SL_RETRN_ITEM_DAY

PKG_DWD_RTL_TRX_EMP_WRKSTN_DAY

The Intra-ETL Package for the population of RETAIL TRANSACTION EMP WORKSTATION DAY DERIVED.

Source Tables


DWB_RTL_

Target Table

DWD_RTL_TRX_EMP_WRKSTN_DAY

PKG_DWD_RTV_ITEM_DAY

The Intra-ETL Package for the population of RETURN TO VENDOR ITEM DAY DERIVED.

Source Tables


DWB_PCHSE_ORDR_LI
DWB_PCHSE_ORDR_LI_STATE

Target Table

DWD_RTV_ITEM_DAY

PKG_DWD_SPACE_UTILIZATION_ITEM_DAY

The Intra-ETL Package for the population of SPACE UTILIZATION ITEM DAY DERIVED.

Source Tables


DWB_INV_SPACE_ALCTN
DWR_DAY
DWR_INV_LOC
DWR_SLNG_LOC

Target Table

DWD_SPACE_UTLZTN_ITEM_DAY

PKG_DWD_VENDOR_CMPLNC_ITEM_DAY

The Intra-ETL Package for the population of VENDOR COMPLIANCE ITEM DAY DERIVED.

Source Tables


DWB_PCHSE_ORDR_LI
DWB_INV_CNTRL_DOC_LI

Target Table

DWD_VNDR_CMPLNC_ITEM_DAY

PKG_INTRA_ETL_PROCESS

The Intra-ETL process execution package. This package populates all the derived and aggregate tables.

PKG_INTRA_ETL_UTIL

The Intra-ETL utility package. During population of derived and aggregate tables, this package inserts one row into the DWC_INTRA_ETL_ACTIVITY table for each derived and aggregate table and keeps track of processing status for the table.

Intra-ETL for Populating Aggregate Tables and Relational Materialized Views

The relational materialized view scripts are at the following locations:

Table 6-2 lists the relational materialized view scripts delivered with Oracle Retail Data Model and provides links to more detailed information about each script.

See also:

"Aggregate Tables".

DWA_ACCT_PAYBL_MO

The script for the population of ACCOUNT PAYABLE MONTH AGGR.

Target Table

DWA_ACCT_PAYBL_MO

Source Tables


DWD_ACCT_PAYBL_DAY
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_ACCT_RCVBL_MO

The script for the population of ACCOUNT RECEIVABLE MONTH AGGR .

Target Table

DWA_ACCT_RCVBL_MO

Source Tables


DWD_ACCT_RCVBL_DAY
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_ACTVTY_RQST_MO

The script for the population of ACTIVITY REQUEST MONTH AGGR.

Target Table

DWA_ACTVTY_RQST_MO

Source Tables


DWD_ACTVTY_RQST_DAY
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_ASSTS_MO

The script for the population of ASSETS MONTH AGGR.

Target Table

DWA_ASSTS_MO

Source Tables


DWD_ASSTS_DAY
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_CARRIER_CMPLNC_WK

The script for the population of CARRIER COMPLIANCE WEEK AGGR.

Target Table

DWA_CARRIER_CMPLNC_WK

Source Tables


DWD_CARRIER_CMPLNC_DAY
DWR_BSNS_WK
DWR_DAY

DWA_COST_MO

The script for the population of COST MONTH AGGR.

Target Table

DWA_COST_MO

Source Tables


DWD_COST_DAY
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_CRTFCT_ACTVTY_DAY

The script for the population of CERTIFICATE ACTIVITY DAY AGGR.

Target Table

DWA_CRTFCT_ACTVTY_DAY

Source Tables


DWD_CRTFCT_ACTVTY_TRX

DWA_CUST_EMP_RLTNSHP_MO

The script for the population of CUSTOMER EMPLOYEE RELATIONSHIP MONTH AGGR.

Target Table

DWA_CUST_EMP_RLTNSHP_MO

Source Tables


DWD_CUST_EMP_RLTNSHP_DAY
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_CUST_EMP_SL_RETRN_MO

The script for the population of CUSTOMER EMPLOYEE SALE RETURN MONTH AGGR.

Target Table

DWA_CUST_EMP_SL_RETRN_MO

Source Tables


DWD_CUST_SKU_SL_RETRN_DAY
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_CUST_TYP_ORDR_DEPT_MO

The script for the population of CUSTOMER TYPE ORDER DEPARTMENT MONTH AGGR.

Target Table

DWA_CUST_TYP_ORDR_DEPT_MO

Source Tables


DWA_CUST_TYP_ORDR_SBC_WK
DWR_ITEM_SBC
DWR_ITEM_CLASS
DWR_ITEM_DEPT
DWR_DAY

DWA_CUST_TYP_ORDR_SBC_WK

The script for the population of CUSTOMER TYPE ORDER SUBCLASS WEEK AGGR.

Target Table

DWA_CUST_TYP_ORDR_SBC_WK

Source Tables


DWD_CUST_TYP_ORDR_ITEM_DAY
DWR_SKU_ITEM
DWR_ITEM
DWR_ITEM_SBC
DWR_DAY

DWA_INV_POSN_DEPT_DAY

The script for the population of INVENTORY POSITION DEPT DAY AGGR.

Target Table

DWA_INV_POSN_DEPT_DAY

Source Tables


DWD_INV_POSN_ITEM_DAY
DWR_SKU_ITEM
DWR_ITEM_SBC
DWR_ITEM_CLASS
DWR_ITEM_DEPT

DWA_INV_POSN_SBC_MO

The script for the population of INVENTORY POSITION SUBCLASS MONTH AGGR.

Target Table

DWA_INV_POSN_SBC_MO

Source Tables


DWD_INV_POSN_ITEM_DAY
DWR_SKU_ITEM
DWR_ITEM
DWR_ITEM_SBC
DWR_DAY

DWA_INV_RCPT_SBC_WK

The script for the population of INVENTORY RECEIPT SUBCLASS WEEK AGGR.

Target Table

DWD_INV_RCPT_ITEM_DAY

Source Tables


DWD_INV_RCPT_ITEM_DAY
DWR_SKU_ITEM
DWR_ITEM
DWR_ITEM_SBC
DWR_DAY
DWR_BSNS_WK

DWA_LIAB_MO

The script for the population of LIABILITY MONTH AGGR.

Target Table

DWA_LIAB_MO

Source Tables


DWD_LIAB_DAY
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_PCHSE_ORDR_DEPT_MO

The script for the population of PURCHASE ORDER DEPARTMENT MONTH AGGR.

Target Table

DWA_PCHSE_ORDR_DEPT_MO

Source Tables


DWA_PCHSE_ORDR_SBC_DAY
DWR_ITEM_SBC
DWR_ITEM_CLASS
DWR_ITEM_DEPT
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_PCHSE_ORDR_LI_DAY

The script for the population of PURCHASE ORDER LINE ITEM DAY AGGR.

Target Table

DWA_PCHSE_ORDR_LI_DAY

Source Tables


DWD_PCHSE_ORDR_LI_STATE

DWA_PCHSE_ORDR_LI_MO

The script for the population of PURCHASE ORDER LINE ITEM MONTH AGGR.

Target Table

DWA_PCHSE_ORDR_LI_MO

Source Tables


DWD_PCHSE_ORDR_LI_STATE
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_PCHSE_ORDR_SBC_DAY

The script for the population of PURCHASE ORDER SUBCLASS DAY AGGR.

Target Table

DWA_PCHSE_ORDR_SBC_DAY

Source Tables


DWD_PCHSE_ORDR_STATE
DWD_PCHSE_ORDR_LI_STATE
DWR_SKU_ITEM
DWR_ITEM
DWR_ITEM_SBC

DWA_POS_RTL_EMP_MO

The script for the population of POS RETAIL EMPLOYEE MONTH AGGR.

Target Table

DWA_POS_RTL_EMP_MO

Source Tables


DWD_POS_RTL
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_RTL_SL_RETRN_DEPT_DAY

The script for the population of RETAIL SALE RETURN DEPARTMENT DAY AGGR.

Target Table

DWA_RTL_SL_RETRN_DEPT_DAY

Source Tables


DWD_RTL_SL_RETRN_ITEM_DAY
DWR_SKU_ITEM
DWR_ITEM
DWR_ITEM_SBC
DWR_ITEM_CLASS
DWR_ITEM_DEPT

DWA_RTL_SL_RETRN_SBC_MO

The script for the population of RETAIL SALE RETURN SUBCLASS MONTH AGGR.

Target Table

DWA_RTL_SL_RETRN_SBC_MO

Source Tables


DWD_RTL_SL_RETRN_ITEM_DAY
DWR_SKU_ITEM
DWR_ITEM
DWR_ITEM_SBC
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_RTV_DEPT_DAY

The script for the population of RETURN TO VENDOR DEPARTMENT DAY AGGR.

Target Table

DWA_RTV_DEPT_DAY

Source Tables


DWD_RTV_ITEM_DAY
DWR_SKU_ITEM
DWR_ITEM
DWR_ITEM_SBC
DWR_ITEM_CLASS
DWR_ITEM_DEPT

DWA_RTV_SBC_MO

The script for the population of RETURN TO VENDOR SUBCLASS MONTH AGGR.

Target Table

DWA_RTV_SBC_MO

Source Tables


DWD_RTV_ITEM_DAY
DWR_SKU_ITEM
DWR_ITEM
DWR_ITEM_SBC
DWR_BSNS_MO
DWR_BSNS_HLF_MO
DWR_BSNS_WK
DWR_DAY

DWA_SPACE_UTLZTN_DEPT_DAY

The script for the population of SPACE UTILIZATION DEPARTMENT DAY AGGR.

Target Table

DWA_SPACE_UTLZTN_DEPT_DAY

Source Tables


DWD_SPACE_UTLZTN_ITEM_DAY
DWR_SKU_ITEM
DWR_ITEM
DWR_ITEM_CLASS
DWR_ITEM_DEPT

Intra-ETL to Load OLAP Analytical Workspace

The Intra-ETL for OLAP is executed as part of the overall, default, Oracle Retail Data Model Intra-ETL Process, as shown in the OLAP_MAP step in Figure 6-1.

The OLAP_MAP mapping is executed after executing the Derived and Aggregate portions of the Intra-ETL Process Workflow. This is independent of Mining Intra-ETL and so ORDM_MNNG_FLW and OLAP_MAP run in parallel. The OLAP_MAP mapping makes a call to OLAP_ETL_AW_BUILD subprogram of OLAP_ETL Package: PKG_ORDM_OLAP_ETL_AW_LOAD.

For more information, see the following:

Intra-ETL to Load/Rebuild Mining Models

The Intra-ETL for Mining is executed as part of the overall, default, Oracle Retail Data Model Intra-ETL Process, as shown in the ORDM_MINING_FLW step in Figure 6-1. The Mining sub-process flow, ORDM_MINING_FLW, is shown in Figure 6-6.

The ORDM_MINING_FLW sub-process flow is executed after the execution of Derived and Aggregate sub-process flows of the main process flow, ORDM_INTRA_ETL_FLW. This is independent of OLAP_MAP mapping, so ORDM_MNNG_FLW and OLAP_MAP run in parallel.

The ORDM_MINING_FLW sub-process flow:

  1. Refreshes mining source materialized views by invoking refresh_mining_source procedure of pkg_ordm_mining mining package.

  2. Refreshes mining models by invoking refresh_model function of pkg_ordm_mining mining package.

For more information, see the following:

Intra-ETL Process Flows

The ORDM_INTRA_ETL_FLW is the complete Intra-ETL process flow designed using Oracle Warehouse Builder, and is composed of individual sub-process flows to populate derived tables and relational materialized views where the data originates from base, reference, and lookup tables. This process flow respects the dependency of each individual program.

You can execute the intra-ETL packages provided with Oracle Retail Data Model in the following ways.

Using the ORDM_INTRA_ETL_FLW Workflow with Oracle Warehouse Builder process flow is faster than using PL/SQL package. The Oracle Warehouse Builder process flow lets you easily understand the data flow. How fast this runs depends on the degree of parallelism on the server.

For more information, see Oracle Retail Data Model Implementation and Operations Guide.

Figure 6-1 shows the main process flow ORDM_INTRA_ETL_FLW. The ORDM_INTRA_ETL_FLW is the complete Intra ETL process designed using Oracle Warehouse Builder, and is composed of individual sub-process flows. Also see "Executing the Intra-ETL" for more information on ORDM_INTRA_ETL_FLW.

Figure 6-1 Intra-ETL Main Process Flow

Description of Figure 6-1 follows
Description of "Figure 6-1 Intra-ETL Main Process Flow"

The process flow ORDM_INTRA_ETL_FLW is initialized from START_PROCESS and this checks if any previous process flows are running. If any process is running then START_PROCESS jumps to END_ERROR or START_PROCESS generate the process number from the sequence. This process number is sent as input to the Derived Flow. In the ORDM_DERIVED_FLW when the START process is initiated this generates the process number and is sent as input to the derived ETL PL/SQL package. Once the number is generated it updates the status in the control tables. If derived table data loading is successful then the derived package updates the status in control tables. For more information on control tables, see Appendix A, "Control Tables".

See Also:

For more information on Oracle Warehouse Builder and details on creating and using activities with Oracle Warehouse Builder, see Oracle Warehouse Builder ETL and Data Quality Guide Guide.

Details of the ORDM_DERIVED_FLW

The ORDM_DERIVED_FLW sub-process flow contains all the PL/SQL package code for populating derived tables, based on the content of the base, reference, and lookup tables.

Figure 6-2 shows the ORDM_DERIVED_FLW sub-process flow for populating derived tables.

Figure 6-2 Intra-ETL Derived Process Flow

Description of Figure 6-2 follows
Description of "Figure 6-2 Intra-ETL Derived Process Flow"

After the ORDM_DERIVED_FLW starts successfully, it moves to the fork. The sub-process FORK performs the derived ETL execution (these run in parallel). For each activity, a record is inserted into control table and state is set to 'RUNNING' and its respective ETL is executed thereafter. After the ETL execution completes successfully, a control table record is inserted before ETL execution, is updated with COMPLETED-SUCCESS status; otherwise it is updated with COMPLETED-ERROR status.

The AND activity specifies whether all the parallel activities have been completed. Then switches to the next activity, for example END_SUCCESS.

Details of the ORDM_AGG_N_DEP_FLW

For each activity, the ORDM_AGG_N_DEP_FLW sub-process flow invokes an Oracle Warehouse Builder procedure, which in turn invokes PL/SQL procedure for refreshing materialized view. The activities in the sub-process flow are all independent, and hence can run in parallel. This sub-process flow has dependency on ORDM_DERIVED_FLW sub-process, that is, ORDM_AGG_N_DEP_FLW is executed only ORDM_DERIVED_FLW is executed successfully.

Figure 6-3 shows the ORDM_AGG_N_DEP_FLW sub-process flow for refreshing all independent materialized views.

Figure 6-3 Intra-ETL Independent MV Process Flow

Description of Figure 6-3 follows
Description of "Figure 6-3 Intra-ETL Independent MV Process Flow"

After the ORDM_AGG_N_DEP_FLW is initiated and started successfully it is moved to the Fork. The FORK process makes the aggregates to run in parallel. The AND activity specifies that whether all the parallel aggregates have been completed, then switches over to the next activity, (for example, END_SUCCESS).

Details of the ORDM_AGG_DEP_FLW

For each activity, the ORDM_AGG_DEP_FLW sub-process flow invokes a PL/SQL procedure for refreshing materialized views. The activities in the sub-process flow have dependencies. This sub-process flow has a dependency on ORDM_DERIVED_FLW sub-process, that is, ORDM_AGG_DEP_FLW is executed only after ORDM_DERIVED_FLW runs successfully.

Figure 6-4 shows the ORDM_AGG_DEP_FLW sub-process flow for refreshing all independent materialized views.

Figure 6-4 Intra-ETL Aggregate Process Flow

Description of Figure 6-4 follows
Description of "Figure 6-4 Intra-ETL Aggregate Process Flow"

After the ORDM_AGG_DEP_FLW is initiated and started successfully it is moved to the Fork. The FORK process makes the aggregates to run in parallel. The AND activity specifies that whether all the parallel aggregates have been completed, then switches over to the next activity, (for example, END_SUCCESS).

Details of the OLAP_MAP Mapping

The OLAP_MAP mapping invokes PKG_ORDM_OLAP_ETL_AW_LOAD.OLAP_ETL_AW_BUILD function of OLAP ETL package that can load from Oracle Retail Data Model reference and derived tables to Oracle Retail Data Model Analytical Workspace dimensions and cubes respectively and calculate the forecast data. It reads OLAP ETL parameters from DWC_OLAP_ETL_PARAMETER table.

Figure 6-5 shows the OLAP_MAP mapping that invokes the OLAP ETL package.

Figure 6-5 OLAP Map Process Flow

Description of Figure 6-5 follows
Description of "Figure 6-5 OLAP Map Process Flow"

Details of the ORDM_MNNG_FLW

The mining process flow, ORDM_MNNG_FLW, first refreshes mining source materialized views then refreshes the mining models.

Figure 6-6 shows the mining process flow, ORDM_MNNG_FLW.

Figure 6-6 Mining Flow Process

Description of Figure 6-6 follows
Description of "Figure 6-6 Mining Flow Process"

Executing the Intra-ETL

One component of Oracle Retail Data Model is the ORDM_INTRA_ETL_FLW process flow which is a complete Intra-ETL process composed of sub process flows to populate the derived tables and materialized views with the data from the base, reference, and lookup tables. This process flow respects the dependency of each individual program. The ORDM_INTRA_ETL_FLW process flow executes the programs in the proper order.

You can execute the Intra ETL by executing the ORDM_INTRA_ETL_FLW from Oracle Warehouse Builder.

The ORDM_INTRA_ETL_FLW is the complete Intra ETL process designed using Oracle Warehouse Builder, and is composed of individual sub-process flows:

The ORDM_AGG_N_DEP_FLW and ORDM_AGG_DEP_FLW sub process flows are executed only after the successful execution of all the activities in the ORDM_DERIVED_FLW. If there is an error in any individual activity in the derived process flow (ORDM_DERIVED_FLW), the execution of the Aggregate Process flows (ORDM_AGG_N_DEP_FLW and ORDM_AGG_DEP_FLW) is skipped. The OLAP_MAP and ORDM_MNNG_FLW execution is dependent on the

Using the Intra-ETL involves the following tasks:

Monitoring the Execution of the Intra-ETL Process

Two control tables, DWC_INTRA_ETL_PROCESS and DWC_INTRA_ETL_ACTIVITY, monitor the execution of the Intra-ETL process. Each normal run, as opposed to an error-recovery run, of a separate Intra-ETL execution performs the following steps: