Facts:
The fact table is at the center of a star schema and holds
the primary measurement
data. They contain the actual numerical measurements that
the business is interested in.
Fact tables express the many-to-many relationships between
dimensions.
A fact table typically has two types of columns: those that
contain measures and those
that are foreign keys to dimension tables. Some key features
of a fact table are
- Multi part Key. i.e. a composite key with one foreign key for each dimension.
- Time is a always a part of the key
- Usually numeric. Keys are surrogate integers and the measures are numeric.
- Typically additive.
Dimensions:
The dimension tables are where the attributes of the
dimensions of the business are stored. The best attributes are textual and discrete and used to
constraint the fact table. Each of these textual descriptions helps us to describe the member of the
respective dimension.
- They are the entry points into the fact tables. They determine the grain of the fact table.
- They serves as a primary source of query constraints grouping and report labels/row headers.
- They are relatively shallow in terms of rows but are wide with many large columns.
- They are not usually time dependent
- Hierarchical relationships.
- Robust dimension attributes delivers analytic slicing and dicing capabilities.
- Dimension tables are de-normalized.
- Examples of Dimensions: Employee, Time Product Customer etc
