comparing fact tables of different granularity

Question

I have fact tables describing [events] and [event_segment] and [event_staff].

[events] are composed of many [event_segment] - e.g. 'introduction', 'pre-check', '1st stage', '2nd stage' etc. The key (i.e. granular unique id) for [events] is [event_ref], however the key (i.e. unique identifier) for [event_segment] is composite - [event_ref] and [segment_order]

[event_staff] has many staff to 1 event, so the unique id is composite of [event_ref] and [staff_id]

There are several conformed dimensions - e.g. day, location etc.

I am struggling to decide on a model for this data, as I want to allow easy comparison of aggregated facts across the 3 tables, particularly in reporting. i.e. count([staff]) vs sum([event_segment_duration]) where [staff_type] = 'basic' and [event_segment_type] != 'clean up'

Because there are many staff attending an event, many segments to an event, but I cannot tie staff to a segment I have to group on [event_ref].

This 'works', but does it count as breaking Kimball/Data warehouse standards as it is joining (well grouping on) events?

Also, should [event_ref] exist in [event_segment] table, as it should be surrogate key, and therefore not available to group on?

Answer 1

I don't think you are breaking any standard.

It just happened to you that you have two additional fact tables with a different level of granularity.

In my opinion you can consider the segment like a child of the event, so you can consider segment part of the event dimension.

The staff is just a different dimension (never a good idea to split a dimension to multiple children) and it happens that the lowest level of the staff dimension doesn't meet the lowest level of the event one.

This is a normal real word scenario and it's quite frequent, you can imagine a football team, you can have some game stats associated to Players (children), some associated to the Team (parent), but also some other information like tickets sold for each Game (different dimension) that cannot be associated to a specific player, but the for sure have more detail than just at Team level.