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
Intra-ETL OLAP Mapping Control Table
Intra-ETL Monitoring Process Control Tables

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` M`X` 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 initial load, specify `BY 2011 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 initial load, specify `BY 2006 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