Data Federation, Horizontal Federation and Vertical Federation in OBIEE

Data Federation is the process of and implementation of integrating metadata from multiple data sources into a single metadata database to allow for the integrated reporting of the data from those multiple sources. In OBIEE terms, Data Federation involves bringing in the metadata from multiple sources into the physical layer of the RPD (BI Repository) and integrating the metadata into a single business model and possibly a single subject area.  In OBIEE, the data sources can be relational (OLTP databases or star-schema data warehouses), multidimensional (OLAP), or files (such as Excel or flat files); and the data can be of varying levels of aggregation in these sources.  This is one of the most powerful features of OBIEE.

When an Analysis (report) is run from Answers that uses data from these sources, the BI Server creates the appropriate SQL (OLTP) or MDX (OLAP) statements to retrieve the data from the appropriate source and then integrates the data for display to the user.  The user does not need to know what the source of the data is, how many sources there are, or how the data is retrieved.

There are two common patterns of how data can be federated in OBIEE for reporting – these patterns/methods are termed Horizontal Federation and Vertical Federation. I will explain them here.

Horizontal Federation involves setting multiple data sources for a common single logical table object in the Business Model and Mapping (BMM) layer of the RPD, such that the granularity of the data from the various sources is at the same level, and some columns of the single logical table come from one source, and some from another source(s) – basically each source adding columns (not rows) to the logical dataset.  A typical scenario for this pattern is where there is related data in multiple sources for a particular subject, but no single source holds the entire body of data for that subject – and this helps to bring all the descriptive data for the subject together into one.

Vertical Federation involves setting multiple data sources for a common single logical table object in the BMM layer of the RPD, such that the data for each columns could be coming from multiple sources, but at varying granularity levels – basically each set adding rows (not columns) to the logical dataset.  A typical scenario for this pattern is where data at an aggregated granularity is sourced from an aggregated OLAP data source or an aggregated OLTP data source, while data at a detailed granularity is sourced from the transaction level (OLTP or detailed-level star-schema) data source.


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


Contain summed data
Dimension tables


Star analysis dimensions
Delete tables


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


Tables that store the dimension’s   hierarchical structure
Dimension Helper tables


Tables that store M:M relationships   between two joining dimension tables
Staging tables for Dimension Helper


Staging tables for storing M:M   relationships between two joining dimension tables
Staging for Dimension Hierarchy


Staging tables for storing the   hierarchy structures of dimensions that have not been through the final ETL   transformations
Staging tables for Dimension


Tables used to   hold dimension information that have not been through the final ETL   transformations.
Fact tables


Contain   the metrics being analyzed by dimensions.
Fact Staging tables


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


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


Tables that store   cross-referencing mappings between the external data ID and the internal ID.
Mini dimension tables


Include   combinations of the most queried attributes of their parent dimensions. The   database joins these small tables to the fact tables.
Primary extract tables


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


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


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


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


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

‘No fact table exists at the requested level’ error in OBIEE 11g

If you receive an error like this when you attempt to run a report, this post might help you.  This is an error that may occur after upgrading from OBIEE 10g to 11g.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 14025] No fact table exists at the requested level of detail:

Check each column used in your report and each column used in the filter definition to ensure that they are referencing the correct table.

A likely cause of this error is that one or more of your select columns or filter columns is referencing a column in a different subject area that the one being used in your report (different from the one you intended).

OBIEE 10g was more forgiving than 11g – and maybe fortunately, so that errors like these do not go unnoticed/undetected.

How to hide the Refresh, ‘Printer Friendly’ and ‘Add to Briefing Book’ Icons at the bottom of a dashboard page in OBIEE 10g

I was recently asked to completely remove or hide, from a particular page, the ‘Refresh’, ‘Printer Friendly’ and ‘Add to Briefing Book’ icons that automatically show up at the bottom of the OBIEE dashboard page in OBIEE 10g.


I had never had the need to do this before, so I did a search and thankfully I quickly found the solution here …

For your convenience, I will reiterate the solution here …

1. Edit Dashboard

2. Add a Text box object into the Dashboard Page

3. Add the following script to the Text Properties

<script type=”text/javascript”>
function RemoveDefaults() {
var tds = document.getElementsByTagName(‘span’);
for (var td = 0; td < tds.length; td++) {
if (tds[td].className != ‘DashboardFormatLinks’) {
tds[td].innerHTML = “&nbsp”;
window.onload = RemoveDefaults;

4. Check the option ” Contains HTML Markup “

5. Save the changes and view the Dashboard Page, the icons for Refresh, Printer Friendly & Add Briefing Book Options are hidden for that specific Dashboard Page alone

Data type related error in OBIEE 11g – Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

If you receive an error like this in OBIEE 11g, this post may help you resolve the problem.  This is an error that you may get after upgrading from OBIEE 10g to 11g.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 59140] The evaluation of the index 11 expression on row count -1 failed. [nQSError: 59001] Binary Logical operation is not permitted on VARBINARY, INTEGER operand(s). (HY000)


OBIEE 10g is a lot more forgiving than 11g.  So what worked in 10g may not work in 11g – such as data type mismatches.  For this error, you need to check the data types of your columns and the data types of the values being used in your column calculations.

For example, you may have a column formula like this …

sum(case when “Table”.”Column”=3 then 1 else 0 end)

Note the column is being used like an INTEGER. This worked in 10g, but does not in 11g.

When you check the data type in your RPD, it may show that the column is a VARCHAR.  You then will need to put quotes around the value (the ‘3’) … like this …

sum(case when “Table”.”Column”=’3’ then 1 else 0 end)

This should hopefully solve the problem.

OBIEE Recent Releases & Support Information

12/5/2012: Picked up this information from the Business Analytics newsletter from Oracle.  It’s good to see that a cumulative patch is now available for all the releases.  Also, note that it seems the support for OBIEE 10.1.3.x will expire in July 2013 – that time will be here before you know it.



The Premier Support for Oracle Business Intelligence Enterprise Edition 10.1.3.x ended in July 2013. For additional information on the de-support status and for details for moving towards recent release refer to:

  • OBIEE 11g: Required and Recommended Patches and Patch Sets [Doc ID 1488475.1]
  • ALERT: Required and Recommended Patch Levels For OBIEE 10g (10.1.3.x) Versions (Doc ID 1082987.1)

Oracle Business Intelligence Enterprise Edition (OBIEE) Bundle Patch was released Oct-12.

This is available for download from the My Oracle Support | Patches and Updates:

Additional information is available from:

Refer to “Retiring Product” section for important information pertaining to OBIEE – Patch Set Updates

The OBIEE Patch Set Update (PSU) that were recently released are as follows:

  • OBIEE PSU  – Sept 2012
  • OBIEE PSU  – Oct 2012
  • OBIEE PSU  – Nov 2012

Oracle Business Intelligence Enterprise Edition (OBIEE) Bundle Patch was released Nov-12.

Bundle patches are a collection of controlled, well tested critical bug fixes for a specific product which may include security contents and occasionally minor enhancements. These are cumulative in nature meaning the latest bundle patch in a particular series would include the contents of the previous bundle patches released. A suite bundle patch is an aggregation of multiple product bundle patches that are part of a product suite.

For OBIEE on, it is planned for monthly run of bundle patch cadence. For the bundle patch:

  • is now for download from My Oracle Support | Patches & Updates
  • includes 67 bug fixes.
  • is cumulative, so it includes everything in,,, and

Bundled Patch Details

  • 1 of 7 – Oracle Business Intelligence Installer [Patch 15844023]
  • 2 of 7 – Oracle Real Time Decisions [Patch 15844066]
  • 3 of 7 – Oracle Business Intelligence Publisher [Patch 14800665]
  • 4 of 7 – Oracle Business Intelligence ADF Components [Patch 15843961]
  • 5 of 7 – Enterprise Performance Management Components Installed from BI Installer [Patch 15844096]
  • 6 of 7 – Oracle Business Intelligence [Patch 14791926]
  • 7 of 7 – Oracle Business Intelligence Platform Client Installers and MapViewer [Patch 15839347]

Additional information may be found in the following Knowledge Articles: