Oracle^® Business Intelligence Applications Installation and Configuration Guide > Oracle Business Analytics Warehouse for Life Sciences Data Considerations >

Incremental Updates in the Oracle Business Analytics Warehouse LS Dimension Tables

This issue is specific to Oracle Business Analytics Warehouse for Life Sciences and does not affect other products. In the LS dimension tables the following incremental updates are supported. Some dimensions have more than one set of attributes.

W_ALIGNMT_DH

Base Dimensional Hierarchy Table.

Attribute. Zip code, Brick, or Account/Contact Hierarchy depends on what alignment item type is selected.

NOTE:  In release 7.9, all Zip Code, Brick and Account/Contact hierarchy alignment can be loaded.

Description. Contains the current version of alignment only.

W_ALIGNVER_DH

Slowly Changing Dimension Type.

Attribute. Historical Zip code, Brick, or Account/Contact Hierarchy depends on what alignment item type is selected.

NOTE:  In release 7.9, all Zip Code, Brick and Account/Contact hierarchy alignment can be loaded.

Description. Tracks historical alignments. Note the following:

• Depending on selected alignment type in AlignmentType.csv, only the selected alignment rules are extracted and loaded into the Oracle Business Analytics Warehouse. You can change the alignment type only when you run a full refresh ETL.

  NOTE:  In release 7.9, all Zip Code, Brick and Account/Contact alignment types can be extracted and loaded.

  • When the alignment rule is loaded into the Oracle Business Analytics Warehouse, the current alignment rule is stored in the W_ALIGNMT_DH base table and the existing historical alignment rules are stored in W_ALIGNVER_DH table.
• The creation of Alignment Versions is controlled though the setting of the Alignment Version flag in DAC console. In Pharma Analytics 7.9 this flag is now set in the Source System Parameters screen within DAC's Design area. The parameter name is "$$ALIGNMENT_VER" and takes a value of either Y or N.
  • If the $$ALIGNMENT_VER parameter is set to N, then the ETL makes changes to existing alignments if there is a change on an assignment rule.
  • If the $$ALIGNMENT_VER parameter is set to Y, it creates a new alignment version. Any existing alignment moves to the W_ALIGNVER_DH table as history. The W_ALIGNVER_DH table is used only when the $$ALIGNMENT_VER parameter is set to Y.

    The dimension has these characteristics:

• The first alignment after Full load is 1.
• Incremental Updates in the Oracle Business Analytics Warehouse LS Dimension Tables
• A new version is created when a new assignment rule (new position or position relationship) or an assignment criteria is modified (change in postal code, brick, contact, or account) if the Alignment Version flag is set to Y.
• Assignment criteria:
  • Contact ZIP Code or Account ZIP Code cannot use ZIP Code ranges. Each ZIP Code assigned to a territory needs to be on a separate row, so the same value needs to be entered for both ZIP Code Low and ZIP Code High. For example, if ZIP Code 09654 is assigned to a territory, the value for both ZIP Code High and ZIP Code Low should be 09654. Also, the same ZIP Code should not be assigned twice to the same territory and a ZIP Code should be unique to a territory when using assignment criteria for Contact ZIP Code or Account ZIP Code.
  • Contact Brick or Account Brick require unique bricks assigned to a territory.
  • Every new version increases the counter by one and is a sequential number. Any pre-existing history version alignment data moves to W_ALIGNVER_DH historical alignment table whenever a new version is created. That is, the W_ALIGNMT_DH table always maintains the latest version while the W_ALIGNVER_DH table maintains the rest of the historical version if the $$ALIGNMENT_VER parameter is set to Y. So, when the historical alignment dimension table gets too large, your administrator can create a SQL query to delete all attributes for version numbers from the W_ALIGNVER_DH table.
  • Effective date of the alignment is assignment activation date.