PK
5Aoa, mimetypeapplication/epub+zipPK 5A iTunesMetadata.plistJ
This chapter provides information about the physical data model of Oracle Airlines Data Model.
This chapter includes the following sections:
The Physical Data Model of the Oracle Airlines Data Model is the physical manifestation of the logical data model into database tables and relationships (or foreign key constraints). Partitions and Materialized Views have been added to aid performance.
Important: Do not make changes to the schemas as such changes are not supported. |
Table 3-1 shows the table name prefix conventions. When you examine the predefined physical model, keep in mind the naming conventions shown in Table 3-1 that use DW (Data Warehouse) prefixes to identify the types of tables and views.
Table 3-1 Table Name Prefix and Suffix Conventions
Prefix | Description |
---|---|
CB$ |
Materialized view of an OLAP cube. This materialized view is automatically created by the OLAP server. Note: Do not report or query against this object. Instead access the corresponding _VIEW object. |
DM_ |
Data Mining Settings |
DMV_ |
Materialized views used for as the source data of data mining model |
DWA_ |
Aggregate tables |
DWB_ |
Base transaction data (3NF) tables |
DWC_ |
Control tables |
DWD_ |
Derived table (including data mining result tables) |
DWL_ |
Lookup tables |
DWM_ |
Dimension tables in an access layer fact table (that is, for a DWD_ or a DWA_ table) |
DWL_ |
Lookup table |
DWR_ |
Reference data tables used as dimension tables in a foundation layer fact table (that is, for a DWB_ table) |
DWV_ |
Relational view of time dimension |
_H |
"Classic" data warehouse table that is used to store both the most recent data and the historical data of a certain entity. For more information, see Oracle Airlines Data Model Implementation and Operations Guide. |
_VIEW |
Suffix specifies relational views of OLAP cubes, dimensions, or hierarchies. |
In the Oracle Airlines Data Model foundation layer, DWR_ tables (also known as reference tables) act as dimension tables to the base (DWB_ ) tables.
Table 3-2 lists the Reference tables in Oracle Airlines Data Model.
Table 3-2 Reference Tables
Table Name | Description and More information |
---|---|
DWR_ACCT | |
DWR_ACCT_H |
ACCOUNT HISTORY |
DWR_AIP | |
DWR_AIP_H |
AIRPORT HISTORY |
DWR_AWRD_VCHR | |
DWR_AWRD_VCHR_H |
AWARD VOUCHER HISTORY |
DWR_BKG_CAMPN | |
DWR_BKG_CAMPN_H |
BOOKING CAMPAIGN HISTORY |
DWR_BKG_OFF | |
DWR_BKG_OFF_H |
BOOKING OFFICE HISTORY |
DWR_BKG_OFF_USR | |
DWR_BKG_OFF_USR_H |
BOOKING OFFICE USER HISTORY |
DWR_BKG_PAX | |
DWR_BKG_PAX_H |
BOOKING PASSENGER HISTORY |
DWR_BKG_PAX_DOC_INFO | |
DWR_BKG_PAX_DOC_INFO_H |
BOOKING PASSENGER DOCUMENT INFORMATION HISTORY |
DWR_BKG_PROD | |
DWR_BKG_PROD_H |
BOOKING PRODUCT HISTORY |
DWR_BKG_SEAT_PREF | |
DWR_BKG_SEAT_PREF_H |
BOOKING SEAT PREFERENCE HISTORY |
DWR_BKG_SERS | |
DWR_BKG_SERS_H |
BOOKING SERIES HISTORY |
DWR_BKG_SSR_BRDG | |
DWR_BKG_SSR_BRDG_H |
BOOKING SSR BRIDGE HISTORY |
DWR_BKG_TST | |
DWR_BKG_TST_H |
BOOKING TRANSITIONAL STORE TICKET HISTORY |
DWR_BKG_TST_PRC | |
DWR_BKG_TST_PRC_H |
BOOKING TRANSITIONAL STORE TICKET PRICE HISTORY |
DWR_BKG_TST_SEG | |
DWR_BKG_TST_SEG_H |
BOOKING TRANSITIONAL STORE TICKET SEGMENT HISTORY |
DWR_BNK_CARD | |
DWR_BNK_CARD_H |
BANK CARD HISTORY |
DWR_CARR | |
DWR_CARR_H |
CARRIER HISTORY |
DWR_CDSH | |
DWR_CDSH_H |
CODESHARE HISTORY |
DWR_CDSH_BRDG | |
DWR_CDSH_BRDG_H |
CODESHARE BRIDGE HISTORY |
DWR_CHKIN_BAG_GRP | |
DWR_CHKIN_BAG_GRP_H |
CHECKING BAGGAGE GROUP HISTORY |
DWR_CHKIN_INDV_BAG | |
DWR_CHKIN_INDV_BAG_H |
CHECKIN INDIVIDUAL BAGGAGE HISTORY |
DWR_CUST_SGMNT | |
DWR_CUST_SGMNT_DTL | |
DWR_FLT | |
DWR_FLT_H |
FLIGHT HISTORY |
DWR_FRQTFLR | |
DWR_FRQTFLR_H |
FREQUENT FLYER HISTORY |
DWR_GRPNG | |
DWR_GRPNG_H |
GROUPING HISTORY |
DWR_INFLT_MEAL | |
DWR_INFLT_MEAL_H |
INFLIGHT MEAL HISTORY |
DWR_LEG | |
DWR_LEG_H |
LEG HISTORY |
DWR_LYLTY_ACCT | |
DWR_LYLTY_ACCT_H |
LOYALTY ACCOUNT HISTORY |
DWR_LYLTY_LVL | |
DWR_LYLTY_LVL_H |
LOYALTY LEVEL HISTORY |
DWR_ODT_ACCT | |
DWR_ODT_ACCT_H |
ODT ACCOUNT HISTORY |
DWR_OPTN | |
DWR_OPTN_H |
OPTION HISTORY |
DWR_PAX_CNTCT | |
DWR_PAX_CNTCT_H |
PASSENGER CONTACT HISTORY |
DWR_PAX_CTRY_ADDR_INFO | |
DWR_PAX_CTRY_ADDR_INFO_H |
PASSENGER COUNTRY ADDRESS INFORMATION HISTORY |
DWR_PAX_VISA_INFO | |
DWR_PAX_VISA_INFO_H |
PASSENGER VISA INFORMATION HISTORY |
DWR_PDI_CHRSTIC | |
DWR_PDI_CHRSTIC_H |
PDI CHARACTERISTIC HISTORY |
DWR_PNR_PARENT_CHILD_RELSHP | |
DWR_PNR_PARENT_CHILD_RELSHP_H |
PNR PARENT CHILD RELATIONSHIP HISTORY |
DWR_POS_GDS_OFF | |
DWR_POS_GDS_OFF_H |
POINTOF SALE GDS OFFICE IDENTIFIER HISTORY |
DWR_PRTY | |
DWR_PRTY_H |
PARTY HISTORY |
DWR_SEAT | |
DWR_SEAT_H |
SEAT HISTORY |
DWR_SEG | |
DWR_SEG_H |
SEGMENT HISTORY |
DWR_SMS_AGNT |
|
DWR_SMS_AGNT_H |
SMS AGENT HISTORY |
DWR_SMS_CUST |
|
DWR_SMS_CUST_H |
SMS CUSTOMER HISTORY |
DWR_STN | |
DWR_STN_H |
STATION HISTORY |
DWR_SVC | |
DWR_SVC_H |
SERVICE HISTORY |
DWR_TKT_CPN | |
DWR_TKT_CPN_H |
TICKET COUPON HISTORY |
DWR_TSM | |
DWR_TSM_H |
TSM HI STORY |
DWR_TSM_PAX | |
DWR_TSM_PAX_H |
TSM PASSENGER HISTORY |
DWR_VHCL | |
DWR_VHCL_H |
VEHICLE HISTORY |
DWR_VIP_PAX_INFO | |
DWR_VIP_PAX_INFO_H |
VIP PASSENGER INFORMATION HISTORY |
In Oracle Airlines Data Model, the base tables present the transaction data in 3NF. Base tables define atomic level transaction data. Data in the base tables support the derived and aggregate layers, and act as a source for Data Mining for advanced analysis.
Table 3-3 lists the Base tables in Oracle Airlines Data Model.
Table 3-3 Base Tables
Table Name | Description and More Information |
---|---|
DWB_ACCT_LVL_HIST | |
DWB_ACCT_LVL_HIST_H |
ACCOUNT LEVEL HISTORY H |
DWB_ACCT_XFER | |
DWB_ACCT_XFER_H |
ACCOUNT TRANSFER HISTORY |
DWB_AUX | |
DWB_BKG | |
DWB_BKG_H |
BOOKING HISTORY |
DWB_CHKIN | |
DWB_CHKIN_H |
CHECKIN HISTORY |
DWB_CMNSTRY_ERNG | |
DWB_CMNSTRY_ERNG_H |
COMPENSATORY EARNING HISTORY |
DWB_CMPL_ADVC | |
DWB_CMPL_ADVC_H |
COMPLAIN ADVICE HISTORY |
DWB_CR_RNTL | |
DWB_DIRCT_ERNG | |
DWB_DIRCT_ERNG_H |
DIRECT EARNING HISTORY |
DWB_ERNG_EVNT | |
DWB_ERNG_EVNT_H |
EARNING EVENT HISTORY |
DWB_FLT_CHNG | |
DWB_FLT_CHNG_H |
FLIGHT CHANGE HISTORY |
DWB_FLT_SCHD | |
DWB_FLT_SCHD_H |
FLIGHT SCHEDULE HISTORY |
DWB_HTL_BKNG | |
DWB_LYLTY_ACCT_BAL_HIST | |
DWB_LYLTY_ACCT_BAL_HIST_H |
LOYALTY ACCOUNT BALANCE HISTORY H |
DWB_LYLTY_ACCT_LVL_HIST | |
DWB_LYLTY_ACCT_LVL_HIST_H |
LOYALTY ACCOUNT LEVEL HISTORY H |
DWB_LYLTY_CONV | |
DWB_LYLTY_CONV_H |
LOYALTY CONVERSION HISTORY |
DWB_LYLTY_PNTS_EXPR | |
DWB_LYLTY_PNTS_EXPR_H |
LOYALTY POINTS EXPIRE HISTORY |
DWB_LYLTY_PRG | |
DWB_LYLTY_PRG_H |
LOYALTY PROGRAM HISTORY |
DWB_PDI | |
DWB_PDI_H |
PDI HISTORY |
DWB_PNR | |
DWB_PNR_H |
PASSENGER NAME RECORD HISTORY |
DWB_PRTY_INTRATN | |
DWB_PRTY_INTRATN_H |
PARTY INTERACTION HISTORY |
DWB_PRTY_INTRATN_CALL | |
DWB_PRTY_INTRATN_CALL_H |
PARTY INTERACTION CALL HISTORY |
DWB_PRTY_INTRATN_EML | |
DWB_PRTY_INTRATN_EML_H |
PARTY INTERACTION EMAIL HISTORY |
DWB_PRTY_INTRATN_FAX | |
DWB_PRTY_INTRATN_FAX_H |
PARTY INTERACTION FAX HISTORY |
DWB_PRTY_INTRATN_ITEM | |
DWB_PRTY_INTRATN_ITEM_H |
PARTY INTERACTION ITEM HISTORY |
DWB_PRTY_INTRATN_LETR | |
DWB_PRTY_INTRATN_LETR_H |
PARTY INTERACTION LETTER HISTORY |
DWB_PRTY_INTRATN_SMS | |
DWB_PRTY_INTRATN_SMS_H |
PARTY INTERACTION SMS HISTORY |
DWB_PRTY_INTRATN_THRD | |
DWB_PRTY_INTRATN_THRD_H |
PARTY INTERACTION THREAD HISTORY |
DWB_PRTY_INTRATN_VST | |
DWB_PRTY_INTRATN_VST_H |
PARTY INTERACTION VISIT HISTORY |
DWB_PTNR_ERNG | |
DWB_PTNR_ERNG_H |
PARTNER EARNING HISTORY |
DWB_SEG_SCHD | |
DWB_SEG_SCHD_H |
SEGMENT SCHEDULE HISTORY |
DWB_TAS | |
DWB_TKT | |
DWB_TKT_H |
TICKET HISTORY |
DWB_TKT_DLVRY_ARNGMNT | |
DWB_TKT_DLVRY_ARNGMNT_H |
TICKET DELIVERY ARANGMENT HISTORY |
DWB_TOUR | |
DWB_XFER_ERNG | |
DWB_XFER_ERNG_H |
TRANSFER EARNING HISTORY |
In Oracle Airlines Data Model, the Derived tables are tables that have as values the result of a non-aggregate calculation. There are two types of derived tables in the Oracle Airlines Data Model:
Tables that hold the results of a calculation.
Result tables for the data mining models.
Table 3-4 lists the Derived tables in Oracle Airlines Data Model.
Table 3-4 Derived Tables
Table Name | Description and More Information |
---|---|
DWD_BKG_FACT | |
DWD_CALL_CNTR_PRFMNC | |
DWD_CHKIN_FACT | |
DWD_CUST_LTV_SVM_FACTOR | |
DWD_CUST_LYLTY_DT_RULES | |
DWD_CUST_LYLTY_SVM_FACTOR | |
DWD_CUST_MNNG | |
DWD_CUST_RFMP_SCR | |
DWD_CUST_SRVY | |
DWD_FFP_PRED_DT_RULES | |
DWD_FFP_PRED_SVM_FACTOR | |
DWD_FLT_DETLS_FACT | |
DWD_LYLTY_ACCT_BAL_HIST | |
DWD_LYLTY_ACCT_LVL_HIST | |
DWD_LYLTY_PRG | |
DWD_NON_FFP_MNNG | |
DWD_PNR | |
DWD_TKT |
In Oracle Airlines Data Model, the Aggregate tables are tables that aggregate or "roll up" the data to one level higher than a base or derived table. The aggregate tables provide a view of the data similar to the view provided by a fact table in a snowflake schema while the dimensions of that table are DWM_ tables.
Table 3-5 lists the Aggregate tables in Oracle Airlines Data Model.
In Oracle Airlines Data Model, the dimension (DWM_ ) tables typically represent dimensions which contain a business hierarchy and are present in the form of snowflake entities containing a table for each level of the hierarchy. This allows you to attach the appropriate set of reference tables for the multiple subject area and fact entities composed of differing granularity. For example, you can use the time dimension table DWM_CLNDR to query against a DAY level Passenger Name Record ( PNR) data such as DWD_PNR.
Table 3-6 lists the Dimension tables in Oracle Airlines Data Model.