1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to Populate a fact table from dimension tables.Kindly reply.

Discussion in 'SQL PL/SQL' started by Ranger, Jan 10, 2009.

  1. Ranger

    Ranger Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Basically my aim is there are 5 dimensional tables that are created

    Student->s_id primary key,upn(unique pupil no),name
    Grade->g_id primary key,grade,exam_level,values
    Subject->sb_id primary key,subjectid,subname
    School->sc_id primary key,schoolno,school_name
    year->y_id primary key,year(like 2008)

    s_id,g_id,sb_id,sc_id,y_id are sequences

    Code (Text):

    select * from student;
    S_ID UPN FNAME COMMONNAME GENDER DOB
    ==============================
    9062 1027 MELISSA ANNE       f  13-OCT-81

    ...

    9000 rows selected
     
    Code (Text):

    select * from grade;
          G_ID GRADE      E_LEVEL         VALUE
    ---------- ---------- ---------- ----------
            73 A          a                 120
            74 B          a                 100
            75 C          a                  80
            76 D          a                  60
            77 E          a                  40
            78 F          a                  20
            79 U          a                   0
            80 X          a                   0
    ...
    18 rows selected
     
    These are basically the dimensional views

    Now according to the specification given, need to create a fact table as facts_table which contains all the dim tables primary keys as foreign keys in it.

    The problem is when i say,I am going to consider a smaller example than the actual no of dimension tables 5 lets say there are 2 dim tables student,grade with s_id,g_id as p key.
    Code (Text):

    create materialized view facts_table(s_id,g_id)
    as
    select  s.s_id,g.g_id
    from   (select distinct s_id from student)s
    ,         (select distinct g_id from grade)g
     
    This results in massive duplication as there is no join between the two tables.But basically there are no common things between the two tables to join,how to solve it?

    Consider it when i do it for 5 tables the amount of duplication being involved, thats why there is not enough tablespace.

    I was hoping if there is no other way then create a fact table with just one column initially

    Code (Text):

    create materialized view facts_table(s_id)
    as
    select s_id
    from student;
     
    then
    Code (Text):

    alter materialized view facts_table add column g_id number;
     
    Then populate this g_id column by fetching all the g_id values from the grade table using some sort of loop even though we should not use pl/sql i dont know if this works?

    Any suggestions.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    hi ranger, when u say 5 dimensional tables are created, are these tables already present in your database? if not can you not modify your design to include a more logical PK,FK relationship between your dimension tables?

    if these tables are already present can you not alter the dimension tables to include the FKs from other dimension tables according to some relation?

    basically you are saying that you have 5 tables with nothing in common yet you want a view from them where relationships exist? how is that possible
     
  3. Ranger

    Ranger Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Ahhh i think i understand a little bit , i get to see your point now

    Say there are 3 dimension tables

    Student
    ====
    s_id pk auto gen sequence
    Studentid
    Studentname

    School
    =====
    sc_id pk auto gen sequence
    school
    schoolname

    grade
    =====
    g_id pk auto gen sequence
    e_level

    fact table
    ========
    s_id fk reference s_id
    sc_id fk reference sc_id
    g_id fk reference g_id

    so when i want to get the data for the query which could be like

    get me the no of students who are studying a-level (advanced level) all over the country

    then the query will be something like
    Code (Text):

    select  count(s.upn)
    from    student s
    ,   grade g
    ,   fact_table ft
    where   ft.s_id=s.s_id
    and ft.g_id=g.g_id
    and g.e_level='a';
     
    If this is allright,

    I kindly need to know how to populate the fact table with the primary keys of the dim tables which become the fkey in fact table without duplication.Because as i explained above in the first post whenever i try to populate the fact table then it produces duplicated keys.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    If I understand your query , You cannot achieve the same except by creating the Cartesian product (as you tried) of all dimension table primary keys unless you establish the referential integrity between the tables. But this will give you the maximum possible combination of those primary keys. ie it will give you 19000*18 records.


    In your example School, student and guide are dimensional table and there is no relation between these tables. So it wont give you any meaningful data unless there are no relation between them.

    If this a real life case (of any project / application) ,check the database . I am sure you will fine some table for correlating these primary keys . Or if this a question from any project / interview ; It is absurd.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    By the way , why do you use materialized view for creating on the fly primary key foreign key combination instead of normal view ? Is it really needed ? The normal view will be enough for you in case you need the fact table to be updated always .

    But if is data warehouse environment ( I this this is more likely such case) , materialized view will be enough. Please refer materialized View documentation
     
  6. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    See the design of the dimension tables you mention is flawed. It's really basic database concept. Let's say you have only two dimension tables students and grade. The design should be

    Student ( s_id pk, studentname, g_id fk)

    grade (g_id, gradename)

    so if you wanted to find the names of all students studying in Grade 'A' you would have to write something like this

    Code (Text):
    select studentname, gradename
    from student ST, grade GD
    where ST.g_id = GD.g_id
    and gradename like 'A'
     

    it's really basic high school stuff. you cannot create a view of relationships if the underlying tables do not contain the relation through PK-FK relations.

    Tell me is this a school project or are you querying an already present application? if the application is present like rajavu said in his post earlier you should check to see if the relations have been stored anywhere in any table.