Authors: "Yadukrishna Munugoti - Oracle " and "Guna Vasireddy - Oracle"
The blog1 and blog2 in this series provided steps on how to get Fusion Flexfields into BIAPPS. This blog, the third in this series, details some common issues you might encounter when doing so and how to resolve those issues.
ODI-40444: Column not found:XXXXXXXXXXXXXXXXXX
After doing the required configuration to get the Fusion Flexfields into BIAPPS, you might encounter the ODI-40444: Column not found error at times. This error can occur for a couple of reasons.
- The flex columns are not present in the Fusion BI Server OTBI RPD.This may happen if the user introduced flex fields in the Fusion Application for the VO but forgot to run BI extender (ESS Job: Import Oracle Fusion Data Extensions for Transactional Business Intelligence) to push those changes to the Fusion BI Server OTBI RPD. This has been the primary reason for this error. To verify if this is cause: Check the source files (.csv & .mdcsv) for the flex VO that are present in UCM (blog). If we find that the column list in .mdcsv file (VO’s metadata file) does not match the actual extract column list in the .csv file(data file), then this is indeed the cause. To fix this issue, it is recommended to reset and do a full extract/load. The steps would be
(a) Clean up all existing MANIFEST.MF files by deleting them from UCM (Oracle WebContent Server)
(b) Run the BI Extender
(c) Reset to full extract using BICCC (Refer to BICCC Documentation)
(d) Re-run the Data extract again from BICCC Console
(e) Reset SDS/WH (Refer to "Functional Configuration Reference Guide" -> Manage Load Plans UI in BIAPPS Documentation)
(f) Run the ODI load plan.
- If you are on BIAPPS 10.1 release and the csv file name exceeds 128 characters. As per the ODI File Technology setting in that BIAPPS version, the maximum allowed file name length is 128.Hence while running FTS map, it automatically does trimming on file name length with 128 chars. Hence FTS map is not able to read the csv file (hence ODI-40444 issue). Download and apply the tech note as available in patch (Patch Number 22278058) to resolve this issue.So check the length of the file name in UCM and if the length is longer than 128 characters and you are in BIAPPS 10.1, then apply the above patch.
Verify if the Flex columns are present in the OTBI RPD
Incase you want to check whether the flex columns were successfully imported to the OTBI RPD before proceeding for BICC extract, please follow below steps to validate:
Figure1: Issue SQL from BI Server.
- Login to OTBI FA analytics link
- Go to the Issue SQL OBIEE link
- Run a simple query to check the column(s) existence in the RPD for a VO.
For Example: Below is the example for HCM VO.
select_physical * FROM "oracle.apps.hcm.model.analytics.applicationModule.HcmTopModelAnalyticsGlobalAM_HcmTopModelGlobalAMLocal".."HcmTopModelAnalyticsGlobalAM.BaseWorkerAsgDFFBIAM.FLEX_BI_BaseWorkerAsgDFF_VI"
Note the count of columns returned by the above query
- Now get all BI enabled column count using below SQL and this column count should match with 3rd step column count.If you see any difference then you will have to run BI Extender job to make them in sync.Note: we can actually run this sql using Issue SQL link from the BI Presentation services like we executed 3rd step SQL statement.
Please change Flex VO’s name/catalog with the one which you are looking for, before running the below SQL. FYI... Below is the example for HCM VO.
EXECUTE PHYSICAL CONNECTION POOL "oracle.apps.hcm.model.analytics.applicationModule.HcmTopModelAnalyticsGlobalAM_HcmTopModelGlobalAMLocal"."Connection Pool"
This way you can make sure that you have all BI flex enabled VO columns were successfully imported to the OTBI RPD.
Limit the number of Flex Columns using the BI Enabled Flag
Are all the flexfields you defined on the VO, relevant for BI? If not, you can restrict the flex extensions in BIAPPS to only those that are relevant to BI. This helps improve performance by restricting the data transfer to only relevant columns. There is also a limit on the how many columns get automatically propagated to the warehouse and this will help ensure the number of columns are within that limit.
When you define a flexfield in Fusion, there is a flag called BI Enabled. Enabling this flag will ensure that this flexfield gets extended to the RPD and will then eventually flow into BIAPPS. If however some columns are not required for BI, then you can disable the flag for those columns.
Figure2: BI Enabled Flag on Fusion Screen.(Click on image to view a bigger version)
On editing the selected flexfield, you can see the flag for existing flexfields. Disable the flag for the ones that are not required for BI. Note: Disabling this flag, will disable it for OTBI as well. So disable it only if it is not required for OTBI and BIAPPS. There is no current way to disable it only for BIAPPS.