Multivalued Dimension - Employee Role

[BIDW]

Hello Guys,

I am modelling a scheduling system for exams. Essentially I have a factless fact table in the center which records a specific schedule in which an exam would take place (unique exam session). This generates dimensions such as time, course, section, term etc. Attached to this event are certain roles which employees play. for e.g. Invigilator, Floater etc. I know that one approach is I could have foreign keys in my fact table for invigilator, floater etc. Thats easy but it does not offer much flexibility. For example if i want to know for a particular employee what roles are they assigned it would not be straight forward. Is this a class multi valued dimension case?

If this is the case what would the bridge table and have and If I am correct I also need to have Role(Invigilator, Floater) as a dimension.

Thanks for the advise.

[BoxesAndLines]

If the roles are limited, just instantiate them like you would different dates to the date dimension.

[ngalemmo]

...however, if you have multiple people for a given role, you would need to use a bridge for that role.

Another approach would be another factless fact table with exam session, role and employee as dimensions.

[Sponsored content]