Results 1 to 3 of 3

Thread: Building Fact table for a data mart

  1. #1
    Junior Member
    Join Date
    Jul 2013
    Posts
    1

    Building Fact table for a data mart

    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?
    SELECT
    DIM_COMPONENT.COMPONENT_KEY,
    DIM_ATTENDANCE.ATTENDANCE_KEY,
    DIM_MODULE.MODULE_KEY,
    DIM_PROGRAM.PROGRAM_KEY,
    DIM_STUDENT_BIODEMO.STUDENT_KEY,
    DIM_STUDENT_BIODEMO.STUDENT_ID,
    DIM_VLE.VLE_KEY,
    DIM_TIME.TIME_KEY
    FROM DIM_ATTENDANCE,
    DIM_COMPONENT,
    DIM_MODULE,
    DIM_PROGRAM,
    DIM_STUDENT_BIODEMO,
    DIM_TIME,
    DIM_VLE
    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.

    Thanks,

    Lina.

  2. #2
    Senior Member vijay's Avatar
    Join Date
    May 2012
    Location
    Hyderabad
    Posts
    515
    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.

Similar Threads

  1. Replies: 0
    Last Post: 03-06-2013, 03:00 PM
  2. star schema fact table population
    By ranip in forum Data Warehouse
    Replies: 0
    Last Post: 12-29-2012, 07:01 PM
  3. How to create a fact table using natural keys
    By ranip in forum Data Warehouse
    Replies: 0
    Last Post: 12-20-2012, 02:35 PM
  4. Granularity level in fact table - Data Warehouse
    By ranip in forum Data Warehouse
    Replies: 0
    Last Post: 08-09-2012, 09:56 AM
  5. Replies: 1
    Last Post: 07-26-2012, 11:16 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •