September 27, 2007 at 10:55 am
Hi everyone,
I work for an airline and am currently working on logical design for our crew tracking system.
The first and most prevelent issue is the multiple granularities of the atomic data.
Here's a little background...
We have measures that only apply to the entire month or bid period so the dimensions used to slice this would be the crew members and bid period. This data can be rolled up by crew member or by year etc...
Now within the bid period there are pairings which is generally a 4 day period while the crew is on a trip. again there are measures specific to just the pairing. The dimensions would be crew member, bid period, pairing number. These can be rolled up by Bid period, crew member, year etc..
Within a pairing is a duty block this is the set of flights a crew member operates each day during a pairing and the measures are only specific to the whole duty block, can't be broken down. The dimensions that descibe this would be crew member, bid period, pairing number, and block number.
Lastly each duty block contains individual flights. The dimensions that describe the flights would be crew member, bid period, pairing number, block number, and flight number. The flight information can be rolled up to any level above it.
I envision each level (Bid Period, Pairing, Duty Block, Flight) being its own fact table. Does this sound correct?
If the user wants to see flight time, duty block time, pairing time, and vacation used for a particular employee for the year the result would pull an aggregate from each of the 4 tables all using the same dimensions (that apply) to return the data. Again does this sound correct?
I have read some articles from the Kimball group about fact table granularity and I'm not sure if I am looking at "granularity" the right way.
Any thoughts on this?
Thanks for taking the time to read all that.
October 1, 2007 at 10:03 am
I'm not following you completly but you can't have a measure in one facttable with a different rollupstate. Normally I create a different facttable for every rolluplevel. When measures have the same rolluplevel (ie date) those can be in the same facttable.
My english isn't so good that I can understand your question for the 100%, I hope this answer helps you.
-- Fact table 1 --
ID MonthID Value
12 200705 4
14 200705 5
19 200706 6
-- Fact table 2 ---
ID DateID Value
12 1-1-07 34
14 12-1-07 67
19 8-3-07 88
October 5, 2007 at 11:36 am
mhilbrink
thanks for the reply.
After reviewing this a bit more and reading my new Ralph Kimball dimensional modeling book I realized I was overthinking this and kept trying to apply some type of date grain to this. The grain used was basically the natural key to the measure. I used this approach to determine my fact tables, grouping measures by same natural key, and thing just fell into place.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply