1 Introduction to Oracle Airlines Data Model Customization

Provides an introduction to customizing the Oracle Airlines Data Model.

Overview of Oracle Airlines Data Model

Oracle Airlines Data Model is a pre-built approach to airline data warehousing enabling an airline to realize the power of insight quickly. Oracle Airlines Data Model reduces costs for both immediate and on-going operations by leveraging out-of-box Oracle based data warehouse and business intelligence solutions, making world-class database and business intelligence technology solutions available with an airline specific data model. You can use Oracle Airlines Data Model in any application environment.

Using Oracle Airlines Data Model you can jump-start the design and implementation of an Oracle Airlines Data Model warehouse to quickly achieve a positive ROI for your data warehousing and business intelligence project with a predictable implementation effort.

Oracle Airlines Data Model provides much of the data modeling work that you must do for an airline business intelligence solution. The Oracle Airlines Data Model logical and physical data models were designed following best practices for airlines.

Components of the Oracle Airlines Data Model

Oracle Airlines Data Model includes several components.

  • Logical model which is a third normal form (3NF) entity-object model. The logical model follows aviation industry standards and is described in Oracle Airlines Data Model Reference.

  • Physical model defined as an Oracle Database schema.

  • Intra-ETL database packages and SQL scripts to extract, transform, and load (ETL) data from the Oracle Airlines Data Model 3NF physical tables to the derived and aggregate tables in Oracle Airlines Data Model.

  • Sample reports and dashboards developed using Oracle Business Intelligence Suite Extended Edition.

  • DDL and installation scripts


When you use the Oracle Installer to install Oracle Airlines Data Model, you have the choice of performing two different types of installations:

  • Installation of the Oracle Airlines Data Model component

  • Installation of sample reports (and schemas)

Oracle Products That Make Up Oracle Airlines Data Model

Several Oracle technologies are involved in building the infrastructure for Oracle Airlines Data Model.

Oracle Database with OLAP, Advanced Analytics and Partitioning Option

Oracle Airlines Data Model uses a complete Oracle technical stack. It leverages the following data warehousing features of the Oracle Database: SQL model, compression, partitioning, materialized views, data mining, and online analytical processing (OLAP).

Oracle Development Tools

You can use the following Oracle tools to customize the predefined physical models provided with Oracle Airlines Data Model, or to populate the target relational tables and materialized cube views.

Table 1-1 Oracle Development Tools Used with Oracle Airlines Data Model

Name Use

SQL Developer or SQL*Plus

To modify, customize, and extend database objects

Analytic Workspace Manager

To view, create, develop, and manage OLAP dimensional objects.

Oracle Business Intelligence Suite Extended Edition Presentation Tools

Oracle Business Intelligence Suite Extended Edition is a comprehensive suite of enterprise BI products that delivers a full range of analysis and reporting capabilities. You can use Oracle Business Intelligence Suite Extended Edition Answers and Dashboard presentation tools to customize the predefined sample dashboard reports that are provided with Oracle Airlines Data Model.

Implementing an Oracle Airlines Data Model Warehouse

Although Oracle Airlines Data Model was designed following best practices for airlines, usually the model requires some customization to meet your business needs. The reasons that you might customize Oracle Airlines Data Model include: your business does not have a business area that is in the logical model of Oracle Airlines Data Model, or you must apply a new or different business rule.

Typical physical model modifications include: adding, deleting, modifying, or renaming tables and columns; or altering foreign keys, constraints, or indexes.

To implement an Oracle Airlines Data Model warehouse, perform the following steps:

  1. Perform the required organizational tasks.

  2. Create a fit-gap analysis report.

  3. In a development environment, install a copy of the Oracle Airlines Data Model.

  4. Customize Oracle Airlines Data Model by making the changes you documented in the fit-gap analysis report. Make the changes in the following order:

    1. Foundation layer of the physical model and the ETL to populate the foundation layer.

    2. Access layer of the physical model and the ETL to populate the access layer.

  5. In a test environment, make a copy of your customized version of Oracle Airlines Data Model. Then, following the documentation you created in Step 2, test the customized version of Oracle Airlines Data Model.

  6. Following your typical procedures, roll the tested customized version of Oracle Airlines Data Model out into pre-production and, then, production.


Keep 'clean' copies of the components delivered with Oracle Airlines Data Model components. This is important when upgrading to later versions of Oracle Airlines Data Model.

See Also:

Before You Begin Customizing the Oracle Airlines Data Model

Before you begin customizing Oracle Airlines Data Model, ensure that certain teams and committees exist.

  • Data warehouse governance steering committee.

  • Implementation team.

  • Fit-gap analysis team. This team consists of business analysts who can identify the business requirements and scope of the Oracle Airlines Data Model and at least some engineers in the Implementation team. Business members of this team must understand logical data modeling so that they can evaluate what changes must be made to the foundation and access layers of the physical model.

After these teams and committees are formed:

  • Discuss the approach and determine the involvement and roles of every party involved in the customization (for example, business and IT).

  • Agree on the scope of the project (that is, agree on what new data must be in the data warehouse and why it is needed).

  • Agree on the timing and the working arrangements.

Prerequisite Knowledge for Implementors

The Oracle Airlines Data Model uses much of the Oracle stack. Consequently, to successfully implement the Oracle Airlines Data Model, the implementation team needs certain prerequisite knowledge.

  • Experience performing information and data analysis and data modeling. (Experience using Oracle SQL Data Modeler, is a plus.)

  • An understanding of the Oracle technology stack, especially data warehouse (Database, Data Warehouse, OLAP, Data Mining, Oracle Business Intelligence Suite Extended Edition)

  • Hands-on experience using:

    • Oracle Database

    • PL/SQL

    • SQL DDL and DML syntax

    • Analytic Workspace Manager

    • Oracle SQL Developer

    • Oracle Business Intelligence Suite Extended Edition Administrator, Answers, and Dashboards

Responsibilities of a Data Warehouse Governance Committee

Governance is of concern to any enterprise, executive team or individual with an interest in the processes, standards, and compliance. It is even more important to organizations that have invested in data warehousing. Data warehouse governance occurs within the context of overall IT governance. It provides the necessary policies, process and procedures, which must be clearly communicated to the entire corporation, from the IT employees to the front-end operational personnel.

Before you customize Oracle Airlines Data Model, setup a data warehouse governance steering committee if one does not exist. The role of this steering committee is to oversee the data warehouse to provide an environment that reaches across the enterprise and drives the best business value.

Data Warehouse Governance Committee: Overall Responsibilities

The data warehouse governance steering committee sets direction and response for the governance framework and covers the follow areas:

  • The entire data warehouse life cycle.

  • Agree on the data to process and make available to end-users.

  • Determine what is the minimum quality criteria for the data that is available to end users and determine how to measure and analyze these criteria against the quality of the data that is the source data for the data warehouse.

  • The business goals of the organization to apply core information from data warehouse.

  • The policies, procedures and standards for data resource and data access.

  • The life cycle of data warehouse component management.

Data Warehouse Governance Committee: Data Governance Responsibilities

The more detailed focus in data warehouse governance is data governance. Data governance tasks include:

  • Approving the data modeling standards, metadata standards and other related standards. This includes determining a metadata strategy and identifying the data modeling tools to use that support these standards.

  • Determining the data retention policy.

  • Designing a data access policy based on legal restrictions and data security rules.

  • Designing a data backup strategy that aligns with the impact analysis to the business unit.

  • Monitoring and reporting on data usage, activity, and alerts.

Managing the Metadata for Oracle Airlines Data Model

Metadata is any data about data and is an important aspect of the data warehouse environment. Metadata allows the end user and the business analyst to navigate through the possibilities without knowing the context of the data or what the data represents.

Metadata management is a comprehensive, ongoing process of overseeing and actively managing metadata in a central environment which helps an enterprise to identify how data is constructed, what data exists, and what the data means. It is particularly helpful to have good metadata management when customizing Oracle Airlines Data Model so that you can do impact analysis to ensure that changes do not adversely impact data integrity anywhere in your data warehouse.

Metadata Categories and Standards

Metadata is organized into three major categories: Business metadata, Technical metadata, and Process Execution metadata.

  • Business metadata describes the meaning of data in a business sense. The business interpretation of data elements in the data warehouse is based on the actual table and column names in the database. Gather this mapping information and business definition and rules information into business metadata.

  • Technical metadata represents the technical aspects of data, including attributes such as data types, lengths, lineage, results from data profiling, and so on.

  • Process execution metadata presents statistics on the results of running the ETL process itself, including measures such as rows loaded successfully, rows rejected, amount of time to load, and so on.

Because metadata is so important in information management, many organizations attempt to standardize metadata at various levels, such as:

  • Metadata Encoding and Transmission Standard (METS): A standard for encoding descriptive, administrative, and structural metadata regarding objects within a digital library.

  • American National Standards Institute (ANSI): The organization that coordinates the US voluntary standardization and conformity-assessment systems.

  • International Organization for Standardization (ISO): The body that establishes, develops, and promotes standards for international exchange.

  • Common Warehouse Metamodel (CWM): A specification, released and owned by the Object Management Group, for modeling metadata for relational, non-relational, multi-dimensional, and most other objects found in a data warehousing environment.

When you implement your metadata management solution, reference your data warehouse infrastructure environment and make the decision about which standard to follow.

Working with a Metadata Repository

You manage metadata using a Metadata Repository. At the highest level, a Metadata Repository includes three layers of information.

The layers are defined in the following order:

  1. A Physical layer: This metadata layer identifies the source data.

  2. A Business Model and Mapping layer: This metadata layer organizes the physical layer into logical categories and records the appropriate metadata for access to the source data.

  3. The Presentation layer: This metadata layer exposes the business model entities for end-user access.

The first step in creating a Metadata Repository is to scope your metadata management needs by:

  • Identifying the metadata consumers. Typically, there are business consumers and technical consumers.

  • Determine the business and technical metadata requirements.

  • Aligning metadata requirements to specific data elements and logical data flows.


  • Decide how important each part is.

  • Assign responsibility to someone for each piece.

  • Decide what constitutes a consistent and working set of metadata

  • Where to store, backup, and recover the metadata.

  • Ensure that each piece of metadata is available only to those people who need it.

  • Quality-assure the metadata and ensure that it is complete and up to date.

  • Identify the Metadata Repository to use and how to control that repository from one place

After creating the metadata definitions, review your data architecture to ensure you can acquire, integrate, and maintain the metadata.

As the data keeps on changing in your data warehouse day by day, update the Metadata Repository. When you want to change business rules, definitions, formulas or process (especially when customizing the Oracle Airlines Data Model), your first step is to survey the metadata and do an impact analysis to list all of the attributes in the data warehouse environment that is affected by a proposed change.

Browsing the Metadata Repository Supplied With Oracle Airlines Data Model

To customize the Oracle Airlines Data Model, you must understand the dependencies among components, especially how the report KPIs are mapped to the physical tables and columns.

Oracle Airlines Data Model provides a tool called the "OADM Metadata" browser that helps you discover these dependencies. When you install Oracle Airlines Data Model with its sample reports, the metadata browser is delivered as a sample Dashboard in the webcat.

To browse the metadata repository:

  1. In the browser, open the login page at http://servername:9704/analytics where servername is the server on which the webcat is installed.

  2. Log in with username of oadm, and provide the password.

  3. Select the Metadata Browser dashboard.

  4. Use the tabs in the Metadata browser to explore the metadata:

    • Measure-Entity tab

      On the Measure-Entity tab you can see the business areas (relational, OLAP, mining), the measures description, corresponding formula, responsible entities, and attributes for the measure.

      To browse the data, select the business area and measure description that you are interested in.

    • Entity-Measure tab

      Using the Entity-Measure tab, you can discover the mappings between entities, attributes, supported measures, and calculations of the measures. You can discover information about particular entities and attributes.

      For example, perform the following steps to learn more about an entity:

      1. Select the entity.

      2. Click GO.

    • Program-Table tab

      Using the Program-Table tab you can browse for information on the intra-ETL mappings and report information. Perform the following steps:

      1. Select the program type (that is, intra-ETL or report) and program name.

      2. Select GO.

    • Table-Program tab

      By default when you go to the Table-Program tab you see all of the tables used for all the reports.

      To discover what reports use a particular table, you must move a particular table from the right pane to the left (Selected) pane.

      For example, to see the reports that use a particular table, perform the following steps:

      1. In the right pane of the Table-Program tab, select the table.

      2. Move the table to the Selected list on the left by clicking on < (left arrow).

      3. Click OK.

      4. Select GO.

      The reports for the selected table are displayed.

Performing Fit-Gap Analysis for Oracle Airlines Data Model

Fit-gap analysis is where you compare your information needs and airline business requirements with the structure that is available with Oracle Airlines Data Model. You identify any required functionality that is not included in the logical model and the default schema, as well as other modifications that are necessary to meet your requirements. The result of your fit-gap analysis is a customization report which is a brief explanation of the adaptations and adjustments required to customize Oracle Airlines Data Model to fit your airline environment.

The fit-gap analysis team writes the customization report by performing the following steps:

  1. If you have performed previous evaluations, review the documentation from the previous phases, and if necessary add team members with the required business and technical expertise.

  2. Review the data and map your logical entities and data structure with the Oracle Airlines Data Model logical model and schema:

    • Starting from business requirements, questions, and rules, identify any entities and attributes that are not in the Oracle Airlines Data Model.

    • Compare the Oracle Airlines Data Model to your existing application model if have one.

    • Compare the Oracle Airlines Data Model to the OLTP data that you are using as a data source to the Oracle Airlines Data Model warehouse.

  3. Determine the differences between your needs and Oracle Airlines Data Model schema. To help you with this task, produce a list of actions people may take with the system (examples rather than models), and create use cases for appraising the functionality of the Oracle Airlines Data Model Warehouse. Answer the following questions about the differences you find:

    • What differences you can live with, and what must be reconciled?

    • What can you do about the differences you cannot live with?

  4. Identify the changes you must make to the default design of Oracle Airlines Data Model to create the customized warehouse. Identify these changes in the following order:

    1. Physical model. Follow the guidelines outlined in Physical Model Customization.

    2. ETL mapping. Follow the guidelines outlined in ETL Implementation and Customization to identify and design the source-ETL that you must create and to identify and make any changes to the intra-ETL provided with Oracle Airlines Data Model.


    When identifying changes, ensure that the changes meet your security and metadata requirements.

  5. Write the customization report, detailing what changes are required to make the Oracle Airlines Data Model match your business needs. This includes any additions and changes to interfaces to existing systems.

  6. Based on the customization report, update the Project Plan and perform the steps outlined in "Implementing an Oracle Airlines Data Model Warehouse".