CS614 Data Warehousing Assignment 3 Solution Spring May 2013

Scenario:
Suppose the airline company wants to store following information:

• Flight departure/arrival status i.e. either flight departed/arrived late or in time.
• Flight type i.e. either flight is connected or direct.

For storing the departure/arrival status a new table with the name “FlightADStatus” is created with following structure and data:

StatusID	StatusName	Description
01	OnTime	This status is set if flight departs or arrives as per schedule
02	Late	This status is set when the flight departs/arrives behind the specified schedule
03	NotDecided	This status is set when the flight is schedule first time. After the flight departs/arrives, the status is modified

For flight types, a master table with the name “Flight Type” is also created with following structure and data:

FTypeID	FTypeName	Description
01	Direct	A flight is direct when it moves directly from source to destination location
02	Connected	A flight is connected with moves indirectly from source to destination involving multiple legs.

For your convenience the modified ERD (with more abstraction level) is given below:
The requirement of airline is to find out total number of connected or direct flights that departed late or in time with in a certain time period.
A sample result will be:
“Total number of direct flights that departed late with in month of November”
“Total number of connected flights that departed in time with in month of October”
“Total number of direct flights that departed in time with in month of October”
For this purpose, organization defines a new business process named “Flight_Status_Aggregate”.
Question: [Marks 20]
There are four steps of dimensional modeling. First step is to identify the business process. Consider the “flight_Status_Aggregate” process described above. Apply the remaining steps of dimensional modeling to find out the fact and dimensional tables.
Note:                                            

• You are required to consider the ER Model given in this assignment and ignore the one developed by you in last assignments.

• Be specific and provide to the point answer. Avoid irrelevant details and definitions.

• Carefully read the scenario and the steps of dimensional modeling before attempting assignment.