Search code examples
dimensional-modeling

DM and hierarchies - dimensions for future use

My very first DM so be gentle..

Modeling a hierarchy with ERD as follows:

enter image description here

Responses are my facts. All the advice I've seen indicates creating a single dimension (say dim_event) and denormalizing event, department and organization into that dimension:

enter image description here

What if I KNOW that there will be future facts/reports that rely on an Organization dimension, or a Department dimension that do not involve this particular fact?

It makes more sense to me (from the OLTP world) to create individual dimensions for the major components and attach them to the fact. That way they could be reused as conformed dimensions.

enter image description here

This way for any updating dimension attributes there would be one dim table; if I had everything denormalized I could have org name in several dimension tables.

--Update--

As requested:

An "event" is an email campaign designed to gather response data from a specific subset of clients. They log in and we ask them a series of questions and score the answers. The "response" is the set of scores we generate from the event.

So an "event" record may look like this:
name: '2019 test Event'
department: 'finance'

"response" records look something like this:
event: '2019 test Event'
retScore: 2190
balScore: 19.98

Solution

  • If your organization and department are tightly coupled (i.e. department implies organization as well), they should be denormalized and created as a single dimension. If department & organization do not have a hierarchical relationship, they would be separate dimensions.

    Your Event would likely be a dim (degenerate) and a fact. The fact would point to the various dimensions that describe the Event and would contain the measures about what happened at the Event (retScore, balScore).

    A good way to identify if you're dealing with a dim or a fact is to ask "What do I know before any thing happens?" I expect you'd know which orgs & depts are available. You may even know certain types of recurring events (blood drive, annual fundraiser), which could also be a separate dimension (event type). But you wouldn't have any details about a specific event, HR Fundraiser 2019 (fact), until one is scheduled.

    A dimension represents the possibilities, but a fact record indicates something actually happens. My favorite analogy for this is a restaurant menu vs a restaurant order. The items on the menu can be referenced even if they've never been ordered. The menu is the dimension, the order is the fact.

    Hope this helps.