About
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:
- Typically, logical fact tables should not contain any keys. The only exception is when you need to send Logical SQL queries against the Oracle BI Server from a client that requires keys. In this case, you need to expose those keys in both the logical fact tables, and in the Presentation layer.
- Normally, all columns in logical fact tables are aggregated measures, except for keys required by external clients, or dummy columns used as a divider. Other non-aggregated columns should instead exist in a logical dimension table.
Unlike relational fact tables, logical fact tables can contain measures of different grains. Because of this, grain is not a reason to split up logical fact tables.
Aggregate sources should be created as separate logical table sources. For fact aggregates, use the Content tab of the Logical Table Source dialog to assign the correct logical level to each dimension.
Fact sources are selected on a combination of the fields selected as well as the levels in the dimensions to which they map.
Articles Related
Fact Table Fragmentation
OBIEE - (Fragment|ation) (Partitioning):
Types of fact tables
The following is a list of the types of fact tables that OBIEE recognizes when using the project feature:
- Simple (base) fact table. Fact tables with nonderived columns or with columns that are derived but whose expressions are made up of constants.
- Compound fact table. Fact tables with derived columns whose components come from other fact tables
- Component fact table. Simple (base) fact tables that are part of a compound fact table. Their nonderived columns are used in the compound fact table.