OBIA 11g - GL Segment Dimensions DO NOT support multiple parent hierarchy rolling to same top most node

Business Intelligence Applications Consumer - Version 11.1.1.7.1 and later
Information in this document applies to any platform.
This applies to the following source systems:
EBS 11.5.1.0 and higher
PSFT 9.0 and higher
Fusion Apps 11.x
The GL Segment Dimensions are not loaded with hierarchy data having multiple parents rolling up to same top most node.

Considering the scenario as follows for an example with EBS source:

The hierarchy set up in EBS is as follows:

Top	#2	#3	#4	#5
100000	110000	111100	111111
100000	110000	111000	111100	111111
100000	111000	111100	111111
100000	111100	111111

The node 111100 has multiple parents like 110000 and 111000 rolling up to the same top most node 100000.

The OOTB load plan task SIL_GLSegmentDimensionHierarchy_Delete runs the following query after populating the W_GL_SEGMENT_DHS from the source:

DELETE FROM W_GL_SEGMENT_DHS
WHERE (hierarchy_id, hierarchy_version, hierarchy_lov_id,datasource_num_id)
IN (
SELECT hierarchy_id, hierarchy_version, hierarchy_lov_id, datasource_num_id
FROM TEST_DW.W_GL_SEGMENT_DHS
GROUP BY integration_id, hierarchy_id, hierarchy_lov_id,hierarchy_version, datasource_num_id
HAVING COUNT (1) > 1
);

This query deletes/removes all the records from the W_GL_SEGMENT_DHS table which have multiple parent records rolling up to same top most node i.e. 100000 in above example.

Hence the W_GL_SEGMENT_DH table will further not have these records.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This is applicable to following tables:

W_GL_SEGMENT_DH

W_COST_CENTER_DH

W_NATURAL_ACCOUNT_DH

W_BALANCING_SEGMENT_DH 

Solution

The current versions of BI Apps DO NOT support this kind of hierarchy with records having multiple parents rolling up to the same top most node.

OBIA 7.9.6.1 DAC CONTAINER ISSUE

QUESTION

Recently we've installed the OBIA 7.9.6.1 release and installed it. The DAC and Informatica ETL does not seem to correspond. We're working with EBS 12.1.1 for which there is a folder within the informatica ETL. However, in the DAC, the ETL folder points to ORA12 and there is no corresponding DAC container for ORA1211! Please can you clarify which one should be used and why there is not a corresponding DAC entry for ORA1211?

ANSWER


Looking in an OOTB 7.9.6.1 DAC there is an Oracle R12.1.1 container.

The user had not de-installed the 7.9.6 installation of the BIApps before installing 7.9.6.1. Once the old version had been de-installed and the new version installed, the DAC container appeared.

OBIA - Performance Issue While Running SIL_APTransactionFact with EBS

In OBIA 7.9.6.4 with EBS:

• Informatica Mapping 'SIL_APTransactionFact runs incrementally every day and used to take approximately 15 mins.
• But now SIL_APTransactionFact is taking over 3 hours.

Cause

The Prune Days DAC Parameter was set to the default value of '30', causing millions of W_AP_XACT_F records to be processed in the LKP_W_APP_XACT_F Lookup 

Solution

Please Note the following:

• 'Prune Days' is used for Siebel data, where they have Remote Field Users who might only 'sync' up their laptop or data once a month. Therefore they may have lost old data created remotely weeks earlier.
• In EBS, there is no concept of a Remote Field User having to work offline and then 'sync up' at a later date, so this functionality is basically redundant in EBS environments.

 To resolve this issue, please set the Prune Days DAC Parameter to '1'.

With the W_AP_XACT_F Lookup processing less data during an Incremental Load, it will now process much less data and complete much faster.

Performance issue with Informatica mapping

Mapping SDE_ORA11510_Adaptor.SDE_ORA_Stage_APTransactionFact_AGGRDerive.log is running for hours without completing.
Query is:
SELECT
MAX(W_GL_ACCOUNT_D.INTEGRATION_ID),
SUM(W_AP_XACT_F.AP_DOC_AMT),
SUM(W_AP_XACT_F.AP_LOC_AMT),
W_ORA_GLRF_F_TMP.GL_JE_HDR_ID,
W_ORA_GLRF_F_TMP.GL_JE_LINE_NUM,
W_ORA_GLRF_F_TMP.DATASOURCE_NUM_ID
FROM
W_ORA_GLRF_F_TMP, W_AP_XACT_F , W_GL_ACCOUNT_D
WHERE W_ORA_GLRF_F_TMP.JE_SOURCE='Payables'
AND W_ORA_GLRF_F_TMP.IMPORT_REF_ID = W_AP_XACT_F.ACCT_DOC_ID
AND W_ORA_GLRF_F_TMP.DATASOURCE_NUM_ID = W_AP_XACT_F.DATASOURCE_NUM_ID
AND W_ORA_GLRF_F_TMP.FIN_STMT_ITEM_CODE='AP'
AND W_AP_XACT_F.GL_ACCOUNT_WID = W_GL_ACCOUNT_D.ROW_WID
GROUP BY
W_ORA_GLRF_F_TMP.GL_JE_HDR_ID,
W_ORA_GLRF_F_TMP.GL_JE_LINE_NUM,
W_ORA_GLRF_F_TMP.DATASOURCE_NUM_ID

Table counts:

select count(*) from W_ORA_GLRF_F_TMP --682198
select count(*) from W_AP_XACT_F -- 8374771
select count(*) from W_GL_ACCOUNT_D -- 601689

W_ORA_GLRF_F_TMP is unindexed.

Cause

These indices will help improve performance on GRF Derive mappings and
AGGRDerive mappings.

1. Composite index on:
W_AR_XACT_F.ACCT_DOC_ID
W_AR_XACT_F.DATASOURCE_NUM_ID
2. Composite index on:
W_AP_XACT_F.ACCT_DOC_ID
W_AP_XACT_F.DATASOURCE_NUM_ID
3. Composite index on:
W_GL_COGS_F.ACCOUNT_DOC_ID
W_GL_COGS_F.DATASOURCE_NUM_ID

These indices have been added to the Data Warehouse in 7.9.5.1 and 7.9.6 and
the change has been pushed to all refernces.
Solution

 Please find the following DDL. You may need to change this to reference your Index tablespace. Also please check to ensure an indexes with these name do not already exist.

CREATE INDEX
W_AR_XACT_F_M6
ON
W_AR_XACT_F
(
ACCOUNT_DOC_ID Asc
,DATASOURCE_NUM_ID Asc
)
NOLOGGING
;


CREATE INDEX
W_AP_XACT_F_M7
ON
W_AP_XACT_F
(
ACCT_DOC_ID Asc
,DATASOURCE_NUM_ID Asc
)
NOLOGGING
;

CREATE INDEX
W_GL_COGS_F_M2
ON
W_GL_COGS_F
(
ACCOUNT_DOC_ID Asc
,DATASOURCE_NUM_ID Asc
)
NOLOGGING

~Yes you would need to add their definition in the DAC

Adding an Index to the Data Warehouse
Follow this procedure to add a new index to the data warehouse.

1. Add the new index definition into the data warehouse database.
2. In the DAC toolbar, select the appropriate source system container from the
drop-down list in the toolbar.
3. In the Menu bar, select Views, then select Design, then select Tables.
4. Query for the table for which you want to import index definitions.
5. Right-click and select Import from Database, then select Import Indices.
6. Choose to import indexes for a selected table or for all the records retrieved in
your query, and click OK.
7. In the Import Indices dialog, select DataWarehouse from the Data Sources
drop-down list.
8. Click Read Indices.
a. In the list of indexes displayed, select the Import check box for the indexes you
want to import.
b. Click Import Indices.
An informational message indicates whether the process was successful.

The Index Usage should be defined as ETL.