question on creating time serise type of datawarehouse fac.
- From: light_wt <lightwt@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 16 Jun 2009 14:45:02 -0700
i need some insight on some time analysis query on a transaction table.
queries are liked:
find out the hours (duration) for this month an airplane has been parked at
the gate.
find out the dates when the gate is most occupied by which flight.
find out the local time and date (pacific) and eastern time and date (new
york) when gate is most frequently occupied
and etc.
find out the gate was use for a given holiday.
find out the money generated for a given time.
the query is from a transactional fact table with arrival and departure
information.
create table arrival_departure (
flight_id int
airport_gate_id int
date_time_stamp datetime
rate_for_docking money
)
I am thinking of creating a date_dim table and a time_dim and a view.
the date_dim will have key, day, date, month, hoilday, and etc info. the
time will have minute, hour info for local pacific time. given there is a 4
hours time difference betweeen pacific and eastern time, i am considering to
adding another set of min, hour for eastern time.
the view will have all three columns from arrival_departure table plus a
date_id column reference key of date_dim and a time_id column reference key
of time_dim.
so my questions is I am not sure if it was a good idea to add eastern time
columns to time_dim.
secondly, i still don't have a query to find duration of when a flight_id
was at a airport_gate.
sample data for arrival_departure
flight_id airportgate date_time_stamp
111 1 2009/1/1 0:01
222 2 2009/1/1 0:12 -- arriving
333 3 2009/1/1 1:21
222 2 2009/1/1 1:45 -- leaving
111 1 2009/1/1 1:46 -- leaving
666 1 2009/1/1 1:55 -- arriving
ideally, the arrival_departure would be arranged into this:
fligh_id 111 was there for the hour one and two (from 0:01am to 01:45am on
1/1)
flight_id 222 was there from hour one and hour 2 (from0:12 am to 1:45 am)
date hour duration fligh_id
1/1 1 0:00-1:00 111
1/1 2 1:59-2:00 111
1/1 1 0:00-1:00 222
1/1 2 1:59-2:00 222
1/1 2 1:59-2:00 333
1/1 2 1:59-2:00 666
.
- Prev by Date: time dimensions
- Next by Date: Grand total with calculated measure multiple fact tables
- Previous by thread: time dimensions
- Next by thread: Grand total with calculated measure multiple fact tables
- Index(es):
Relevant Pages
|
