OBIA 11g : Location Dimension Not Loading Past (Old) Location Data (Doc ID 2064505.1)

Last updated on OCTOBER 14, 2015

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.7.1 to 11.1.1.9.2 [Release 11g]
Information in this document applies to any platform.

Symptoms

A filter exists on the ODI mapping for the location dimension table which causes only the most recent location record to be loaded from the source system into W_BUSN_LOCATION_D table on a full load. It would be expected that past locations are loaded as well. This means that only the most recent location is reported against any Purchase Order that was created before the effdt of the location with a WID of 0 which is not representative of the source data.

Filter condition: LOC.EFFDT = (SELECT MAX(B.EFFDT) FROM QUALIFY(PS_LOCATION_TBL) B WHERE LOC.SETID = B.SETID AND LOC.LOCATION = B.LOCATION AND LOC.EFFDT <= SESSSTARTTIME )

Without loading past locations any reporting for the location is lost for PO's created before its most recent EFFDT of 2/2/2014.

Steps to Reproduce - Example

Source system scenario:  

*Location is created with effdt of 1/1/2012  (Jurisdiction is NEW YORK)
*PO is created on 2/2/2012
*Location is modified with an effdt of 1/1/2014 (Jurisdiction changes to EAST NEW YORK)
*PO is created on 2/2/2014

It would be expected that the PO created 2/2/2012 is reported as being in NEW YORK, and the PO Created on 2/2/2014 is reported being in EAST NEW YORK.

BAW currently:

*Only the Location with effdt 1/1/2014(EAST NEW YORK) is loaded to the dimension table because of ODI Filter.
*PO Created on 2/2/2012 gets reported as __NOT_APPLICABLE__ (WID = 0)
*PO Created on 2/2/2014 gets reported as EAST NEW YORK (which is correct).

Without loading past locations any reporting for the location is lost for PO's created before its most recent EFFDT of 2/2/2014.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms