3 Replies Latest reply: Sep 10, 2014 12:00 PM by 2695110 RSS

    Using dynamically created source tables.... using a Variable in the table name?

    user507190

      Hello All,

      We have a situation where another process creates and populates tables in our data mart.  The tables will always have the same format and the same name.  For example, the process will create 25 tables with the same format.  The names will be <process_id>_load_table.  We need ODI to use each table as a source table and process the data further.  The creation and processing of each table is time-sensitive so each needs to be processed as soon as the table is loaded.  The process that creates and loads the tables can kick-off an ODI process when it finishes the table loads. 



      A couple of questions / comments: 

      --  we thought we could reverse engineer a dummy table in ODI to be used in the integration development.  Obviously it would reflect the same format as the dynamically created table(s).  Will this work?

      --  Is there a way to dynamically create the table name during the interface execution so ODI uses the <process_id>_load_table name to select the correct table?


      Thoughts?

        • 1. Re: Using dynamically created source tables.... using a Variable in the table name?
          Nishikant-Oracle

          Hi

          Reverse Engineer works for an existing physical table.

           

          What we can do in this scenario is, we already have a reversed engineered datastore in the Model. Lets say the process id for this is 1, and the table name is 1_LOAD_TABLE. Open this datastore, and modify the resource name to have a variable instead of the actual table name. The steps for doing it are below.

           

          • Create a new variable #P_PROCESS_ID in the project and add it to the ODI package before the step which uses the Source table in the interface (or mapping) or procedure. This can be a set variable where you can set the value to 1, or if you have a dynamic package then you can write a query to pick the required process id (refresh variable), or the data mart process which creates and loads the table and launches the subsequent ODI load scenario can pass this paramater (using declare variable)
          • In the package, create and add another variable #P_TABLE_NAME, where the code should concatenate the #P_PROCESS_ID and '_LOAD_TABLE'.
          • In the Datastore for the table, rename resource name to #P_TABLE_NAME'

           

          Now when the package (or scenario) runs, the variable will be replaced with the actual value and will be used.

           

          Always remember to add the variable steps in each of the ODI package where it is used. Don't reply on the last value, it doesn't always work correctly.

           

          Tell me if you need snapshots.

           

          Regards

          Nishikant

          • 2. Re: Using dynamically created source tables.... using a Variable in the table name?
            user507190

            Thanks, Nishikant,

            This is exactly what I think I am looking for!  Going to test it asap!  I'll keep you updated!

            • 3. Re: Using dynamically created source tables.... using a Variable in the table name?
              2695110

              Sorry for the delay in my reply.  I modified the requirement so I only need a variable that defines the table name.  I did the following:

              • Changed the resource name in the datastore to #DYN_TBL
              • Created a variable called #DYN_TBL
              • Created a package - declare the variable, set the variable, called the interface
              • Using the #DYN_TBL as the source table in the interface

              The package is failed during the Integration step with ODI-1228 The multi-part identifier "ABDC_123.Col1' could not be bound.   Where ABDC_123 = the variable value and Col1 = column.  Not sure what I am missing.  Could you please send screenshots and maybe I can figure where I went wrong?

               

              Appreciate the assistance!  This is being critical!