7 Oracle Airlines Data Model OLAP Model Dimensions

This chapter of Oracle Airlines Data Model Reference describes the Data Flow between fact tables and dimension tables of Oracle Airlines Data Model relational.

This chapter includes the following sections:

Oracle Airlines Data Model Introduction to OLAP Dimensions
Oracle Airlines Data Model OLAP Dimensions

For more information, see Chapter 8, "Oracle Airlines Data Model OLAP Model Cubes".

Oracle Airlines Data Model Introduction to OLAP Dimensions

Oracle Airlines Data Model contains low level combination of base tables and summary, average, and so on, of Base and Derived data. Each dimension includes the following information:

Levels
Hierarchies
Attributes and Attribute mappings

Oracle Airlines Data Model OLAP Dimensions

Table 7-1 lists the Dimension tables.

Table 7-1 Oracle Airlines Data Model Dimension Tables

Dimension
Booking Class: BKCLS
Booking Office: BKOFC
Geography: GEO
Interaction Reason: IRSN
Loyalty Level: LOYLV
Operating Flight: OPFLT
Operating Segment: OPSMT
Route: ROUTE
Service: SRVC
Time: TIME

Booking Class: BKCLS

Table 7-2 briefly describes all the information of the Booking Class Dimension.

Table 7-2 Booking Class (BKCLS) Levels and Hierarchies

Level	Description	Booking Class Hierarchy (HBKCLS)
TBKCLS	Total Booking Class	TBKCLS
SVCLS	Service Class	SVCLS
BKCLS	Booking Class	BKCLS

Attribute Name: Long Description (LONG_DESCRIPTION)

Table 7-3 Booking Class Long Description Attribute Mapping

Level	Mapping (Physical Column)
TBKCLS	Total Booking Class
SVCLS	DWM_BKG_CLS_TYP.SVC_CLS_DESC
BKCLS	DWM_BKG_CLS_TYP.BKG_CLS_DESC

Attribute Name: Short Description (SHORT_DESCRIPTION)

Table 7-4 Booking Class Short Description Attribute Mapping

Level	Mapping (Physical Column)
TBKCLS	Total Booking Class
SVCLS	DWM_BKG_CLS_TYP.SVC_CLS_CD
BKCLS	DWM_BKG_CLS_TYP.BKG_CLS_CD

Booking Office: BKOFC

Table 8-3 briefly describes all the information of the Booking Office Dimension.

Table 7-5 Booking Office (HBKOFC) Levels and Hierarchies

Levels	Description	Booking Office Hierarchy (HBKOFC)	Channel Type Hierarchy (HCNTYP)
TBKOFC	Total Booking Office	TBKOFC	TBKOFC
ACNTNT	Agent Continent	ACNTNT
ACUNTRY	Agent Country	ACUNTRY
ARGN	Agent Region	ARGN
ACITY	Agent City	ACITY
BKOFC	Booking Office	BKOFC
CNTYP	Channel Type		CNTYP
AGNT	Agent	AGNT	AGNT

Hierarchy of HBKOFC

Attribute Name: Long Description (LONG_DESCRIPTION)

Table 7-6 Booking Office HBKOFC Long Description Attribute Mapping

Levels	Mapping (Physical Column)
TBKOFC	Total Booking Office
ACNTNT	DWM_BKG_OFF.AGNT_CONT
ACUNTRY	DWM_BKG_OFF.AGNT_CTRY
ARGN	DWM_BKG_OFF.AGNT_REGN
ACITY	DWM_BKG_OFF.AGNT_CITY
BKOFC	DWM_BKG_OFF.OFF_NM
AGNT	DWM_BKG_OFF.AGNT_NM

Attribute Name: Short Description (SHORT_DESCRIPTION)

Table 7-7 Booking Office HBKOFC Short Description Attribute Mapping

Level	Mapping (Physical Column)
TBKOFC	Total Booking Office
ACNTNT	DWM_BKG_OFF.AGNT_CONT_CD
ACUNTRY	DWM_BKG_OFF.AGNT_CTRY_CD
ARGN	DWM_BKG_OFF.AGNT_REGN_CD
ACITY	DWM_BKG_OFF.AGNT_CITY_CD
BKOFC	DWM_BKG_OFF.OFF_NM
AGNT	DWM_BKG_OFF.AGNT_NM

Attribute Name: Agent Status (ASTUS)

Table 7-8 Booking Office HBKOFC Agent Status Attribute Mapping

Level	Mapping (Physical Column)
TBKOFC
ACNTNT
ACUNTRY
ARGN
ACITY
BKOFC
AGNT	DWM_BKG_OFF.AGNT_STATE

Attribute Name: Agent Name (ANM)

Table 7-9 Booking Office HBKOFC Agent Name Attribute Mapping

Level	Mapping (Physical Column)
TBKOFC
ACNTNT
ACUNTRY
ARGN
ACITY
BKOFC
AGNT	DWM_BKG_OFF.AGNT_NM

Attribute Name: Agent IATA Code (AITCD)

Table 7-10 Booking Office HBKOFC Agent IATA Code Attribute Mapping

Level	Mapping (Physical Column)
TBKOFC
ACNTNT
ACUNTRY
ARGN
ACITY
BKOFC
AGNT	DWM_BKG_OFF.IATA_CD

Hierarchy of HCNTYP

Attribute Name: Long Description (LONG_DESCRIPTION)

Table 7-11 Booking Office HCNTYP Long Description Mapping

Level	Mapping (Physical Column)
TBKOFC	Total Booking Office
CNTYP	DWM_BKG_OFF.CHNL_TYP
AGNT	DWM_BKG_OFF.AGNT_NM

Attribute Name: Short Description (SHORT_DESCRIPTION)

Table 7-12 Booking Office HCNTYP Short Description Mapping

Level	Mapping (Physical Column)
TBKOFC	Total Booking Office
CNTYP	DWM_BKG_OFF.CHNL_TYP
AGNT	DWM_BKG_OFF.AGNT_NM

Attribute Name: Agent IATA Code (AITCD))

Table 7-13 Booking Office HCNTYP IATA Code Attribute Mapping

Level	Mapping (Physical Column)
TBKOFC
CNTYP
AGNT	DWM_BKG_OFF.IATA_CD

Attribute Name: Agent Name (ANM)

Table 7-14 Booking Office HCNTYP Agent Name Attribute Mapping

Level	Mapping (Physical Column)
TBKOFC
CNTYP
AGNT	DWM_BKG_OFF.AGNT_NM

Attribute Name: Agent Status (ASTUS)

Table 7-15 Booking Office HCNTYP Agent Status Attribute Mapping

Level	Mapping (Physical Column)
TBKOFC
CNTYP
AGNT	DWM_BKG_OFF.AGNT_STATE

Geography: GEO

Table 8-4 briefly describes all the information of the Geography Dimension.

Table 7-16 Geography (HGEO) Levels and Hierarchies

Level	Description	Geography Hierarchy (HGEO)
TGEO	Total Geography	TGEO
CONT	Continent	CONT
COUNTRY	Country	COUNTRY
CITY	City	CITY

Attribute Name: Long Description (LONG_DESCRIPTION)

Table 7-17 Geography Long Description Attribute Mapping

Level	Mapping (Physical Column)
TGEO	Total Geography
CONT	DWM_GEOGRY.CONT_NM
COUNTRY	DWM_GEOGRY.CTRY_NM
CITY	DWM_GEOGRY.CITY_NM

Attribute Name: Short Description (SHORT_DESCRIPTION)

Table 7-18 Geography Short Description Attribute Mapping

Level	Mapping (Physical Column)
TGEO	Total Geography
CONT	DWM_GEOGRY.CONT_SURNM
COUNTRY	DWM_GEOGRY.CTRY_SURNM
CITY	DWM_GEOGRY.CITY_CD

Interaction Reason: IRSN

Table 8-5 briefly describes all the information of the Interaction Reason Dimension.

Table 7-19 Interaction Reason (HIRSN) Levels and Hierarchies

Level	Description	Interaction Reason Hierarchy (HIRSN)
TIRSN	Total Interaction Reason	TIRSN
IRSN	Interaction Reason	IRSN

Attribute Name: Long Description (LONG_DESCRIPTION)

Table 7-20 Interaction Reason Long Description Attribute Mapping

Level	Mapping (Physical Column)
TIRSN	Total Interaction Reason
IRSN	DWL_INTERACTION_RSN.INTERACTION_RSN_NM

Attribute Name: Short Description (SHORT_DESCRIPTION)

Table 7-21 Interaction Reason Short Description Attribute Mapping

Level	Mapping (Physical Column)
TIRSN	Total Interaction Reason
IRSN	DWL_INTERACTION_RSN.INTERACTION_RSN_DESC

Loyalty Level: LOYLV

Table 8-6 briefly describes all the information of the Loyalty Level Dimension.

Table 7-22 Loyalty Level (HLOYLY) Levels and Hierarchies

Level	Description	Loyalty Level Hierarchy (HLOYLY)
TLOYLY	Total Loyalty Level	TLOYLY
LOYLY	Loyalty Level	LOYLY

Attribute Name: Long Description (LONG_DESCRIPTION)

Table 7-23 Loyalty Level Long Description Attribute Mapping

Level	Mapping (Physical Column)
TLOYLY	Total Loyalty Level
LOYLY	DWR_LOYALTY_LVL.LOYALTY_LVL_NM

Attribute Name: Short Description (SHORT_DESCRIPTION)

Table 7-24 Loyalty Level Short Description Attribute Mapping

Level	Mapping (Physical Column)
TLOYLY	Total Loyalty Level
LOYLY	DWR_LOYALTY_LVL.LOYALTY_LVL_NM

Attribute Name: Level Qualifying Start Points (LVL_STPN)

Table 7-25 Loyalty Level Qualifying Start Points Attribute Mapping

Level	Mapping (Physical Column)
TLOYLY
LOYLY	DWR_LOYALTY_LVL.LVL_QUALIFYING_STRT_POINTS

Operating Flight: OPFLT

Table 8-7 briefly describes all the information of the Operating Flight Dimension.

Table 7-26 Operating Flight (OPFLT) Levels and Hierarchies

Level	Description	Operating Flight Hierarchy (HOPFLT)
TOPFLT	Total Operating Flight	TOPFLT
OPFLT	Operating Flight	OPFLT

Attribute Name: Long Description (LONG_DESCRIPTION)

Table 7-27 Operating Flight Long Description Attribute Mapping

Level	Mapping (Physical Column)
TOPFLT	Total Operating Flight
OPFLT	DWM_FLT.FLT_TXT_DESC

Attribute Name: Short Description(SHORT_DESCRIPTION)

Table 7-28 Operating Flight Short Description Attribute Mapping

Level	Mapping (Physical Column)
TOPFLT	Total Operating Flight
OPFLT	DWM_FLT.FLT_NBR

Operating Segment: OPSMT

Table 8-8 briefly describes all the information of the Operating Segment Dimension.

Table 7-29 Operating Segment (OPSMT) Levels and Hierarchies

Level	Description	Operating Segment Hierarchy (HOPSMT)
TOPSMT	Total Operating Segment	TOPSMT
TFRGN	Traffic Region	TFRGN
OPSMT	Operating Segment	OPSMT

Attribute Name: Long Description (LONG_DESCRIPTION)

Table 7-30 Operating Segment Long Description Attribute Mapping

Level	Mapping (Physical Column)
TOPSMT	Total Operating Segment
TFRGN	DWM_SEG.OFPNT_CONT
OPSMT	DWM_SEG.OFPNT_AIP_NM

Attribute Name: Short Description (SHORT_DESCRIPTION)

Table 7-31 Operating Segment Short Description Attribute Mapping

Level	Mapping (Physical Column)
TOPSMT	Total Operating Segment
TFRGN	DWM_SEG.OFPNT_CONT
OPSMT	DWM_SEG.BRD_AIP_NM

Attribute Name: Segment Pair (SEG_PAIR)

Table 7-32 Operating Segment Segment Pair Attribute Mapping

Level	Mapping (Physical Column)
TOPSMT
TFRGN
OPSMT	DWM_SEG.OFPNT_AIP_NM \|\| DWM_SEG.BRD_AIP_NM

Route: ROUTE

Table 8-9 briefly describes all the information of the Route Dimension.

Table 7-33 Route (ROUTE) Levels and Hierarchies

Level	Description	Route Hierarchy (HROUTE)
TROUTE	Total Route	TROUTE
ROUTE	Route	ROUTE

Attribute Name: Long Description (LONG_DESCRIPTION)

Table 7-34 Route Long Description Attribute Mapping

Level	Mapping (Physical Column)
TROUTE	Total Route
ROUTE	ROUTE LD

Attribute Name: Short Description (SHORT_DESCRIPTION)

Table 7-35 Route Short Description Attribute Mapping

Level	Mapping (Physical Column)
TROUTE	Total Route
ROUTE	ROUTE SD

Service: SRVC

Table 8-10 briefly describes all the information of the Service Dimension.

Table 7-36 Service (SRVC) Levels and Hierarchies

Level	Description	Service Hierarchy (HSRVC)
SVTYP	Service Type	SVTYP
SRVC	Service	SRVC

Attribute Name: Long Description (LONG_DESCRIPTION)

Table 7-37 Service Long Description Attribute Mapping

Level	Mapping (Physical Column)
SVTYP	DWR_SERVICE.SERVICE_DESC
SRVC	DWR_SERVICE.SERVICE_TYP_DESC

Attribute Name: Short Description (SHORT_DESCRIPTION)

Table 7-38 Service Short Description Attribute Mapping

Level	Mapping (Physical Column)
SVTYP	DWR_SERVICE.SERVICE_NM
SRVC	DWR_SERVICE.SERVICE_TYP_NM

Time: TIME

Table 8-11 briefly describes all the information of the Time Dimension.

Table 7-39 Time (TIME) Levels and Hierarchies

Level	Description	Time Hierarchy (HTIME)
TIME	Total Time	TIME
YEAR	Year	YEAR
HLFY	Half Year	HLFY
QTR	Quarter	QTR
MONTH	Month	MONTH
WEEK	Week	WEEK
DAY	Day	DAY

Attribute Name: Long Description (LONG_DESCRIPTION)

Table 7-40 Time Long Description Attribute Mapping

Level	Mapping (Physical Column)
TIME	Total Time
YEAR	DWM_CLNDR.CLNDR_YR_DESC
HLFY	DWM_CLNDR.CLNDR_HALF_YR_DESC
QTR	DWM_CLNDR.CLNDR_QTR
MONTH	DWM_CLNDR.CLNDR_MONTH_NM
WEEK	DWM_CLNDR.CLNDR_WEEK_DESC
DAY	DWM_CLNDR.CLNDR_DT_DESC

Attribute Name: Short Description (SHORT_DESCRIPTION)

Table 7-41 Time Short Description Attribute Mapping

Level	Mapping (Physical Column)
TIME	Total Time
YEAR	DWM_CLNDR.CLNDR_YR_CD
HLFY	DWM_CLNDR.CLNDR_HALF_YR_CD
QTR	DWM_CLNDR.CLNDR_QTR_CD
MONTH	DWM_CLNDR.CLNDR_MONTH_CD
WEEK	DWM_CLNDR.CLNDR_WEEK_CD
DAY	DWM_CLNDR.CLNDR_DT_DESC

Attribute Name: End Date (END_DATE)

Table 7-42 Time End Date Attribute Mapping

Level	Mapping (Physical Column)
TIME	DWM_CLNDR.CLNDR_YR_END_DT
YEAR	DWM_CLNDR.CLNDR_YR_END_DT
HLFY	DWM_CLNDR.CLNDR_HALF_YR_END_DT
QTR	DWM_CLNDR.CLNDR_QTR_END_DT
MONTH	DWM_CLNDR.CLNDR_MONTH_END_DT
WEEK	DWM_CLNDR.CLNDR_WEEK_END_DT
DAY	DWM_CLNDR.CLNDR_DT

Attribute Name: Time Span (TIME_SPAN)

Table 7-43 Time Span Attribute Mapping

Level	Mapping (Physical Column)
TIME	DWM_CLNDR.CLNDR_YR_TIMESPAN
YEAR	DWM_CLNDR.CLNDR_YR_TIMESPAN
HLFY	DWM_CLNDR.CLNDR_HALF_YR_TIMESPAN
QTR	DWM_CLNDR.CLNDR_QTR_TIMESPAN
MONTH	DWM_CLNDR.CLNDR_MONTH_TIMESPAN
WEEK	DWM_CLNDR.CLNDR_WEEK_TIMESPAN
DAY	1

Attribute Name: Calendar Week Number In Year (CWIY)

Table 7-44 Time Calendar Week Number in Year Attribute Mapping

Level	Mapping (Physical Column)
TIME
YEAR
HLFY
QTR
MONTH
WEEK	DWM_CLNDR.CLNDR_WEEK_NBR_IN_YR
DAY	DWM_CLNDR.CLNDR_WEEK_NBR_IN_YR

Attribute Name: Day of Week Number (DOWN)

Table 7-45 Time Day of Week Number Attribute Mapping

Level	Mapping (Physical Column)
TIME
YEAR
HLFY
QTR
MONTH
WEEK
DAY	DWM_CLNDR.DAY_OF_WEEK_NBR

Attribute Name: Day of Week Name (DOWNM)

Table 7-46 Time Day of Week Name Attribute Mapping

Level	Mapping (Physical Column)
TIME
YEAR
HLFY
QTR
MONTH
WEEK
DAY	DWM_CLNDR.DAY_OF_WEEK_NM