OBIEE - Identifying a Fact Table
About
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.
Articles Related
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.
Illustration
The picture bellow illustrates the many-to-one joins to a fact table in a Business Model Diagram.
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
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.