attribute on fact table or dimension table?

View previous topic View next topic Go down

attribute on fact table or dimension table?

Post  abyss on Mon Jun 03, 2013 9:36 pm

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

abyss

Posts : 8
Join date : 2013-05-27

View user profile

Back to top Go down

Re: attribute on fact table or dimension table?

Post  ngalemmo on Tue Jun 04, 2013 4:11 am

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.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

thanks for your reply

Post  abyss on Wed Jun 05, 2013 2:37 am

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

abyss

Posts : 8
Join date : 2013-05-27

View user profile

Back to top Go down

Re: attribute on fact table or dimension table?

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