Shows Oracle Business Intelligence-specific data warehouse application tables.
W_ = Warehouse
Unique table name.
Indicates the table type.
_A = Aggregate
_D = Dimension
_DEL = Delete
_DH = Dimension Hierarchy
_DHL = Dimension Helper
_DHLS = Staging for Dimension Helper
_DHS = Staging for Dimension Hierarchy
_DS = Staging for Dimension
_F = Fact
_FS = Staging for Fact
_G, _GS = Internal
_H = Helper
_HS = Staging for Helper
_MD = Mini Dimension
_PE = Primary Extract
_PS = Persisted Staging
_RH = Row Flattened Hierarchy
_TL = Translation Staging (supports multi-language support)
_TMP = Pre-staging or post-staging temporary table
_UD = Unbounded Dimension
_WS = Staging for Usage Accelerator
And below table lists the types of tables used in the Oracle Business Analytics Warehouse:
Aggregate tables (_A)
Contain summed (aggregated) data.
Dimension tables (_D)
Star analysis dimensions.
Delete tables (_DEL)
Tables that store IDs of the entities that were physically deleted from the source system and should be flagged as deleted from the data warehouse.
Note that there are two types of delete tables: _DEL and _PE. For more information about the _PE table type, see the row for Primary extract tables (_PE) in this table.
Dimension Hierarchy tables (_DH)
Tables that store the dimension's hierarchical structure.
Dimension Helper tables (_DHL)
Tables that store many-to-many relationships between two joining dimension tables.
Staging tables for Dimension Helper (_DHLS)
Staging tables for storing many-to-many relationships between two joining dimension tables.
Dimension Hierarchy Staging table
Staging tables for storing the hierarchy structures of dimensions that have not been through the final extract-transform-load (ETL) transformations.
Dimension Staging tables (_DS)
Tables used to hold information about dimensions that have not been through the final ETL transformations.
Fact tables (_F)
Contain the metrics being analyzed by dimensions.
Fact Staging tables (_FS)
Staging tables used to hold the metrics being analyzed by dimensions that have not been through the final ETL transformations.
Internal tables (_G, _GS)
General tables used to support ETL processing.
Helper tables (_H)
Inserted between the fact and dimension tables to support a many-to-many relationship between fact and dimension records.
Helper Staging tables (_HS)
Tables used to hold information about helper tables that have not been through the final ETL transformations.
Mini dimension tables (_MD)
Include combinations of the most queried attributes of their parent dimensions. The database joins these small tables to the fact tables.
Primary extract tables (_PE)
Tables used to support the soft delete feature. The table includes all the primary key columns (integration ID column) from the source system. When a delete event happens, the full extract from the source compares the data previously extracted in the primary extract table to determine if a physical deletion was done in the Siebel application. The soft delete feature is disabled by default. Therefore, the primary extract tables are not populated until you enable the soft delete feature.
Note that there are two types of delete tables: _DEL and _PE. For more information about the _DEL table type, see the row for Delete table (_DEL) in this table.
Persisted Staging table (_PS)
Tables that source multiple data extracts from the same source table.
These tables perform some common transformations required by multiple target objects. They also simplify the source object to a form that is consumable by the warehouse needed for multiple target objects. These tables are never truncated during the life of the data warehouse. These are truncated only during full load, and therefore, persist the data throughout.
Row Flattened Hierarchy Table (_RH)
Tables that record a node in the hierarchy by a set of ancestor-child relationships (parent-child for all parent levels).
Translation Staging tables (_TL)
Tables store names and descriptions in the languages supported by Oracle BI Applications.
Pre-staging or post-staging Temporary table (_TMP)
Source-specific tables used as part of the ETL processes to conform the data to fit the universal staging tables (table types_DS and _FS). These tables contain intermediate results that are created as part of the conforming process.
Unbounded dimension (_UD)
Tables containing information that is not bounded in transactional database data but should be treated as bounded data in the Oracle Business Analytics Warehouse.
Staging tables for Usage Accelerator (_WS)
Tables containing the necessary columns for the ETL transformations.