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

8 Reasons Oracle BI Apps is likely to have a Lower TCO than home-grown BI environments

Oracle Business Intelligence Applications, aka Oracle BI Apps or OBIA, is a set of pre-built BI solutions, that provide information to business stakeholders that they use to make more informed decisions and improve business outcomes.
Oracle provides BI Analytics Applications for ERP – which include Financials, HR, Procurement & Spend, Projects, Supply Chain management, Order management, Logistics; and for CRM functions – which include Sales, Marketing, Service, Call Center, Price, and Loyalty.
The pre-built solutions include Source adaptors, ETL, data warehouse data models, metadata repository, dashboards and reports – the entire BI/DW value chain.  The platform also allows for customizations.

This short article shares some reasons why implementing Oracle BI Apps will likely have a much lower TCO than traditional home-grown BI environments.

(1) Implementation of Oracle BI Apps is faster and requires a smaller implementation team. There will be much less design effort, far less redo’s, much less waiting between team members, and a lot less risk.

(2) After implementing one or more Oracle BI Apps, the platform can be extended to add additional Applications as needed by the business. And since all applications share conformed dimensions (including standardized codes), they will integrate easily for cross-functional area analysis.

(3) Maintenance of Oracle BI Apps is standardized and has a proven recommended methodology.  If the appropriate steps are followed, maintenance of the solution is smooth, and requires less team members when compared to a traditional BI platform.

(4) A great base of a solution on which your company can grow. Having such a solid starting point for the Apps that you implement will lead to quick user adoption and early generation of many more high-value ideas about the data that they would like to see, manipulate, analyze, etc. This is a much higher value activity than scrambling to come up with ALL the reporting requirements and definitions when starting a project from scratch.

(5) Since the Oracle BI Apps are performance and administration optimized, a BI team will spend less time working on performance and adminsitration tasks.

(6) The features of the DAC and the overall BI Apps design lead to faster ETL run times resulting in more effective batch windows.

(7) Although all companies are different and have different needs for business analytics, Oracle BI Apps are created from tremendous research and feedback from hundreds of companies.  What you get out-the-box will undoubtedly be of tremendous value, as your company will benefit from all those ideas and best practices developed from working with hundreds of customers over many years. You will be getting and benefitting from metrics that you would not have though about.

(8) Since OBIA is built on top of OBIEE, the applications are easily customizable.  With the existing framework built using best practices, an implementation team can follow that framework to add custom objects throughout the BI value chain – from the source through to the presentation layer.

Despite these benefits, each business is different and must be analyzed carefully to determine if the Oracle BI Apps is an appropriate solution.  Good luck with your BI/DW plans!