attribute on fact table or dimension table?

[abyss]

Hi All
I am working on a project for university. and our university just had a faculty reconstruction, all the students will form into new faculties base on their course and the majors. for the double degree courses, they may have 2 faculties. we mainly analysis data base on the new faculty however we still need to analysis the students' data base on old faculty every now and then.
we have a confirmed dimension table called student_course, this table record each student enrolls in what course. we also have a confirmed faculty table.
now I am having a disagreement with my team numbers, the argument is they think the faculty attributes (new faculty student belongs and old faculty student belongs) should be in the fact table because it is easier for the business user to use the data mart, easier to join. I think they should be in student_course dimension table, the reasons why are:
1. They are fixed values, change very slowly.
2. Reduce number of attributes in fact table.
3. I don't think its much harder for the business user to use it.
4. They are reusable, if we create another model involve student_course_faculty information, we can just put the confirmed student_course dimension table there, don't need to recalculate those values.
Thanks
Howard

[ngalemmo]

No, the perceived value of placing flags in the fact is that is improves performance.

In reality, such a solution will often underperform a strict dimensional model.

The reason for this is if those flags were in a dimension table, the table would be very small. Every modern database would hold these in memory. The join does not introduce as much overhead as one would think. On the other hand, placing the flags in the fact table makes the table larger. A few bytes per row add up, and increase the disk reads, which are much, much slower that memory reads.

If you are worried about ease of use, create a view.

[abyss]

thanks ngalemmo
i just want to double check it with you:
we should put all faculty information into student_course dimension table: faculty_1_name, faculty_2_name, old_faculty_1_name, old_faculty_2_name.

so performance will be much better than put faculty_keys on fact table. am i right?

regrads
howard

[Sponsored content]