Oracle Business Analytics Warehouse provides Category 2 slowly changing dimension (SCD) functionality, which allows you to track the history of updates to dimension records. When a record in Oracle Business Analytics Warehouse has an update, the updated information is posted into a new row and the old information is kept for historical reporting purposes.
Oracle Business Analytics Warehouse identifies and applies the slowly changing dimension logic chosen by the user after data has been extracted and transformed to be source-independent. Users may configure Oracle BI Applications to support both Category 1 SCDs, in which data is overwritten with updates, and Category 2 SCDs, in which the original records are maintained while a new record stores the updated data. Choosing Category 1 or Category 2 SCDs depends on identifying your historically significant attributes.
Users can choose Category 1 or Category 2 by setting the value for $$TYPE2_FLG to Y or N in Oracle BI Applications Configuration Manager.
These tables have TYPE2 defined by default (by default, it is set to ON):
Common Dimensions
W_PRODUCT_D
W_INVENTORY_PRODUCT_D
W_POSITION_D
W_USER_D
W_INT_ORG_DH
W_PARTY_ORG_D
W_PARTY_PER_D
HCM
W_HR_PERSON_LEG_D
W_HR_POSITION_D
W_JOB_D
W_PAY_GRADE_D
W_SUPERVISOR_D and W_SUPERVISOR_STATUS_D:
Note:
These are not the classical Type-2 dimensions. They have EFFECTIVE_FROM_DT and EFFECTIVE_TO_DT and are set to Type2.However, HCM handles the dates internally and does not rely on the SCDUpdate mappings for these two. These tables are used to build the Supervisor Hierarchy, and not exposed in RPD after the physical layer.
Finance
W_FIXED_ASSET_D
These tables have TYPE2 supported in applications but not set by default (by default, it is OFF; it can be turned ON if required).
Common Dimensions
W_COST_CENTER_D
W_COST_CENTER_DH
W_BUSN_LOCATION_D
W_TERR_DH
Finance
W_AP_TERMS_D
W_BALANCING_SEGMENT_D
W_BANK_D
W_ASSET_BOOK_D
W_ASSET_CATEGORY_D
W_ASSET_LOCATION_D
W_GL_ACCOUNT_D
W_GL_SEGMENT_D
W_NATURAL_ACCOUNT_D
W_PAYMENT_TERMS_D
CRM/OM/PIM
No SCD2 dims
SCM/Procurement/Sourcing/Expense
No SCD2 dims