Travel industry example

Refering to the DW toolkit book for the airline or transportation industry, the fact table uses the trip and segment data to record the information. If users would like to analyze on airports that may or maynot be part of a trip and/or segment, how would we achieve that in the fact table.

Example:

Trip 1 SFO to DEN could have SFO - DFW - ATL - DEN (This would be three records in the fact SFO - DFW, DFW - ATL, ATL - DEN)
Trip 2 SFO to DEN could have SFO - DFW - HOU - ATL - DEN (This would be four records in the fact SFO - DFW, DFW - HOU, DFW - ATL, ATL - DEN)
Trip 3 LAX to NYC could have LAX - DFW - DEN - NYC (This would be three records in the fact LAX - DFW, DFW - DEN, DEN - NYC)
Trip 4 LAX to BOS could have LAX - HOU - DFW - NYC (This would be three records in the fact LAX - HOU, HOU - DFW, DFW - NYC)
Trip 5 LAX to BOS could have LAX - HOU - DEN - NYC (This would be three records in the fact LAX - HOU, HOU - DEN, DEN - NYC)

Users would like to analyze information between DFW and DEN which should result in the Trip 1,2 and 3 only.

With the way the Segment Level Flight Activity Fact is set up as shown below, the queries will result only in Trip 3 if I selected the segment and ignore all others.

Scheduled Departure Date Key (FK)
Scheduled Departure Time Key (FK)
Actual Departure Date Key (FK)
Actual Departure Time Key (FK)
Frequent Flyer Key (FK)
Frequent Flyer Profile Key (FK)
Segment Origin Airport Key (FK)
Segment Destination Airport Key (FK)
Trip Origin Airport Key (FK)
Trip Destination Airport Key (FK)
Flight Key (FK)
Aircraft Key (FK)
Class Key (FK)
Fare Basis Key (FK)
Sales Channel Key (FK)
Itinerary Number (DD)
Ticket Number (DD)
Segment Sequence Number (DD)
.... measures...

FYI There could be more than 50 segments within a trip.

Thank you for all your help!

You would want to identify itineraries that either arrive or depart from DEN and arrives or departs from DFW. You would then do analysis on those itineraries (direction, stops between, etc). Finding itineraries is a self-join query against the fact. A simplified version of a query to find the itinaries would be:

SELECT DISTINCT den.itinarary_number
FROM segments den,
segments dfw
WHERE den.itinarary_number = dfw.itinarary_number
AND (den.segment_origin = DEN or den.segment_destination = DEN)
AND (dfw.segment_origin = DFW or dfw.segment_destination = DFW)

Thank you for your suggestions, but the results would give me information about the entire trip/itinerary.

My desired result should be information only about the segments that I am trying to analyze and not the entire itinerary

For trip1, we want information about segments, DFW-ATL and ATL-DEN (2 Rows), for trip 2 DFW-HOU, HOU-ATL and ATL-DEN (3 rows) and trip 3 DFW-DEN (1 row)

With the way the Segment Level Flight Activity Fact is set up as shown below, the queries will result only in Trip 3 if I selected the segment and ignore all others.

My suggestion was to resolve this issue. It will give you a list of all the trips you need to examine. I assumed you could take it from there. You want the segments in those trips that start or end in DEN or DFW. Its not that hard but will require a process with multiple passes, think it through...

I agree, that there will have to be multiple passes, but I am trying to decide if the table may need to be modified or create another table that would store the trip and all the airports in that trip as one row.

If that would be a good route or not..

Example

Create a table that will hold the following

Itinerary Number (DD)
All Airports in Itinerary (Text delimited with pipe)

The above table will be joined to the Fact table and queries will have to be built using the All Airports in Itinerary.

OR

I am complicating it way too much and there may be a simpler solution..

Hi,

it will e better if you have a trid_id stored as it will help you to analyze information related to trips or you could use the ticket number (i think in airline industry it is called PNR ).

thanks

I can get the trip information easily, but we want the information about the segments within the trips.. Like ngalemmo suggested, we will have to do multiple passes to get that information, I am trying to see if there are more options.

Hi,

you will have to do a self join and get the information, any idea how many stop over's will be there, if you know the maximum then may be you can flatten the data.

thanks

guru_new wrote:I agree, that there will have to be multiple passes, but I am trying to decide if the table may need to be modified or create another table that would store the trip and all the airports in that trip as one row.

If that would be a good route or not..

Example

Create a table that will hold the following

Itinerary Number (DD)
All Airports in Itinerary (Text delimited with pipe)

The above table will be joined to the Fact table and queries will have to be built using the All Airports in Itinerary.

OR

I am complicating it way too much and there may be a simpler solution..

The table is fine, you would use the list of itineraries to pull the desired trips and then find the min and max segment numbers of segments (assuming these numbers are assigned chronologically) that touch either airport. Use the itinerary and min/max segment numbers to pull the specific rows you actually need into a temp table for further analysis. You could create the temp table in one statement by embedding selects into FROM clauses or IN expressions. It may or may not run very well depending on your platform, volumes, and configuration, so you may need to handle each step with temporary tables so you can restart at different points in the process. How often do you expect such queries?

I am building a Business Objects Universe based on this table and the query requests could range from 1 to 20 per day.

Since it is going to be a universe, I do not have the option to create a temporary table etc.. I have been trying to use the min and max segment number logic but still have some isses, as the segment number for the originating airport could range from 1 to 10 and the segment number for the destination airport could range from 1 to 10.