6 Oracle Airlines Data Model Intra-ETL

This chapter includes the following sections:

Introduction to Oracle Airlines Data Model Intra-ETL

Note:

Do not make changes to the ETL as such changes are not supported.

In Oracle Airlines Data Model, reference tables store master and reference data; and the base, derived, and aggregate tables store transaction and fact data at different granularities. The base tables store the transaction data at the lowest level of granularity, while the derived and aggregate tables store consolidated and summary transaction data.

Two types of Extract, Transform, and Load (ETL) operations populate the tables with data. The source-ETL operations populate the reference and base tables with data from the source On-Line Transaction Processing (OTLP) applications. Additional Intra-ETL operations populate the derived and aggregate tables with the data in the base and reference tables. While the source ETL operations are not a part of Oracle Airlines Data Model, the Intra-ETL operations are:

  • Derived Population: A database package containing scripts that populate the derived tables based on the content of the base and reference tables.

  • Aggregate Population: A database package containing scripts to refresh the Oracle Airlines Data Model aggregate tables based on the content of the derived tables and some reference tables.

Derived and Aggregate tables are implemented using Oracle tables.

For more information, see "Intra-ETL Process Flows" and the Oracle Airlines Data Model Implementation and Operations Guide.

Value Lookup Models for PL/SQL Procedures

Oracle Airlines Data Model value lookup values contains the lookup tables and the associated values which are used in Intra-ETL mapping.

Table 6-1 shows the tables and values which are used in join conditions and filter conditions in Intra-ETL mapping.

Table 6-1 shows the lookup tables and values which are used in Intra-ETL mapping.

Table 6-1 Value Lookup Values for Intra-ETL Mapping

Hard Coded Value Table Name Hard Coded Value Column Value used ETL Program Name ETL Usage Type

DWR_SL_CHNL

SL_CHNL_CD

Airline Agent, Other Agent

PKG_DWD_CALL_CNTR_PRFMNC

Source Input

DWR_INTRACN_RSLT

INTRACN_RSLT_NAME

Satisfy, Dissatisfy

PKG_DWA_CUST_SRVEY

Source Input

DWR_INTRACN_RSN

INTRACN_RSN_NAME

Survey,Service Call,Inbound Marketing,Outbound Marketing,Customer Complaint

PKG_DWA_CUST_SRVEY

Source Input

Intra-ETL PL/SQL Mapping Source and Target Tables

Table 6-2 shows the PL/SQL packages for mapping source tables to target tables to populate Aggregate tables.

Table 6-3 shows the PL/SQL packages for mapping source tables to target tables to populate Derived tables.

PKG_DWA_CUST_SRVY Mapping

Table 6-4 shows the source to target mapping to populate target table DWA_CUST_SRVY. For more information, see CUSTOMER SURVEY AGG.

Source Tables

DWD_CUST_SRVY

DWR_INTRATN_RSN

DWR_INTRATN_RSLT

Table 6-4 PKG_DWA_CUST_SRVY ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description

CLNDR_KEY

DWD_CUST_SRVY

CLNDR_KEY

Direct Mapping.The foreign key to DWR_CLNDR

INTRACN_RSN_KEY

DWD_CUST_SRVY

INTRACN_RSN_KEY

Direct Mapping.The foreign key to DWR_INTRACN_RSN

MO_KEY

DWD_CUST_SRVY

MO_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

SATISFY_CN

DWR_INTRACN_RSLT rslt,DWR_INTRACN_RSN rsn

rslt.INTRACN_RSLT_NAME,rsn.INTRACN_RSN_NAME

  

SRVC_KEY

DWD_CUST_SRVY

SRVC_KEY

Direct Mapping.The foreign key to DWR_SRVC

TOT_SRVY_CNT

DWR_INTRACN_RSLT rslt

INTRACN_RSLT_NAME

DWD_CUST_SRVY left join DWR_INTRACN_RSLT rslt on (ccp.INTRACN_RSLT_KEY=rslt.INTRACN_RSLT_KEY and rslt.CURR_IND='Y')

PKG_DWA_DLY_BKG Mapping

Table 6-5 shows the mapping to populate target table DWA_DLY_BKG. For more information, see DAILY BOOKING FACT.

Source Tables

DWD_BKG_FCT

DWR_CMPGN

DWR_RVN_CST_DRVD

Table 6-5 PKG_DWA_DLY_BKG_ ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

BELLY_CARGO_RVN

DWD_RVN_CST_DRVD

CARGO_FRGHT_RVN

DWD_BKG_FCT left join DWD_RVN_CST_DRVD rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY)

SUM(Nvl(rvn.CARGO_FRGHT_RVN,0))

BKD

DWD_BKG_FCT

BKG_CD

  

Count(bkg.BKG_CD)

BKG_CLS_KEY

DWD_BKG_FCT

BKG_CLS_KEY

Direct Mapping.The foreign key to DWR_BKG_CLS

  

CARRIER_KEY

DWD_BKG_FCT

CARRIER_KEY

Direct Mapping.The foreign key to DWR_CARRIER

  

CDSHR_RVN

DWD_BKG_FCT

CPN_AMT

  

Sum(Case When bkg.CDSHR_IND='Y' Then nvl(bkg.CPN_AMT,0) Else 0 end )

CHARTER_RVN

DWD_RVN_CST_DRVD

CHARTER_RVN

DWD_BKG_FCT left join DWD_RVN_CST_DRVD rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY)

SUM(Nvl(rvn.OTHR_RVN_CHARTER,0)) CHARTER_RVN

CITY_KEY

DWD_BKG_FCT

CITY_KEY

Direct Mapping.The foreign key to DWR_GEO

  

CLNDR_KEY

DWD_BKG_FCT

CLNDR_KEY

Direct Mapping.The foreign key to DWR_CLNDR

  

CMPGN_KEY

DWR_CMPGN

CMPGN_KEY

Direct Mapping.The foreign key to DWR_CMPGN

  

CNCL_CNT

DWD_BKG_FCT

BKG_CD

  

Count(Case When bkg.CNCL_DTTIME is not null Then bkg.BKG_CD end) CNCL_CNT

CNCL_FROM_CNFRMD

DWD_BKG_FCT

BKG_CD

  

Count(Case When bkg.CNFRM_DTTIME<bkg.CNCL_DTTIME then bkg.BKG_CD end ) CNCL_FROM_CNFRMD

CNFRMD_CNT

DWD_BKG_FCT

BKG_CD

  

Count(Case When bkg.CNFRM_DTTIME is not null Then bkg.BKG_CD end ) CNFRMD_CNT

CPN_CNT

DWD_BKG_FCT

CPN_CD

  

Count(bkg.CPN_CD) CPN_CNT

CRPRT_CUST_KEY

DWD_BKG_FCT

CRPRT_CUST_KEY

Direct Mapping.The foreign key to DWR_CRPRT_CUST

  

EXCSS_BAG_RVN

DWD_RVN_CST_DRVD

EXCSS_BAG_RVN

DWD_BKG_FCT left join DWD_RVN_CST_DRVD rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY)

Sum(Nvl(rvn.EXCSS_BAG_RVN,0)) EXCSS_BAG_RVN

FLN_PAX_CNT

DWD_BKG_FCT

FLN_PAX_CNT

Direct Mapping.This indicates the FLOWN PASSENGER COUNT

Sum(Nvl(bkg.FLN_PAX_CNT,0))

FLN_RVN

DWD_BKG_FCT

FLN_RVN

Direct Mapping.This indicates the FLOWN REVENUE

Sum(Nvl(bkg.FLN_RVN,0))

FLN_RVN_ORGN_TO_DSTN_OFRD

DWD_BKG_FCT

FLN_RVN_ORGN_TO_DSTN_OFRD

Direct Mapping.This indicates the FLOWN REVENUE ORIGIN TO DESTINATION OFFERED

Sum(Nvl(bkg.FLN_RVN_ORGN_TO_DSTN_OFRD,0))

FLT_KEY

DWD_BKG_FCT

FLT_KEY

Direct Mapping.The foreign key to DWR_FLT

  

GRP_BKD_QTY

DWD_BKG_FCT

BKG_CD

  

Count(Case When bkg.GRPNG_IND is not null Then bkg.BKG_CD end ) GRP_BKD_QTY

GRP_PAX_CNT

DWD_BKG_FCT

PAX_KEY

  

Count(Case When bkg.GRPNG_IND is not null Then bkg.PAX_KEY end ) GRP_PAX_CNT

INDVL_BKD_QTY

DWD_BKG_FCT

BKG_CD

  

Count(Case When bkg.GRPNG_IND is null Then bkg.BKG_CD end ) INDVL_BKD_QTY

INDVL_PAX_CNT

DWD_BKG_FCT

PAX_KEY

  

Count(Case When bkg.GRPNG_IND is null Then bkg.PAX_KEY end ) INDVL_PAX_CNT

MO_KEY

DWD_BKG_FCT

MO_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

NET_BKD

DWD_BKG_FCT

BKG_CD

  

Count(Case When not bkg.CNFRM_DTTIME<bkg.CNCL_DTTIME then bkg.BKG_CD end ) NET_BKD

NET_CNFRMD

DWD_BKG_FCT

KG_CD

  

Count(Case When bkg.CNFRM_DTTIME is not null and not bkg.CNFRM_DTTIME<bkg.CNCL_DTTIME Then bkg.BKG_CD end ) NET_CNFRMD

NON_RVN_FLN_PAX_CNT

DWD_BKG_FCT

NON_RVN_FLN_PAX_CNT

Direct Mapping.This indicates the NON REVENUE FLOWN PASSENGER COUNT

Sum(Nvl(bkg.NON_RVN_FLN_PAX_CNT,0))

OFF_KEY

DWD_BKG_FCT

OFFC_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

OFRD_ORGN_TO_DSTN_FLN_PAX_CNT

DWD_BKG_FCT

OFRD_ORGN_TO_DSTN_FLN_PAX_CNT

Direct Mapping.This indicates the OFFERED ORIGIN TO DESTINATION FLOWN PASSENGER COUNT

Sum(Nvl(bkg.OFRD_ORGN_TO_DSTN_FLN_PAX_CNT,0))

ONBOARD_RVN

DWD_BKG_FCT

ONBOARD_RVN

Direct Mapping.This indicates the ONBOARD REVENUE

Sum(Nvl(bkg.ONBOARD_RVN,0))

ONFLT_ORGN_TO_DSTN_FLT_RVN

DWD_BKG_FCT

ONFLT_ORGN_TO_DSTN_FLT_RVN

Direct Mapping.This indicates the ONFLIGHT ORIGIN TO DESTINATION FLIGHT REVENUE

Sum(Nvl(bkg.ONFLT_ORGN_TO_DSTN_FLT_RVN,0))

ONFLT_ORGNTO_DSTN_FLN_PAX_CNT

DWD_BKG_FCT

ONFLT_ORGNTO_DSTN_FLN_PAX_CNT

Direct Mapping.

Sum(Nvl(bkg.ONFLT_ORGNTO_DSTN_FLN_PAX_CNT,0))

OTHR_CHRGS

DWD_RVN_CST_DRVD

OTHR_OVRFLYNG_CHRGS

DWD_BKG_FCT left join DWD_RVN_CST_DRVD rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY)

Sum(Nvl(rvn.OTHR_OVRFLYNG_CHRGS,0)) OTHR_CHRGS

OTHR_RVN

DWD_RVN_CST_DRVD

OTHR_RVN_CHARTER

DWD_BKG_FCT left join DWD_RVN_CST_DRVD rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY)

Sum(Nvl(rvn.OTHR_RVN_CHARTER,0)) OTHR_RVN

PAX_CNT

DWD_BKG_FCT

PAX_KEY

  

Count(bkg.PAX_KEY) PAX_CNT

SEG_KEY

DWD_BKG_FCT

SEG_KEY

Direct Mapping.The foreign key to DWR_SEG

  

SEG_PAIR_KEY

DWD_BKG_FCT

SEG_PAIR_KEY

Direct Mapping.The foreign key to DWR_SEG_PAIR

  

SL_CHNL_KEY

DWD_BKG_FCT

SL_CHNL_KEY

Direct Mapping.The foreign key to DWR_SL_CHNL

  

TAX_AMT

DWD_RVN_CST_DRVD

PAX_TAX

DWD_BKG_FCT left join DWD_RVN_CST_DRVD rvn on (rvn.DAY_KEY=bkg.CLNDR_KEY and rvn.FLT_KEY=bkg.FLT_KEY)

Sum(Nvl(rvn.PAX_TAX,0)) TAX_AMT

TCKT_AMT

DWD_BKG_FCT

CPN_AMT

  

Sum(Case When bkg.TCKT_CD is not null Then Nvl(CPN_AMT,0) Else 0 end) TCKT_AMT

TCKTD

DWD_BKG_FCT

BKG_CD

  

Count(Case When bkg.TCKT_CD is not null Then bkg.BKG_CD end ) TCKTD

TRFC_CTGRY_KEY

DWD_BKG_FCT

TRFC_CTGRY_KEY

Direct Mapping.The foreign key to DWR_TRFC_CTGRY

  

WTLSTD

DWD_BKG_FCT

BKG_CD

    

PKG_DWA_DLY_CALL_CNTR_PRFMNC Mapping

Table 6-6 shows the mapping to populate target table DWA_DLY_CALL_CNTR__PRFMNC. For more information, see DAILY CALL CENTER PERFORMANCE.

Source Tables

DWD_CALL_CNTR_PRFMNC

Table 6-6 PKG_DWA_DLY_CC_PRFM ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

ACCESSIBLE_CNT

DWD_CALL_CNTR_PRFMNC

TALK_DRTN,HNDL_BY_IVR_IND

  

SUM(CASE WHEN TALK_DRTN = 0 AND HNDL_BY_IVR_IND= 'Y' THEN 1 ELSE 0 END ) AS ACCESSIBLE_CNT ,COUNT(CALL_CNTR_AGNT_KEY) AS AGNT_CNT

AGNT_CNT

DWD_CALL_CNTR_PRFMNC

CALL_CNTR_AGNT_KEY

This indicates the CALL CENTER AGENT KEY

COUNT(CALL_CNTR_AGNT_KEY) AS AGNT_CNT

CALL_CNT

DWD_CALL_CNTR_PRFMNC

PRTY_INTRACN_CALL_CD

  

COUNT(PRTY_INTRACN_CALL_CD ) AS CALL_CNT

CLNDR_KEY

DWD_CALL_CNTR_PRFMNC

CLNDR_KEY

Direct Mapping.The foreign key to DWR_CLNDR

  

MNT_OF_CALL_DRTN

DWD_CALL_CNTR_PRFMNC

CUST_SATISFACTN_IND,DLY_CALL_CNTR_PRFMNC_KEY

  

COUNT(CASE WHEN CUST_SATISFACTN_IND= 'Y' THEN DLY_CALL_CNTR_PRFMNC_KEY END ) AS STFY_CNT

MO_KEY

DWD_CALL_CNTR_PRFMNC

MO_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

OFFC_KEY

DWD_CALL_CNTR_PRFMNC

OFFC_KEY

Direct Mapping.The foreign key to DWR_BKG_OFFC

  

SATISFY_CALL

DWD_CALL_CNTR_PRFMNC

CUST_SATISFACTN_IND

This indicates the SOURCE SYSTEM IDENTIFIER

SUM(CASE WHEN CUST_SATISFACTN_IND='Y' THEN 1 ELSE 0 END)

PKG_DWA_DLY_FLT_DTLS Mapping

Table 6-7 shows the mapping to populate target table DWA_DLY_FLT_DTLS.

Source Tables

DWD_FLT_DETLS

Table 6-7 PKG_DWA_DLY_FLT_DTLS ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

ACV_TOT_CPCTY

DWD_FLT_DTLS

ACV_TOT_CPCTY

Direct Mapping.This indicates the AIRCRAFTVERSION TOTAL CAPACITY

Sum(nvl(ACV_TOT_CPCTY,0))

FLT_DT_KEY

DWD_FLT_DTLS

CLNDR_KEY

Direct Mapping.The foreign key to DWR_CLNDR

  

FLT_KEY

DWD_FLT_DTLS

FLT_KEY

Direct Mapping.The foreign key to DWR_FLT

  

NAUTICAL_MLS

DWD_FLT_DTLS

NAUTICAL_MILES

Direct Mapping.This indicates the NAUTICAL MILES

Sum(nvl(NAUTICAL_MILES,0))

SALEBLE_TOT_CPCTY

DWD_FLT_DTLS

SALEBLE_TOT_CPCTY

Direct Mapping.This indicates the SALEBLE TOTAL CAPACITY

Sum(nvl(SALEBLE_TOT_CPCTY,0))

SEG_KEY

DWD_FLT_DTLS

SEG_KEY

Direct Mapping.The foreign key to DWR_SEG

  

PKG_DWA_DLY_LYALTY_ACCT_BKG Mapping

Table 6-8 shows the mapping to populate target DWA_DLY_LYALTY_ACCT_BKG. For more information, see DAILY LOYALTY ACCOUNT BOOKING.

Source Tables

DWD_BKG_FACT

DWR_FREQ_FLYR

Table 6-8 PKG_DWA_DLY_LYALTY_ACCT_BKG ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

ACTVTY_CNT

DWR_FREQ_FLYR

ACTV_ACCT_IND

Direct Mapping.

Sum ( Case When ff.ACTV_ACCT_IND='Y' Then 1 Else 0 end ) ACTVTY_CNT

BKD_CNT

DWD_BKG_FCT

    

count(1) --bkg.BKD_CNT

CLNDR_KEY

DWD_BKG_FCT

CLNDR_KEY

Direct Mapping.The foreign key to DWR_CLNDR

  

CNCL_CNT

DWD_BKG_FCT

CNCL_DTTIME

  

Sum ( Case When bkg.CNCL_DTTIME is not null Then 1 Else 0 end ) CNCL_CNT

CNFRMD_CNT

DWD_BKG_FCT

CNFRM_DTTIME

  

Sum ( Case When bkg.CNFRM_DTTIME is not null Then 1 Else 0 end ) CNFRMD_CNT

FLN_PAX_CNT

DWD_BKG_FCT

FLN_PAX_CNT

Direct Mapping.This indicates the FLOWN PASSENGER COUNT

Sum (Nvl(bkg.FLN_PAX_CNT,0)) FLN_PAX_CNT

FLN_RVN

DWD_BKG_FCT

FLN_RVN

Direct Mapping.This indicates the FLOWN REVENUE

Sum (Nvl(bkg.FLN_RVN,0)) FLN_RVN

FLN_RVN_BY_ACTVTY

DWD_BKG_FCT,DWR_FREQ_FLYR

FLN_RVN

Direct Mapping.

Sum ( Case When ff.ACTV_ACCT_IND='Y' Then Nvl(bkg.FLN_RVN,0) Else 0 end ) FLN_RVN_BY_ACTVTY

FLT_CNT

DWD_BKG_FCT

FLT_KEY

  

Count(Distinct bkg.FLT_KEY) FLT_CNT

LYLTY_ACCT_CNT

DWD_BKG_FCT

FRQTFLR_CARD_KEY

  

Count(Distinct bkg.FRQTFLR_CARD_KEY) LYLTY_ACCT_CNT

LYLTY_LVL_KEY

DWR_FREQ_FLYR

LYLTY_LVL_KEY

Direct Mapping.The foreign key to DWR_LYLTY_LVL

  

LYLTY_PROG_KEY

DWR_FREQ_FLYR

LYLTY_PROG_KEY

Direct Mapping.The foreign key to DWR_LYLTY_PROG

  

MO_KEY

DWD_BKG_FCT

MO_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

OFFC_KEY

DWD_BKG_FCT

OFFC_KEY

It indicates the foreign key which is the primary key of the other table

  

PAX_CNT

DWD_BKG_FCT

PAX_KEY

  

Count(bkg.PAX_KEY) PAX_CNT

TCKT_AMT

DWD_BKG_FCT

CPN_AMT

Direct Mapping.

Sum(Nvl(bkg.CPN_AMT,0)) TCKT_AMT

TCKT_AMT_LCL

DWD_BKG_FCT

CPN_AMT_LCL

Direct Mapping.

Sum(Nvl(bkg.CPN_AMT_LCL,0)) TCKT_AMT_LCL

TCKT_AMT_RPT

DWD_BKG_FCT

CPN_AMT_RPT

Direct Mapping.

Sum(Nvl(bkg.CPN_AMT_RPT,0)) TCKT_AMT_RPT

PKG_DWA_DLY_LYALTY_ACCT Mapping

Table 6-9 shows the mapping to populate target table DWA_DLY_LYALTY_ACCT. For more information, see DAILY LOYALTY ACCOUNT.

Source Tables

DWR_FREQ_FLYR

DWD_LYLTY_ACCT_LVL_HIST

DWD_LYLTY_ACCT_BAL_HIST

Table 6-9 PKG_DWA_DLY_LYALTY_ACCT ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

ACTVTY_CNT

DWD_LYLTY_ACCT_LVL_HIST,DWR_FREQ_FLYR

FRQTFLR_CARD_KEY

Direct Mapping.

Count(Distinct CASE WHEN ff.ACTV_ACCT_IND='Y' Then lalh.FRQTFLR_CARD_KEY end ) ACTVTY_CNT

CITY_KEY

DWR_FREQ_FLYR

CITY_KEY

Direct Mapping.The foreign key to DWR_GEO

  

CLNDR_KEY

DWD_LYLTY_ACCT_LVL_HIST

CLNDR_KEY

Direct Mapping.The foreign key to DWR_CLNDR

  

DEGRADE_CNT

DWD_LYLTY_ACCT_LVL_HIST,DWR_FREQ_FLYR

lalh.FRQTFLR_CARD_KEY

DWD_LYLTY_ACCT_BAL_HIST left join DWR_FREQ_FLYR ff on (lalh.FRQTFLR_CARD_KEY=ff.LYLTY_ACCT_KEY and ff.CURR_IND='Y')

Count(Distinct CASE WHEN last_lvl.LAST_LVL_KEY > ff.LYLTY_LVL_KEY Then lalh.FRQTFLR_CARD_KEY end ) DEGRADE_CNT

LYLTY_ACCT_CNT

DWD_LYLTY_ACCT_LVL_HIST,DWR_FREQ_FLYR

FRQTFLR_CARD_KEY

DWD_LYLTY_ACCT_BAL_HIST left join DWR_FREQ_FLYR ff on (lalh.FRQTFLR_CARD_KEY=ff.LYLTY_ACCT_KEY and ff.CURR_IND='Y')

Count(Distinct CASE WHEN ff.ACCT_CLS_DT is null Then lalh.FRQTFLR_CARD_KEY end ) --LYLTY_ACCT_CNT

LYLTY_LVL_KEY

DWD_LYLTY_ACCT_LVL_HIST

LYLTY_LVL_KEY

Direct Mapping.The foreign key to DWR_LYLTY_LVL

  

LYLTY_PTROGRAM_KEY

DWD_LYLTY_ACCT_BAL_HIST

LYLTY_PROG_KEY

The foreign key to DWR_LYLTY_PROG

  

MILES_ERND_AMT

DWD_LYLTY_ACCT_BAL_HIST,

labh.CURR_MILES_AMT,last_amt.LAST_MILES_AMT

DWD_LYLTY_ACCT_LVL_HIST left join DWD_LYLTY_ACCT_BAL_HIST labh on (lalh.FRQTFLR_CARD_KEY=labh.FRQTFLR_CARD_KEY and lalh.MO_KEY=labh.MO_KEY and lalh.CLNDR_KEY=labh.CLNDR_KEY)

Sum(Case when labh.CURR_MILES_AMT>last_amt.LAST_MILES_AMT then labh.CURR_MILES_AMT-last_amt.LAST_MILES_AMT Else 0 end ) MILES_ERND_AMT

MILES_ERND_AMT_LCL

DWD_LYLTY_ACCT_BAL_HIST

labh.CURR_MILES_AMT_LCL,last_amt.LAST_MILES_AMT_LCL

Direct Mapping.

Sum(Case when labh.CURR_MILES_AMT_LCL>last_amt.LAST_MILES_AMT_LCL then labh.CURR_MILES_AMT_LCL-last_amt.LAST_MILES_AMT_LCL Else 0 end ) MILES_ERND_AMT_LCL

MILES_ERND_AMT_RPT

DWD_LYLTY_ACCT_BAL_HIST

labh.CURR_MILES_AMT_RPT,last_amt.LAST_MILES_AMT_RPT

Direct Mapping.

Sum(Case when labh.CURR_MILES_AMT_RPT>last_amt.LAST_MILES_AMT_RPT then labh.CURR_MILES_AMT_RPT-last_amt.LAST_MILES_AMT_RPT Else 0 end ) MILES_ERND_AMT_RPT

MILES_REDMD_AMT

DWD_LYLTY_ACCT_BAL_HIST

last_amt.LAST_MILES_AMT,labh.CURR_MILES_AMT

DWD_LYLTY_ACCT_LVL_HIST left join DWD_LYLTY_ACCT_BAL_HIST labh on (lalh.FRQTFLR_CARD_KEY=labh.FRQTFLR_CARD_KEY and lalh.MO_KEY=labh.MO_KEY and lalh.CLNDR_KEY=labh.CLNDR_KEY)

Sum(Case when labh.CURR_MILES_AMT<last_amt.LAST_MILES_AMT then last_amt.LAST_MILES_AMT - labh.CURR_MILES_AMT Else 0 end ) MILES_REDMD_AMT

MILES_REDMD_AMT_LCL

DWD_LYLTY_ACCT_BAL_HIST

last_amt.LAST_MILES_AMT_LCL - labh.CURR_MILES_AMT_LCL

Direct Mapping.

Sum(Case when labh.CURR_MILES_AMT_LCL<last_amt.LAST_MILES_AMT_LCL then last_amt.LAST_MILES_AMT_LCL - labh.CURR_MILES_AMT_LCL Else 0 end ) MILES_REDMD_AMT_LCL

MILES_REDMD_AMT_RPT

DWD_LYLTY_ACCT_BAL_HIST

last_amt.LAST_MILES_AMT_RPT - labh.CURR_MILES_AMT_RPT

Direct Mapping.

Sum(Case when labh.CURR_MILES_AMT_RPT<last_amt.LAST_MILES_AMT_RPT then last_amt.LAST_MILES_AMT_RPT - labh.CURR_MILES_AMT_RPT Else 0 end ) MILES_REDMD_AMT_RPT

MO_KEY

DWD_LYLTY_ACCT_LVL_HIST

MO_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

TOT_MILES_AMT

DWD_LYLTY_ACCT_BAL_HIST

labh.CURR_MILES_AMT

DWD_LYLTY_ACCT_LVL_HIST left join DWD_LYLTY_ACCT_BAL_HIST labh on (lalh.FRQTFLR_CARD_KEY=labh.FRQTFLR_CARD_KEY and lalh.MO_KEY=labh.MO_KEY and lalh.CLNDR_KEY=labh.CLNDR_KEY)

Sum(Case When labh.CLNDR_KEY Between labh.VALID_FROM and labh.VALID_UPTO Then labh.CURR_MILES_AMT else 0 end ) TOT_MILES_AMT

TOT_MILES_AMT_LCL

DWD_LYLTY_ACCT_BAL_HIST

labh.CURR_MILES_AMT_LCL

Direct Mapping.

Sum(Case When labh.CLNDR_KEY Between labh.VALID_FROM and labh.VALID_UPTO Then labh.CURR_MILES_AMT_LCL else 0 end ) TOT_MILES_AMT_LCL

TOT_MILES_AMT_RPT

DWD_LYLTY_ACCT_BAL_HIST

labh.CURR_MILES_AMT_RPT

Direct Mapping.

Sum(Case When labh.CLNDR_KEY Between labh.VALID_FROM and labh.VALID_UPTO Then labh.CURR_MILES_AMT_RPT else 0 end ) TOT_MILES_AMT_RPT

UPGD_CNT

DWD_LYLTY_ACCT_LVL_HIST

lalh.FRQTFLR_CARD_KEY

  

Count(Distinct CASE WHEN last_lvl.LAST_LVL_KEY < ff.LYLTY_LVL_KEY Then lalh.FRQTFLR_CARD_KEY end ) UPGD_CNT

PKG_DWD_BKG_FCT Mapping

Table 6-10 shows the list of source tables for PKG_DWD_BKG_FCT. Table 6-11 shows the mapping to populate target table DWD_BKG_FCT. For more information, see BOOKING FACT.

Table 6-10 PKG_DWD_ BKG_FCT ETL Mapping Source Tables

Source Table Name

DWB_BKG

DWB_BKG_CMPGN_ASGN

DWB_BKG_SSR

DWB_SEG_SCHL

DWB_TCKT

DWB_TCKT_CPN

DWR_CARRIER

DWR_FLT

DWR_GEO

DWR_SL_CHNL

DWR_TIME

DWR_TRFC_CTGRY

Table 6-11 PKG_DWD_ BKG_FCT ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

ACCT_KEY

DWB_BKG

ACCT_KEY

Direct Mapping.

  

APIS_CMPLT_IND

DWB_BKG

APIS_CMPLT_IND

Direct Mapping.

  

BID_PRC

DWB_BKG

BID_PRC

Direct Mapping.

  

BKG_CD

DWB_BKG

BKG_CD

Direct Mapping.

  

BKG_CLS_KEY

DWB_BKG

BKG_CLS_KEY

Direct Mapping.

  

BKG_CMPGN_CD

DWB_BKG_CMPGN_ASGN

CMPGN_CD

DWB_BKG left join DWB_BKG_CMPGN_ASGN bca on (bkg.BKG_CD=bca.BKG_CD)

  

BKG_CRTN_TMSTMP

DWB_BKG

SRC_SYS_CRTD_TMSTMP

Direct Mapping.

  

BKG_GRP_IND

DWB_BKG

BKG_GRP_IND

Direct Mapping.

  

BKG_LAST_UPDT_TMSTMP

DWB_BKG

SRC_SYS_UPDTD_TMSTMP

Direct Mapping.

  

BKG_MKTG_FLT_DT

DWB_BKG

MKTG_FLT_DPRT_DT_TIME

Direct Mapping.

  

BKG_MKTG_FLT_DT_LCL

DWB_BKG

MKTG_FLT_DPRT_DT_TIME

Direct Mapping.

  

BKG_OPERTNG_FLT_DT_UTC

DWB_BKG

OPRN_FLT_DPTR_DT_UTC

Direct Mapping.

  

BKG_OPRTNG_FLT_DT_LCL

DWB_BKG

OPRN_FLT_DPTR_DT

Direct Mapping.

  

BKG_OPRTNL_FLT_DT

DWB_BKG

OPRN_FLT_DPTR_DT

Direct Mapping.

  

BKG_STAT_CHNG_IND

DWB_BKG

BKG_STAT_CHNG_IND

Direct Mapping.

  

BKG_TYP

DWB_BKG

BKG_TYP

Direct Mapping.

  

BRDNG_IND

DWB_BKG

BRDNG_IND

Direct Mapping.

  

BSNS_IND

DWB_BKG

BSNS_IND

Direct Mapping.

  

CARRIER_KEY

DWR_CARRIER

CARRIER_KEY

DWB_BKG left join DWR_CARRIER dc on (bkg.CARRIER_CD=dc.CARRIER_CD and dc.CURR_IND='Y')

  

CBN_CD

DWB_BKG

CBN_CD

Direct Mapping.

  

CDSHR_AGRMNT

DWB_BKG

CDSHR_AGRMNT

Direct Mapping.

  

CDSHR_CLS

DWB_BKG

CDSHR_CLS

Direct Mapping.

  

CDSHR_IND

  

CDSHR_CLS

  

case when CDSHR_CLS is not null then 'Y' else 'N' end --bkg.CDSHR_IND

CITY_KEY

DWR_GEO

CITY_KEY

DWR_BKG_OFFC left join DWR_GEO geo on (bkgoff.CITY_CD=geo.CITY_CD and geo.CURR_IND='Y')

  

CLNDR_KEY

DWB_BKG

PNR_CRTN_DT

to_char(bkg.PNR_CRTN_DT,'YYYYMMDD') --CLNDR_KEY

  

CLS

DWB_BKG

BKG_CLS

Direct Mapping.

  

CNCL_DTTIME

DWB_BKG

BKG_CNCL_DTTIME

Direct Mapping.

  

CNFRM_DTTIME

DWB_BKG

BKG_CNFRM_DTTIME

Direct Mapping.

  

CNFRM_IND

DWB_BKG

CNFRM_IND

Direct Mapping.

  

CPN_AMT

DWB_TCKT

TOT_AMT

DWB_TCKT_CPN left join DWB_TCKT tckt on (tcpn.CPN_CD=tckt.TCKT_CD)

  

CPN_CD

DWB_BKG

PAX_CPN_CD

Direct Mapping.

  

CRPRT_CUST_KEY

DWB_BKG

PAX_KEY

Case When bkg.PAX_TYP='CRPRT_CUST' Then bkg.PAX_KEY end

  

CURR_STAT

DWB_BKG

CURR_IND

Direct Mapping.

  

DEAD_IND

  

BKG_CNCL_DTTIME

  

case when BKG_CNCL_DTTIME is not null then 'Y' else 'N' end --bkg.DEAD_IND

DWFEED_CD

DWB_BKG

DWFEED_CD

Direct Mapping.

  

ECONOMY_IND

DWB_BKG

ECONOMY_IND

Direct Mapping.

  

FIRST_IND

DWB_BKG

FIRST_IND

Direct Mapping.

  

FLN_PAX_CNT

DWB_BKG

FLN_PAX_CNT

Direct Mapping.

  

FLN_RVN

DWB_TCKT

TOT_AMT

DWB_TCKT_CPN left join DWB_TCKT tckt2 on (tcpn2.CPN_CD=tckt2.TCKT_CD)

case when bkg.FLN_DT is not null then tckt2.TOT_AMT else 0 end --bkg.FLN_RVN

FLN_RVN_ORGN_TO_DSTN_OFRD

DWB_TCKT

TOT_AMT

DWB_TCKT_CPN left join DWB_TCKT tckt2 on (tcpn2.CPN_CD=tckt2.TCKT_CD)

case when bkg.FLN_DT is not null then tckt2.TOT_AMT else 0 end --bkg.FLN_RVN_ORGN_TO_DSTN_OFRD

FLT_KEY

DWR_FLT

FLT_KEY

DWB_BKG left join DWR_FLT flt on (bkg.FLT_NBR=flt.FLT_NBR and to_char(bkg.OPRN_FLT_DPTR_DT,'DY') =flt.WKDAY_CD and flt.CURR_IND='Y')

  

FRQTFLR_CARD_KEY

DWB_BKG

FRQTFLR_CARD_KEY

Direct Mapping.

  

FRQTFLR_NBR

DWB_BKG

FRQTFLR_NBR

Direct Mapping.

  

GRPNG_IND

DWB_BKG

GRPNG_IND

Direct Mapping.

  

INFNT_CPN_AMT

DWB_TCKT

TOT_AMT

DWB_TCKT_CPN left join DWB_TCKT tckt2 on (tcpn2.CPN_CD=tckt2.TCKT_CD)

  

INFNT_CPN_CD

DWB_BKG

INFNT_CPN_CD

Direct Mapping.

  

INFO_IND

DWB_BKG

INFORMATIONAL_IND

Direct Mapping.

  

MEAL_CD

DWB_BKG

MEAL_CD

Direct Mapping.

  

MKTG_SEG_DPRT_DT_LCL

DWB_SEG_SCHL

SCHL_SEG_DPRT_DTTIME_LCL

DWB_BKG left join DWB_SEG_SCHL segs2 on (bkg.MKTG_SEG_SCHL_CD=segs2.SEG_SCHL_CD)

  

MKTG_SEG_DPRT_DT_UTC

DWB_SEG_SCHL

SCHL_SEG_DPRT_DTTIME_GMT

DWB_BKG left join DWB_SEG_SCHL segs2 on (bkg.MKTG_SEG_SCHL_CD=segs2.SEG_SCHL_CD)

  

MO_KEY

DWB_BKG

PNR_CRTN_DT

It indicates the foreign key which is the primary key of the other table

to_char(bkg.PNR_CRTN_DT,'YYYYMM') || '01' --bkg.MO_KEY

NEGOSPACE_REF

DWB_BKG

NEGOSPACE_REF

Direct Mapping.

  

NIGHT_IND

DWB_BKG

NIGHT_IND

Direct Mapping.

  

NON_RVN_FLN_PAX_CNT

DWB_BKG

NON_RVN_FLN_PAX_CNT

Direct Mapping.

  

OFF_KEY

DWB_BKG

OFFC_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

OFRD_ORGN_TO_DSTN_FLN_PAX_CNT

DWB_BKG

OFRD_ORGN_TO_DSTN_FLN_PAX_CNT

Direct Mapping.

  

ONBOARD_RVN

DWB_TCKT

TOT_AMT

DWB_TCKT_CPN left join DWB_TCKT tckt2 on (tcpn2.CPN_CD=tckt2.TCKT_CD)

case when bkg.CNFRM_IND='Y' then tckt2.TOT_AMT else 0 end --bkg.ONBOARD_RVN

OPEN_IND

DWB_BKG

OPEN_IND

Direct Mapping.

  

OPRTNG_SEG_DEPTR_LCL_DT_CD

DWB_SEG_SCHL

SCHL_SEG_DPRT_DTTIME_LCL

DWB_BKG left join DWB_SEG_SCHL segs on (bkg.OPERTNG_SEG_SCHL_CD=segs.SEG_SCHL_CD )

  

OPRTNG_SEG_DEPTR_UTC_DT_CD

DWB_SEG_SCHL

SCHL_SEG_DPRT_DTTIME_GMT

DWB_BKG left join DWB_SEG_SCHL segs on (bkg.OPERTNG_SEG_SCHL_CD=segs.SEG_SCHL_CD )

  

ORIGINL_ACTN_CD

DWB_BKG

ORGNL_ACTN_CD

Direct Mapping.

  

OVERBKG_RSN

DWB_BKG

OVERBKG_RSN_DSCR

Direct Mapping.

  

OVERBKG_TYP

DWB_BKG

OVERBKG_TYP

Direct Mapping.

  

PAX_KEY

DWB_BKG

PAX_KEY

Direct Mapping.

  

PNR_CRTN_DT

DWB_BKG

PNR_CRTN_DT

Direct Mapping.

  

PNR_KEY

DWB_BKG

PNR_KEY

Direct Mapping.

  

PNR_RECORDLOCATOR

DWB_BKG

BKG_PNR_RLOC

Direct Mapping.

  

SEAT_NBR

DWB_BKG

SEAT_NBR

Direct Mapping.

  

SEG_KEY

DWB_SEG_SCHL

SEG_KEY

DWB_BKG left join DWB_SEG_SCHL segs on (bkg.OPERTNG_SEG_SCHL_CD=segs.SEG_SCHL_CD )

  

SL_CHNL_KEY

DWR_SL_CHNL

SL_CHNL_KEY

DWB_BKG left join DWR_SL_CHNL schn on (bkg.OFFC_KEY=schn.OFFC_KEY and schn.CURR_IND='Y')

  

SRC_SYS_CRTD_TMSTMP

DWB_BKG

SRC_SYS_CRTD_TMSTMP

Direct Mapping.

  

SRC_SYS_DEL_IND

DWB_BKG

SRC_SYS_DEL_IND

Direct Mapping.

  

SRC_SYS_ID

DWB_BKG

SRC_SYS_ID

Direct Mapping.

  

SRC_SYS_UPDTD_TMSTMP

DWB_BKG

SRC_SYS_UPDTD_TMSTMP

Direct Mapping.

  

SSR_KEY

DWB_BKG_SSR

SSR_KEY

DWB_BKG left join DWB_BKG_SSR bssr on (bkg.BKG_CD=bssr.BKG_CD)

  

STAT_CD

DWB_BKG

STAT_CD

Direct Mapping.

  

TCKT_CD

DWB_TCKT_CPN

TCKT_CD

DWB_BKG left join DWB_TCKT_CPN tcpn on (bkg.PAX_CPN_CD=tcpn.CPN_CD)

  

TIME_KEY

DWR_TIME

TIME_KEY

DWB_BKG left join DWR_TIME tim on (to_char(bkg.PNR_CRTN_DT,'HH24')=tim.HR_OF_DAY and to_char(bkg.PNR_CRTN_DT,'MI')=tim.MNT_OF_HR )

  

TRFC_CTGRY_KEY

DWR_TRFC_CTGRY

TRFC_CTGRY_KEY

DWB_BKG left join DWR_TRFC_CTGRY tc on (bkg.FLT_NBR=tc.FLT_NBR and tc.CURR_IND='Y')

  

TST_CD

DWB_BKG

TST_CD

Direct Mapping.

  

TST_INFNT_FARE_BASIS_CD

DWB_BKG

TST_INFNT_FARE_BASIS_CD

Direct Mapping.

  

TST_PAX_FARE_BASIS_CD

DWB_BKG

TST_PAX_FARE_BASIS_CD

Direct Mapping.

  

WAITLIST_DTTIME

DWB_BKG

WAITLIST_DTTIME

Direct Mapping.

  

WAITLIST_IND

DWB_BKG

WAITLIST_IND

Direct Mapping.

  

YLD

DWB_BKG

YLD

Direct Mapping.

  

PKG_DWD_CALL_CNTR_PRFMNC Mapping

Table 6-12 shows the mapping to populate target table DWD_CALL_CNTR_PRFMNC. For more information, see CALL CENTER PERFORMANCE.

Source Tables

DWB_PRTY_INTRACN_CALL

DWR_CALL_CNTR

DWR_CALL_CNTR_AGNT

Table 6-12 PKG_DWD_CALL_CNTR_PRFMNC ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

CALL_CNTR_AGNT_KEY

DWR_CALL_CNTR_AGNT

CALL_CNTR_AGNT_KEY

DWB_PRTY_INTRACN_CALL left join DWR_CALL_CNTR_AGNT cca on (pic.CALL_CNTR_AGNT_CD=cca.CALL_CNTR_AGNT_CD and cca.CURR_IND='Y')

  

CLNDR_KEY

DWB_PRTY_INTRACN_CALL

INTRACN_EVT_DT_AND_TIME

It indicates the foreign key which is the primary key of the other table

to_char(pic.INTRACN_EVT_DT_AND_TIME,'YYYYMMDD')

CUST_SATISFACTN_IND

DWB_PRTY_INTRACN_CALL

CUST_SATISFACTN_IND

Direct Mapping.This indicates the CUSTOMER SATISFACTION INDICATOR

  

HLD_DRTN

DWB_PRTY_INTRACN_CALL

HLD_DRTN

Direct Mapping.This indicates the HOLD DURATION

  

HNDL_BY_IVR_IND

DWB_PRTY_INTRACN_CALL

HNDL_BY_IVR_IND

Direct Mapping.This indicates the HANDLED BY IVR INDICATOR

  

INTRACN_DRTN

DWB_PRTY_INTRACN_CALL

INTRACN_DRTN

Direct Mapping.This indicates the INTERACTION DURATION

  

MO_KEY

DWB_PRTY_INTRACN_CALL

INTRACN_EVT_DT_AND_TIME

It indicates the foreign key which is the primary key of the other table

to_char(pic.INTRACN_EVT_DT_AND_TIME,'YYYYMM') || '01'

OFFC_KEY

DWR_CALL_CNTR

CALL_CNTR_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

PRTY_INTRACN_CALL_CD

DWB_PRTY_INTRACN_CALL

PRTY_INTRACN_CALL_CD

Direct Mapping.

  

QUE_DRTN

DWB_PRTY_INTRACN_CALL

QUE_DRTN

Direct Mapping.This indicates the QUEUE DURATION

  

TALK_DRTN

DWB_PRTY_INTRACN_CALL

TALK_DRTN

Direct Mapping.This indicates the TALK DURATION

  

PKG_DWD_CHKIN Mapping

Source Tables

DWB_BKG

DWB_CHKIN

DWB_SEG_SCHL

DWR_CHKIN_BAG_GRP

DWR_LEG

Table 6-13 PKG_DWD_ CHKIN ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

ACPT_DSCR

DWB_CHKIN

ACPT_DSCR

Direct Mapping.This indicates the ACCEPTANCE DESCRIPTION

  

ACPT_STAT

DWB_CHKIN

ACPT_STAT

Direct Mapping.This indicates the ACCEPTANCE STATUS

  

ARPRT_KEY

DWB_CHKIN

DPRT_ARPRT_KEY

Direct Mapping.

  

ARRVL_ARPRT

DWB_CHKIN

ARRVL_ARPRT_KEY

Direct Mapping.

  

BAG_GRP_CHKIN_BAG_CNT

DWB_CHKIN

TOT_BAG_NBR

Direct Mapping.This indicates the TOTAL BAGGAGE NUMBER

  

BAG_GRP_CHKIN_BAG_WEIGHTT

DWB_CHKIN

TOT_BAG_WT

Direct Mapping.This indicates the TOTAL BAGGAGE WEIGHT

  

BAG_GRP_KEY

DWR_CHKIN_BAG_GRP

BAG_GRP_KEY

DWB_CHKIN left join DWR_CHKIN_BAG_GRP cbg on (dc.PDI_CD=cbg.PDI_CD and leg.CURR_IND='Y')

  

BAG_GRP_POOL_REF

DWB_CHKIN

BAG_GRP_POOL_REF

Direct Mapping.

  

BAG_GRP_TYP

DWR_CHKIN_BAG_GRP

GRP_TYP

DWB_CHKIN left join DWR_CHKIN_BAG_GRP cbg on (dc.PDI_CD=cbg.PDI_CD and leg.CURR_IND='Y')

  

BAG_STAT

DWB_CHKIN

BAG_STAT

Direct Mapping.This indicates the BAGGAGE STATUS

  

BKG_KEY

DWB_CHKIN

BKG_CD

Direct Mapping.

  

BKG_PDI_INFNT_IND

DWB_CHKIN

BKG_PDI_INFNT_IND

Direct Mapping.This indicates the BOOKING PDI INFANT INDICATOR

  

BRDNG_STAT

DWB_CHKIN

BRDNG_STAT

Direct Mapping.This indicates the BOARDING STATUS

  

CARRIER_KEY

DWB_CHKIN

CARRIER_KEY

Direct Mapping.

  

CHNL_KEY

DWB_CHKIN

CHNL_KEY

Direct Mapping.

  

CLNDR_KEY

DWB_CHKIN

DPRT_DT

This indicates the DEPARTURE DATE

to_char(dc.DPRT_DT,'YYYYMMDD')

CURR_STAT

DWB_CHKIN

CURR_IND

Direct Mapping.

  

CUST_REC_STAT

DWB_CHKIN

CUST_REC_STAT

Direct Mapping.This indicates the CUSTOMER RECORD STATUS

  

DPRT_ARPRT

DWB_CHKIN

DPRT_ARPRT_KEY

Direct Mapping.

  

DWFEED_CD

DWB_CHKIN

DWFEED_CD

Direct Mapping.

  

FLT_KEY

DWB_CHKIN

FLT_KEY

Direct Mapping.

  

IATC_ORGNL_INFO

DWB_CHKIN

IATC_ORGN_INFO

Direct Mapping.This indicates the IATC ORIGIN INFORMATION

  

IATCI_ORGNL_CMPNY

DWB_CHKIN

IATCI_ORGN_CMPNY

Direct Mapping.This indicates the IATCI ORIGIN COMPANY

  

IATCI_TRGT

DWB_CHKIN

IATCI_TRGT

Direct Mapping.This indicates the IATCI TARGET

  

IATCI_TRGT_CMPNY

DWB_CHKIN

IATCI_TRGT_CMPNY

Direct Mapping.This indicates the IATCI TARGET COMPANY

  

IATCI_TRGT_CMPNY_CD

DWB_CHKIN

IATCI_TRGT_CMPNY

Direct Mapping.This indicates the IATCI TARGET COMPANY

  

LEG_KEY

DWR_LEG

LEG_KEY

DWB_LEG_SCHL left join DWR_LEG leg on (legs.DPRT_ARPRT_KEY=leg.DPRT_ARPRT_KEY and legs.ARRVL_ARPRT_KEY=leg.ARRVL_ARPRT_KEY and leg.CURR_IND='Y')

  

MO_KEY

DWB_CHKIN

DPRT_DT

  

to_char(dc.DPRT_DT,'YYYYMM') || '01'

OFF_KEY

DWB_CHKIN

OFFC_KEY

Direct Mapping.

  

PAX_KEY

DWB_BKG

PAX_KEY

DWB_CHKIN left join DWB_BKG bkg on (dc.BKG_CD=bkg.BKG_CD)

  

PDI_CD

DWB_CHKIN

PDI_CD

Direct Mapping.

  

REGRADE_CBN_CD

DWB_CHKIN

REGRADE_CBN_CD

Direct Mapping.This indicates the REGRADE CABIN CODE

  

REGRADE_DSCR

DWB_CHKIN

REGRADE_DSCR

Direct Mapping.This indicates the REGRADE DESCRIPTION

  

REGRADE_STAT

DWB_CHKIN

REGRADE_STAT

Direct Mapping.This indicates the REGRADE STATUS

  

RESPBL_CUST_KEY

DWB_CHKIN

RESPBL_CUST_KEY

Direct Mapping.

  

RESPBL_CUST_NAME

DWB_CHKIN

RESPBL_CUST_NAME

Direct Mapping.This indicates the RESPONSIBLE CUSTOMER NAME

  

SEAT_DTLS

DWB_CHKIN

SEAT_DTL

Direct Mapping.This indicates the SEAT DETAIL

  

SEG_KEY

DWB_SEG_SCHL

SEG_KEY

DWB_CHKIN left join DWB_SEG_SCHL segs on (dc.SEG_SCHL_CD=segs.SEG_SCHL_CD)

  

TOT_BAG_CNT

DWB_CHKIN

TOT_BAG_NBR

Direct Mapping.This indicates the TOTAL BAGGAGE NUMBER

  

PKG_DWD_CUST_SRVY Mapping

Table 6-14 shows the mapping to populate target table DWD_CUST_SRVY. For more information, see CUSTOMER SURVEY DERIVED.

Source Tables

DWB_PRTY_INTRACN_THRD

Table 6-14 PKG_DWD_CUST_SRVY ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

CLNDR_KEY

DWB_PRTY_INTRACN_THRD

INTRACN_THRD_STRT_DT

It indicates the foreign key which is the primary key of the other table

to_char(INTRACN_THRD_STRT_DT,'YYYYMMDD') CLNDR_KEY

CUST_KEY

DWB_PRTY_INTRACN_THRD

CUST_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

INTRACN_RSLT_KEY

DWB_PRTY_INTRACN_THRD

INTRACN_RSLT_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

INTRACN_RSN_KEY

DWB_PRTY_INTRACN_THRD

INTRACN_RSN_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

MO_KEY

DWB_PRTY_INTRACN_THRD

INTRACN_THRD_STRT_DT

It indicates the foreign key which is the primary key of the other table

to_char(INTRACN_THRD_STRT_DT,'YYYYMM') || '01' MO_KEY

PRTY_INTRACN_THRD_CD

DWB_PRTY_INTRACN_THRD

PRTY_INTRACN_THRD_CD

Direct Mapping.

  

SRVC_KEY

DWB_PRTY_INTRACN_THRD

SRVC_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

PKG_DWD_FLT_DTLS Mapping

Table 6-15 shows the mapping to populate target table DWD_FLT_DTLS. For more information, see FLIGHT DETAILS FACT.

Source Tables

DWB_FLT_SCHD

DWB_LEG_SCHL

DWB_SEG_SCHL

DWR_ARCFT_VRSN

DWR_LEG

Table 6-15 PKG_DWD_FLT_DTLS ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

ACV_KEY

DWB_LEG_SCHL

ACV_KEY

DWB_FLT_SCHL left join DWB_LEG_SCHL LEG on (flt.FLT_SCHL_CD=leg.FLT_SCHL_CD)

  

ACV_TOT_CPCTY

DWR_ARCFT_VRSN

ACV_TOT_CPCTY

DWB_LEG_SCHL left join DWR_ARCFT_VRSN ARCFT on (leg.ACV_KEY=arcft.ACV_KEY and arcft.CURR_IND='Y')

  

CDSHR_TYP

DWB_FLT_SCHL

FLT_CDSHR_TYP

Direct Mapping.This indicates the FLIGHT CODESHARE TYPE

  

CLNDR_KEY

DWB_LEG_SCHL

DPRT_DTTIME_UTC

DWB_FLT_SCHL left join DWB_LEG_SCHL LEG on (flt.FLT_SCHL_CD=leg.FLT_SCHL_CD)

to_char(LEG.DPRT_DTTIME_UTC,'yyyymmdd')

EFF_DT

DWB_FLT_SCHL

EFF_DT

Direct Mapping.This indicates the EFFECTIVE DATE

  

END_DT

DWB_FLT_SCHL

END_DT

Direct Mapping.This indicates the END DATE

  

FLT_ALPHA_SUFX

DWB_FLT_SCHL

FLT_ALPHA_SUFX

Direct Mapping.This indicates the FLIGHT ALPHA SUFFIX

  

FLT_CARRIER_CD

DWB_FLT_SCHL

FLT_CARRIER_CD

Direct Mapping.This indicates the FLIGHT CARRIER CODE

  

FLT_DT

DWB_FLT_SCHL

FLT_FST_DPRT_DT

Direct Mapping.This indicates the FLIGHT FST DEPARTURE DATE

  

FLT_DTLS_FCT_KEY

DWB_FLT_SCHL

FLT_DTLS_FCT_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

FLT_KEY

DWB_FLT_SCHL

FLT_KEY

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

FLT_NBR

DWB_FLT_SCHL

FLT_NBR

Direct Mapping.This indicates the FLIGHT NUMBER

  

LEG_KEY

DWR_LEG

LEG_KEY

DWB_LEG_SCHL left join DWR_LEG DL on (leg.DPRT_ARPRT_KEY=DL.DPRT_ARPRT_KEY and leg.ARRVL_ARPRT_KEY=dl.ARRVL_ARPRT_KEY and DL.CURR_IND='Y')

  

MO_KEY

DWB_FLT_SCHL

DPRT_DTTIME_UTC

It indicates the foreign key which is the primary key of the other table

to_char(flt.DPRT_DTTIME_UTC,'yyyymm')||'01' MO_KEY

NAUTICAL_MILES

DWB_LEG_SCHL

NAUTICAL_MILES

DWB_FLT_SCHL left join DWB_LEG_SCHL LEG on (flt.FLT_SCHL_CD=leg.FLT_SCHL_CD)

  

NAUTICAL_TO_KM_CNVRSN_IND

DWB_LEG_SCHL

NAUTICAL_TO_KM_CNVRSN_IND

DWB_FLT_SCHL left join DWB_LEG_SCHL LEG on (flt.FLT_SCHL_CD=leg.FLT_SCHL_CD)

  

SALEBLE_TOT_CPCTY

DWB_FLT_SCHL

TOT_SLBL_CPCTY

Direct Mapping.This indicates the TOTAL SALEABLE CAPACITY

  

SEG_KEY

DWB_SEG_SCHL

SEG_KEY

DWB_FLT_SCHL left join DWB_SEG_SCHL SEG on (leg.FLT_SCHL_CD=seg.FLT_SCHL_CD and leg.SEG_SEQ_NBR=seg.SEQ_NBR)

  

STAT_FLG

DWB_FLT_SCHL

STAT_FLG

Direct Mapping.This indicates the STATUS FLAG

  

TRFC_CTGRY_KEY

DWB_FLT_SCHL

TRAFIC_CTGRY_CD

Direct Mapping.It indicates the foreign key which is the primary key of the other table

  

PKG_DWD_LYLTY_ACCT_BAL_HIST Mapping

Table 6-16 shows the mapping to populate target table DWD_LYLTY_ACCT_BAL_HIST. For more information, see.

Source Tables

DWB_LYLTY_ACCT_BAL_HIST

DWR_FREQ_FLYR

DWR_LYLTY_ACCT

Table 6-16 PKG_DWD_LYLTY_ACCT_BAL_HIST ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

CITY_KEY

DWR_FREQ_FLYR

CITY_KEY

DWB_LYLTY_ACCT_BAL_HIST left join DWR_FREQ_FLYR ff on (labh.LYLTY_ACCT_KEY=ff.LYLTY_ACCT_KEY and ff.CURR_IND='Y')

  

CLNDR_KEY

DWB_LYLTY_ACCT_BAL_HIST

BAL_DT

It indicates the foreign key which is the primary key of the other table

to_char(labh.BAL_DT,'YYYYMMDD')

CURR_MILES_AMT

DWB_LYLTY_ACCT_BAL_HIST

CURR_MILES_AMT

Direct Mapping.This indicates the CURRENT MILES AMOUNT

  

CURR_MILES_AMT_LCL

DWB_LYLTY_ACCT_BAL_HIST

CURR_MILES_AMT_LCL

Direct Mapping.

  

CURR_MILES_AMT_RPT

DWB_LYLTY_ACCT_BAL_HIST

CURR_MILES_AMT_RPT

Direct Mapping.

  

EXPIRED_MILES_AMT

DWB_LYLTY_ACCT_BAL_HIST

EXPIRED_MILES_AMT

Direct Mapping.This indicates the EXPIRED MILES AMOUNT

  

EXPIRED_MILES_AMT_LCL

DWB_LYLTY_ACCT_BAL_HIST

EXPIRED_MILES_AMT_LCL

Direct Mapping.

  

EXPIRED_MILES_AMT_RPT

DWB_LYLTY_ACCT_BAL_HIST

EXPIRED_MILES_AMT_RPT

Direct Mapping.

  

EXTRA_MILES_AMT

DWB_LYLTY_ACCT_BAL_HIST

EXTRA_MILES_AMT

Direct Mapping.This indicates the EXTRA MILES AMOUNT

  

EXTRA_MILES_AMT_LCL

DWB_LYLTY_ACCT_BAL_HIST

EXTRA_MILES_AMT_LCL

Direct Mapping.

  

EXTRA_MILES_AMT_RPT

DWB_LYLTY_ACCT_BAL_HIST

EXTRA_MILES_AMT_RPT

Direct Mapping.

  

FRQTFLR_CARD_KEY

DWR_FREQ_FLYR

FRQTFLR_CARD_KEY

DWB_LYLTY_ACCT_BAL_HIST left join DWR_FREQ_FLYR ff on (labh.LYLTY_ACCT_KEY=ff.LYLTY_ACCT_KEY and ff.CURR_IND='Y')

  

LAST_BAL_AMT

DWB_LYLTY_ACCT_BAL_HIST

LAST_BAL_AMT

Direct Mapping.This indicates the LAST BALANCE AMOUNT

  

LAST_BAL_AMT_LCL

DWB_LYLTY_ACCT_BAL_HIST

LAST_BAL_AMT_LCL

Direct Mapping.

  

LAST_BAL_AMT_RPT

DWB_LYLTY_ACCT_BAL_HIST

LAST_BAL_AMT_RPT

Direct Mapping.

  

LYLTY_ACCT_BAL_HIST_CD

DWB_LYLTY_ACCT_BAL_HIST

LYLTY_ACCT_BAL_HIST_CD

Direct Mapping.This indicates the LOYALTY ACCOUNT BALANCE HISTORY IDENTIFIER

  

LYLTY_PROG_KEY

DWR_LYLTY_ACCT

LYLTY_PROG_KEY

DWB_LYLTY_ACCT_BAL_HIST left join DWR_LYLTY_ACCT la on (labh.LYLTY_ACCT_KEY=la.LYLTY_ACCT_KEY and la.CURR_IND='Y')

  

MO_KEY

DWB_LYLTY_ACCT_BAL_HIST

BAL_DT

It indicates the foreign key which is the primary key of the other table

to_char(labh.BAL_DT,'YYYYMM') || '01'

OTHR_NON_AIR_MILES_AMT

DWB_LYLTY_ACCT_BAL_HIST

OTHR_NON_AIR_MILES_AMT

Direct Mapping.This indicates the OTHER NON AIR MILES AMOUNT

  

OTHR_NON_AIR_MILES_AMT_LCL

DWB_LYLTY_ACCT_BAL_HIST

OTHR_NON_AIR_MILES_AMT_LCL

Direct Mapping.This indicates the OTHER NON AIR MILES AMOUNT

  

OTHR_NON_AIR_MILES_AMT_RPT

DWB_LYLTY_ACCT_BAL_HIST

OTHR_NON_AIR_MILES_AMT_RPT

Direct Mapping.This indicates the OTHER NON AIR MILES AMOUNT

  

PRMTN_MILES_AMT

DWB_LYLTY_ACCT_BAL_HIST

PRMTN_MILES_AMT

Direct Mapping.This indicates the PROMOTION MILES AMOUNT

  

PRMTN_MILES_AMT_LCL

DWB_LYLTY_ACCT_BAL_HIST

PRMTN_MILES_AMT_LCL

Direct Mapping.

  

PRMTN_MILES_AMT_RPT

DWB_LYLTY_ACCT_BAL_HIST

PRMTN_MILES_AMT_RPT

Direct Mapping.

  

REDM_MILES_AMT

DWB_LYLTY_ACCT_BAL_HIST

REDM_MILES_AMT

Direct Mapping.This indicates the REDEEM MILES AMOUNT

  

REDM_MILES_AMT_LCL

DWB_LYLTY_ACCT_BAL_HIST

REDM_MILES_AMT_LCL

Direct Mapping.

  

REDM_MILES_AMT_RPT

DWB_LYLTY_ACCT_BAL_HIST

REDM_MILES_AMT_RPT

Direct Mapping.

  

PKG_DWD_LYLTY_ACCT_LVL_HIST Mapping

Table 6-17 shows the mapping to populate target table DWD_LYLTY_ACCT_LVL_HIST. For more information, see LOYALTY ACCOUNT LEVEL HISTORY.

Source Tables

DWB_LYLTY_ACCT_LVL_HIST

DWR_FREQ_FLYR

DWR_LYLTY_LVL

Table 6-17 PKG_DWD_LYLTY_ACCT_LVL_HIST ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

ACCT_LVL_HIST_CD

DWB_LYLTY_ACCT_LVL_HIST

ACCT_LVL_HIST_CD

Direct Mapping.

  

CLNDR_KEY

DWB_LYLTY_ACCT_LVL_HIST

VALID_DT

It indicates the foreign key which is the primary key of the other table

to_char(lalh.VALID_DT,'YYYYMMDD')

FRQTFLR_CARD_KEY

DWR_FREQ_FLYR

FRQTFLR_CARD_KEY

DWB_LYLTY_ACCT_LVL_HIST left join DWR_FREQ_FLYR ff on (lalh.FRQTFLR_CARD_CD=ff.FRQTFLR_NBR and ff.CURR_IND='Y')

  

LYLTY_LVL_KEY

DWR_LYLTY_LVL

LYLTY_LVL_KEY

DWB_LYLTY_ACCT_LVL_HIST left join DWR_LYLTY_LVL llvl on (lalh.LYLTY_LVL_CD=llvl.LYLTY_LVL_CD and llvl.CURR_IND='Y')

  

MO_KEY

DWB_LYLTY_ACCT_LVL_HIST

VALID_DT

It indicates the foreign key which is the primary key of the other table

to_char(lalh.VALID_DT,'YYYYMM') || '01'

VALID_FROM

DWB_LYLTY_ACCT_LVL_HIST

VALID_DT

Direct Mapping.

  

VALID_UPTO

DWB_LYLTY_ACCT_LVL_HIST

EXP_DT

Direct Mapping.

  

PKG_DWD_TCKT Mapping

Table 6-18 shows the mapping to populate target table DWD_TCKT. For more information, see TICKET.

Source Table

DWB_TKT

Table 6-18 PKG_DWD_TCKT ETL Source to Target Mapping

Column Name Source Table Name Source Column Name Transformation Description Comments (Formula If Any)

CNJCTVE_TCKT_CNT

DWB_TCKT

NBR_OF_CONJUCTIVE_TCKT

Direct Mapping.This indicates the NUMBER OF CONJUCTIVE TICKET

  

CRNCY

DWB_TCKT

TCKT_CRNCY

Direct Mapping.This indicates the TICKET CURRENCY

  

DT_OF_ISSUE

DWB_TCKT

TCKT_ISSUING_DT

Direct Mapping.This indicates the TICKET ISSUING DATE

  

ISSUE_OFFC_IATA_CD

DWB_TCKT

ISSUING_OFFC_IATA_CD

Direct Mapping.This indicates the ISSUING OFFICE IATA CODE

  

ISSUING_OFFC_CD

DWB_TCKT

ISSUING_OFFC_IATA_CD

Direct Mapping.This indicates the ISSUING OFFICE IATA CODE

  

MO_KEY

DWB_TCKT

TCKT_ISSUING_DT

It indicates the foreign key which is the primary key of the other table

to_char(TCKT_ISSUING_DT,'yyyymm')||'01'

PAX_TYP

DWB_TCKT

PAX_TYP

Direct Mapping.This indicates the PASSENGER TYPE

  

PRMRY_NBR

DWB_TCKT

PRMRY_TCKT_NBR

Direct Mapping.This indicates the PRIMARY TICKET NUMBER

  

TCKT_CD

DWB_TCKT

TCKT_CD

Direct Mapping.This indicates the TICKET CURRENCY

  

TOT_AMT

DWB_TCKT

TOT_AMT

Direct Mapping.This indicates the TOTAL AMOUNT

  

TOT_AMT_LCL

DWB_TCKT

TOT_AMT_LCL

Direct Mapping.

  

TOT_AMT_RPT

DWB_TCKT

TOT_AMT_RPT

Direct Mapping.

  

Intra-ETL Process Flows

Intra-ETL is delivered as a component of Oracle Airlines Data Model. This intra-ETL is delivered as a PL/SQL package named PKG_INTRA_ETL_PROCESS which is a complete Intra-ETL process composed of sub process flows to populate the derived and aggregate tables with the data from the base and reference tables. The PKG_INTRA_ETL_PROCESS flow respects the dependency of each individual program and executes the programs in the proper order.

The PKG_INTRA_ETL_PROCESS is composed of individual sub-process procedures and functions. The sub-processes execute in the order indicated:

  1. Populate_Derived - Populates the derived (DWD_) tables based on the content of the base (DWB_) tables.

    Step 1: Populate table DWD_BKG_FCT, the code in ETL package is as follows:

    PKG_DWD_BKG_FCT.Load('DWD_BKG_FCT',p_process_no);

Step 2: Populate table DWD_CALL_CNTR_PRFMNC, the code in ETL package is as follows:

    PKG_DWD_CALL_CNTR_PRFMNC.Load('DWD_CALL_CNTR_PRFMNC',p_process_no);

Step 3: Populate table DWD_CHKIN, the code in ETL package is as follows:

    PKG_DWD_CHKIN.Load('DWD_CHKIN',p_process_no);

Step 4: Populate table DWD_CUST_SRVY, the code in ETL package is as follows:

     PKG_DWD_CUST_SRVY.Load('DWD_CUST_SRVY',p_process_no);

Step 5: Populate table DWD_FLT_DTLS, the code in ETL package is as follows:

    PKG_DWD_FLT_DTLS.Load('DWD_FLT_DTLS',p_process_no);

Step 6: Populate table DWD_LYLTY_ACCT_BAL_HIST, the code in ETL package is as follows:

    PKG_DWD_LYLTY_ACCT_BAL_HIST.Load('DWD_LYLTY_ACCT_BAL_HIST',p_process_no);

Step 7: Populate table DWD_LYLTY_ACCT_LVL_HIST, the code in ETL package is as follows:

    PKG_DWD_LYLTY_ACCT_LVL_HIST.Load('DWD_LYLTY_ACCT_LVL_HIST',p_process_no);

Step 8: Populate table DWD_RVN_CST_DRVD, the code in ETL package is as follows:

    PKG_DWD_RVN_CST_DRVD.Load('DWD_RVN_CST_DRVD',p_process_no);
 
Step 9: Populate table DWD_TCKT, the code in ETL package is as follows:

    PKG_DWD_TCKT.Load('DWD_TCKT',p_process_no);

  2. Populate_Aggregate - Refreshes all of the aggregate (DWA_) tables using data from the reference (DWR_) and derived (DWD_) tables.

    Step 1: Populate table DWA_CUST_SRVE, the code in ETL package is as follows:
  
    PKG_DWA_CUST_SRVEY.Load('DWA_CUST_SRVEY',p_process_no);

Step 2: Populate table DWA_DLY_BKG, the code in ETL package is as follows:
  
    PKG_DWA_DLY_BKG.Load('DWA_DLY_BKG',p_process_no);

Step 3: Populate table DWA_DLY_CALL_CNTR_PRFMNCE, the code in ETL package is as follows:

    PKG_DWA_DLY_CALL_CNTR_PRFMNCE.Load('DWA_DLY_CALL_CNTR_PRFMNCE',p_process_no);

Step 4: Populate table DWA_DLY_FLT_DTLS, the code in ETL package is as follows:
 
    PKG_DWA_DLY_FLT_DTLS.Load('DWA_DLY_FLT_DTLS',p_process_no);

Step 5: Populate table DWA_DLY_LYALTY_ACCT, the code in ETL package is as follows:

    PKG_DWA_DLY_LYALTY_ACCT.Load('DWA_DLY_LYALTY_ACCT',p_process_no);

Step 6: Populate table DWA_DLY_LYALTY_ACCT_BKG, the code in ETL package is as follows:
  
    PKG_DWA_DLY_LYALTY_ACCT_BKG.Load('DWA_DLY_LYALTY_ACCT_BKG',p_process_no);

  3. Populate_Aw - Loads data from Oracle Airlines Data Model aggregate (DWA_) tables into the Oracle Airlines Data Model Analytical Workspace and calculates the forecast data. It reads OLAP ETL parameters from DWC_OLAP_ETL_PARM table.

        PKG_OADM_OLAP_ETL_AW_LOAD.olap_etl_aw_build(l_build_methd,l_cube_nm,l_maxjobques,l_calc_fcst,l_no_fcst_yrs,l_fcst_mthd,l_fcst_st_yr,l_fcst_end_yr,null,null);

  4. Populate_MINING: This sub-process flow triggers the data mining models.

        PKG_OCDM_MINING.REFRESH_MODEL(l_apply_day_key,NULL);