from <Data Warehouse Toolkit>
Agenda
- Fact table
- Dimensional table
- Facts and Dimensions Joined in a Star Schema
Fact Table
Fact table — stores the performance measurements resulting from an organizations’ business process events.
Fact represents a business measure.
Each row in a fact table corresponds to a measurement event.
Grain — The data on each row is at a specific level of detail (one row per product sold on a sales transaction)
Fact Tables:
- Facts are continuous values (the dollar sales amount)
- Two or more foreign keys that connect to the dimension table’s primary keys
- Have its own primary key composed of a subset of the foreign key — composite key
- Express many-to-many relationships
Dimension tables
Dimension tables — integral companions to a fact table. containing the textual context associated with a business process measurement event.
- Describe “who, what, when, how, and why” associated with the event
- Have fewer rows than fact tables, but can be wide with many large text columns
- Each dimension is defined by a single primary key, which serves as the basis for referential integrity with any given fact table to which it is joined.
Dimension attributes serve as the primary source of the query constraints, groupings, and report labels.
Attributes are identified as the by words. For example, when a user wants to see dollar sales by brand, brand must be available as a dimension attribute.
Facts and Dimensions Joined in a Star Schema
dimension attributes supply the report — filters and labeling
fact tables supply the report’s — numeric values
Star Schemas and OLAP Cubes
Star schemas — are dimensional structures deployed in RDBMS, consisting of fact tables linked to associated dimension tables via primary/foreign key relationships
OLAP cube — is a dimensional structure implemented in a multidimensional database.