Oracle Business Analytics Warehouse (OBAW) Table Types & Descriptions

Oracle Business Analytics Warehouse (OBAW) Table Types & Descriptions

Oracle Business Intelligence Applications are driven by a data warehouse (Oracle Business Analytics Warehouse – OBAW) the stores transformed and aggregated data from the many sources that the application supports.  This post describes the various types of tables that make up the OBAW – how they are used and how to identify them.

Table Type	Table   Prefix	Table Type Description
Aggregate Tables	_A	Contain summed data
Dimension tables	_D	Star analysis dimensions
Delete tables	_DEL	Tables that store the IDs of the   entities that were physically deleted from the source system and should be   flagged as deleted from the data warehouse.
Dimension Hierarchy tables	_DH	Tables that store the dimension’s   hierarchical structure
Dimension Helper tables	_DHL	Tables that store M:M relationships   between two joining dimension tables
Staging tables for Dimension Helper	_DHLS	Staging tables for storing M:M   relationships between two joining dimension tables
Staging for Dimension Hierarchy	_DHS	Staging tables for storing the   hierarchy structures of dimensions that have not been through the final ETL   transformations
Staging tables for Dimension	_DS	Tables used to   hold dimension information 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 analyzedby dimensions   that have not been through the final ETL   transformations.
Internal tables	_G, _GS,   _S	Internal tables   are used primarily by ETL mappings for data transformation and controlling   ETL runs.
Helper tables	_H	Helper tables are   inserted between the fact and dimension tables to support a many-to-many   (M:M) relationship between fact and dimension records.
Map dimension tables	_M	Tables that store   cross-referencing mappings between the external data ID and the internal ID.
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 that are   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.
Persisted staging tables	_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.
Pre-staging temporary tables	_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 tables	_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.

Staging Tables
The primary Staging Tables are the Dimension Staging (_DS) and Fact Staging (_FS) tables.  Other Staging tables include Staging for Dimension Helper (_DHLS), Staging for Dimension Hierarchy (_DHS), Persisted Staging (_PS), and Staging for Usage Accelerator (_WS).

When data is extracted from Sources, it is first loaded into a staging area before being loaded into the OBAW.

The Staging Tables stage incremental data from the source system.  On each ETL run, the staging tables’ data is truncated and then re-populated with new or changed data from the source. So these Staging Tables will hold only incremental data except for the initial load when it holds all the data from the source for a predetermined period of time.

Staging tables’ structure is independent of source data structures and resembles the structure of data warehouse tables. This resemblance allows staging tables to be used also as interface tables between the transactional database sources and data warehouse target tables.

Fact Tables
The OBAW Fact tables are suffixed with _F.

Fact tables contain the metrics generated from transactions in the business captured by the source systems.

Fact tables in the Oracle Business Analytics Warehouse contain a surrogate key if the records in the fact table need to be updated or if the fact table has an aggregate table on top of it. Each fact table also contains one or more numeric foreign key columns to link it to various dimension tables.

Aggregate Tables
The OBAW Aggregate tables are suffixed with _A.

The Aggregate tables hold pre-aggregated Fact and Dimension table data. This is one of the major performance features of a data warehouse because the data does not have to be aggregated on-demand, and query results can be quickly returned.

Dimension Tables
The Dimension tables in OBAW are suffixed with _D.

The Dimension Tables provide context / meaning to the Fact tables measures data.

The unique numeric key (ROW_WID) for each dimension table is generated during the load process. This key is used to join each dimension table with its corresponding fact table or tables. It is also used to join the dimension with any associated hierarchy table or extension table. The ROW_WID columns in the Oracle Business Analytics Warehouse tables are numeric. In every dimension table, the ROW_WID value of zero is reserved for Unspecified. If one or more dimensions for a given record in a fact table is unspecified, the corresponding key fields in that record are set to zero.

Mini Dimension Tables
The Mini Dimension tables are suffixed with _MD.  Their purpose is for performance enhancement.

Mini-dimension tables include combinations of the most queried attributes of their parent dimensions. They improve query performance because the database does not need to join the fact tables to the big parent dimensions but can join these small tables to the fact tables instead.

Hierarchy Tables
Hierarchy tables in the OBAW are suffixed with _DH.

Some dimension tables have hierarchies into which each record rolls. This hierarchy information is stored in a separate table, with one record for each record in the corresponding dimension table. This information allows users to drill up and down through the hierarchy in reports.  There are two types of hierarchies in the Oracle Business Analytics Warehouse: a structured hierarchy in which there are fixed levels, and a hierarchy with parent-child relationships. Structured hierarchies are simple to model, since each child has a fixed number of parents and a child cannot be a parent. The second hierarchy, with unstructured parent-child relationships, is difficult to model because each child record can potentially be a parent and the number of levels of parent-child relationships is not fixed.

Helper Tables
Helper tables in OBAW are prefixed with _H (Helper) and _DHL (Dimension Helper).

Helper tables are used by the Oracle Business Analytics Warehouse to solve complex problems that cannot be resolved by simple dimensional schemas.  In a typical dimensional schema, fact records join to dimension records with a many-to-one relationship. To support a many-to-many relationship between fact and dimension records, a helper table is inserted between the fact and dimension tables.  The helper table can have multiple records for each fact and dimension key combination. This allows queries to retrieve facts for any given dimension value. It should be noted that any aggregation of fact records over a set of dimension values might contain overlaps (due to a many-to-many relationship) and can result in double counting.

At times there is a requirement to query facts related to the children of a given parent in the dimension by specifying only the parent value (example: manager’s sales fact that includes sales facts of the manager’s subordinates). In this situation, one helper table containing multiple records for each parent-child dimension key combination is inserted between the fact and the dimension. This allows queries to be run for all subordinates by specifying only the parent in the dimension.-

Internal Tables
Internal tables in the OBAW are suffixed with _G, _GS and _S.

Internal tables are used primarily by ETL (extract-transform-load) mappings for data transformation and for controlling ETL runs. These tables are not queried by end users and are not directly managed by the Oracle Data Warehouse Administration Console (DAC).