System Columns in Oracle Business Analytics WarehouseTables

Oracle Business Analytics Warehouse tables contain system fields. These system fields are populated automatically and should not be modified by the user.

The following table lists the system columns used in data warehouse dimension tables:

System Column Description

ROW_WID

Surrogate key to identify a record uniquely.

CREATED_BY_WID

Foreign key to the W_USER_D dimension that specifies the user who created the record in the source system.

CHANGED_BY_WID

Foreign key to the W_USER_D dimension that specifies the user who last modified the record in the source system.

CREATED_ON_DT

The date and time when the record was initially created in the source system.

CHANGED_ON_DT

The date and time when the record was last modified in the source system.

AUX1_CHANGED_ON_DT

System field. This column identifies the last modified date and time of the auxiliary table's record that acts as a source for the current table.

AUX2_CHANGED_ON_DT

System field. This column identifies the last modified date and time of the auxiliary table's record that acts as a source for the current table.

AUX3_CHANGED_ON_DT

System field. This column identifies the last modified date and time of the auxiliary table's record that acts as a source for the current table.

AUX4_CHANGED_ON_DT

System field. This column identifies the last modified date and time of the auxiliary table's record that acts as a source for the current table.

DELETE_FLG

This flag indicates the deletion status of the record in the source system. A value of Y indicates the record is deleted from the source system and logically deleted from the data warehouse. A value of N indicates that the record is active.

W_INSERT_DT

Stores the date on which the record was inserted in the data warehouse table.

W_UPDATE_DT

Stores the date on which the record was last updated in the data warehouse table.

DATASOURCE_NUM_ID

Unique identifier of the source system from which data was extracted. In order to be able to trace the data back to its source, it is recommended that you define separate unique source IDs for each of your different source instances.

ETL_PROC_WID

System field. This column is the unique identifier for the specific ETL process used to create or update this data.

INTEGRATION_ID

Unique identifier of a dimension or fact entity in its source system. In case of composite keys, the value in this column can consist of concatenated parts.

TENANT_ID

Unique identifier for a tenant in a multi-tenant environment. This column is typically be used in an Application Service Provider (ASP)/Software as a Service (SaaS) model.

X_CUSTOM

Column used as a generic field for customer extensions.

CURRENT_FLG

This is a flag for marking dimension records as "Y" in order to represent the current state of a dimension entity. This flag is typically critical for Type II slowly changing dimensions, as records in a Type II situation tend to be numerous.

EFFECTIVE_FROM_DT

This column stores the date from which the dimension record is effective. A value is either assigned by Oracle BI Applications or extracted from the source.

EFFECTIVE_TO_DT

This column stores the date up to which the dimension record is effective. A value is either assigned by Oracle BI Applications or extracted from the source.

SRC_EFF_FROM_DT

This column stores the date from which the source record (in the Source system) is effective. The value is extracted from the source (whenever available).

STC_EFF_TO_DT

This column stores the date up to which the source record (in the Source system) is effective. The value is extracted from the source (whenever available).