2 Replies Latest reply: Sep 19, 2011 2:08 PM by Jason Michaelides RSS

    concatenation of multiple fact table??

    Sushil Kumar

      Hi All,

       

      i have three fact table. i want to concatenate them and create a qvd file with incremental load.. please help me how to do it..

      Please have a look the tables shown below.. All the dimensions are common to all three fact tables..

       

      Thanks.

      table.bmp

        • concatenation of multiple fact table??
          Sushil Kumar

          Please share your experience...

          • concatenation of multiple fact table??
            Jason Michaelides

            Hi - try this:

             

            FACT:

            LOAD

               'Source1' AS FACT_Type,

               *

            FROM Source1....;

             

            CONCATENATE (FACT)

             

            LOAD

               'Source2' AS FACT_Type,

               *

            FROM Source2....;

             

            CONCATENATE (FACT)

             

            LOAD

               'Source3' AS FACT_Type,

               *

            FROM Source3....;

             

            STORE FACT INTO FACT.qvd;

             

             

             

            Then, on your incremental load (S_ORIG_CODE is a unique identifier):

             

            FACT:

             

            LOAD * FROM FACT.qvd;

             

            CONCATENATE (FACT)

             

            LOAD

               'Source1' AS FACT_Type,

               *

            FROM Source1....

            WHERE NOT EXISTS(S_ORIG_CODE);

             

            CONCATENATE (FACT)

             

            LOAD

               'Source2' AS FACT_Type,

               *

            FROM Source2....

            WHERE NOT EXISTS(S_ORIG_CODE);

             

            CONCATENATE (FACT)

             

            LOAD

               'Source3' AS FACT_Type,

               *

            FROM Source3....

            WHERE NOT EXISTS(S_ORIG_CODE);

             

            STORE FACT INTO FACT.qvd;

             

             

            Hope this helps,

             

            Jason