I have a question about design of a Fact table in a data warehouse and wanted to see your opinions considering the grain of the data. I'll forego defining the Dimensions and just place the actual values for the dimension key columns for simplicity:

Regardless of how many people are in the party, there is only one trip logged for each reservation; it's generated based on the client. Also, you can see that each passenger has their own fare based on what PassengerType they are. The trip needs to have a trip total fare, but I also need to drill down to the passenger.

My question is whether or not from a design perspective, you think it would be better to create two separate facts (FactPassengers and FactTrips), or is it acceptable from a modeling perspective to include the two totals. My guess is that it should get separated out OR to just use the FactPassengers and design logic to drill down or up based on what is being asked (e.g. if one wants to know the trip totals, select reservationid, count(*) from FactPassenger group by reservationid)

I would prefer keeping them separate as both fares are at different level. I would keep a table at Trip level say FactTrips where it will have all details related to trip in general and also total trip fare. Then i'll have passenger details stored in FactPassengers with FK to FactTrip on TripKey and then have passenger details along with their fares inside it. Any analysis that requires passenger details will use FactPassengers and any analysis which looks at Trip at aggregated level use FactTrips. For drilldown, we will analyse FactPassengers based on tripkey value from FactTrip to get the related passengers

Hi Vis...Thanks for the advice! I've spent most of the evening modeling both ways and definitely see the pitfalls of trying to put them both into the same table. THanks again...it looks like I'll be keeping them separated.

If you are buiding a dataware house and using a dimensional model, I'd suggest you are doing it wrong. the Dimensional Model is fine for cubes and some reporting, but it has too many flaws to be good for a warehouse.

If you are really interested in data warehousing (and assuming you are already at a mid-to-expert level in relational architechtiure), you should take a look at a book called Temporal Data & the Relational Model.

Thanks for the info; I'll check out the book for my own edification. We were instructed to develop data marts as part of a larger, agency-wide enterprise data warehouse effort based on a dimensional model. Still interested in the topic you raised though if for nothing else than my own education.

Thanks for the info; I'll check out the book for my own edification. We were instructed to develop data marts as part of a larger, agency-wide enterprise data warehouse effort based on a dimensional model. Still interested in the topic you raised though if for nothing else than my own education.