11 Replies Latest reply: Apr 19, 2013 5:59 PM by onedbguru RSS

    Updation on Fact Table

    LPS
      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
          It is faulty design if/when any key changes value.
          • 2. Re: Updation on Fact Table
            LPS
            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
              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
                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
                  Yes its exactly same what you mentioned in 3 steps ...onedbguru
                  • 6. Re: Updation on Fact Table
                    sb92075
                    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
                      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
                        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
                          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
                            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
                              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