OBIEE - Identifying a Fact Table

Bi Server Architecture With Client


A fact table is a table with measures. Measures need to be defined in a logical fact; any column with an aggregation rule is a measure. Oracle BI Server understands that any table in a repository that only has many-to-one (N:1) joins to it is a fact table. The Oracle BI Server uses the logical joins in the business model to identify a fact table.

In other words, in the business model, a table where all the joins have the crow’s feet symbol (indicating the “many” side) pointing into the table, and no joins pointing out of it, is, in essence, a fact table. One exception to this rule is a bridge table.

Gold Rule

The Administration Tool considers a table to be a logical fact table when

  • the table has only many-to-one (N:1) logical joins pointing to it

We said that the fact table is at the many end of a all logical (0, 1:N) (zero, one-to-many relationship) joins.


The picture bellow illustrates the many-to-one joins to a fact table in a Business Model Diagram.

Star Schema

How to verify that OBIEE consider your table as a fact table ?

To verify this in a business model, you can :

  • check the cardinalities of the joins
  • see in the diagram that all joins have the crow's feet symbol (indicating the many side) pointing into the Fact-Pipeline table and no joins pointing out of it.
  • launch the “Selected Fact Table and Dimensions” command from the business model (see below)

To launch the “Selected Fact Table and Dimensions” command : in a business model, open a repository in the Administration Tool, right-click a fact table (in yellow), and select Business Model Diagram > Selected Fact Table and Dimensions

Obiee Fact Bm Select Fact Dim

A bridge table is an exception to this joins rule.

It is recommended that you do not specify logical keys for logical fact tables

If the table is not yet joined to any other table, the option is not available because it is considered a fact table.

Recommended Pages
Bi Server Architecture With Client
OBIEE - Fact table (of logical fact table)

Dimension tables are expected to store columns that cannot be aggregated whereas fact tables are expected to store measure columns that can be aggregated. As a general rule: don’t include...
Bi Server Architecture With Client
OBIEE - Repository Design

... .. A consideration to take when designing a subject area is to pay attention at the final user. Do you design for a user:OBIEE Analytics/Reporting...
Obiee Dimension Drill Down Parameters
OBIEE 10G/11G - Level-based Hierarchy

Level-based hierarchy is the first type of hierarchy in OBIEE. Dimension hierarchy levels allow : to perform aggregate navigation, to configure level-based measure calculations, users from Dashboard...

Share this page:
Follow us:
Task Runner