A star schemas organizes relational data into fact and dimension tables, typically to store data in a data warehouse. Fact tables consist of measurements and metrics (e.g. sales) while dimension tables consist of related data that can be used for filtering and categorization (e.g. which products were sold).
This allows reports to be easily built. For example, to calculate TV sales by brand and year using SQL:
SELECT date_dims.Year, product_dims.Brand, SUM(sales_facts.UnitCount) as TotalUnitsSold FROM sales_facts INNER JOIN date_dims ON (sales_facts.DateId = date_dims.DateId) INNER JOIN store_dims ON (sales_facts.StoreId = store_dims.StoreId) INNER JOIN product_dims ON (sales_facts.ProductId = product_dims.ProductId) WHERE product_dims.Category = 'TV' GROUP BY product_dims.Brand, date_dims.Year ORDER BY date_dims.Year, product_dims.Brand, -- Sample output -- /* Year | Brand | TotalUnitsSold --------------------------------------- 2020 | Panasonic | 123 2020 | Samsung | 121 2020 | Sony | 234 2021 | Panasonic | 271 2021 | Samsung | 90 2021 | Sony | 115 */
Types of fact tables
Transaction Fact Tables
Transaction fact tables contain the lowest level of detail available and are the most common type of fact table. Each row in a transaction fact table captures a single event (e.g., a click on a web page) and link to all available dimensions for that event.
Periodic snapshot fact tables
Periodic snapshot fact tables, or simply snapshot fact tables, summarize measurements for a given time period (e.g. hourly, daily, etc.). Snapshot fact tables contain pre-aggregated measures that can be derived from transaction fact tables. Examples of periodic snapshot fact tables include hourly snapshots of clicks on a webpage, daily sales, and monthly account balances.
Accumulating snapshot fact tables
Accumulating snapshot fact tables capture key milestones in a given business process to simplify analysis of time between each step of the process. For example, an accumulating snapshot fact table for the checkout process of an e-commerce website might store one row for every user and mark the key steps (when the cart was reviewed, when shipment information was provided, when payment was provided, etc.).
Deeper Knowledge on Star Schemas
Star schemas with normalized dimension tables
Broader Topics Related to Star Schemas
Data warehouses: Architecture, data flow, and related topics
Online Analytical Processing (OLAP)
A technique to create views and calculations from multi-dimensional data