Travel industry example

View previous topic View next topic Go down

Travel industry example

Post  guru_new on Thu Mar 29, 2012 2:38 pm

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!

guru_new

Posts : 5
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Travel industry example

Post  ngalemmo on Thu Mar 29, 2012 3:04 pm

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)
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Travel industry example

Post  guru_new on Fri Mar 30, 2012 10:36 am

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)

guru_new

Posts : 5
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Travel industry example

Post  ngalemmo on Fri Mar 30, 2012 1:22 pm

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...
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Travel industry example

Post  guru_new on Fri Mar 30, 2012 2:32 pm

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..


guru_new

Posts : 5
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Travel industry example

Post  hkandpal on Fri Mar 30, 2012 5:07 pm

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Travel industry example

Post  guru_new on Fri Mar 30, 2012 5:31 pm

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.

guru_new

Posts : 5
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Travel industry example

Post  hkandpal on Fri Mar 30, 2012 6:04 pm

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


hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Travel industry example

Post  ngalemmo on Fri Mar 30, 2012 6:30 pm

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Travel industry example

Post  guru_new on Tue Apr 03, 2012 9:35 am

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.

guru_new

Posts : 5
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Travel industry example

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum