7 Oracle Retail Data Model OLAP ETL

OLAP Component Initial Load Script

The OLAP cube initial load script ordm_cube_historical_load.sql loads the dimensions and fact data from the relational star schema into the analytic workspace dimension and cubes and typically, executes the OLAP forecasts.

Pre-requisites

• ordm_sys schema should have been created and all requisite privileges granted to the schema user.

• Analytical Workspace "ORDM" should have been created/installed correctly (without errors in the install logs).

• Data should be setup in OLAP ETL Parameter table DWC_OLAP_ETL_PARAMETER such that it corresponds to the specifics of the Oracle Retail Data Model installation.

• Data should have been into the Oracle Retail Data Model Derived/Reference tables via a successful execution of the Intra-ETL.

Actions/Tasks Performed:

• To populate the OLAP cubes in Oracle Retail Data Model, the OLAP cube initial load script executes the olap_etl_awbuild procedure of Package PKG_OLAP_ETL_AW_LOAD in HISTORICAL mode. This procedure in turn invokes other procedures:

  • non-forecast cubes build olap_etl_nf_cube_build

  • forecast cubes build olap_etl_nf_cube_build

• This populates all of analytic workspace non-forecast cubes/dimensions with relational data.

• Forecast cubes are loaded via OLAP DML programs which execute Forecast commands and generate data for the Forecast Cubes within the OLAP Analytical Workspace itself.

• The build method used for loading the cubes in the Analytical Workspace is 'C' indicating a COMPLETE load from relational schema source tables.

Post Load Actions:

• Sets up the data in OLAP ETL Parameter table suitable for subsequent loads to be performed in Incremental mode. Typically this involves modifying the settings in the OLAP ETL Parameter table as follows: build_method_type is set to 'INCREMENTAL', build_method is set to '?', calc_fcst to Y/N based on whether Forecast process needs to be run for incremental loads, hist_st_mo and forecast_st_mo parameters can be shifted forward by a month, and so on.

OLAP Component Incremental Load Script

The OLAP cube initial load script ordm_cube_incremental_load.sql loads the dimensions and fact data from the relational star schema into the analytic workspace dimension and cubes and optionally, executes the OLAP forecasts. This script typically loads only the freshly modified/recently updated data in the relational source objects onto the Analytical Workspace and generally, would not load the entire relational source object on to the corresponding OLAP object.

Pre-requisites:

• The ordm_sys schema should have been created and all requisite privileges granted to the schema user.

• Analytical Workspace "ORDM" should have been created/installed correctly (without errors in install logs).

• The initial load script should have been executed so that the relational schema data and OLAP Analytical Workspace Dimensions/Cubes were in sync at the end of the initial load process.

• After that, additional data should have been loaded into the relational source objects (Reference/Derived) typically via the Intra-ETL programs.

• OLAP ETL Parameter table has been updated for the data boundary parameters as well as OLAP Forecast process related parameters to indicate the nature of the incremental load desired.

Actions/Tasks Performed:

• Executes the OLAP_ETL_AWBUILD subprogram in INCREMENTAL mode. This procedure in turn invokes other procedures:

  • non-forecast cubes build olap_etl_nf_cube_build

  • optional: forecast cubes build olap_etl_nf_cube_build

• This populates all of analytic workspace non-forecast cubes/dimensions with updated relational data.

• Based on the settings relating to Forecast parameters in OLAP ETL Parameter table, it optionally runs the forecast process in an incremental fashion and serves to update the Forecast Cubes data in the Analytical Workspace.

• The build method used for loading the cubes in the Analytical Workspace is '?' indicating a FAST refresh (or failing that, COMPLETE) load is to be attempted while loading from relational schema source tables.

Post Load Actions:

• Sets up the data in OLAP ETL Parameter table suitable for subsequent loads to be performed in Incremental mode. Typically this involves shifting forward by a month the parameters hist_st_mo and forecast_st_mo in the OLAP ETL Parameter table.

Populating During the Initial Load of the OLAP Cube Data

This load is performed by a SQL script ordm_cube_historical_load.sql that is delivered with the Oracle Retail Data Model OLAP component.

When relational data exists in the Oracle Retail Data Model data warehouse, the OLAP cube initial load script (also called the Historical Load) loads relational table data into the OLAP cubes. It also performs the OLAP forecasts as per the entries in OLAP Configuration table DWC_OLAP_ETL_PARAMETER.

You can execute the OLAP cube initial load SQL script explicitly after you have installed the Oracle Retail Data Model OLAP component and populated the relational source objects. In this case, you execute the OLAP cube initial load SQL program as you would any other SQL program.

• Go to directory $ORACLE_HOME/ordm/pdm/olap/

• Login to SQL * PLUS using ordm_sys login

• Verify the configuration/default entries in table DWC_OLAP_ETL_PARAMETER

For more information, see Oracle Retail Data Model Installation Guide.

• Run the Historical Load script:

  SQL>@ordm_cube_historical_load.sql
  

Populating on a Continuous or Scheduled Basis to Update the OLAP Cube Data

On a continuous or 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 initial load of the OLAP cubes.

This type of load (also called as Incremental Load) is performed by a SQL script ordm_cube_incremental_load.sql that is delivered with the Oracle Retail Data Model OLAP component. The Incremental Load script adds new/updated relational data from the source tables into the OLAP Dimensions/Cubes present in Analytical Workspace "ORDM".

Based on the configuration parameters in OLAP ETL Parameter table, the Forecast process may or may not be executed. If the Forecast process is triggered/executed, then the data in Sales and Inventory Forecast Cubes (SLS_FCST, SLS_FCST_STTSTC, INV_FCST, INV_FCST_STTSTC) would also be modified/updated.

You can execute the OLAP cube Incremental Load SQL script explicitly after you have executed the Oracle Retail Data Model OLAP component Initial Load script and additional data has been populated in the relational source objects. In this case, you execute the OLAP cube initial load SQL program as you would any other SQL program.

• Go to directory $ORACLE_HOME/ordm/pdm/olap/

• Login to SQL * PLUS using ordm_sys login

• Verify the configuration/default entries in table DWC_OLAP_ETL_PARAMETER

For more information, see Oracle Retail Data Model Installation Guide.

• Run the Incremental Load script:

  SQL>@ordm_cube_incremental_load.sql
  

For more information on executing OLAP loads and for updating the data in the OLAP forecast cubes, see Oracle Retail Data Model Implementation and Operations Guide.

For detailed information about the behavior of the OLAP cube Initial and Incremental Load Scripts, see "OLAP Component Load Scripts".

Summary of the PKG_OLAP_ETL_AW_LOAD Subprograms

Table 7-3 lists the all of the package subprograms.

OLAP_ETL_AW_BUILD

This function is used to build the complete Analytical Workspace. This function in turn calls the non-forecast cubes load function olap_etl_nf_cube_build as well as forecast cubes load function olap_etl_fcst_build.

Returns 0 in case of successful execution, -1 otherwise after putting error details in standard output (to be redirected to log file).

Syntax

FUNCTION PKG_ORDM_OLAP_ETL_AW_LOAD.OLAP_ETL_AW_BUILD (
p_build_method char default '?',
p_build_method_type varchar2 default 'INCREMENTAL',
p_cubename varchar2 default NULL,
p_maxjobqueues integer default 4,
p_calc_fcst char default 'N',
p_no_fcst_yrs integer default NULL,
p_fcst_mthd varchar2 default 'AUTO',
p_hist_st_mo varchar2 default NULL,
p_fcst_st_mo varchar2 default NULL,
p_other1 varchar2 default NULL,
p_other2 varchar2 default NULL
)
RETURN INTEGER;

Parameters

Table 7-4 shows the OLAP_ETL_AW_BUILD Function Parameters

OLAP_ETL_NF_CUBE_BUILD

This function loads the non-forecast cubes one after the other.

Returns 0 in case of successful execution, -1 otherwise after putting error details in standard output (to be redirected to log file).

Syntax

FUNCTION PKG_ORDM_OLAP_ETL_AW_LOAD.OLAP_ETL_NF_CUBE_BUILD (
p_build_method char default '?',
p_cubename varchar2 default 'ALL',
p_maxjobqueues integer default 4
)
RETURN INTEGER;

Parameters

Table 7-5 shows the OLAP_ETL_NF_CUBE_BUILD Function Parameters.

OLAP_ETL_FCST_BUILD

This function calls the OLAP DML forecast programs and loads the forecast cubes.

Returns 0 in case of successful execution, -1 otherwise after putting error details in standard output (to be redirected to log file).

Syntax

FUNCTION PKG_ORDM_OLAP_ETL_AW_LOAD.OLAP_ETL_FCST_BUILD (
p_fcst_mthd varchar2 default 'AUTO',
p_hist_st_mo varchar2 default NULL,
p_fcst_st_mo varchar2 default NULL,
p_no_fcst_yrs integer default NULL
)
RETURN INTEGER;

Parameters

Table 7-6 shows the OLAP_ETL_FCST_BUILD Function Parameters.