Multivalued attributes for dimension

View previous topic View next topic Go down

Multivalued attributes for dimension

Post  BIDW on Wed Jan 28, 2015 12:52 am

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!


BIDW

Posts : 25
Join date : 2015-01-18

View user profile

Back to top Go down

Re: Multivalued attributes for dimension

Post  ron.dunn on Wed Jan 28, 2015 4:12 am

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.

ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

Re: Multivalued attributes for dimension

Post  BoxesAndLines on Wed Jan 28, 2015 10:12 pm

The flatter the better.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Multivalued attributes for dimension

Post  nick_white on Thu Jan 29, 2015 3:30 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Multivalued attributes for dimension

Post  BIDW on Thu Jan 29, 2015 3:43 am

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.

BIDW

Posts : 25
Join date : 2015-01-18

View user profile

Back to top Go down

Re: Multivalued attributes for dimension

Post  ngalemmo on Thu Jan 29, 2015 6:01 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multivalued attributes for dimension

Post  BIDW on Mon Feb 02, 2015 1:14 am

This makes it clear. Thank you so much.

BIDW

Posts : 25
Join date : 2015-01-18

View user profile

Back to top Go down

Re: Multivalued attributes for dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum