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

A Control Tables

Some tables are defined in the ordm_sys schema and use a DWC_ prefix; these are control tables. You use the DWC_ control tables when processing the model. For example, when you are loading data or when you are monitoring errors.

This appendix includes the following sections:

Intra-ETL Load Parameters Control Table

Invoke the procedure pkg_intra_etl_process.run to manually execute the Intra-ETL. Before you run the Intra-ETL, for an incremental load, you must update the Oracle Retail Data Model Relational ETL parameters in DWC_ETL_PARAMETER table so that this information can be used when loading the relational data. This program reads several ETL parameters (functional/operational/environmental) from DWC_ETL_PARAMETER table, as shown in Table A-1, and DWC_OLAP_ETL_PARAMETER table, as shown in Table A-2.

The PKG_DWD* packages load data from Oracle Retail Data Model base tables into the Oracle Retail Data Model derived tables. These packages read relational ETL parameters from the DWC_ETL_PARAMETER table.

You update the parameters in DWC_ETL_PARAMETER control table in the ordm_sys schema so that this information can be used when loading the derived and aggregate tables and views.

Table A-1 describes the valid values for the DWC_ETL_PARAMETER table.

Table A-1 DWC_ETL_PARAMETER Table

Column Description

Process_name

ORDM-INTRA-ETL

from_date_etl

The start date of ETL period.

to_date_etl

The end date of ETL period.

load_dt

The date when this record are populated.

last_updt_dt

The date when this record are last updated

last_updt_by

The user who last updated this record


Intra-ETL OLAP Mapping Control Table

The OLAP MAP mapping that loads OLAP cube data invokes the analytic workspace build function from the PKG_ORDM_OLAP_ETL_AW_LOAD package. This package loads data from Oracle Retail Data Model aggregate materialized views into the Oracle Retail Data Model analytical workspace and calculates the forecast data. The PKG_ORDM_OLAP_ETL_AW_LOAD reads OLAP ETL parameters from the DWC_OLAP_ETL_PARAMETER table.

You update the Oracle Retail Data Model OLAP ETL parameters in DWC_OLAP_ETL_PARAMETER control table in the ordm_sys schema so that this information can be used when loading the OLAP cube data.

Table A-2 describes the valid values for the DWC_OLAP_ETL_PARAMETER table. For more information on the values to specify when performing an initial load of OLAP cube data or when refreshing the OLAP cubes after an initial load, see Oracle Retail Data Model Operations Guide.

Table A-2 ETL Parameters in DWC_OLAP_ETL_PARAMETER

Column Name Value

BUILD_METHOD

Use the build method parameter to indicate a full or a fast (partial) refresh. The following are the possible values for BUILD_METHOD:

  • C: Complete refresh clears all dimension values before loading. (Default value).

  • F: Fast refresh of a cube materialized view, which performs an incremental refresh and re-aggregation of only changed rows in the source table.

  • ?: Fast refresh if possible, and otherwise a complete refresh.

  • P: Recomputes rows in a cube materialized view that are affected by changed partitions in the detail tables.

  • S: Fast solve of a compressed cube. A fast solve reloads all the detail data and re-aggregates only the changed values.

Note:

In a fast refresh, only changed rows are inserted in the cube and the affected areas of the cube are re-aggregated.

The C, S, and ? methods always succeed and can be used on any cube.

The F and P methods require that the cube have a materialized view that was created as a fast or a rewrite materialized view.

For initial load, specify C which specifies a complete refresh which clears all dimension values before loading.

BUILD_METHOD_TYPE

HISTORICAL or INCREMENTAL indicating whether this is an initial load of OLAP AW or an incremental load of the OLAP AW.

For initial load, specify HISTORICAL

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.

For initial load, specify Y.

CUBENAME

One of the following values that specifies the cubes you build:

  • ALL specifies a build of the cubes in the Oracle Retail Data Model analytic workspace.

  • cubename[[|cubename]...] specifies one or more cubes to build.

For initial load, specify ALL.

FCST_MTHD

If the value for the CALC_FCST column is Y, then specify AUTO; otherwise, specify NULL. Another valid value is MANUAL which sets the forecasting approach to APPMANUAL instead of APPAUTO (APPAUTO and APPMANUAL are internal terms used by Oracle OLAP Forecasting command). This parameter is ignored if CALC_FCST column is N.

For initial load, specify AUTO.

FCST_ST_MO

If the value for the CALC_FCST column is Y, then specify value specified as BY YYYY MX which is the "end business month" of a historical period; otherwise, specify NULL. This parameter is ignored if CALC_FCST column is N.

The value X is the month number in a year.

For example:

BY 2011 M7, or BY 2011 M11

For the sample data present in the sample schema installed with Oracle Retail Data Model Sample Reports, for initial load, specify: BY 2012 M1

HIST_ST_MO

If the value for the CALC_FCST column is Y, then specify value specified as BY YYYY MX which is the "start business month" of historical data; otherwise, specify NULL. This parameter is ignored if CALC_FCST column is N. X is the month number in a year.

For example: BY 2011 M7, or BY 2011 M11

For the sample data present in the sample schema installed with Oracle Retail Data Model Sample Reports, for initial load, specify: BY 2010 M1

MAXJOBQUEUES

A decimal value that specifies the number of parallel processes to allocate to this job. (Default value is 4.) The value that you specify varies depending on the setting of the JOB_QUEUE_PROCESSES database initialization parameter.

NO_FCST_YRS

If the value for the CALC_FCST column is Y, specify a decimal value that specifies how many years forecast data to calculate; otherwise, specify NULL. This parameter is ignored if CALC_FCST column is N.

For initial load, specify 2

OTHER1

Not used. Specify NULL.

OTHER2

Not used. Specify NULL.

PROCESS_NAME

'ORDM-OLAP-ETL'


Intra-ETL Monitoring Process Control Tables

The two control table in the ordm_sys schema, DWC_INTRA_ETL_PROCESS and DWC_INTRA_ETL_ACTIVITY, monitor the execution of the Intra-ETL process.

Table A-3 contains column name information for DWC_INTRA_ETL_PROCESS. Table A-4 contains column name information for DWC_INTRA_ETL_ACTIVITY.

Table A-3 DWC_INTRA_ETL_PROCESS Columns

Columns Name Data Type Not Null Remarks

PROCESS_KEY

NUMBER(30)

Yes

Primary Key, System Generated Unique Identifier

PROCESS_START_TIME

DATE

Yes

ETL Process Start Date and Time

PROCESS_END_TIME

DATE

ETL Process End Date and Time

 

PROCESS_STATUS

VARCHAR2(30)

Yes

Current status of the process

FROM_DATE_ETL

DATE

Start Date (ETL) -

From Date of the ETL date range

 

TO_DATE_ETL

DATE

End Date (ETL) - To

Date of the ETL date range

 

LOAD_DT

DATE

Record Load Date -

Audit Field

 

LAST_UPDT_DT

NUMBER(30)

Last Update Date and

Time - Audit Field

 

LAST_ UPDT_BY

VARCHAR(30)

Last Update By -

Audit Field

 

Table A-4 DWC_INTRA_ETL_ACTIVITY Columns

Columns Name Data Type Not Null Remarks

ACTIVITY_KEY

NUMBER(30)

Yes

Primary Key, System Generated Unique Identifier

PROCESS_KEY

NUMBER(30)

Yes

Process Key. FK to DWC_INTRA_ETL_

PROCESS table

ACTIVITY_NAME

VARCHAR2(50)

Yes

Activity Name or Intra ETL Program

Name

ACTIVITY_DESC

VARCHAR2(500)

Activity description

 

ACTIVITY_START_TIME

DATE

Yes

Intra ETL Program Start Date and Time

ACTIVITY_END_TIME

DATE

Intra ETL Program End Date and Time

 

ACTIVITY_STATUS

VARCHAR2(30)

Yes

Current status of the process

ERROR_DTL

VARCHAR2(2000)

Error details if any

 

LOAD_DT

DATE

Record Load Date -

Audit Field

 

LAST_UPDT_DT

NUMBER(30)

Last Update Date and

Time - Audit Field

 

LAST_ PDT_BY

VARCHAR(30)

Last Update By -

Audit Field