master-detail scenarios
2 posters
Page 1 of 1
master-detail scenarios
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...
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...
ebk- Posts : 3
Join date : 2012-07-03
Re: master-detail scenarios
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.
....well, there IS something particular to the master level.....
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?
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?
ebk- Posts : 3
Join date : 2012-07-03
Similar topics
» Reporting at all levels master detail
» the master detail tables facts
» Combining master and history tables to fact
» Modeling invoice detail with rebate detail
» Snowflake scenarios
» the master detail tables facts
» Combining master and history tables to fact
» Modeling invoice detail with rebate detail
» Snowflake scenarios
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum