master-detail scenarios

[ebk]

I'm wondering about the proper way to model master-detail situations.

    We have a master-detail situation involving trips, with several expenses per trip.  Many of our queries are counts at the trip level, and many of the queries are for totals of trip expenses.
  This seems to be a general situation that always occurs with master-detail situations. They invite queries at both levels - ie:

counts at the master level sums at the detail level

How many orders for blue shirts? What's the total revenue for orders of blue shirts?
How many trips in July for women over 40? How much was spent on trip meals in July by women over 40?

So do we use:
2 fact tables, one at the trip level, one at the trip expense level, with conformed dimensions relating to both of them?
or
1 fact table, with the 'master' fields pushed down to the trip expense level?

I see that either level of query can be answered with one fact table, with the fact at the detail level (ie, order line, trip expense line) - you can do a count(distinct ..) on the natural key of order_no or the trip_id to answer the master-level questions. But I just want to count trips -- wouldn't grinding through all those trip expenses with count(distinct...), be wayyyyy slower than having 2 separate fact tables?

Insight much appreciated...

[ngalemmo]

Unless there are specific measures that can only be captured at the master level, such situations are modeled using a single fact table. A count(distinct) won't take that long.

[ebk]

Trips have 1 or more appointments. eg. I go on a trip to see a dentist, an eye doctor, and a GP. Appointments have nothing to do with travel costs - the source system does not apportion part of the overall trip cost for the dentist appt, part of the trip cost for the GP appt., etc. However, counts are important -- eg. how many trips originating in Wisconsin in 2010 involved dentists?

This seems to lend itself nicely to a 2-star situation, with the Appointment dimension only related to the 'master-level' Trip star. Seems to me that cost measures in the Trip star are semi-additive - they're not additive on the Appointment dimension, but they would be fully additive on the other dims. (eg, date, location, client.....). Counts would be fully additive on the Appointment dim.

My original question about 2 stars was for performance purposes.

This question is regarding the additional relationship (Appointments) that only pertains to the 'master' level (Trips).

Comments on my design musings?

[Sponsored content]