Help with Complex One-to-Many relationships
2 posters
Page 1 of 1
Help with Complex One-to-Many relationships
I am trying to model a set of dimensional tables for conference events. I am not sure how to properly handle all the permutations of event/sessions/leader/registrant elements. Here are the main relationships in the operational database:
Events can have zero or more Sessions, zero or more Leaders, and zero or more Registrants.
Sessions are always for a specific Event, and they can have zero or more Leaders and zero or more Registrants.
Leaders can be assigned to zero or more Sessions. They have ratings that are associated with a specific Event and Session (if the leader is associated with a session).
Registrants can be assigned to zero or more Sessions.
I have tried a combination of dimensions, fact, and bridges, but nothing seems to fall into place logically and elegantly. I have already divided the event-related information into two tables, an Events dimension and an Events Summary fact. I am on the cusp of doing the same thing with Sessions, but I am still stymied by having Leaders that are not associated with sessions. Their ratings seem like "facts," since they are semi-additive values that have a distinct value for every individual session at every individual event. But by the same token they seem like they belong as part of a "bridge" of leader groupings between the sessions and the leaders (for those leaders of sessions) or between the events and the leaders (for leaders with no session).
Can someone help me untangle this web and see my way to a clear-cut, hopefully intuitive way to put this information into the data warehouse?
Thanks in advance.
Events can have zero or more Sessions, zero or more Leaders, and zero or more Registrants.
Sessions are always for a specific Event, and they can have zero or more Leaders and zero or more Registrants.
Leaders can be assigned to zero or more Sessions. They have ratings that are associated with a specific Event and Session (if the leader is associated with a session).
Registrants can be assigned to zero or more Sessions.
I have tried a combination of dimensions, fact, and bridges, but nothing seems to fall into place logically and elegantly. I have already divided the event-related information into two tables, an Events dimension and an Events Summary fact. I am on the cusp of doing the same thing with Sessions, but I am still stymied by having Leaders that are not associated with sessions. Their ratings seem like "facts," since they are semi-additive values that have a distinct value for every individual session at every individual event. But by the same token they seem like they belong as part of a "bridge" of leader groupings between the sessions and the leaders (for those leaders of sessions) or between the events and the leaders (for leaders with no session).
Can someone help me untangle this web and see my way to a clear-cut, hopefully intuitive way to put this information into the data warehouse?
Thanks in advance.
Geoff- Posts : 4
Join date : 2016-01-07
Re: Help with Complex One-to-Many relationships
Ratings and registration are facts, so it is just a matter of the appropriate dimensionality.
The leader relationship can be handled as a separate dimension key (leader group) supported by a bridge referencing one or more leaders. This would appear in appropriate facts which would also include session and event keys. This allows the specification of leader to be independent of session and event.
The leader relationship can be handled as a separate dimension key (leader group) supported by a bridge referencing one or more leaders. This would appear in appropriate facts which would also include session and event keys. This allows the specification of leader to be independent of session and event.
Simplifying the problem
It is the inconsistency of having uneven bifurcation between the Event and the Session level that is adding most of the complexity, I believe. So I am going to try creating a "placeholder" Session for every Event that will stand in for the Event overall at the same level as the Sessions. That way Leaders and Registrants will attach consistently to the Session level and not at two different levels. It might not solve every wrinkle, but it should go some distance toward simplifying the table relationships.
Geoff- Posts : 4
Join date : 2016-01-07
Re: Help with Complex One-to-Many relationships
Okay, I think I get what you're suggesting. Please see if these tables align with your idea:
Events (dimension with a link to a Leader Groups bridge)
Sessions (dimension with a link to the Events dimension as an outrigger and with a link to a Leader Groups bridge)
Registrations (fact, one row per registrant per event per session (placeholder Sessions Key value of 0 for registrations to the event itself and not to a session))
Leader Ratings (fact, one row per leader per event per session (placeholder Sessions Key value of 0 for leaders with no session assignment))
Session Ratings (fact, one row per session)
Events (dimension with a link to a Leader Groups bridge)
Sessions (dimension with a link to the Events dimension as an outrigger and with a link to a Leader Groups bridge)
Registrations (fact, one row per registrant per event per session (placeholder Sessions Key value of 0 for registrations to the event itself and not to a session))
Leader Ratings (fact, one row per leader per event per session (placeholder Sessions Key value of 0 for leaders with no session assignment))
Session Ratings (fact, one row per session)
Geoff- Posts : 4
Join date : 2016-01-07
Re: Help with Complex One-to-Many relationships
Basically, yes. You don't need to create a 'not applicable' session for each event. You should be able to get away with one. Inclusion of session information in the query should be more than sufficient to qualify things. Also, if you use mutually exclusive surrogate keys for all your dimensions (i.e. use one sequence) you could get away with a single bridge table that is populated with both session/leader and event/leader relationships.
Re: Help with Complex One-to-Many relationships
I reconsidered the design, moving all the one-to-many values that I had modeled as bridges into facts. Given the ways in which the data will be analyzed, I think this will make more sense in the end, and it will certainly be easier for the ETL process. Below is a complete set of tables for the Events Data Mart, including some values I haven't mentioned before:
Dimensions
Credit Categories
Dates (a role-playing dimension, of course, with roles for multiple dates throughout the other dimensions and facts)
Events
Event Fee Types
Event Registration Information (a junk dimension for various data related to the each event registration)
Event Sessions (with an outrigger for Events; this could be in essence a "factless fact" for reporting on the sessions per se)
Firms (a role-playing dimension with roles for Exhibitor, Vendor, and Facility)
Individuals (a role-playing dimension with roles for Leader, Registrant, et al.)
Facts
Event Credits (credit categories and hours applied directly to sessions have a Sessions Key <> 0, and others have a Sessions Key = 0)
Event Exhibitors (a "factless fact" connecting multiple exhibitors per event)
Event Fee Billing
Event Fee Payments
Event Leaders (session leaders have a Sessions Key <> 0, and others have a Sessions Key = 0)
Event Registrations (session registrations have a Sessions Key <> 0, and event-only registrations have a Sessions Key = 0)
Event Summary (an accumulating fact for event-level dates, ratings, and other data)
Event Vendors (a "factless fact" connecting multiple vendors per event)
Dimensions
Credit Categories
Dates (a role-playing dimension, of course, with roles for multiple dates throughout the other dimensions and facts)
Events
Event Fee Types
Event Registration Information (a junk dimension for various data related to the each event registration)
Event Sessions (with an outrigger for Events; this could be in essence a "factless fact" for reporting on the sessions per se)
Firms (a role-playing dimension with roles for Exhibitor, Vendor, and Facility)
Individuals (a role-playing dimension with roles for Leader, Registrant, et al.)
Facts
Event Credits (credit categories and hours applied directly to sessions have a Sessions Key <> 0, and others have a Sessions Key = 0)
Event Exhibitors (a "factless fact" connecting multiple exhibitors per event)
Event Fee Billing
Event Fee Payments
Event Leaders (session leaders have a Sessions Key <> 0, and others have a Sessions Key = 0)
Event Registrations (session registrations have a Sessions Key <> 0, and event-only registrations have a Sessions Key = 0)
Event Summary (an accumulating fact for event-level dates, ratings, and other data)
Event Vendors (a "factless fact" connecting multiple vendors per event)
Geoff- Posts : 4
Join date : 2016-01-07
Similar topics
» Complex Inventory Model
» Accumulating Snapshot in case of complex pipelines
» Complex Bridge Table
» Same attribute in multiple dimensions or Create new dimension?
» How to model a complex region dimension
» Accumulating Snapshot in case of complex pipelines
» Complex Bridge Table
» Same attribute in multiple dimensions or Create new dimension?
» How to model a complex region dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum