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