Transfer Courses Fact/Dimensions Design

Dear All,

I have a question related to a good star schema design for a business process which is called as Transfer Courses Credits. The number of credits can be summed up for example by student so we could have a fact table at the center.

What would be a good design for fact and dimensions.

The obvious dimensions related to this process are course,student which obviously makes a good candidate for dimension because it can have lot of attributes.

However my concern is regarding some attributes such as institution, grade mode, term, grade and level. If I make these as indvidual dimensions example dimension table for institution, dimension table for grade mode. Basically I would just be  replicating a validation table in the source system and not really a dimension table simply because the attributes are just too few.

What is a good design in such cases. Thanks for this.

HK

If those attributes are useful for analysis (and I would assume, they are), there are few ways to go about it. One way is to store them as attributes of the transfer course. This means a unique course row for each course, institution and other characteristics. If, however, the 'course' is a generic (not institution specific) description of the allowed course, then the institution information should be a separate dimension and referenced from the fact relating to the transfer. It may be institution is it's own dimension and the other characteristics (grade mode, term, grade, level etc…) be part of a degenerate dimension.

Thanks for your reply. So basically having course, term, grading mode, level, grade, student and institution as a separate dimension would not be a good idea because thats just snow-flaking correct? and not dimensional modelling.

Instead what you suggested makes sense to have course and institution as their own dimensions because the institution would have its own attributes such as country which would be interesting to report on.

Thirdly having a degenerate dimension for attributes such as level, grading mode, grade does make lot of sense to me. However I am not too sure about including term in it because term has got several important attributes such as Fall or Spring, Term Description etc which the users might be interested to report by. Do you think terms goes in as a separate dimension itself and a degenerate dimension with level, grading mode and grade is the appropriate option. Thanks for your help.

Yes, as it has business meaning and its own attributes, term should be its own dimension. A junk dimension is for leftover attributes that have no where else to go.

Thanks. This makes it very clear. I really wanted to get this concept of dimensional modelling clear because of the natural tendency to drift towards normalization in ER. Thanks again.

Hi,

I have one last question please. In the source systems I have validation tables which have things like level code and level description, division code and division description and nothing else which could be useful. Can i create a junk dimension say student term which will have something like this.

LEVEL CODE LEVEL DESCRIPTION DIVISION CODE DIVISION DESCRIPTION.

Basically all possible combinations coming out of a fact table for level and divisions. Thanks for your advise.

Hi ngalemmo,

I just wanted to check with you if it makes sense to create a junk dimension out of course, grade mode, level, grade and some textual indicators. Only term and institution would have their own dimensions.

Thanks for your feedback.

Hi - it is usual practice to denormalise these descriptions back into the relevant dimension where the code is being used; so if Level is an attribute of the Course Dim then you would include both the Level Code and Level Description (there is an argument not to include the Code if it is just an internal code and not something that has business meaning - but I tend to include it and then I have the option whether or not to expose it to my end users)

When you say junk dimension, aren't most/all of these attributes of the Course and therefore shouldn't there be a Course Dimension? If Level and Grade mode apply to a course then they should be attributes of the Course Dim; if, instead, they are attributes of the grading process (along with Grade) then maybe there should be a Grading Dim? What is Division? If it is an attribute of Course then it should be in the Course Dim, etc.

Hope this helps a bit

A junk dimension is for stuff that has no other place to go. For example, status codes relating to a transaction. Things that represent a component of the business get to be dimensions. So, course, department, etc… are usually dimensions is their own right. It may be, as Nick pointed out, you denormalize things a bit and put department as an attribute of course. This may or may not make sense in the bigger picture. For example, if budgets and staffing are managed at a department level, it may make sense that department is its own dimension as it could provide a point of integration when looking at budgets and enrollment.

Thank you Guys for your input. This is certainly extremely helpful but I have some concerns I would like to share with you.

Term, Institution and Grade look good and go into their respective dimensions.

Division is an attribute of course as you rightly pointed.

When it comes to level it is also an attribute of course but the problem is one course could be part of more than one level in a term. Users in the transfer course form in the ERP have the option to select the level from the list of available ones. If I only bring one level for each course in the course dimension there is a problem because the user might have selected a different one in the transfer course form.

Subject Code, Course Number and Course Title also belong to the course dimension but there is a small issue there. Users on the transfer course form in the ERP have the option to enter a course number which may not be part of the course dimension. How do we handle such cases.

Grade Mode is also an attribute of the course dimension. However course dimension is something like this TERM CODE SUBJECT CODE AND COURSE NUMBER FOR EVERY TERM. When it comes to the grade mode there can be more than one acceptable grade mode (2 rows). How do we handle such cases.This is exactly similar to the level issue I mentioned above. If we handle that we can handle this.

Thanks for sharing the best practice.

Thanks.

What are the fact tables?

You need to consider the context in which the dimensions are used rather than simply looking at dimensions themselves.

For example, is 'grade mode' a function of the course or the enrollment in the course? If someone enrolled for a grade, while another enrolled pass/fail, it is not an attribute of the course, but rather a dimension of the enrollment fact (probably degenerate).

Thanks for your reply. The transfer courses is just a faceless fact table. Users are typically interested to see what courses a student has received transfer credits for.

Level and Grade modes are function of the course for which the student has received a transfer credit.

One course can have more than 1 valid acceptable grading modes (typically 2). Similary one course can be associated with more than one level (Typically 2).

Do you think this relationship between course and grading mode and course and level should be resolved in the course dimension itself. Then the factless fact table would simply have foreign keys for term, student, institution and course.

HERE IS THE CURRENT TABLE STRUCTURE/GRAIN FOR COURSE DIMENSION

TERM_CD SUBJECT_CD CRS_NBR
00001 PHYSICS M12
00002 PHYSICS M12
00001 MATH N88
00002 MATH N88

Proposed change

TERM_CD SUBJECT_CD CRS_NBR GRADING_MODE LEVEL
00001 PHYSICS M12 UNV BACHELOR
00001 PHYSICS M12 UNV MASTERS
00001 PHYSICS M12 NOR BACHELOR
00001 PHYSICS M12 NOR MASTERS

Please let me know your thoughts.