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

Incremental Updates in the Siebel Data Warehouse LS Dimension Tables


In the LS dimension tables, the following incremental updates are supported. Some dimensions have more than one set of attributes.

W_ALIGNMT_DH

Slowly Changing Dimension Type. 2

Attribute. Link between ZIP, Brick, Account, and Contact Hierarchy

Description. Tracks historical alignments.

  • 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.

    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.
    • Activation date changes have to be entered manually. The ETL looks for the change and creates a new version, even though the rules have not been applied to the Siebel transactional database.
    • 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. When a dimension table gets too large, your administrator can create an SQL query to delete all attributes for version numbers.
    • 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_PROD_RANK_D

Slowly Changing Dimension Type. 3

Attributes. RANKING, RANKING1, RANKING2, RANKING3

Description. Keep three previous rankings to allow restatement. Effective date is ETL run date.

Attributes. RATING, RATING1, RATING2, RATING3

Description. Keep three previous ratings to allow restatement. 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.

Siebel Data Warehouse Installation and Administration Guide