Hi, I am currently scripting a data mart as we currently do not have etl tool. i have built all of my dimensions which contain their own key eg student table has a student_key created by a nextval sequence, attendance table has an attendance_key created by a nextval sequence etc. I am now at the stage where i can build my fact table but im not sure how to go about it. I thought i would be able to select all of the keys from all dimensions and join on student id and termcode which exist in all dimensions except the time dimension. i have created an insert script in pl/sql which loads 1000 records at a time, but my data set is so huge i am not able to create it.
My questions are:
1. is this the correct way to create a fact table?
WHERE DIM_STUDENT_BIODEMO.STUDENT_ID = to_cHAR(DIM_ATTENDANCE.STUDENT_ID)
AND (DIM_STUDENT_BIODEMO.STUDENT_ID = DIM_COMPONENT.STUDENT_ID)
AND (DIM_STUDENT_BIODEMO.STUDENT_ID = DIM_MODULE.STUDENT_ID)
AND (DIM_STUDENT_BIODEMO.STUDENT_ID = DIM_PROGRAM.STUDENT_ID)
AND (DIM_STUDENT_BIODEMO.STUDENT_ID = DIM_VLE.STUDENT_ID)
AND (DIM_STUDENT_BIODEMO.TERMCODE = DIM_MODULE.TERMCODE)
AND (DIM_STUDENT_BIODEMO.TERMCODE = DIM_COMPONENT.TERM_CODE)
AND (DIM_STUDENT_BIODEMO.TERMCODE = DIM_ATTENDANCE.TERMCODE)
AND (DIM_STUDENT_BIODEMO.TERMCODE = DIM_PROGRAM.TERMCODE)
AND (DIM_STUDENT_BIODEMO.TERMCODE = DIM_VLE.TERM_CODE)
AND (DIM_TIME.TERMCODE = DIM_STUDENT_BIODEMO.TERMCODE)
and DIM_STUDENT_BIODEMO.TERMCODE = '201213';
2. Should i be inserting into thwe fact table one dimension at a time? and inserting '-1' where the keys do not exist?
Any help or advice would be much apprweciated.
Join the fact table directly with the all the dimension tables. If you are maintaining the missing dimension keys in the fact table itself, then do a left outer join.