If there is a flight transportation data mart, and you have a fact table for the itinerary, another fact table for the trip, and a third for the individual flight segment, must you create 3 separate contexts (I’ve read all about the 1 context per fact table rule)?
These 3 fact tables could probably have all been part of one large fact table, but then the measures for the parent levels (itinerary, trip) would be repeated, and not additive at the lowest level (segment). The thing is, each has different dimensionality.
[list]Itinerary Fact => Frequent Flyer Key, Travel Agency, Airline Office, Booking Date, Itinerary ID (degenerate dimension, key for this fact table)
Trip Fact => Itinerary ID (degenerate dimension - foreign key to Itinerary Fact), Trip ID (degenerate dim, key for this fact table), Location Group, Trip Start Date, Trip End Date
Segment Fact => Itinerary ID, Trip ID, Location Group - all degenerate dimensions plus the Location Group, here to avoid a join if possible, Segment Start Date, Segment End Date[/list]
Do I really need 3 contexts in this case - or can I avoid this golden rule?
Thanks
Nile
Nile (BOB member since 2004-02-12)