Multivalued attributes for dimension

Dear All,

I have a course dimension table which has one record for every course each term.

Now the course dimension has an attribute called course level. Each course could be offered in more than one level.

Similarly the course dimension has another attribute called course grading mode. Each course could have more than one acceptable grading mode.

What are the best practices for modelling such a relationship. Thanks Guys!

It depends on your requirements.

If there is a requirement to report on, say, the relationship between course enrolment and pass rate at a given level, then you'll have to keep level as a separate dimension.

If you only ever report on level as a further breakdown of course, then it is represented as an hierarchy in one dimension.

(I suspect you'll have the former case)

Ron.

The flatter the better.

Hi - if an attribute is not 1:1 with a dimension then I would look at whether it is an attribute purely for that dimension.

When you say "Each course could be offered in more than one level" does that mean that an instance of the course can only be at one level (i.e. it's level 1 when it's run this semester but it will be level 2 when it's run next semester)? Does it only have meaning in the context of the student taking the course (i.e. for Student A it is level 1 but for Student B taking the same course at the same time it is Level 2)?

If you can answer these types of questions it will probably help you understand how to model the attributes

Thanks Nick. For example lets take a course Computer Programming 101, this course is offered at Bachelor level and Associate Bachelor level. Student A takes this course at bachelor level and student B takes this course at applied bachelor level. In my fact table I have level code available to me. My idea was instead of having a separate dimension for level why I cannot have level as part of my course dimension. Similarly I have grading mode, instead of having  a separate dimension for grading mode why I cannot attach grading mode to the course dimension. one course can have more than 1 acceptable grading modes. exactly same as the level problem i discussed.

Thanks for your help. Also I have read this article on kimball below, can you please explain me what it is. Is this the same issue I am facing. The only reason I have confused is because it says many to one but in my case I think it is one to many. One course part of many levels. Makes sense?

Rule #6: Resolve many-to-one relationships in dimension tables.

Hierarchical, fixed-depth many-to-one (M:1) relationships between attributes are typically denormalized or collapsed into a flattened dimension table. If you’ve spent most of your career designing entity-relationship models for transaction processing systems, you’ll need to resist your instinctive tendency to normalize or snowflake a M:1 relationship into smaller subdimensions; dimension denormalization is the name of the game in dimensional modeling.

It is relatively common to have multiple M:1 relationships represented in a single dimension table. One-to-one relationships, like a unique product description associated with a product code, are also handled in a dimension table. Occasionally many-to-one relationships are resolved in the fact table, such as the case when the detailed dimension table has millions of rows and its roll-up attributes are frequently changing. However, using the fact table to resolve M:1 relationships should be done sparingly.

The reason you do not have level as part of the course dimension is because level has nothing to do with the course. It has to do with how the student enrolled in the course. It is a context of the enrollment and would be reflected as a dimension of the enrollment fact.

By corrupting the course dimension with non-related attributes, the course dimension no longer represents a course and cannot serve as a conformed dimension for other facts.

If the enrollment has a handful of attributes that do not relate to existing conformed dimensions, such as level and grading mode, standard practice is to place these in a type 4 dimension (formerly known as a junk dimension).

What Ralph is talking about in the excerpt is doing things such as placing a brand attribute in a product dimension. In a fully normalized model, brand would be a separate entity with a 1:M relationship to products. Rather than snowflake, you denormalize the product dimension by adding brand.

In your case there is no relationship between course and level.

This makes it clear. Thank you so much.