Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

attribute on fact table or dimension table?

2 posters

Go down

attribute on fact table or dimension table? Empty attribute on fact table or dimension table?

Post  abyss 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

Back to top Go down

attribute on fact table or dimension table? Empty Re: attribute on fact table or dimension table?

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

ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

attribute on fact table or dimension table? Empty thanks for your reply

Post  abyss 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

Back to top Go down

attribute on fact table or dimension table? Empty Re: attribute on fact table or dimension table?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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