Advertisements

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

Advertisements

4 Responses to Oracle Business Analytics Warehouse (OBAW) Table Types & Descriptions

  1. Jags says:

    Thanks. This note is very clear and made me understand the process.
    I am in the process of installing OBIEE in multi-node environment. I can upload the architecture diagram. Can I get some suggestion.

  2. Ryan Simpkins says:

    Do you have anything like a data dictionary to show the OTB elements and descriptions of their contents..I’m interested in capturing the OTB ones populated by the ETL and being able to give the users something so they understand the each element.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: