Identifying Logical Fact Tables

Logical fact tables in the Business Model and Mapping layer contain measures that have aggregations built into their definitions.

Logical fact tables are different from physical fact tables in relational models, which instead define facts at the lowest grain of the table.

Measures aggregated from facts must be defined in a logical fact table. Measures are typically calculated data such as dollar value or quantity sold, and they can be specified in terms of dimensions. For example, you might want to determine the sum of dollars for a given product in a given market over a given time period.

Each measure has its own aggregation rule such as SUM, AVG, MIN, or MAX. A business might want to compare values of a measure and need a calculation to express the comparison. Also, aggregation rules can be specific to particular dimensions. The Oracle BI Server lets you define complex, dimension-specific aggregation rules.

You do not explicitly label tables in the Business Model and Mapping layer as fact tables or dimension tables. Rather, the Oracle BI Server treats tables at the one end of a join as dimension tables, and tables at the many end of a join as fact tables.

The image shows the many-to-one joins to a fact table in a Business Model Diagram. In the diagram, all joins have an arrow, indicating the one side, pointing away from the Fact-Pipeline table; no joins are pointing to it. For an example of this in a business model, open a repository in the Administration Tool, right-click a fact table, and select Business Model Diagram > Whole Diagram.