X

Oracle Customer Engineering & Advocacy Lab (CEAL) Blog covers the Infra, functional, updates, release and articles...

  • 4. OBIA |
    Tuesday, November 18, 2014

Adding trim functions to the column expressions in ODI

Background and Issue Description

Product: Oracle Business Intelligence Application

  • OBIA Release: 11.1.1.8.1 and above
  • OLTP Source: JDE90/JDE91

While
working with one of our customer who was implementing OBIA 11.1.1.8.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.

On
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.

The
trailing spaces in warehouse columns can cause a potential issue while joining
2 warehouse tables either in the ETL or in the RPD.

Ex: If
‘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.

To
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
trim

While
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.

Table
Name

Field
Name

W_GL_OTHER_FS

JOURNAL_SOURCE_ID

W_AR_XACT_FS

RECEIPT_NUM

W_USER_D

FULL_NAME

W_INT_ORG_D_TL

ORG_NAME

W_DOMAIN_MEMBER_CODE_DS

DOMAIN_MEMBER_CODE

W_INVENTORY_PRODUCT_DS

INVENTORY_ORG_ID

W_INT_ORG_D

INTEGRATION_ID

Instruction overview:

Following
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.

Note:
Before applying the fix, please
refer to the OBIA suggested customization guidelines and follow the guidelines.

Solution for example field
‘LEDGER_ID’

1) Open the SDE JDE task folder SDE_JDE_APInvoiceLineFact in
the custom adaptor folder and open the Main interface
‘SDE_JDE_APInvoiceLineFact .

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.

Conclusion:

While
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.

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.Captcha
Oracle

Integrated Cloud Applications & Platform Services