6 ETL for the Oracle Retail Data Model

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.

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

• Derived Population: A database package containing scripts that populate the derived tables based on the content of the base, reference, and lookup tables.

• Aggregate Population: A database package containing scripts to refresh the Oracle Retail Data Model aggregate objects, implemented as Materialized Views, based on the content of the derived tables and some reference tables.

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

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

• As a Workflow within Oracle Warehouse Builder (using the ORDM_INTRA_ETL_FLW): this is a process flow designed using the Oracle Warehouse Builder Workflow component which includes the dependency of each individual sub process flow and executes each process flow in the proper order. The result of each table loading is tracked in DWC_ control tables.

• Without using Oracle Warehouse Builder Workflow (using run procedure in the PL/SQL package pkg_intra_etl_process). Thus, if you do not have Oracle Warehouse Builder Workflow or do not want to use it, you can use this method to perform the Intra-ETL tasks.

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".

Intra-ETL for Populating Aggregate Tables and Relational Materialized Views

The relational materialized view scripts are at the following locations:

• Relational materialized views are created from a script located in $ORACLE_HOME/ordm/pdm/relational/sql_scripts, and the script is mv.sql.

• Relational materialized view log creation scripts are located at $ORACLE_HOME/ordm/pdm/relational/sql_scripts, and the script is mvlog.sql.

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.

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:

• "Details of the OLAP_MAP Mapping"

• "OLAP_ETL_AW_BUILD"

• Chapter 7, "Oracle Retail Data Model OLAP ETL"

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:

• "Details of the ORDM_MNNG_FLW"

• Chapter 10, "Data Mining Models in Oracle Retail Data Model"

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.

• As a Workflow within Oracle Warehouse Builder (using the ORDM_INTRA_ETL_FLW): this is a process flow designed using the Oracle Warehouse Builder Workflow component which includes the dependency of each individual sub process flow and executes each process flow in the proper order. The result of each table loading is tracked in DWC_ control tables.

• Without using Oracle Warehouse Builder Workflow (using the run procedure in the PL/SQL package pkg_intr_etl_process). Thus, if you do not have Oracle Warehouse Builder Workflow or do not want to use it, you can use this method to perform the Intra-ETL tasks.

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 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".

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 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 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 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 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 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:

• ORDM_DERIVED_FLW - This sub-process flow contains all derived ETL package code for populating derived tables based on the content of base, reference, and lookup tables.

• ORDM_AGG_N_DEP_FLW - This sub-process flow contains PL/SQL code for refreshing all aggregate materialized views, which are all independent. The ORDM_AGG_N_DEP_FLW has materialized views with no dependencies among them.

• ORDM_AGG_DEP_FLW - This sub-process flow contains PL/SQL code for refreshing all aggregate materialized views, a few of which are dependent. Thus, the ORDM_AGG_DEP_FLW flow has materialized views with dependencies. For example, the DWA_CUST_ORDR_MO table that is defined on DWA_CUST_ORDR_WK (for this you first need to refresh the *_WK materialized views, and then update the *_MO materialized views).

• OLAP_MAP - This mapping invokes OLAP ETL package to load data to OLAP objects.

• ORDM_MNNG_FLW - This sub-process flow invokes Mining package to refresh mining source materialized views and refresh mining models.

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:

• Executing the Intra-ETL for Oracle Retail Data Model

• Monitoring the Execution of the Intra-ETL Process

• Recovering an Intra-ETL Process

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:

• Inserts a record into table DWC_INTRA_ETL_PROCESS with a monotonically increasing system generated unique process key, SYSDATE as process start time, RUNNING as the process status, input date range in the fields FROM_DATE_ETL and TO_DATE_ETL.

• Invokes each of the individual Intra-ETL programs in the appropriate order of dependency. Before the invocation of each program, the procedure inserts a record into the Intra-ETL Activity detail table DWC_INTRA_ETL_ACTIVITY with a system generated unique activity key, the process key value corresponding to the Intra-ETL process, individual program name as the Activity Name, a suitable activity description, SYSDATE as activity start time, RUNNING as the activity status.

• Updates the corresponding record in the DWC_INTRA_ETL_ACTIVITY table for the activity end time and activity status after the completion of each individual ETL program (either successfully or with errors. For successful completion of the activity, the procedure updates the status as COMPLETED-SUCCESS. If an error occurs, the procedure updates the activity status as 'COMPLETED-ERROR', and also updates the corresponding error detail in the ERROR_DTL column.

• Updates the record corresponding to the process in the DWC_INTRA_ETL_PROCESS table for the process end time and status, after the completion of all individual intra-ETL programs. If all the individual programs succeed, the procedure updates the status to 'COMPLETED-SUCCESS'; otherwise it updates the status to 'COMPLETED-ERROR'. You can monitor the execution state of the Intra-ETL, including current process progress, time taken by individual programs, or the complete process, by viewing the contents of the DWC_INTRA_ETL_PROCESS and DWC_INTRA_ETL_ACTIVITY tables corresponding to the maximum process key. Monitoring can be done both during and after the execution of the Intra-ETL procedure.