Performance issue with Informatica mapping

Business Intelligence Applications Consumer - Version: 7.9.5.1 [AA 1448] - Release: V7

Information in this document applies to any platform.

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

~) Bug 7348859: NEED TO CREATE THE FOLLOWING ETL INDICES IN 7.9.5.1

~) This BUG contains the following:

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.

CAUSE JUSTIFICATION
===============

~) Bug 7348859: NEED TO CREATE THE FOLLOWING ETL INDICES IN 7.9.5.1

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.