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

7 Oracle Retail Data Model OLAP ETL

This chapter describes 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.

This chapter includes the following sections:

For more information, see Chapter 8, "Oracle Retail Data Model OLAP Dimensions".

Oracle Retail Data Model OLAP Source Objects

The source data needed to load the OLAP Analytical Workspace are present in various types of objects: base, derived, lookup, and reference tables as well as relational views.

The following tables list the source objects used to load the OLAP Dimensions and Cubes.

Table 7-1 lists the Relational source objects used to load the OLAP Dimensions.

Table 7-1 Relational Source Objects for Loading OLAP Dimensions

Relational Object Name Relational Object Type OLAP Dimension(s) # of Dimension(s) More Information

DWL_ACTVTY_RQST_TYP

Lookup

ACTRQSTTYP

1

Activity Request Type: ACTRQSTTYP

DWL_ASSTS_TYP

Lookup

ASSTTYPE

1

Assets Type: ASSTTYPE

DWL_CUST_TYP

Lookup

CUSTOMER

1

Customer: CUSTOMER

DWL_EMP_TYP

Lookup

EMPLOYEE

1

Employee: EMPLOYEE

DWL_ENV_TYP

Lookup

ENVTYPE

1

Environment Type: ENVTYPE

DWL_INTRACN_RSN

Lookup

INTRACNRSN

1

Interaction Reason: INTRACNRSN

DWL_INTRACN_STAT

Lookup

INTRACNSTAT

1

Interaction Status: INTRACNSTAT

DWL_INTRACN_TYP

Lookup

INTRACNTYP

1

Interaction Type: INTRACNTYP

DWL_INV_LOC_TYP

Lookup

INVLOC

1

Inventory Location: INVLOC

DWL_LIAB_TYP

Lookup

LIABTYP

1

Liability Type: LIABTYP

DWL_ORDR_TYP

Lookup

ORDRTYP

1

Order Type: ORDRTYP

DWL_PAY_TYP

Lookup

PAYTYPE

1

Pay Type: PAYTYPE

DWL_RFMP_MTHD

Lookup

RFMP

1

RFMP: RFMP

DWL_RQST_ORIGIN

Lookup

RQSTORIGIN

1

Request Origin: RQSTORIGIN

DWL_RSN

Lookup

REASON

1

Reason: REASON

DWL_SHFT_TYP

Lookup

BUSHIFT

1

Business Unit Shift: BUSHIFT

DWL_UOM

Lookup

UOM

1

Unit Of Measure: UOM

DWL_UOM_TYP

Lookup

UOM

1

Unit Of Measure: UOM

DWL_VNDR_CLASS

Lookup

VENDOR

1

Vendor: VENDOR

DWR_BSNS_HLF_MO

Reference

TIME

1

Time: TIME

DWR_BSNS_HLF_YR

Reference

TIME

1

Time: TIME

DWR_BSNS_MO

Reference

TIME

1

Time: TIME

DWR_BSNS_QTR

Reference

TIME

1

Time: TIME

DWR_BSNS_UNIT_SHFT

Reference

BUSHIFT

1

Business Unit Shift: BUSHIFT

DWR_BSNS_WK

Reference

TIME

1

Time: TIME

DWR_BSNS_YR

Reference

TIME

1

Time: TIME

DWR_CARRIER

Reference

CARRIER

1

Carrier: CARRIER

DWR_CLNDR_HLF_MO

Reference

TIME

1

Time: TIME

DWR_CLNDR_HLF_YR

Reference

TIME

1

Time: TIME

DWR_CLNDR_MO

Reference

TIME

1

Time: TIME

DWR_CLNDR_QTR

Reference

TIME

1

Time: TIME

DWR_CLNDR_WK

Reference

TIME

1

Time: TIME

DWR_CLNDR_YR

Reference

TIME

1

Time: TIME

DWR_CMPGN

Reference

CMPGNMEDIA

1

Campaign Media: CMPGNMEDIA

DWR_CMPGN_MEDIA

Reference

CMPGNMEDIA

1

Campaign Media: CMPGNMEDIA

DWR_CUST

Reference

CUSTOMER

1

Customer: CUSTOMER

DWR_CUST_CLSTR

Reference

CUSTOMER

1

Customer: CUSTOMER

DWR_DAY

Reference

TIME

1

Time: TIME

DWR_EMP

Reference

EMPLOYEE

1

Employee: EMPLOYEE

DWR_INV_LOC

Reference

INVLOC

1

Inventory Location: INVLOC

DWR_ITEM

Reference

PRODUCT, PRODQR

2

Product: PRODUCT, Product QR Dimension: PRODQR

DWR_ITEM_CLASS

Reference

PRODUCT, PRODQR

2

Product: PRODUCT, Product QR Dimension: PRODQR

DWR_ITEM_CLSTR

Reference

PRODUCT

1

Product: PRODUCT

DWR_ITEM_CMPNY

Reference

PRODUCT, PRODQR

2

Product: PRODUCT, Product QR Dimension: PRODQR

DWR_ITEM_DEPT

Reference

PRODUCT, PRODQR

2

Product: PRODUCT, Product QR Dimension: PRODQR

DWR_ITEM_DIV

Reference

PRODUCT, PRODQR

2

Product: PRODUCT, Product QR Dimension: PRODQR

DWR_ITEM_GRP

Reference

PRODUCT, PRODQR

2

Product: PRODUCT, Product QR Dimension: PRODQR

DWR_ITEM_SBC

Reference

PRODUCT, PRODQR

2

Product: PRODUCT, Product QR Dimension: PRODQR

DWR_MEDIA

Reference

CMPGNMEDIA

1

Campaign Media: CMPGNMEDIA

DWR_ORG_AREA

Reference

ORGANIZATION, ORGQR

2

Organization: ORGANIZATION, Organization QR Dimension: ORGQR

DWR_ORG_BNR

Reference

ORGANIZATION

1

Organization: ORGANIZATION

DWR_ORG_BSNS_UNIT

Reference

ORGANIZATION, ORGQR

2

Organization: ORGANIZATION, Organization QR Dimension: ORGQR

DWR_ORG_CHAIN

Reference

ORGANIZATION, ORGQR

2

Organization: ORGANIZATION, Organization QR Dimension: ORGQR

DWR_ORG_CMPNY

Reference

ORGANIZATION, ORGQR

2

Organization: ORGANIZATION, Organization QR Dimension: ORGQR

DWR_ORG_DIV

Reference

ORGANIZATION

1

Organization: ORGANIZATION

DWR_ORG_DSTRCT

Reference

ORGANIZATION, ORGQR

2

Organization: ORGANIZATION, Organization QR Dimension: ORGQR

DWR_ORG_RGN

Reference

ORGANIZATION, ORGQR

2

Organization: ORGANIZATION, Organization QR Dimension: ORGQR

DWR_POSTCD

Reference

POSTCD

1

Post Code: POSTCD

DWR_SKU_ITEM

Reference

PRODUCT, PRODQR

2

Product: PRODUCT, Product QR Dimension: PRODQR

DWR_TCHPNT

Reference

TOUCHPOINT

1

Touchpoint: TOUCHPOINT

DWR_TOT_TIME

Reference

TIME

1

Time: TIME

DWR_VNDR

Reference

VENDOR

1

Vendor: VENDOR

DWR_VNDR_ITEM

Reference

VENDORITEM

1

Vendor Item: VENDORITEM

DWR_VNDR_SITE

Reference

VNDRSITE

1

Vendor Site: VNDRSITE


Table 7-2 lists the Relational source objects used to load the OLAP Cubes.

Table 7-2 Relational Source Objects for Loading OLAP Cubes

Relational Object Name Relational Object Type OLAP Cube(s) Number of Cube(s) More Information

DWB_SL_PLAN_ITEM_ORG_HRCHY_WK

Base

SLPLN

1

Sales Plan Item Organization Hierarchy Cube: SLPLN

DWD_ACTVTY_RQST_DAY

Derived

AR

1

Activity Request Cube: AR

DWD_ASSTS_DAY

Derived

ASSET

1

Asset Cube: ASSET

DWD_CARRIER_CMPLNC_DAY

Derived

CC

1

Carrier Compliance Cube: CC

DWD_CUST_SKU_SL_RETRN_DAY

Derived

CSSR

1

Customer SKU Sale Return Cube: CSSR

DWD_CUST_TYP_ORDR_ITEM_DAY

Derived

CO

1

Customer Order Cube: CO

DWD_EMP_LBR

Derived

EL

1

Employee Labor Cube: EL

DWD_EMP_WG_PYMT_DAY

Derived

EWGP

1

Employee Wage Payment Cube: EWGP

DWD_INV_ADJ_ITEM_DAY

Derived

IA

1

Inventory Adjustment Cube: IA

DWD_INV_POSN_ITEM_DAY

Derived

INV

1

Inventory Cube: INV

DWD_INV_RCPT_ITEM_DAY

Derived

IR

1

Inventory Receipt Cube: IR

DWD_INV_UNAVL_ITEM_DAY

Derived

IU

1

Inventory Unavailable Cube: IU

DWD_LIAB_DAY

Derived

LIABILITY

1

Liability Cube: LIABILITY

DWD_ORG_BSNS_UNT_HRS_DAY

Derived

OBUH

1

Store Hours Cube: OBUH

DWD_ORG_BSNS_UNT_TRFC_DAY

Derived

OBUT

1

Store Traffic Cube: OBUT

DWD_PCHSE_ORDR_LI_STATE

Derived

POLIS

1

Purchase Order Line Item State Cube: POLIS

DWD_PCHSE_ORDR_STATE

Derived

POS

1

Purchase Order State Cube: POS

DWD_RTL_SL_RETRN_ITEM_DAY

Derived

SLS, SLSQR

2

Sales Cube: SLS, Sales Cube - Cube based QR enabled: SLSQR

DWD_RTL_TRX_EMP_WRKSTN_DAY

Derived

RTEW

1

Retail Transaction Employee Workstation Cube: RTEW

DWD_SPACE_UTLZTN_ITEM_DAY

Derived

SU

1

Space Utilization Cube: SU

DWD_VNDR_CMPLNC_ITEM_DAY

Derived

VC

1

Vendor Compliance Cube: VC

DWV_CUST_RFMP_SCR_VIEW

View (on Derived, Lookup)

CRFMP, CRFMPDC

2

Customer RFMP Cube: CRFMP, Customer RFMP DC Cube: CRFMPDC


General Process to Populate the OLAP Analytical Workspace in Oracle Retail Data Model

Load the Oracle Retail Data Model OLAP Analytical Workspace with the following steps:

  1. Identify and load the relational tables/MVs/Views using Oracle Retail Data Model Intra-ETL.

  2. Map the leaf level data from the relational Tables/MVs/Views to the OLAP cube.

  3. Map the dimensions of the cube to the Relational Tables (Reference/Lookup)/Views.

  4. Initiate a load using the process flow OLAP_MAP as explained in the section "OLAP Analytical Workspace ETL". This in turn makes calls to certain procedures/functions present in the OLAP ETL Package PKG_ORDM_OLAP_ETL_AW_LOAD which have been built for this purpose.

  5. Monitor the loads using the entries present in the CUBE_BUILD_LOG table as well as CUBE_BUILD_REPORT, and CUBE_BUILD_REPORT_LATEST views.

SQL Access to Analytical Workspace including Query Rewrite to Cube Organized Materialized Views

Oracle Retail Data Model reports can use SQL to query the relational base tables and the optimizer transparently translates the SQL to access either the table materialized views or the cube materialized views (and hence the analytic workspace cubes and dimensions) depending upon which provides the better performance.

Cube organized materialized views represent the cube to SQL-based applications as materialized views that you can use for both refresh and query rewrite. With Query Re-write enabled, Oracle will automatically re-write SQL queries targeted against relational tables.

All cubes, those available for Cube MV based Query Rewrite and otherwise, are available for user SQL based Query Tool access through CUBE_TABLE based SQL Views which are created and maintained automatically during the cube build/update process.

Using SQL to access the cubes and dimensions is a significant feature of Oracle OLAP because it enables reporting tools that only generate SQL to use all of the powerful features of the analytic workspace.

This allows all of the benefits of the analytic workspace to be easily available to any product using regular SQL. This feature is enabled for the Cube SLSQR in Oracle Retail Data Model OLAP Analytical Workspace "ORDM".

OLAP Component ETL

Oracle Retail Data Model 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 populated using the Intra-ETL workflow process OLAP_MAP. The workflow calls procedures/functions defined in the PKG_ORDM_OLAP_ETL_AW_LOAD package that is provided with the OLAP component.

This section discusses:

OLAP Component Installation Process

The Oracle Retail Data Model Oracle Universal Installer (OUI) triggers the execution of the environment setup SQL script:

$ORACLE_HOME/ordm/cfgtool/ordm_install.sbs

This script invokes the script:

$ORACLE_HOME/ordm/pdm/olap/ordm_aw_import.sql

which installs the OLAP Analytical Workspace "ORDM" in the product schema.

The OLAP environment setup script, ordm_install.sbs creates and sets up the Oracle Retail Data Model OLAP environment.

The OLAP environment setup script ordm_install.sbs performs the following tasks:

  • Creates user ordm_sys

  • Creates default, temporary and OLAP tablespaces for Oracle Retail Data Model schema and assigns the same to user ordm_sys.

  • Assigns required grants and privileges (including directories) to ordm_sys user.

  • Gathers statistics on sys.aw_obj$ table.

  • Invokes script: $ORACLE_HOME/ordm/pdm/olap/ordm_aw_import.sql

  • Defines additional OLAP specific views used by Reporting in the ordm_sys schema.

The script ordm_aw_import.sql performs the following tasks:

  • Sets up the OLAP logging mechanism (functional as well as operational logging)

  • Checks version compatibility of the database with Oracle Retail Data Model OLAP Component

  • Creates the analytic workspace(s) that define all of the analytic workspace objects used by the OLAP component.

  • Sets up data in OLAP ETL Parameter table with default values suitable for initial load of schema.

For more information about the objects defined by the ordm_sys schema, including the analytic workspaces defined by the schema, see "Oracle Retail Data Model OLAP Source Objects".

OLAP Component Load Scripts

The OLAP component load scripts includes the following scripts:

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.

Note:

The script that performs the OLAP cube initial load: For Oracle Retail Data Model 11.3.1, the script is ordm_cube_historical_load.sql

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.

Note:

The script that performs the OLAP cube incremental load: For Oracle Retail Data Model 11.3.1, the script is ordm_cube_incremental_load.sql

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.

When is the OLAP Analytical Workspace Populated?

OLAP cubes are populated at the following times:

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

OLAP_ETL Package: PKG_ORDM_OLAP_ETL_AW_LOAD

The PKG_ORDM_OLAP_ETL_AW_LOAD package contains subprograms (or functions) which populate the Analytical Workspace "ORDM". Broadly speaking, the following tasks are performed by the 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.

  • olap_etl_nf_cube_build: This function loads the non-forecast cubes one after the other. It can work on a subset of the complete list of non-forecast cubes through the p_cubename input argument (ALL indicates all applicable cubes. 'SLS|INV|CO|CRFMP|CRFMPDC' indicates that we want the cubes SLS, INV, CO, CRFMP and CRFMPDC alone to be loaded.)

  • olap_etl_fcst_build: This function calls the OLAP DML forecast programs and loads the 4 forecast cubes - SLS_FCST, SLS_FCST_STTSTC, INV_FCST and INV_FCST_STTSTC.

See also:

"OLAP Component Installation Process", Oracle Retail Data Model Installation Guide, and "Summary of the PKG_OLAP_ETL_AW_LOAD Subprograms".

Summary of the PKG_OLAP_ETL_AW_LOAD Subprograms

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

Table 7-3 PKG_OLAP_ETL_AW_LOAD Package Subprograms

Subprogram Description

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.

OLAP_ETL_NF_CUBE_BUILD

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

OLAP_ETL_FCST_BUILD

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


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

Table 7-4 OLAP_ETL_AW_BUILD Function Parameters

Parameter Description

p_build_method

A single character indicating the type of load to be attempted while loading the OLAP Cubes from relational sources.

Typically users are expected to use either of the following values

  • C: Complete refresh clears all dimension values before loading. (Default) during Initial load of the AW and

  • ?: Fast refresh if possible, and otherwise a complete refresh during the Incremental loads of the AW.

p_build_method_type

One of the following values: HISTORICAL or INCREMENTAL (default)

Used for logical/logging purposes.

p_cubename

Cubename to be loaded

  • ALL - All cubes as applicable

  • cubename[[|cubename]...] specifies one or more cubes to build. List of Cubenames delimted by '|' For example: 'SLS|CRFMP'

max_job_queues

Specifies the number of parallel jobs used to execute the aggregation steps.

Default value: 4

Recommended value: number-of-CPUs -1

p_calc_fcst

One of the following values depending on whether you calculate forecast cubes:

  • Y specifies calculate forecast cubes.

  • N specifies do not calculate forecast cubes.

p_no_fcst_yrs

Integer value that specifies how many years of forecast data is to be calculated/generated.

p_fcst_mthd

One of the following values: AUTO or MANUAL. Typically this is set to AUTO.

p_hist_st_mo

specify value as BYYYYY MX which is the "start business month" of historical data

p_fcst_st_mo

specify value as BYYYYY MX which is the "start business month" of the forecasting period

p_other1

Not used. Specify NULL.

p_other2

Not used. Specify NULL.


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.

Table 7-5 OLAP_ETL_NF_CUBE_BUILD Function Parameters

Parameter Description

p_build_method

A single character indicating the type of load to be attempted while loading the OLAP Cubes from relational sources.

Typically users are expected to use either of the following values

  • C: Complete refresh clears all dimension values before loading. (Default) during Initial load of the AW and

  • ?: Fast refresh if possible, and otherwise a complete refresh during the Incremental loads of the AW.

p_cubename

Cubename to be loaded

  • ALL - All cubes as applicable

  • cubename[[|cubename]...] specifies one or more cubes to build. List of Cubenames delimted by '|' For example: 'SLS|CRFMP'

max_job_queues

Specifies the number of parallel jobs used to execute the aggregation steps.

Default value: 4

Recommended value: number-of-CPUs -1


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.

Table 7-6 OLAP_ETL_FCST_BUILD Function Parameters

Parameter Description

p_fcst_mthd

One of the following values: AUTO or MANUAL. Typically this is set to AUTO.

p_hist_st_mo

Specify value as BYYYYY MX which is the "start business month" of historical data

p_fcst_st_mo

Specify value as BYYYYY MX which is the "start business month" of the forecasting period

p_no_fcst_yrs

Integer value that specifies how many years of forecast data is to be calculated/generated