Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« Significance of ETL in BI Reporting | Main | Quick Tips for EPBCS Series 1 - Adding a little pep to the morning Latte makes the difference! »

Implementation of Oracle BI Applications for Multi Instance ERP Financials Source

Oracle BI Applications (OBIA) supports the loading of data into the Oracle Business Analytics Warehouse (OBAW)  from multiple source systems and multiple instance of the same source system. The main features of OBIA Multi Instance Implementation for common facts and dimensions are as follows

ETL:

       Each source is assigned a DATASOURCE_NUM_ID to avoid data load failures due to a duplicate primary key between transactional systems.

       Lookups are done using source primary key (INTEGRATION_ID) and DATASOURCE_NUM_ID thus ensuring unique record is returned

       Each new insert into the Oracle DW fact or dimension is assigned a new ROW_WID (surrogate key)

REPORTING :

       All Joins in reporting layer are on ROW_WID and not the natural keys from the source

Multiinstance_ERP_4.png
















The following table gives a few scenarios along with details of its implementation in OBIA

Scenario

Impact

Customization

Different dimension values from different sources/instances

Treated seamlessly as different rows of the given dimensions at ETL and reporting layer

No

Different Transaction data in facts from 2 different sources/instances

Treated seamlessly as different rows of the given fact at ETL and reporting layer

No

Same dimension from different sources (item 1 is present in both)

       Duplicate rows in Oracle DW dimension with different ROW_WID. Thus prompts might show duplicates and both need to be selected

       Facts data will point to correct row of the dimension as lookup is on source primary key (INTEGRATION_ID) and DATASOURCE_NUM_ID and aggregation will show correct value

       The duplicated data will result in multiple rows in reports where there should only be one row at the most granular level

Optional if de-duplication of dimension is desired. Ideal would be to have an MDM solution

Same fact from different sources (Same PO in both)

       Each transaction will pick the correct dimension values and 2 rows will be inserted into the fact table with different ROW_WID

       The duplicated data will result in multiple rows in reports where there should only be one row at most granular level

Optional if de-duplication of fact is desired. Ideal would be to have an MDM solution



HANDLING DIFFERENT COA STRUCTURES FROM DIFFERENT Oracle EBS INSTANCES

OBIA can handle different COA structures present in different instances of Oracle EBS. For example, we will consider one instance to be Fusion Cloud EBS while the other is a legacy on prem EBS as shown in table below. The chart of account for On Prem source has 4 segments while that for fusion cloud source has 7 segments. Moreover, the same segment type for example 'Company' is stored in different segments of GL_CODE_COMBINATIONS in the 2 sources.


Segment Type

101 (On Prem)

2002 (Cloud)

Company

SEGMENT1

SEGMENT6

Sales Division

SEGMENT2

Account

SEGMENT3

SEGMENT3

Cost Center

SEGMENT4

SEGMENT7

LOCATION

SEGMENT5

P&L

SEGMENT2

INTERCOMPANY

SEGMENT1

FUTURE1

SEGMENT4


The configuration file file_glacct_segment_config_ora for Financial Analytics, helps ensure that same segment types of different COA of different instances get loaded into the same column of the OBAW table W_GL_ACCOUNT_D as shown in screenshot below.

 

For example, we can store 'Company' segments from On Prem COA and Cloud COA in the segment 1 column in W_GL_ACCOUNT_D; and Cost Center segments from on prem and cloud COA in the segment 2 column in W_GL_ACCOUNT_D, and so on as shown below


Multiinstance_ERP_5.png

Multiinstance_ERP_7.png

HANDLING DIFFERENT CONFIGURATION PARAMETERS FROM DIFFERENT ERP INSTANCES
We will have to define source file directory (\biapps\etl\data_files ) corresponding to each source/instance while registering a Data Source in Configuration Manager.  Please refer below URL for more details. https://blogs.oracle.com/biapps/registering-bi-applications-data-sources
Also other parameters like the Master Inventory Org Setup , Product Category Setup are all source specific. Thus will be defined for each source when defining the load plan.

CONCLUSION
Thus OBIA is designed to handle data from multiple sources and multiple instances of the same source and caters to most scenarios that might be needed during implementation.

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles