Background and Issue Description
Product: Oracle Business Intelligence Application
working with one of our customer who was implementing OBIA 184.108.40.206.1 for JDE
9.0 adaptor, it was noticed that some of the data warehouse columns were having
trailing spaces after the data was extracted from JDE source system and loaded
into the warehouse.
further analysis it was observed that the JDE system stores the data with
padded spaces in the database. During the ETL process of OBIA, when the columns
from JDE DB are moved into the OBIA warehouse, corresponding warehouse columns
was loaded with trailing spaces.
trailing spaces in warehouse columns can cause a potential issue while joining
2 warehouse tables either in the ETL or in the RPD.
‘W_INVENTORY_PRODUCT_DS. INVENTORY_ORG_ID’ was loaded from JDE source without
applying the trim function and ‘W_INT_ORG_DS.ORG_ID’ was loaded from JDE source
after applying the trim function and later if these 2 tables are joined using
the above mentioned columns, the join between these 2 tables will result in no
rows returned because of the appended spaces in ‘W_INVENTORY_PRODUCT_DS. INVENTORY_ORG_ID.
avoid the above issue and any other issues because of padded spaces in the
warehouse columns, it is advisable to add trim function in the ODI mappings
while populating the OBIA warehouse.
Some of warehouse columns that needs
working with the customer, it was noticed that the following are some the
warehouse target columns that needed application of trim function. Trim
function need to be applied in corresponding ODI maps on the source columns
while populating the warehouse columns.
steps gives the details of how apply a fix in the ODI maps to remove the padded
space while populating the warehouse column. In the below example, ‘LEDGER_ID’
is considered for applying the trim function. Similar approach should be
applied on all the necessary fields in the corresponding ODI maps.
Before applying the fix, please
refer to the OBIA suggested customization guidelines and follow the guidelines.
Solution for example field
1) Open the SDE JDE task folder SDE_JDE_APInvoiceLineFact in
the custom adaptor folder and open the Main interface
2) To apply TRIM function for LEDGER_ID column, the original
expression, SQ_F0411.RPCO||'~'||'#AP_LEDGER_TYPE' Should be modified as TRIM(SQ_F0411.RPCO)||'~'||'#AP_LEDGER_TYPE'.
The execute On should be set to ‘Staging’.
3) Apply and Save the Interface
4) For SDE_JDE_APInvoiceLineFact in the custom folder, go to
Packages and generate the scenario using the option to generate the scenario as
if all underlying objects are materialized. Set the version number to 002.
working with the OBIA warehouse columns that were populated from JDE source
system, if it is noticed that the warehouse column has padded space. Apply the
above workaround in your warehouse. Subsequent release of OBIA will handle the
trimming of the columns that were not handled in this release.