Siebel Analytics Applications Installation and Administration Guide > Siebel Data Warehouse for Life Sciences Data Considerations >

Incremental Updates in the Siebel Data Warehouse LS Dimension Tables

This issue is specific to Analytics 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.

Description. Contains the current version of alignment only.

W_ALIGNVER_DH

Slowly Changing Dimension Type. 2

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

Description. Tracks historical alignments.

• In Siebel Analytics Applications Version 7.8.3:
  • Depending on what alignment item type is selected in AlignmentType.csv, only the selected alignment rule is extracted and loaded into the Siebel Data Warehouse. You can change the alignment item type only when you run full refresh ETL.
  • When the alignment rule is loaded into Siebel Data 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.
• If ETL Alignment Version is set to N, ETL makes changes to existing alignment if there is a change on an assignment rule.
• If ETL Alignment Version 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 ETL Alignment Version is set to Y

  The dimension has these characteristics:

  • The first alignment after Full load is 1.
  • 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 ETL Alignment Version 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 ETL Alignment Version 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.

W_ORG_D

Slowly Changing Dimension Type. 3

Attributes. ACCNT_TYPE_CD, ACCNT_TYPE_CD1, ACCNT_TYPE_CD2, ACCNT_TYPE_CD3

Description. Tracks three past account types. If the account type changes a fourth time, the first change is deleted and only the past three changes are maintained. Effective date is ETL run date to each group of attributes.

Attributes. NAME, NAME1, NAME2, NAME3

Description. Tracks three past account names. If the account name changes a fourth time, the first change is deleted and only the past three changes are maintained. Effective date is ETL run date.

Attributes. NUMB_OF_BEDS, NUMB_OF_BEDS1, NUMB_OF_BEDS2, NUMB_OF_BEDS3

Description. For an account type of hospital and clinic, tracks three past quantities. If the number of beds changes a fourth time, the first change is deleted and only the past three changes are maintained. Effective date is ETL run date.

Attributes. PAR_INTEGRATION_ID, PAR_ITGR_ID1, PAR_ITGR_ID2, PAR_ITGR_ID3

Description. Tracks three past parent accounts. If the parent account changes a fourth time, the first change is deleted and only the past three changes are maintained. Effective date is ETL run date.

W_POSTN_CON_D

Slowly Changing Dimension Type. 3

Attributes. STATUS, STATUS1, STATUS2, STATUS3

Description. Tracks three past contact statuses. If the contact status changes a fourth time, the first change is deleted and only the past three changes are maintained. Effective date is ETL run date.

W_POSITION_D

Slowly Changing Dimension Type. 3

Attributes. EMP_FST_NAME, EMP_MID_NAME, EMP_LAST_NAME, EMP_FST_NAME_H1, EMP_MID_NAME_H1, EMP_LAST_NAME_H1, EMP_FST_NAME_H2, EMP_MID_NAME_H2, EMP_LAST_NAME_H2, EMP_FST_NAME_H3, EMP_MID_NAME_H3, EMP_LAST_NAME_H3

Description. Tracks three past employee names assigned to a position. If the employee name changes a fourth time, the first change is deleted and only the past three changes are maintained. Effective date is ETL run date.

W_ACCNT_RNK_D/W_ACCNT_RNK_SCD

Slowly Changing Dimension Type. 3 and 2 respectively

Attributes. RANKING, RANKING1, RANKING2, RANKING3

Description. Keep three previous rankings to allow restatement in W_ACCNT_RNK_D table. In addition, the historical account ranking is all maintained in W_ACCNT_RNK_SCD. Effective date is ETL run date.

Attributes. RATING, RATING1, RATING2, RATING3

Description. Keep three previous ratings to allow restatement in W_ACCNT_RNK_D table. In addition, the historical account ratings are all maintained in W_ACCNT_RNK_SCD. Effective date is ETL run date.

W_CON_RNK_D/W_CON_RNK_SCD

Slowly Changing Dimension Type. 3 and 2 respectively

Attributes. RANKING, RANKING1, RANKING2, RANKING3

Description. Keep three previous rankings to allow restatement in W_CON_RNK_D table. In addition, the historical contact rankings are all maintained in W_CON_RNK_SCD. Effective date is ETL run date.

Attributes. RATING, RATING1, RATING2, RATING3

Description. Keep three previous ratings to allow restatement in W_CON_RNK_D table. In addition, the historical contact ratings are all maintained in W_CON_RNK_SCD. Effective date is ETL run date.

W_PRODUCT_D

Slowly Changing Dimension Type. 3

Attributes. VENDOR_LOC, VENDOR_LOC1, VENDOR_LOC2, VENDOR_LOC3

Description. Tracks three past vendor locations. If the vendor location changes a fourth time, the first change is deleted and only the past three changes are maintained. Effective date is ETL run date.