Skip Headers
Oracle® Airlines Data Model Reference
11g Release 2 (11.2)

Part Number E26208-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master 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 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.

This appendix includes the following sections:

Intra-ETL Load Parameters Control Table

Before you run the Intra-ETL, for an incremental load, you must update the Oracle Airlines Data Model Relational ETL parameters in 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 DWC_ETL_PARAMETER table, as shown in Table A-1, and DWC_OLAP_ETL_PARM table, as shown in Table A-2. For more information on running the Intra-ETL, see Oracle Airlines Data Model Implementation and Operations Guide

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 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 describes the valid values for the DWC_ETL_PARAMETER table.

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


Intra-ETL OLAP Mapping Control Table

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 describes the valid values for the DWC_OLAP_ETL_PARM 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 Airlines Data Model Implementation and Operations Guide.

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:

  • C specifies a complete refresh which clears all dimension values before loading.

  • ? specifies a fast refresh if possible; otherwise, a complete refresh. (Default)

CUBENAME

Specifies the cubes you want to build:

ALL builds all of the cubes in the Oracle Airlines Data Model analytic workspace.

cubename[[|cubename]...] specifies one or more cubes, as specified with cubename, to build.

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

CALC_FCST

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

  • Y specifies calculate forecast cubes.

  • N specifies do not 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.


Intra-ETL Monitoring Process Control Tables

The two control table 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 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,0)

No

Primary Key, System

Generated Unique Identifier

PROCESS_TYPE

VARCHAR2(20 BYTE)

No

 

PROCESS_START_TIME

DATE

No

ETL Process Start Date and Time

PROCESS_END_TIME

DATE

Yes

 

PROCESS_STATUS

VARCHAR2(30 BYTE)

No

Current status of the process

OLD_PROCESS_KEY

NUMBER(22,0)

Yes

 

FROM_DATE_ETL

DATE

Yes

 

TO_DATE_ETL

DATE

Yes

 

LOAD_DT

DATE

Yes

 

LAST_UPDT_DT

DATE

Yes

 

LAST_UPDT_BY

VARCHAR2(30 BYTE)

Yes

 

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

 

ACTIVITY_START_TIME

DATE

No

Intra ETL Program Start Date

and Time

ACTIVITY_END_TIME

DATE

Yes

 

ACTIVITY_STATUS

VARCHAR2(30 BYTE)

No

Current status of the process

COPIED_REC_IND

CHAR(1 BYTE)

Yes

 

ERROR_DTL

VARCHAR2(2000 BYTE)

Yes

 

LOAD_DT

DATE

Yes

 

LAST_UPDT_DT

DATE

Yes

 

LAST_UPDT_BY

VARCHAR2(30 BYTE)

Yes

 

Intra-ETL Parameter Management Tables

Table A-5 contains column name information for DWC_ACTIVITY.

Table A-6 contains column name information for DWC_ACTIVITY_PARM.

Table A-7 contains column name information for DWC_ACTIVITY_PARM_TYP.

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. The approach to insert and update of these tables is defined in detail in Oracle Airlines Data Model Implementation and Operations Guide.

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


Intra-ETL Error Management Table

Table A-8 contains column name information for DWC_ERROR_LOG.

Table A-9 contains column name information for DWC_MESSAGE.

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

 

CRE_BY

VARCHAR2(60 BYTE)

YES

 

CRE_TMSTMP

TIMESTAMP(6)

YES

 

UPD_BY

VARCHAR2(60 BYTE)

YES

 

UPD_TMSTMP

TIMESTAMP(6)

   

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