Dimensional Modeling Exercise

An airport would like to set up a data warehouse as an Airport Information System. As a consultant, you are called upon to support the client in the data modeling phase of the project.

The airport has two terminals (1 and 2) with gate areas A and B in terminal 1, and gate areas C, D, and E in terminal 2.

All take-offs and landings should be recorded and made available for evaluation. The following information is needed for the evaluations:

            Airline (AA, DL, UA, LH, etc.)

            Flight number (DL234, UA822, etc.)

            Aircraft type (B737, B747, A310, etc.)

            Originating and Destination Airports

            Gate (A01, C14, etc.)

Date, time, day of the week, flight plan (summer or winter), and the hour (00:00 – 00:59, 01:00 – 01:59, etc.) are required as time characteristics.

When carrying out evaluations on the aircraft, the aircraft weight, maximum number of passengers, and freight capacity should be displayed.

The following queries should be supported by the data model:

How many take-offs and landings have been undertaken on a Monday, for example, in the last year?

What was the capacity load on an individual aircraft or individual airline carrier with respect to passenger and freight volumes?

What was the passenger throughput per gate or terminal?

How many tons of freight have been carried in the last 12 months?

What were the fixed and variable costs, and the total cost for an airline per month for flights to Germany and other countries?

Develop a dimensional schema for the Airport Information System.

Airline Flight Schema – Discussion Questions

Draw the flight dimensional schema that was discussed in class (the Airline Dimensional Modeling exercise was handed out in class; I’ve also posted it on D2L under Week 5). Identify the attributes of the 6 dimensions (Time, Airline, Airport, Gate, etc.). In addition to the attributes that have been explicitly stated in the problem, make sure to include attributes based on your knowledge of the air travel domain. For drawing the diagram, use Excel. If you use any other drawing tool (like Visio), make sure to convert it into a pdf file.

Develop a monthly snapshot schema (diagram) based on the flight schema. The snapshot should cover all flights that took off from a given airport for each airline (e.g., United, American, etc.). Make sure to include summary facts in the snapshot schema and suppress (add) non-relevant (relevant) dimensions. Show all the keys and attributes of the fact table and the dimensional tables.

What is the grain size of the snapshot fact table? (Be specific; you can explain the grain size using an example.)