| Oracle® Retail Data Model Reference 10g Release 2 (10.2) Part Number E10084-04 |
|
|
PDF · Mobi · ePub |
| Oracle® Retail Data Model Reference 10g Release 2 (10.2) Part Number E10084-04 |
|
|
PDF · Mobi · ePub |
This chapter contains the following topics about the ETL for the relational model of Oracle Retail Data Model:
It also contains discussions about the ETL used for the optional components of Oracle Retail Data Model:
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:
Source_ETL process that extracts data from the source On-Line Transaction Processing (OTLP) 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.
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 and is processed in the following order:
Populate the derived and aggregate tables and materialized views with the data from the base, reference, and lookup tables. For information about these intra-ETL packages, see "PKG_INTRA_ETL_PROCESS", "Intra-ETL Packages for Populating Derived Tables" and "Intra-ETL Scripts for Populating Aggregate Tables and Relational Materialized Views". .
Populate the tables used for the data mining models. For more information on using this intra-ETL, see "Data Mining Component ETL"
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. For more information on populating OLAP cubes in a Oracle Retail Data Model warehouse, see "OLAP Component ETL".
See:
For instructions for using the Intra-ETL and SQL scripts to populate an Oracle Retail Data Model data warehouse, see Oracle Retail Data Model Operations Guide.There are two ways that you can execute the Intra-ETL packages provided with Oracle Retail Data Model. The method you use depends on whether you answered "yes" or "no" to to the question "Indicate if this installation will be used to store transaction level history" when you installed Oracle Retail Data Model:
If you selected "yes" during installation, then Level0 is MV and you can execute the Intra-ETL using Oracle Warehouse Builder.
If you selected "no" during installation, then Level0 is Table and you must explictly execute the PKG_INTRA_ETL_PROCESS Intra-ETL package.
The database package PKG_INTRA_ETL_PROCESS is a complete Intra-ETL process composed of individual population programs (database packages and MV refresh scripts). This package includes the dependency of each individual program and executes them in the proper order.
The PKG_INTRA_ETL_PROCESS.RUN procedure starts the Intra-ETL process. This procedure can be invoked manually, or by another process such as Source-ETL, or according to a predefined schedule such as Oracle Job Scheduling.
PKG_INTRA_ETL_PROCESS.RUN does not accept parameters. This procedure calls other programs in the correct order to load the data for current day (according to the Oracle system date). The result of each table loading are tracked in DWC_ control tables.
See:
For instructions for populating an Oracle Retail Data Model data warehouse using the Intra-ETL, see Oracle Retail Data Model Operations Guide.The Intra-ETL packages for populating derived tables are located at
ORACLE_HOME/ORDM/PDM/Relational/Intra_ETL/Derived
Table 5-1 lists the Intra-ETL packages for populating tables and provides links to more detailed information about each package.
Table 5-1 Intra-ETL Scripts for Database Packages
| SI Number | Package Name |
|---|---|
|
1 |
|
|
2 |
|
|
3 |
|
|
4 |
|
|
5 |
|
|
6 |
|
|
7 |
|
|
8 |
|
|
9 |
|
|
10 |
|
|
11 |
|
|
12 |
|
|
13 |
|
|
14 |
|
|
15 |
|
|
16 |
|
|
17 |
|
|
18 |
The Derived tables are described in "Derived Tables".
(SI Number 1) The Intra-ETL Package for the population of DWD_SPACE_UTLZTN_ITEM_DAY.
DWD_SPACE_UTLZTN_ITEM_DAY
(SI Number 2) The Intra-ETL Package for the population of DWD_CUST_EMP_RLTNSHP_DAY.
DWB_RTL_SLS_RETRN_LINE_ITEM
DWD_CUST_EMP_RLTNSHP_DAY
(SI Number 3) The Intra-ETL Package for the population of DWD_CUST_SKU_SL_RETRN_DAY.
DWD_CUST_SKU_SL_RETRN_DAY
(SI Number 4) Intra-ETL Package for the population of DWD_INV_UNAVL_BY_ITEM_DAY.
DWD_INV_UNAVL_BY_ITEM_DAY
(SI Number 5) The Intra-ETL Package for the population of DWD_INV_ADJ_BY_ITEM_DAY.
DWD_INV_ADJ_BY_ITEM_DAY
(SI Number 6) The Intra-ETL Package for the population of DWD_INV_POSN_BY_ITEM_DAY.
DWD_INV_POSN_BY_ITEM_DAY
(SI Number 7) The Intra-ETL Package for the population of DWD_CUST_ORDR_LI_STATE.
DWD_CUST_ORDR_LI_STATE
(SI Number 8) The Intra-ETL Package for the population of DWD_CERTIFICATE_ACTVTY_TRX.
DWB_RTL_TNDR_LI
DWD_CERTIFICATE_ACTVTY_TRX
(SI Number 9) The Intra-ETL Package for the population of DWD_POS_CNTRL.
DWD_POS_CNTRL
(SI Number 10) The Intra-ETL Package for the population of DWD_CTLG_RQST_BY_DAY.
DWD_CTLG_RQST_BY_DAY
(SI Number 11) The Intra-ETL Package for the population of DWD_POS_RTL.
DWD_POS_RTL
(SI Number 12) The Intra-ETL Package for the population of DWD_RTV_ITEM_DAY.
DWD_RTV_ITEM_DAY
(SI Number 13) Intra-ETL Package for the population of DWD_CUST_ORDR_ITEM_DAY.
DWD_CUST_ORDR_ITEM_DAY
(SI Number 14) The Intra-ETL Package for the population of DWD_POS_STORE_FINCL.
DWD_POS_STORE_FINCL
(SI Number 15) The Intra-ETL Package for the population of DWD_RTL_SL_RETRN_ITEM_DAY.
DWD_RTL_SL_RETRN_ITEM_DAY
(SI Number 16) The Intra-ETL Package for the population of DWD_POS_TNDR_FLOW.
DWD_POS_TNDR_FLOW
(SI Number 17) The Intra-ETL process execution package. It populates all the derived and aggregate tables.
(SI Number 18) The Intra-ETL utility package.
During population of derived and aggregate tables, the package insert one row into DWC_INTRA_ETL_ACTIVITY table for each derived and aggregate table to keep the track of that table.
The relational materialized view scripts are at the following locations:
Relational materialized view creation scripts are located at ORACLE_HOME/ORDM/PDM/Install/Schema_Script/DDL(Normal, with-Partition-Tablespace, With-Tablespace-only)/MV
Relational materialized view log creation scripts are located at ORACLE_HOME/ORDM/PDM/Install/Schema_Script/DDL(Normal, with-Partition-Tablespace, With-Tablespace-only)/MVLog
Aggregate view scripts are located at ORACLE_HOME/ORDM/PDM/Install/Schema_Script/DDL(Normal, with-Partition-Tablespace, With-Tablespace-only)/AggrView
Table 5-2 lists the relational materialized view scripts delivered with Oracle Retail Data Model and provides links to more detailed information about each script.
Table 5-2 Relational Materialized View Scripts
| SI Number | Relational Materialized View Creation Script |
|---|---|
|
1 |
|
|
2 |
|
|
3 |
|
|
4 |
|
|
5 |
|
|
6 |
|
|
7 |
|
|
8 |
|
|
9 |
|
|
10 |
|
|
11 |
|
|
12 |
|
|
13 |
|
|
14 |
|
|
15 |
|
|
16 |
|
|
17 |
|
|
18 |
|
|
19 |
|
|
20 |
|
|
21 |
|
|
22 |
|
|
23 |
|
|
24 |
|
|
25 |
|
|
26 |
|
|
27 |
|
|
28 |
|
|
29 |
|
|
30 |
|
|
31 |
|
|
32 |
|
|
33 |
|
|
34 |
|
|
35 |
|
|
36 |
|
|
37 |
|
|
38 |
|
|
39 |
|
|
40 |
|
|
41 |
|
|
42 |
|
|
43 |
|
|
44 |
|
|
45 |
|
|
46 |
|
|
47 |
(SI Number 1) Script for creation of DWA_CUST_EMP_SL_RETRN_WK_MV from DWD_CUST_SKU_SL_RETRN_MV and DWV_TIME_DAY.
DWA_CUST_EMP_SL_RETRN_WK
Aggregate View Creation Script
DWA_CUST_EMP_SL_RETRN_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 2) Script for creation of relational materialized view DWA_CUST_ORDR_ITEM_WK_MV from DWD_CUST_ORDR_ITEM_DAY and DWV_TIME_DAY.
DWA_CUST_ORDR_ITEM_WK
Aggregate View Creation Script
DWA_CUST_ORDR_ITEM_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 3) Script for creation of relational materialized view DWA_CUST_ORDR_SBC_DAY_MV from DWD_CUST_ORDR_ITEM_DAY and DWR_SKU_ITEM.
DWA_CUST_ORDR_SBC_DAY
Aggregate View Creation Script
DWA_CUST_ORDR_SBC_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 4) Script for creation of relational materialized view DWA_INV_RCPT_BY_ITEM_WK_MV from DWA_INV_RCPT_BY_ITEM_DAY_MV and DWV_TIME_DAY.
DWA_INV_RCPT_BY_ITEM_WK
Aggregate View Creation Script
DWA_INV_RCPT_BY_ITEM_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 5) Script for creation of relational materialized view DWA_INV_RCPT_BY_SBC_DAY_MV from DWA_INV_RCPT_BY_ITEM_DAY_MV and DWR_SKU_ITEM.
DWA_INV_RCPT_BY_SBC_DAY
Aggregate View Creation Script
DWA_INV_RCPT_BY_SBC_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 6) Script for creation of relational materialized view DWA_INV_TRNSFR_BY_ITEM_WK_MV from DWA_INV_TRNSFR_BY_ITEM_DAY_MV and DWV_TIME_DAY.
DWA_INV_TRNSFR_BY_ITEM_WK
Aggregate View Creation Script
DWA_INV_TRNSFR_BY_ITEM_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 7) Script for creation of relational materialized view DWA_INV_TRNSFR_BY_SBC_DAY_MV from DWA_INV_TRNSFR_BY_ITEM_DAY_MV and DWR_SKU_ITEM.
DWA_INV_TRNSFR_BY_SBC_DAY
Aggregate View Creation Script
DWA_INV_TRNSFR_BY_SBC_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 8) Script for creation of relational materialized view DWA_RTL_MRKDN_ITEM_DAY_MV from DWD_RTL_SL_RETRN_ITEM_DAY.
DWA_RTL_MRKDN_ITEM_DAY
DWD_RTL_SL_RETRN_ITEM_DAY
Aggregate View Creation Script
DWA_RTL_MRKDN_ITEM_DAY.sql
Relational Materialized View Log Creation Script
DWD_RTL_SL_RETRN_ITEM_DAY_MV_LOG.sq
(SI Number 9) Script for creation of relational materialized view DWA_RTL_SL_RETRN_ITEM_WK_MV from DWD_RTL_SL_RETRN_ITEM_DAY and DWV_TIME_DAY.
DWA_RTL_SL_RETRN_ITEM_WK
Aggregate View Creation Script
DWA_RTL_SL_RETRN_ITEM_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 10) Script for creation of relational materialized view DWA_RTL_SL_RETRN_SBC_DAY_MV from DWD_RTL_SL_RETRN_ITEM_DAY and DWR_SKU_ITEM.
DWA_RTL_SL_RETRN_SBC_DAY
Aggregate View Creation Script
DWA_RTL_SL_RETRN_SBC_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 11) Script for creation of relational materialized view DWA_RTL_TRX_EMP_WRKSTN_MV from DWB_TILL_HIST, DWV_TIME_DAY, DWB_TILL_TNDR_HIST, DWD_POS_CNTRL, and DWD_POS_RTL.
DWA_RTL_TRX_EMP_WRKSTN
Aggregate View Creation Script
DWA_RTL_TRX_EMP_WRKSTN.sql
Relational Materialized View Log Creation Scripts
(SI Number 12) Script for creation of relational materialized view DWA_SPACE_UTLZTN_DEPT_DAY_MV from DWD_SPACE_UTLZTN_ITEM_DAY, DWR_SKU_ITEM, and DWR_SEASON.
DWA_SPACE_UTLZTN_DEPT_DAY
Aggregate View Creation Script
DWA_SPACE_UTLZTN_DEPT_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 13) Script for creation of relational materialized view DWA_TILL_HIST_WRKSTN_MV from DWB_TILL_HIST, DWV_TIME_DAY and DWR_EMP.
DWA_TILL_HIST_WRKSTN
Aggregate View Creation Script
DWA_TILL_HIST_WRKSTN.sql
Relational Materialized View Log Creation Scripts
(SI Number 14) Script for creation of relational materialized view DWA_TILL_TNDR_HIST_EMP_MV from DWB_TILL_TNDR_HIST, DWR_EMP, and DWR_USERS.
DWA_TILL_TNDR_HIST_EMP
Aggregate View Creation Script
DWA_TILL_TNDR_HIST_EMP.sql
Relational Materialized View Log Creation Scripts
(SI Number 15) Script for creation of relational materialized view DWD_INV_POSN_BY_ITEM_DAY and DWV_TIME_DAY.
DWA_INV_POSN_BY_ITEM_WK
Aggregate View Creation Script
DWA_INV_POSN_BY_ITEM_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 16) Script for creation of relational materialized view DWD_INV_POSN_BY_ITEM_DAY and DWR_SKU_ITEM.
DWA_INV_POSN_BY_SBC_DAY
Aggregate View Creation Script
DWA_INV_POSN_BY_SBC_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 17) Script for creation of relational materialized view DWA_CERTIFICATE_ACTVTY_DAY_MV from DWD_CERTIFICATE_ACTVTY_TRX.
DWA_CERTIFICATE_ACTVTY_DAY
DWD_CERTIFICATE_ACTVTY_TRX
Aggregate View Creation Script
DWA_CERTIFICATE_ACTVTY_DAY.sql
Relational Materialized View Log Creation Script
DWD_CERTIFICATE_ACTVTY_TRX_MV_LOG.sq
(SI Number 18) Script for creation of relational materialized view DWA_CARRIER_CMPLNC_WK_MV from DWB_INV_CNTRL_DOC, DWB_INV_CNTRL_DOC_LI, and DWV_TIME_DAY.
DWA_CARRIER_CMPLNC_WK
Aggregate View Creation Script
DWA_CARRIER_CMPLNC_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 19) Script for creation of relational materialized view DWA_CUST_EMP_RLTNSHP_MO_MV from DWD_CUST_EMP_RLTNSHP_DAY, DWR_CUST, and DWV_TIME_DAY.
DWA_CUST_EMP_RLTNSHP_MO
Aggregate View Creation Script
DWA_CUST_EMP_RLTNSHP_MO.sql
Relational Materialized View Log Creation Scripts
(SI Number 20) Script for creation of relational materialized view DWA_INV_ITEM_STATE_HIST_WK_MV from DWB_INV_ITEM_STATE and DWV_TIME_DAY.
DWA_INV_ITEM_STATE_HIST_WK
Aggregate View Creation Script
DWA_INV_ITEM_STATE_HIST_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 21) Script for creation of relational materialized view DWA_INV_RCPT_BY_ITEM_DAY_MV from DWB_INV_CNTRL_DOC_LI, DWB_INV_CNTRL_DOC, DWR_SKU_ITEM_SLNG_PRICE, DWR_SKU_ITEM, DWV_TIME_DAY, and DWR_ITEM_SEASON.
DWA_INV_RCPT_BY_ITEM_DAY
Aggregate View Creation Script
DWA_INV_RCPT_BY_ITEM_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 22) Script for creation of relational materialized view DWA_INV_TRNSFR_BY_ITEM_DAY_MV from DWB_INV_CNTRL_DOC_LI, DWB_INV_CNTRL_DOC, DWR_SKU_ITEM_SLNG_PRICE, DWR_SKU_ITEM, DWV_TIME_DAY and DWR_ITEM_SEASON.
DWA_INV_TRNSFR_BY_ITEM_DAY
Aggregate View Creation Script
DWA_INV_TRNSFR_BY_ITEM_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 23) Script for creation of relational materialized view DWA_CUST_EMP_SL_RETRN_MO_MV from DWA_CUST_EMP_SL_RETRN_WK_MV and DWR_BSNS_WK.
DWA_CUST_EMP_SL_RETRN_MO
Aggregate View Creation Script
DWA_CUST_EMP_SL_RETRN_MO.sql
Relational Materialized View Log Creation Scripts
(SI Number 24) Script for creation of relational materialized view DWA_CUST_ORDR_DEPT_DAY_MV from DWA_CUST_ORDR_SBC_DAY_MV and DWR_ITEM_SBC.
DWA_CUST_ORDR_DEPT_DAY
Aggregate View Creation Script
DWA_CUST_ORDR_DEPT_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 25) Script for creation of relational materialized view DWA_CUST_ORDR_ITEM_MO_MV from DWA_CUST_ORDR_ITEM_WK_MV and DWV_TIME_BSNS_WK.
DWA_CUST_ORDR_ITEM_MO
Aggregate View Creation Script
DWA_CUST_ORDR_ITEM_MO.sql
Relational Materialized View Log Creation Scripts
(SI Number 26) Script for creation of relational materialized view DWA_CUST_ORDR_SBC_WK_MV from DWA_CUST_ORDR_SBC_DAY_MV and DWV_TIME_DAY.
DWA_CUST_ORDR_SBC_WK
Aggregate View Creation Script
DWA_CUST_ORDR_SBC_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 27) Script for creation of relational materialized view DWA_INV_RCPT_BY_SBC_WK_MV from DWA_INV_RCPT_BY_SBC_DAY_MV and DWV_TIME_DAY.
DWA_INV_RCPT_BY_SBC_WK
Aggregate View Creation Script
DWA_INV_RCPT_BY_SBC_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 28) Script for creation of relational materialized view DWA_INV_TRNSFR_BY_SBC_WK_MV from DWA_INV_TRNSFR_BY_SBC_DAY_MV and DWV_TIME_DAY.
DWA_INV_TRNSFR_BY_SBC_WK
Aggregate View Creation Script
DWA_INV_TRNSFR_BY_SBC_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 29) Script for creation of relational materialized view DWA_RTL_MRKDN_DEPT_DAY_MV from DWA_RTL_MRKDN_ITEM_DAY_MV and DWR_SKU_ITEM.
DWA_RTL_MRKDN_DEPT_DAY
Aggregate View Creation Script
DWA_RTL_MRKDN_DEPT_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 30) Script for creation of relational materialized view DWA_RTL_MRKDN_ITEM_WK_MV from DWA_RTL_MRKDN_ITEM_DAY_MV and DWV_TIME_DAY.
DWA_RTL_MRKDN_ITEM_WK
Aggregate View Creation Script
DWA_RTL_MRKDN_ITEM_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 31) Script for creation of relational materialized view DWA_RTL_SL_RETRN_DEPT_DAY_MV from DWA_RTL_SL_RETRN_SBC_DAY_MV and DWR_ITEM_SBC.
DWA_RTL_SL_RETRN_DEPT_DAY
Aggregate View Creation Script
DWA_RTL_SL_RETRN_DEPT_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 32) Script for creation of relational materialized view DWA_RTL_SL_RETRN_ITEM_MO_MV from DWA_RTL_SL_RETRN_ITEM_WK_MV and DWV_TIME_BSNS_WK.
DWA_RTL_SL_RETRN_ITEM_MO
Aggregate View Creation Script
DWA_RTL_SL_RETRN_ITEM_MO.sql
Relational Materialized View Log Creation Scripts
(SI Number 33) Script for creation of relational materialized view DWA_RTL_SL_RETRN_SBC_WK_MV from DWA_RTL_SL_RETRN_ITEM_WK_MV and DWR_SKU_ITEM.
DWA_RTL_SL_RETRN_SBC_WK
Aggregate View Creation Script
DWA_RTL_SL_RETRN_SBC_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 34) Script for creation of relational materialized view from DWA_INV_POSN_BY_SBC_DAY_MV and DWV_TIME_DAY.
DWA_INV_POSN_BY_SBC_WK
Aggregate View Creation Script
DWA_INV_POSN_BY_SBC_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 35) Script for creation of relational materialized view DWA_INV_POSN_BY_DEPT_DAY_MV from DWA_INV_POSN_BY_SBC_DAY_MV and DWR_ITEM_SBC.
DWA_INV_POSN_BY_DEPT_DAY
Aggregate View Creation Script
DWA_INV_POSN_BY_DEPT_DAY.sql
Relational Materialized View Log Creation Scripts
(SI Number 36) Script for creation of relational materialized view DWA_RTL_SL_RT_ORG_HRCHY_DAY_MV from DWA_RTL_SL_RETRN_DEPT_DAY_MV.
DWA_RTL_SL_RT_ORG_HRCHY_DAY
Aggregate View Creation Script
DWA_RTL_SL_RT_ORG_HRCHY_DAY.sql
Relational Materialized View Log Creation Scripts
DWA_RTL_SL_RETRN_DEPT_DAY_MV_LOG.sq
(SI Number 37) Script for creation of relational materialized view DWA_RTL_MRKDN_DEPT_WK_MV from DWA_RTL_MRKDN_DEPT_DAY_MV and DWV_TIME_DAY.
DWA_RTL_MRKDN_DEPT_WK
DWD_RTL_SL_RETRN_ITEM_DAY, DWR_SKU_ITEM, DWR_DAY
Aggregate View Creation Script
DWA_RTL_MRKDN_DEPT_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 38) Script for creation of relational materialized view DWA_RTL_SL_RETRN_DEPT_WK_MV from DWA_RTL_SL_RETRN_DEPT_DAY_MV and DWV_TIME_DAY.
DWA_RTL_SL_RETRN_DEPT_WK
Aggregate View Creation Script
DWA_RTL_SL_RETRN_DEPT_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 39) Script for creation of relational materialized view DWA_RTL_SL_RETRN_SBC_MO_MV from DWA_RTL_SL_RETRN_ITEM_MO_MV and DWR_SKU_ITEM.
DWA_RTL_SL_RETRN_SBC_MO
Aggregate View Creation Script
DWA_RTL_SL_RETRN_SBC_MO.sql
Relational Materialized View Log Creation Scripts
(SI Number 40) Script for creation of relational materialized view DWA_CUST_ORDR_SBC_MO_MV from DWA_CUST_ORDR_SBC_WK_MV and DWV_TIME_BSNS_WK.
DWA_CUST_ORDR_SBC_MO
Aggregate View Creation Script
DWA_CUST_ORDR_SBC_MO.sql
Relational Materialized View Log Creation Scripts
(SI Number 41) Script for creation of the relational materialized views DWA_INV_POSN_BY_DEPT_WK_MV from DWA_INV_POSN_BY_DEPT_DAY_MV and DWV_TIME_DAY.
DWA_INV_POSN_BY_DEPT_WK
Aggregate View Creation Script
DWA_INV_POSN_BY_DEPT_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 42) Script for creation of relational materialized view DWA_CERTIFICATE_ACTVTY_WK_MV from DWA_CERTIFICATE_ACTVTY_DAY_MV and DWV_TIME_DAY.
DWA_CERTIFICATE_ACTVTY_WK
Aggregate View Creation Script
DWA_CERTIFICATE_ACTVTY_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 43) Script for creation of relational materialized view DWA_CUST_ORDR_DEPT_MO_MV from DWA_CUST_ORDR_SBC_MO_MV and DWR_ITEM_SBC.
DWA_CUST_ORDR_DEPT_MO
Aggregate View Creation Script
DWA_CUST_ORDR_DEPT_MO.sql
Relational Materialized View Log Creation Scripts
(SI Number 44) Script for creation of relational materialized view DWA_MKT_SLS_DEPT_WK_MV from DWA_MKT_SLS_ITEM_WK,DWR_ITEM_MKT_DATA.
DWA_MKT_SLS_DEPT_WK
Aggregate View Creation Script
DWA_MKT_SLS_DEPT_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 45) Script for creation of relational materialized view DWA_INV_VNDR_CMPLNC_MV from DWB_INV_CNTRL_DOC_LI,DWR_SKU_ITEM,DWR_ITEM_SEASON,DWB_PCHSE_ORDR_LI.
DWA_INV_VNDR_CMPLNC
Aggregate View Creation Script
DWA_INV_VNDR_CMPLNC.sql
Relational Materialized View Log Creation Scripts
(SI Number 46) Script for creation of relational materialized view DWA_VNDR_CMPLNC_ITEM_WK_MV from DWB_INV_CNTRL_DOC_LI,DWB_PCHSE_ORDR_LI and DWV_TIME_DAY.
DWA_VNDR_CMPLNC_ITEM_WK
Aggregate View Creation Script
DWA_VNDR_CMPLNC_ITEM_WK.sql
Relational Materialized View Log Creation Scripts
(SI Number 47) Script for creation of relational materialized view DWA_VNDR_CMPLNC_WK_MV from DWB_PCHSE_ORDR_LI, DWB_INV_CNTRL_DOC_LI, and DWV_TIME_DAY.
DWA_VNDR_CMPLNC_WK
Aggregate View Creation Script
DWA_VNDR_CMPLNC_WK.sql
Relational Materialized View Log Creation Scripts
Oracle Retail Data Model provides an optional data mining component. This data mining component extends the core extends the core functionality of Oracle Retail Data Model by adding data mining models. This section provides reference information about:
See:
For information on how to use these packages and procedures , see Oracle Retail Data Model Operations Guide.Oracle Retail Data Model includes data mining packages. The data mining portion of Oracle Retail Data Model consists of source tables that are populated by detail data for use by the data mining packages. This data is organized in a specific way to be compatible with the data mining modules so they can properly analyze and mine the data. Data mining packages pull in the source data and feed it into the data mining packages, and populate the target tables with the results. The data in the target tables are presented by the OBIEE reports.
When you install the Data Mining component of Oracle Retail Data Model, two types of data mining ETL packages are installed:
Packages that populate the Source Input Tables for the data mining models.
In the BIA_RTL_mining schema, tables of the form *_SRC contain source input data for the data mining models. The data in the *_SRC tables is populated from base or derived tables in the BIA_RTL schema using the Mining ETL packages. The Mining ETL packages have names of the form PKG_POP_DM_*.
The procedures PKG_POP_DM_*.LOADDATA(p_yearmonth) load data from base and derived tables in the BIA_RTL schema to mining source tables (*_SRC in the BIA_RTL_mining schema).The parameter p_yearmonth is the Business Month that you want to analyze. All Business Months are stored in DWR_BSNS_MO in BIA_RTL schema.The input of p_yearmonth must be in DWR_BSNS_MO.MO_KEY.
Packages that create data mining models.
The Oracle Retail Data Model packages PKG_RBIW_DM_* create mining models. The table below lists the packages that create mining models and the mining model that each package creates:
Table 5-3 Data Mining Packages in Oracle Retail Data Model
| Model | Model ETL Package | Model Creation Package |
|---|---|---|
|
PKG_POP_DM_ASSBAS |
PKG_RBIW_DM_ASSBAS |
|
|
PKG_POP_DM_ASSLOSS |
PKG_RBIW_DM_ASSLOSS |
|
|
PKG_POP_DM_ASSSLS |
PKG_RBIW_DM_ASSSLS |
|
|
PKG_POP_DM_CUSTCATGMIX |
PKG_RBIW_DM_CUSTCATGMIX |
|
|
PKG_POP_DM_CUSTLTY |
PKG_RBIW_DM_CUSTLTY |
|
|
PKG_POP_DM_FSCATGMIX |
PKG_RBIW_DM_FSCATGMIX |
|
|
PKG_POP_DM_ITMBAS |
PKG_RBIW_DM_ITMBAS |
|
|
PKG_POP_DM_ITMPOSLOSS |
PKG_RBIW_DM_ITMPOSLOSS |
|
|
PKG_POP_DM_POSFLOW |
PKG_RBIW_DM_POSFLOW |
|
|
PKG_POP_DM_STRLOSS |
PKG_RBIW_DM_STRLOSS |
See:
For information on how to use these packages , see Oracle Retail Data Model Operations Guide.Oracle Retail Data Model creates mining models using the following three Oracle Data Mining algorithms: Adaptive Bayes (ABN, Decision Tree (DT) , and Association (APASS).
Note:
In Oracle Data Mining, Association is abbreviated as AR.These algorithms all build models that have rules. For information about the algorithms, see Oracle Data Mining Concepts.
Each package (analysis) builds models using one or two of these three algorithms. The models built depend on the analysis being performed. The output of the model build is a view containing rules generated by the model.
This section describes
See:
For detailed information on how to use these packages to create data mining models, see Oracle Retail Data Model Operations Guide.There is a build procedure for each type of model. The build procedures are as follows:
Adaptive Bayes Network (ABN) models
PRC_RUNALL_ABN(
p_case_table IN VARCHAR2,
p_model_name IN VARCHAR2,
p_year IN NUMBER,
p_month IN VARCHAR,
p_drop_output IN BOOLEAN
)
Decision Tree (DT) models:
PRC_RUNALL_DT (
p_case_table IN VARCHAR2 ,
p_model_name IN VARCHAR2 ,
p_year IN NUMBER,
p_month IN VARCHAR,
p_drop_output IN BOOLEAN
)
Association (APASS) models (using the Apriori algorithm):
PRC_RUNALL_APASS(
p_case_table IN VARCHAR2,
p_model_name IN VARCHAR2,
p_year IN NUMBER,
p_month IN VARCHAR,
p_drop_output IN BOOLEAN
)
All of the packages use the same parameters:
| Parameter | Description |
|---|---|
p_case_table |
Mining source table name, the full table name with the suffix _SRC |
p_model_name |
Name of the new model |
p_year |
The year for model predictions. The value must exist in the *_SRC.YEAR column, where *_SRC is the corresponding source table for this package |
p_month |
The month for model predictions. The value must exist in the *_SRC.MONTH column, where *_SRC is the corresponding source table for this package |
p_drop_output |
A Boolean value indicating if an existing model is dropped before build:
|
After the mining model is created, the rules are saved in the tables RBIW_DM_RULES and RBIW_DM_APASS_RULES. Analysis-specific views are defined based on these two tables. The views can be used to retrieve the rules of each model. The models for each type of analysis and the corresponding Views containing the model rules are as follows:
| Data Mining Model (Analysis) | Model Type | View Containing Model Rules |
|---|---|---|
| Associate Basket Analysis Model | ABN, DT | ASSOCIATE_BASKET_RULES |
| Associate Loss Analysis Model | ABN, DT | ASSOCIATE_LOSS_RULES |
| Associate Sales Analysis Model | ABN, DT | ASSOCIATE_SALES_RULES |
| Customer Category Mix Analysis Model | ABN, DT | CUST_CATEGORY_MIX_RULES |
| Customer Loyalty Analysis Model | ABN, DT | CUSTOMER_LOYALTY_RULES |
| Frequent Shopper Category Mix Analysis Model | ABN,DT | FS_CATEGORY_MIX_RULES |
| Item Basket Analysis Model | ABN, DT | ITEM_BASKET_RULES |
| Item POS Loss Analysis Model | ABN, DT | ITEM_POS_LOSS_RULES |
| POS Flow Analysis Model | ABN, DT | POS_FLOW_RULES |
| Store Loss Analysis Model | ABN, DT | STORE_LOSS_RULES |
| Frequent Shopper Category Mix Analysis Model | APASS | FS_CATEGORY_MIX_APASS_RULES |
| Customer Category Mix Analysis Model | APASS | CUST_CATEGORY_MIX_APASS_RULES |
Oracle Retail Data Model provides an optional OLAP component. This OLAP component extends the core functionality of Oracle Retail Data Model by adding OLAP cubes for OLAP analysis and forecasting.
Oracle Retail Data Model OLAP cubes are not populated using a formal Extract, Transform, and Load workflow process. Instead, OLAP cubes are populated through SQL scripts that use the RBIA_OLAP_ETL_AW_LOAD package that is provided with the OLAP component.
This section discusses:
OLAP cubes are populated at the following times:
During the intial load of the OLAP cubes.
This load is performed by a SQL script (sometimes called the "OLAP cube intial load script") that is delivered with the Oracle Retail Data Model OLAP component. The actual script that performs the OLAP cube intial load varies depending on Oracle Database release:
For Oracle Database 10g, the script is ordm_olap_install_scr.sql
For Oracle Databse 11g, the script is ordm_olap_11g_install_scr.sql
When the relational data exists in the Oracle Retail Data Model data warehouse, the OLAP cube intial load script loads relational table data (from a specified start date to the present time) into the OLAP cubes. It also performs the default OLAP forecasts. (For detailed information about the behavior of the OLAP cube initial load script, see "OLAP Component Installation Scripts".)
You can execute the OLAP cube intial load SQL script in the following ways:
Implicitly, by installing the Oracle Retail Data Model OLAP component after you have loaded data into the Oracle Retail Data Model relational tables. For instructions on how to install the Oracle Retail Data Model OLAP component, see Oracle Retail Data Model Installation Guide.
Explicitly after you have installed the Oracle Retail Data Model OLAP component and populated the relational tables. In this case, you execute the OLAP cube intial load SQL program as you would any other SQL program.
On a scheduled basis to update the OLAP cube data with the relational data that has been added to the Oracle Retail Data Model data warehouse since the intial load of the OLAP cubes.
This type of load (sometimes referred to as an "intermittent" or "refresh" load) merely adds relational data from a specified time period to the data in the Sales and Inventory cubes; it does not change the data in the Sales Forecast and Inventory Forecast cubes.
Oracle Retail Data Model does not provide an OLAP intermittent load cube script. You must write your own OLAP intermittent load cube script using the RBIA_OLAP_ETL_AW_LOAD package.
For information on writing your own intermittent OLAP cube program and for updating the data in the OLAP forecast cubes, see Oracle Retail Data Model Operations Guide.
Installing the Oracle Retail Data Model OLAP component triggers the execution of two SQL scripts that are provided with the OLAP component: the Oracle Retail Data Model OLAP environment setup script, and the Oracle Retail Data Model OLAP cube initial load script.
Oracle Retail Data Model OLAP environment setup script
The OLAP environment setup script creates and sets up the Oracle Retail Data Model OLAP environment.
Note:
The actual script that sets up the OLAP environment varies depending on Oracle Database release:For Oracle Database 10g the script is ordm_olap_user_env_scr.sql
For Oracle Databse 11g, the script is ordm_olap_11g_user_env_scr.sql
The environment setup script performs the following tasks:
Creates OLAP tablespaces and Oracle Retail Data Model OLAP schema user bia_rtl_olap.
Assigns required grants and privileges to bia_rtl_olap user
Creates the analytic workspace(s) that define all of the analytic workspace objects used by the OLAP component.
See:
For more detailed information about the objects defined by thebia_rtl_olap schema, including the analytic workspaces defined by the schema, see the discussion of the physical model of the OLAP component in Oracle Retail Data Model Reference.Oracle Retail Data Model OLAP cube inital load script
The OLAP cube initial load script loads the dimensions and fact data from the relational star schema into the analytic workspace dimension and cubes and executes the OLAP forecasts.
Note:
The actual script that performs the OLAP cube intial load varies depending on Oracle Database release:For Oracle Database 10g, the script is ordm_olap_install_scr.sql
For Oracle Databse 11g, the script is ordm_olap_11g_install_scr.sql
To populate the OLAP cubes in Oracle Retail Data Model, the OLAP cube inital load script performs the following tasks:
Executes the RBIA_OLAP_ETL_AW_LOAD.OLAP_ETL_AWBUILD subprogram in HISTORICAL mode from historical start date to historical end date. This populates all of analytic workspace objects (including the OLAP forecast cubes) with relational data within the date range specified.
Executes the RBIA_OLAP_ETL_AW_LOAD.OLAP_ETL_AWBUILD subprogram in INCREMENTAL mode from historical_end_date+1 to SYSDATE-1. This adds relational table data within the date range specified to the Sales and Inventory cubes. No changes are made to the Sales Forecast and Inventory Forecast cubes.
See:
For a more complete description of the actions performed during an historical or incremental load, see the description of the OLAP_ETL_AW_BUILD subprogram of the OLAP_ETL_AW_LOAD Package.The OLAP_ETL_AW_LOAD package contains subprograms that create the analytic workspace(s), the analytic workspace definitions for the OLAP cube, and populates the cube. The behavior of subprograms in the OLAP_ETL_AW_LOAD package varies depending on are the type of load being performed (that is, an historical or incremental load) and the date range of the load:
You specify the type of load as a parameter (HISTORICAL or INCREMENTAL) of the subprogram. In the installation scripts provided with Oracle Retail Data Model, data is loaded into the OLAP cubes in HISTORICAL mode. You specify INCREMENTAL mode in any scripts that you write to refresh the data in the OLAP cubes.
The subprograms retrieve the value for the date range of the load from the BIA_RTL.DWC_ETL_PARAMETER table for process name "RBIA-INTRA-ETL-OLAP". This value is populated at the following times:
During the installation process when the user is enters the start and end dates in response to a program prompt. The values specified by the user are used by the SQL scripts that install the OLAP component.
At any time, by issuing a SQL UPDATE BIA_RTL.DWC_ETL_PARAMETERSET statement.
See also:
"OLAP Component Installation Scripts", Oracle Retail Data Model Installation Guide, and "Summary of the OLAP_ETL_AW_LOAD Subprograms".Table 5-4 lists the all of the package subprograms.
Note:
Although the subprogram code varies depending on the Oracle Database release for which you have installed the OLAP component (that is, for Oracle Database 10g or Oracle Database 11g), the subprogram behavior is the same for both releases.Table 5-4 OLAP_ETL_AW_LOAD Package Subprograms
| Subprogram | Description |
|---|---|
|
Resets the relational views of the source relational tables based on the start and end date values for the |
|
|
The HISTORICAL load populates the OLAP dimensions and cubes based on the existing status of the dimension and fact source views. After populating the dimensions and cubes, it performs the default forecasting. |
|
|
Populates dimensions in the analytic workspace. |
|
|
Populates a specified cube in PSLSINV analytic workspace. |
|
|
Functionality in historical mode varies by release:
|
|
|
Sets the date ranges of the fact data to be captured as part of the Oracle Retail Data Model OLAP load process. For both historical and incremental loads, the date range is read from the |
Resets the relational views of the source relational tables based on the start and end date values for the"RBIA-INTRA-ETL-OLAP" process of the BIA_RTL.DWC_ETL_PARAMETE table, builds the PSLSINV analytic workspace, and loads the analytic workspace data objects.
RBIA_OLAP_ETL_AW_LOAD.OLAP_ETL_AW_BUILD ( mode VARCHAR2, build_type VARCHAR2, max_job_queues INTEGER);
Table 5-5 OLAP_ETL_AW_BUILD Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The following modes are supported:
Specifically, when you specify
Specifically, when you specify
|
|
|
One of the following values: |
|
|
Specifies the number of parallel jobs used to execute the aggregation steps. Default value: Recommended value: |
The HISTORICAL load populates the OLAP dimensions and cubes based on the existing status of the dimension and fact source views. After populating the dimensions and cubes, it performs the default forecasting.
RBIA_OLAP_ETL_AW_LOAD.OLAP_ETL_AW_CUBES ( mode VARCHAR2, build_type VARCHAR2, max_job_queues INTEGER);
The parameters are described in the following table.
Table 5-6 OLAP_ETL_AW_CUBES Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The following modes are supported:
The historical load loads data as follows:
|
|
|
One of the following values: |
|
|
Specifies the number of parallel jobs used to execute the aggregation steps. Default value: Recommended value: |
Builds all of the dimensions in the PSLSINV analytic workspace.
RBIA_OLAP_ETL_AW_LOAD.OLAP_ETL_AW_DIMBUILD ( mode VARCHAR2, build_type VARCHAR2, max_job_queues INTEGER);
The parameters are described in the following table.
Table 5-7 OLAP_ETL_AW_DIMBUILD Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The following modes are supported:
|
|
|
One of the following values: |
|
|
Specifies the number of parallel jobs used to execute the aggregation steps. Default value: Recommended value: |
Populates a specified cube in the PSLSINV analytic workspace.
RBIA_OLAP_ETL_AW_LOAD.OLAP_ETL_AW_CUBEBUILD ( name VARCHAR2, mode VARCHAR2, build_type VARCHAR2, max_job_queues INTEGER);
The parameters are described in the following table.
Table 5-8 OLAP_ETL_AW_CUBEBUILD Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The fully-qualified name of the cube you want to build. The default cube name varies depending on the Oracle Database release:
|
|
|
The following modes are supported:
|
|
|
One of the following values: |
|
|
Specifies the number of parallel jobs used to execute the aggregation steps. Default value: Recommended value: |
Functionality in historical mode varies by release:
(10g) Replaces the definitions in the specified target analytic workspace with the definitions from the specified source analytic workspace.
(11g) Clears the data in the PSLSINV analytic workspace data objects by deleting the dimension members of all the dimensions of the analytical workspace.
RBIA_OLAP_ETL_AW_LOAD.OLAP_ETL_AW_REPL_DEFN ( mode VARCHAR2, sourceAW VARCHAR2, targetAW VARCHAR2);
The parameters are described in the following table.
Table 5-9 OLAP_ETL_AW_REPL_DEFN Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The following modes are supported:
|
|
|
(Oracle Database 10g only) The analytic workspace that contains the definitions for the analytic workspace, but not the data. Default value varies by Oracle Database release:
|
|
|
Adds a record in table for the value specified by this parameter. Default value is |
Sets the date ranges of the fact data to be captured as part of the Oracle Retail Data Model OLAP load process. For both historical and incremental loads, the date range is read from the DWC_ETL_PARAMETER table for record with PROCESS_NAME = 'RBIA-INTRA-ETL-OLAP'. The fact cubes are always loaded from the *_CURR fact views. However the *_CURR view points to either the *_FULL or *_INCR view based on the load type.
RBIA_OLAP_ETL_AW_LOAD.OLAP_ETL_AW_RESET_VIEWS ( mode VARCHAR2);
The parameters are described in the following table.
Table 5-10 OLAP_ETL_AW_BUILD Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The following modes are supported:
|
|
 Copyright © 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|