X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

Importing Custom tables from Source Systems

At times, the source system for BIAPPS might have additional tables that you want to extract the data from and get into BIAPPS. This blog specifies the steps on how to import a source table that is there in Source system but not present in the BIAPPS ODI repository.

It is assumed that the user has already registered the Source system before attempting any of the steps in this document. Please refer to the BIAPPS documentation incase you need help with the Source Registration.

Identify the Model in BIAPPS Repository:

First step is to identify the model in the BIAPPS repository corresponding to the source system in question. For the purpose of the blog, we will assume there is a EBS table CST_ITEM_COST_DETAILS that we want to import from EBS 12.2 Source system. The models are visible under Model tab in the Designer window of ODI studio. Select the right model corresponding to the Source System Version and then double click on the model in ODI studio. Below screenshot shows the EBS 12.2 model opened.

Once you have the model open in ODI Studio, select the Reverse Engineer tab and click on Customized. This will allow the user to select the BIAPPS RKM. Ensure that the RKM relevant for the Source System is selected. In the above example, the RKM ought to be RKM BIAPPS E-Business Suite(Oracle).BI Apps Project. Specify the table you want to import using the LIST_OF_TABLES option. You can leave the other parameters as defaults. In the example above, the LIST_OF_TABLES would be set to CST_ITEM_COST_DETAILS to import a table called CST_ITEM_COST_DETAILS in the source table.

Once the appropriate RKM is selected and the required table specified in the RKM options, click on Reverse Engineer on the top to start the process

Monitor the RKM Session log

Once you start the reverse engineer process, it will start a session that is visible from Operator just like any other scenario session. Monitor that session to check if the process completed. If the session failed, then view the session log to see the reasons for failure. Screenshot below show the session in progress as visible from Operator.

 

Once the run completes successfully, you should be able to see the desired table in the ODI repository. The table would normally get placed in the appropriate sub-model. Certain RKMs (like the one for BIAPPS warehouse) would place it in a sub-model like others. If you are not able to locate the newly imported table (as there are many sub-models), you can run a backend query against the ODI repository to identify the sub-model it got imported under.

select m.mod_name,s.smod_name, t.res_name table_name
from snp_table t
inner join snp_model m on
t.i_mod=m.i_mod
inner join snp_sub_model s on
t.i_sub_model = s.i_smod
where
t.res_name='CST_ITEM_COST_DETAILS'

Common Pitfalls:

Some of the common mistakes that should be avoided are listed below:

  1. Not selecting the customized radio button in Reverse Engineer tab. If you don't do that, ODI will use the standard RKM which is not designed to extract table metadata from ERP sources.
  2. Not selecting the appropriate RKM. Once you select the customized radio button, you will have the option to select the RKM from the "Knowledge Module" drop down list. Ensure that the RKM relevant to the Source System is selected in the drop down
  3. Specifying the table list in wrong place or not specifying a list at all. The table(s) to be imported should be specified against the KM option LIST_OF_TABLES. Use comma separated list in case you have more than one table ( E.g. TABLE1,TABLE2). Do not enclose names in quotes or in braces. Do not specify the names against the MASK text box that appears above the KM. That won't be used. Do not run the RKM without specifying a list as that will attempt to import all tables from the Source and can unnecessarily increase the size of the ODI repository, thereby slowing it down.
  4. If the table you are trying to import is a synonym in the user that you are connecting to the database with, then the table will get imported as a synonym and won't show any columns. If you want to import the columns as well, you should connect to the database using the user who is the owner of the table that the synonym is pointing to.
  5. The ERP Specific RKM may read application specific tables to get the table/column metadata. For example, the EBS RKM in example above, looks at fnd_tables/fnd_columns tables in EBS to get the table/column metadata. If the custom table that you are importing, is not present in those application tables, then the RKM will run fine but nothing will be imported into ODI. If you want to still import such tables into ODI, then use the ODI provided standard RKM instead to import those tables. Please note however that doing so, will not populate any of the BIAPPS table/column flexfields or any additional information that BIAPPS mappings use (Like CDC columns) and hence adding these tables to existing BIAPPS mappings may cause failures. The customer should instead use them in his custom mappings only.The Steps to run standard RKM would be as below:

    a)Open the model
    b)Go to Reverse Engineer Tab
    c)Select Standard
    d)Provide the table name in the mask field
    e)Click on Reverse Engineer
    f)The table should then get imported directly under the model (not under any sub model). Now move the table to the desired sub model.

       Refer to ODI standard documentation for more details

 

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.