| Oracle® Business Intelligence Applications New Features Guide Version 7.9.6.2 Part Number E16811-01 |
|
|
View PDF |
| Oracle® Business Intelligence Applications New Features Guide Version 7.9.6.2 Part Number E16811-01 |
|
|
View PDF |
This chapter contains the following topics:
"Back-dated changes" refer to changes occurring in OLTP records after those records have already been processed and loaded into the warehouse. Position hierarchy represents the employee and supervisor reporting relationship that is maintained in the Oracle BI Applications data warehouse. In HR, it is the Supervisor Hierarchy; and in CRM, it is the Position Hierarchy. Position Hierarchy supports historical versions of the hierarchies with the current stamp on the current hierarchy rows. Position hierarchy is delivered with history tracking enabled, and the attributes that are tracked for Type 2 slowly changing are an employee's organization and supervisor. In addition, for CRM position hierarchy, a change in an employee's position is also tracked for Type 2 slowly changing.
In previous releases, Position Hierarchy was not designed to support back-dated changes to an employee's supervisor, organization, or other information to support deletes. If there is a correction or delete in the OLTP primary driving table or auxiliary tables that are sourced to build the supervisor hierarchy structure in the warehouse, changes that are effective prior to the last ETL run date will be loaded into the warehouse as new records but not as corrections, thereby introducing duplicates during the index creation process and causing the ETL to abort. In this release, back-dated changes and deletes in the OLTP that affect the supervisor and position hierarchy will be processed accordingly by the ETL to reflect the retroactive changes in the warehouse.
The Position Hierarchy is a column-flattened structure that is loaded from the Position dimension, which maintains the parent-child relationships. The scenarios described in this section illustrate the effects of how back-dated changes affect the position dimension which in turn will affect the Position Hierarchy.
In an OLTP source system such as Oracle EBS, corrections can be made to historical rows or the current rows. Corrections can be made to OLTP tables that provide the primary data source for the data warehouse or to tables that serve as auxiliary lookups in the ETL process. Corrections can affect the following attributes:
Attributes that do not capture history, which are treated as a type 1 slowly changing dimension (SCD) in the data warehouse.
Attributes that capture history, which are treated as a type 2 SCD in the data warehouse.
Position hierarchy is affected by back-dated changes in different tables that directly or indirectly influence the ETL process. For example, Division Name can be changed retroactively without corresponding employee assignment changes in the source OLTP system. This back-dated Division Name change will trigger historical updates to the Position Hierarchy.
There are three main types of OLTP tables that affect Supervisor or Position Hierarchy. The following examples represent the tables in a source OLTP system:
Type A: The primary driving table that contains all historical assignment data, for example PER_ALL_ASSIGNMENTS_F. This table is the primary driver for the employee assignment history in the data warehouse.
Type B: Auxiliary source table which keeps no history (Type 1 SCD) in OLTP but the warehouse tracks attribute changes as Type 2 SCD, for example HR_ORGANIZATION_UNITS. This table is the source for attributes that are history tracked in the warehouse.
Type C: Auxiliary source table which keeps no history in OLTP and the warehouse also keeps no historical changes for the attribute (Type 1 SCD), such as PER_JOBS. This table is the source for attributes that are not history tracked in the warehouse.
The functionality in Oracle BI Applications Release 7.9.6 assumes that:
Position hierarchy tracks history as a type 2 SCD in the data warehouse
Division Name tracks history as a type 2 slowly changing attribute in the data warehouse
Person Name (a type 1 slowly changing attribute) does not track history in the data warehouse
Job (a type 1 slowly changing attribute) does not track history in the warehouse
The following table contains an example of employee P1's assignment history in the warehouse before the back-dated changes:
Table 8-1 Example of Employee P1's Assignment History Before the Back-Dated Changes
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
|
Row 1 |
P1 |
CRM |
Mgr1 |
2006 |
2008 |
|
Row 2 |
P1 |
My Division |
Mgr2 |
2008 |
2009 |
|
Row 3 |
P1 |
My Division |
Mgr3 |
2009 |
4712 |
This section describes the types of OLTP back-dated changes that affect the Position Hierarchy and the solutions provided in the warehouse to address the back-dated change scenarios.
A division name changed as a correction to the current record, for example Division Name 'My Division' was renamed to 'My New Division' in 2010. The employee did not change divisions as a result of a transfer. This is a Type B change as the source table does not track Division Name history, which is a Type 2 slowly changing attribute in the warehouse.
Option 1
This option is treated as a Type 2 (SCD) change and will introduce a new Type 2 (SCD) row in the warehouse. This option is enabled if DAC parameter UPDATE_CORRECTIONS_FLG is set to 'N'.
| Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|
| P1 | CRM | Mgr1 | 2006 | 2008 |
| P1 | My Division | Mgr2 | 2008 | 2009 |
| P1 | My Division | Mgr3 | 2009 | 2010 |
| P1 | My New Division | Mgr3 | 2010 | 4712 |
Option 2
This option is treated as a correction and will change historical data only without a new Type 2 row in the warehouse. This option is enabled if DAC parameter UPDATE_CORRECTIONS_FLG is set to 'Y'.
| Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|
| P1 | CRM | Mgr1 | 2006 | 2008 |
| P1 | My New Division | Mgr2 | 2008 | 2009 |
| P1 | My New Division | Mgr3 | 2009 | 4712 |
Division name changed as a correction to a historical record, for example Division Name 'My Division' was retroactively renamed to 'MD' 2008. The employee did not change divisions as a result of a transfer. This is also a Type B change as Division Name history is not tracked in the source OLTP system but is tracked as a Type 2 SCD in the data warehouse.
Option
Update the name change in the relevant history records.
| Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|
| P1 | CRM | Mgr1 | 2006 | 2008 |
| P1 | MD | Mgr2 | 2008 | 2009 |
| P1 | My New Division | Mgr3 | 2009 | 4712 |
Auxiliary table change: Job information is changed in the source OLTP system that is referred to in the historical data in the warehouse, for example Job name is changed to lower case. This is a Type C change where neither the OLTP nor the data warehouse track historical changes.
Option
New Job is propagated to all historical rows.
| Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|
| P1 | CRM | Mgr1, job2 | 2006 | 2008 |
| P1 | My Division | Mgr2, job2 | 2008 | 2009 |
| P1 | My Division | Mgr3, job2 | 2009 | 4712 |
Employee changed assignment divisions as a result of a transfer, for example Employee P1 transferred to division 'GRC' reporting to Mgr4 in 2010. This is a Type A change as the change occurs on a main OLTP driving table with history tracking.
Option
The data warehouse will insert a new row for tracking the employee as having a new manager. This is the standard case.
| Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|
| P1 | CRM | Mgr1 | 2006 | 2008 |
| P1 | My Division | Mgr2 | 2008 | 2009 |
| P1 | My Division | Mgr3 | 2009 | 2010 |
| P1 | GRC | Mgr4 | 2010 | 4712 |
This is a variation of Scenario 3, for example Employee's transfer from 'CRM' to 'My Division' actually occurred in 2007, not in 2008. This is a correction to the historical assignment record. The back-dated change is to the effective_from and effective_to date of the driving OLTP history table.
Option 1
Update the historical data in the warehouse. This doesn't require fact table updates. This option is enabled if DAC parameter UPDATE_CORRECTIONS_FLG is set to 'Y'.
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
| 1 | P1 | CRM | Mgr1 | 2006 | 2007 |
| 2 | P1 | My Division | Mgr2 | 2007 | 2009 |
| 3 | P1 | My Division | Mgr3 | 2009 | 4712 |
Option 2
This option introduces a new warehouse row to track changes. This option is enabled if DAC parameter UPDATE_CORRECTIONS_FLG is set to 'N'.
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
| 1 | P1 | CRM | Mgr1 | 2006 | 2007 |
| 4 | P1 | My Division | Mgr2 | 2007 | 2008 (NEW) |
| 2 | P1 | My Division | Mgr2 | 2008 | 2009 |
| 3 | P1 | My Division | Mgr3 | 2009 | 4712 |
Prior to the back-dated change, the fact table had some transactions pointing to the row 1 of the hierarchy table and some with foreign keys to the row 2. Fact rows with foreign key to row 1 will continue to have the same foreign key or the foreign key will be updated to match either row 2 or row 4 depending on the transaction date.
A back-dated change in the source OLTP system that results in the splitting of the record. For example, the employee changed managers from Mgr1 to Mgr5 in 2007. In the source OLTP system, the original assignment record with Mgr1 has a new end date of 2007 and a new record was added to assign the employee to the new manager Mgr5 in 2007. The warehouse will respond to the OLTP source change as described in the following table.
Option
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
| 1 | P1 | CRM | Mgr1 | 2006 | 2007 |
| 4 | P1 | CRM | Mgr5 | 2007 | 2008 (NEW) |
| 2 | P1 | My Division | Mgr2 | 2008 | 2009 |
| 3 | P1 | My Division | Mgr3 | 2009 | 4712 |
Fact rows with foreign keys to the row 1 will continue to have the same foreign key or the foreign key will be updated to row 4 depending on the transaction date.
A back-dated change gets cascaded to all the records since a particular date, for example Mgr2 in row 3 should be Mgr4 and also change managers for all future rows since 2008. The employee's current assignment records are as follows in the data warehouse:
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
| 1 | P1 | CRM | Mgr1 | 2006 | 2007 |
| 2 | P1 | CRM | Mgr5 | 2007 | 2008 (NEW) |
| 3 | P1 | My Division | Mgr2 | 2008 | 2009 |
| 4 | P1 | My Division | Mgr3 | 2009 | 4712 |
Option
Update the historical and current records in the warehouse:
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
| 1 | P1 | CRM | Mgr1 | 2006 | 2007 |
| 2 | P1 | CRM | Mgr5 | 2007 | 2008 (NEW) |
| 3 | P1 | My Division | Mgr4 | 2008 | 2009 |
| 4 | P1 | My Division | Mgr4 | 2009 | 4712 |
A back-dated change is a row delete in OLTP. Row 2 is deleted in OLTP and row 1 is updated to the end date of 2009.
Option
Transactions will not be deleted in warehouse; but rows will be updated to reflect the changed attributes in the time range including changes initiated by auxiliary tables. This will void fact foreign key updates.
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
| 1 | P1 | CRM | Mgr1 | 2006 | 2008 |
| 2 | P1 | CRM | Mgr1 | 2008 | 2009 (NEW) |
| 3 | P1 | My Division | Mgr3 | 2009 | 4712 |
This section describes the objects have been changed to support back-dated changes in Position Hierarchy.
The following changes were made to the data model:
Added SCD1_WID to W_POSITION_D and W_POSITION_DH.
Adding DELETE_FLG to W_POSITION_DH
Adding new tables W_POSITION_DH_BAK, W_POSITION_DH_PRE_CHG_TMP, W_POSITION_DH_POST_CHG_TMP
Adding new columns SCD_TYPE1B_FLG and SCD_TYPE2B_FLG to W_POSITION_DH_BASE_TMP
This section describes the code changes in Informatica.
The following new mappings have been added:
SIL_PositionDimensionHierarchy_SoftDelete
SIL_PositionDimensionHierarchy_IdentifyBaseModified_TypeB
SIL_PositionDimensionHierarchy_PreChangeTemp
SIL_PositionDimensionHierarchy_PostChangeTemp
SIL_PositionDimensionHierarchy_CopyDelete
SIL_PositionDimensionHierarchy_Delete
The following mappings have been updated:
SIL_PositionDimensioHierarchy
SIL_PositionDimensioHierarchy_Full
SIL_PositionDimensionHierarchy_IdentifyBaseModified
The following sessions/workflows have been added to Informatica:
SIL_PositionDimensionHierarchy_SoftDelete
SIL_PositionDimensionHierarchy_IdentifyBaseModified_TypeB
SIL_PositionDimensionHierarchy_PreChangeTemp
SIL_PositionDimensionHierarchy_PostChangeTemp
SIL_PositionDimensionHierarchy_CopyDelete
SIL_PositionDimensionHierarchy_Delete
The following tasks have been added to DAC:
SIL_PositionDimensionHierarchy_SoftDelete
SIL_PositionDimensionHierarchy_IdentifyBaseModified_TypeB
SIL_PositionDimensionHierarchy_PreChangeTemp
SIL_PositionDimensionHierarchy_PostChangeTemp
SIL_PositionDimensionHierarchy_CopyDelete
SIL_PositionDimensionHierarchy_Delete
|
 Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
