Some tables are defined in the OADM_SYS schema and use a DWC_ prefix, but are not part of Oracle Airlines Data Model. You use the DWC_ control tables when processing the model. For example, when loading data or when monitoring errors.
oadm_sys
schema, DWC_INTRA_ETL_PROCESS
and DWC_INTRA_ETL_ACTIVITY
, monitor the execution of the Intra-ETL process.Parent topic: Appendices
Before you run the Intra-ETL, for an incremental load, you must update the Oracle Airlines Data Model Relational ETL parameters in the DWC_ETL_PARAMETER table so that this information can be used when loading the relational data. This program prompts for several environment parameter values, and reads ETL parameters from the DWC_ETL_PARAMETER table.
The PKG_DWD_*_MAP loads data from Oracle Airlines Data Model base tables into the Oracle Airlines Data Model derived tables. These packages read relational ETL parameters from the DWC_ETL_PARAMETER table.
You update the parameters in the DWC_ETL_PARAMETER
control table in the oadm_sys
schema so that this information can be used when loading the derived and aggregate tables and views.
Table A-1 DWC_ETL_PARAMETER Table
Column | Description |
---|---|
PROCESS_NAME |
OADM-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 |
Parent topic: Control Tables
The OLAP MAP mapping that loads OLAP cube data invokes the analytic workspace build function from the PKG_OADM_OLAP_ETL_AW_LOAD package. This package loads data from Oracle Airlines Data Model aggregate materialized views into the Oracle Airlines Data Model analytical workspace and calculates the forecast data.
The PKG_OADM_OLAP_ETL_AW_LOAD reads OLAP ETL parameters from the DWC_OLAP_ETL_PARM table.
You update the Oracle Airlines Data Model OLAP ETL parameters in DWC_OLAP_ETL_PARM
control table in the oadm_sys
schema so that this information can be used when loading the OLAP cube data.
Table A-2 ETL Parameters in the DWC_OLAP_ETL_PARM Table
Column Name | Description |
---|---|
BUILD_METHOD |
Cube build/refresh method specified by a value:
|
CUBENAME |
Specifies the cubes you want to build:
|
MAXJOBQUEUES |
A decimal value that specifies the number of parallel processes to allocate to this job. (Default value is The value that you specify varies depending on the setting of the JOB_QUEUE_PROCESSES database initialization parameter |
CALC_FCST |
One of the following values depending on whether you want to calculate forecast cubes:
|
NO_FCST_YRS |
If the value for the CALC_FCST column is Y, specify a decimal value that specifies how many years forecast data you want to calculate; otherwise, specify NULL. |
FCST_MTHD |
If the value for the CALC_FCST column is Y, then specify AUTO; otherwise, specify NULL. |
FCST_ST_YR |
If the value for the CALC_FCST column is Y, then specify value specified as yyyy which is the "start business year" of a historical period;. |
FCST_END_YR |
If the value for the CALC_FCST column is Y, then specify value specified as yyyy which is the "end business year" of a historical period; |
OTHER1 |
Specify NULL. |
OTHER2 |
Specify NULL. |
Parent topic: Control Tables
Two control tables in the oadm_sys
schema, DWC_INTRA_ETL_PROCESS
and DWC_INTRA_ETL_ACTIVITY
, monitor the execution of the Intra-ETL process.
Table A-3 DWC_INTRA_ETL_PROCESS Columns
Columns Name | Data Type | Not Null | Remarks |
---|---|---|---|
PROCESS_KEY |
NUMBER(30,0) |
No |
Primary Key, System Generated Unique Identifier |
PROCESS_TYPE |
VARCHAR2(20 BYTE) |
No |
No value |
PROCESS_START_TIME |
DATE |
No |
ETL Process Start Date and Time |
PROCESS_END_TIME |
DATE |
Yes |
No value |
PROCESS_STATUS |
VARCHAR2(30 BYTE) |
No |
Current status of the process |
OLD_PROCESS_KEY |
NUMBER(22,0) |
Yes |
No value |
FROM_DATE_ETL |
DATE |
Yes |
No value |
TO_DATE_ETL |
DATE |
Yes |
No value |
LOAD_DT |
DATE |
Yes |
No value |
LAST_UPDT_DT |
DATE |
Yes |
No value |
LAST_UPDT_BY |
VARCHAR2(30 BYTE) |
Yes |
No value |
Table A-4 DWC_INTRA_ETL_ACTIVITY Columns
Columns Name | Data Type | Not Null | Remarks |
---|---|---|---|
ACTIVITY_KEY |
NUMBER(30,0) |
No |
Primary Key, System Generated Unique Identifier |
PROCESS_KEY |
NUMBER(30,0) |
No |
Process Key. FK to DWC_INTRA_ETL_PROCESS table. |
ACTIVITY_NAME |
VARCHAR2(50 BYTE) |
No |
Activity Name or Intra-ETL Program Name |
ACTIVITY_DESC |
VARCHAR2(500 BYTE) |
Yes |
No value |
ACTIVITY_START_TIME |
DATE |
No |
Intra ETL Program Start Date and Time |
ACTIVITY_END_TIME |
DATE |
Yes |
No value |
ACTIVITY_STATUS |
VARCHAR2(30 BYTE) |
No |
Current status of the process |
COPIED_REC_IND |
CHAR(1 BYTE) |
Yes |
No value |
ERROR_DTL |
VARCHAR2(2000 BYTE) |
Yes |
No value |
LOAD_DT |
DATE |
Yes |
No value |
LAST_UPDT_DT |
DATE |
Yes |
No value |
LAST_UPDT_BY |
VARCHAR2(30 BYTE) |
Yes |
No value |
Parent topic: Control Tables
The design of the parameter management enables you to restrict the control on the parameter values. The parameter restrictions should be managed only by a project DBA and architect. A project DBA must provide only read access to others.
Table A-5 DWC_ACTIVITY Columns
Columns Name | Data Type | Not Null | Remarks |
---|---|---|---|
ACTIVITY_ID |
NUMBER |
No |
Marks the identifier for PL/SQL procedures. |
ACTIVITY_NAME |
VARCHAR2(255 BYTE) |
Yes |
Name of the PL/SQL program. |
Table A-6 DWC_ACTIVITY_PARM Columns
Columns Name | Data Type | Not Null | Remarks |
---|---|---|---|
ACTIVITY_ID |
NUMBER |
No |
The identifier for PL/SQL procedures |
PARM_TYPE_ID |
NUMBER |
No |
The identifier for a defined parameter |
PARM_POSITION |
NUMBER |
Yes |
A unique number for repeated use of the same parameter in a program |
PARM_VAL_TXT |
VARCHAR2(255 BYTE) |
Yes |
The true value of the parameter |
Table A-7 DWC_ACTIVITY_PARM_TYP Columns
Columns Name | Data Type | Not Null | Remarks |
---|---|---|---|
PARM_TYPE_ID |
NUMBER |
No |
The identifier for a defined parameter. |
PARM_TYPE_NAME |
VARCHAR2(255 BYTE) |
Yes |
Name of the parameter |
Parent topic: Control Tables
Shows the Intra-ETL error control management and message tables.
Table A-8 DWC_ERROR_LOG Columns
Columns Name | Data Type | Not Null | Remarks |
---|---|---|---|
ERROR_ID |
NUMBER |
NO |
Primary Key, System Generated Unique Identifier |
ERROR_CD |
VARCHAR2(30 BYTE) |
YES |
It contains error code which generate at execution time. |
ERROR_DESC |
VARCHAR2(600 BYTE) |
YES |
It contains the long description of error. |
SRC_ID |
NUMBER |
YES |
It contains the primary key of the source table. |
LOAD_DT |
TIMESTAMP(6) |
YES |
It contains the execution timestamp which helps to determine the load time. |
OBJECT_TYP |
VARCHAR2(25 BYTE) |
YES |
The attribute stores the type of object. For example, Package or Procedure and so on. |
OBJECT_NM |
VARCHAR2(250 BYTE) |
YES |
The attribute stores object name. |
OWNR |
VARCHAR2(40 BYTE) |
YES |
None |
CRE_BY |
VARCHAR2(60 BYTE) |
YES |
None |
CRE_TMSTMP |
TIMESTAMP(6) |
YES |
None |
UPD_BY |
VARCHAR2(60 BYTE) |
YES |
None |
UPD_TMSTMP |
TIMESTAMP(6) |
None |
None |
Table A-9 DWC_MESSAGE Columns
Columns Name | Data Type | Not Null |
---|---|---|
MESSAGE_NO |
NUMBER(6,0) |
NO |
LANGUAGE |
VARCHAR2(50 BYTE) |
NO |
MESSAGE_TEXT |
VARCHAR2(200 BYTE) |
NO |
Parent topic: Control Tables