This discussion is archived
11 Replies Latest reply: Apr 19, 2013 3:59 PM by onedbguru RSS

Updation on Fact Table

LPS Journeyer
Currently Being Moderated
Hi ,

We have a fact table which have 25 surrogate key which are updated from 25 different diffent table,so we 25 merge statement to update the 25
25 surrogate key ,so we are hitting the fact table 25 times,so what is the best approach to update the fact table without hitting 25 times.
  • 1. Re: Updation on Fact Table
    sb92075 Guru
    Currently Being Moderated
    It is faulty design if/when any key changes value.
  • 2. Re: Updation on Fact Table
    LPS Journeyer
    Currently Being Moderated
    Its an OLAP system,its a batch run for each feeds loaded into the system ,from there we are proccessing.
  • 3. Re: Updation on Fact Table
    onedbguru Pro
    Currently Being Moderated
    Sounds like a very poor design to me. why on earth would you make so much work for yourself. If the surrogate key is part of a PK or partition scheme, you can expect a LOT of row movement and possibly index updates. No matter what you choose you can expect a LOT of redo/archivelogs.

    Now, let's see if we can understand what is you are asking.

    Are you saying that

    1) the 25 other tables each have a surrogate key.
    2) the FACT table has 25 columns - one for each dimension table
    3) you need to update row xyz and insert these 25 surrogate keys?

    is this correct?
  • 4. Re: Updation on Fact Table
    sb92075 Guru
    Currently Being Moderated
    LPS wrote:
    Its an OLAP system,its a batch run for each feeds loaded into the system ,from there we are proccessing.
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 5. Re: Updation on Fact Table
    LPS Journeyer
    Currently Being Moderated
    Yes its exactly same what you mentioned in 3 steps ...onedbguru
  • 6. Re: Updation on Fact Table
    sb92075 Guru
    Currently Being Moderated
    Handle:     LPS
    Status Level:     Journeyer (440)
    Registered:     May 25, 2010
    Total Posts:     528
    Total Questions:     35 (33 unresolved)


    I extend my condolences to you since you rarely get answers to your questions here.
  • 7. Re: Updation on Fact Table
    LPS Journeyer
    Currently Being Moderated
    What condololence you want to say ,if don't want to answer keep quiet and proceed with next post.
  • 8. Re: Updation on Fact Table
    sb92075 Guru
    Currently Being Moderated
    LPS wrote:
    What condololence you want to say ,if don't want to answer keep quiet and proceed with next post.
    why are so MANY of your questions still unanswered?
    where does the problem reside?
  • 9. Re: Updation on Fact Table
    jeneesh Guru
    Currently Being Moderated
    sb92075 wrote:
    Handle:     LPS
    Status Level:     Journeyer (440)
    Registered:     May 25, 2010
    Total Posts:     528
    Total Questions:     35 (33 unresolved)


    I extend my condolences to you since you rarely get answers to your questions here.
    LPS wrote:What condololence you want to say ,if don't want to answer keep quiet and proceed with next post.
    That is bad response to one of the senior most and helpful members in the forum.. He was right that you are not marking the threads as answered once the issue is resolved. It is a bad forum etiquete. From my experience with this forum, it is hard to believe that your 33 issues are still not resolved..

    Coming to your issue - It is definitely a bad design if you are updating keys in a FACT table in a DWH.

    Can you explain the business scenario on which you are firing these updates?
  • 10. Re: Updation on Fact Table
    onedbguru Pro
    Currently Being Moderated
    So, essentially you are creating the OLAP cubes?? Can you show that script that creates this cube? So, you are actually not updating a FACT table, you are creating a CUBE that reflects data in the FACT table. Correct?

    Not being an OLAP guru, I will defer to others that may be able to assist.

    http://docs.oracle.com/cd/E11882_01/olap.112/e17123/cubes.htm
  • 11. Re: Updation on Fact Table
    onedbguru Pro
    Currently Being Moderated
    So, essentially you are creating the OLAP cubes?? Can you show that script that creates this cube? So, you are actually not updating a FACT table, you are creating a CUBE that reflects data in the FACT table. Correct?

    Not being an OLAP guru, I will defer to others that may be able to assist.

    http://docs.oracle.com/cd/E11882_01/olap.112/e17123/cubes.htm

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points