Star schemas

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).

erDiagram sales_facts { int DateId int StoreId int ProductId int UnitCount float Total } date_dims ||--o{ sales_facts : DateId store_dims ||--o{ sales_facts : StoreId product_dims ||--o{ sales_facts : ProductId date_dims { int DateId Datetime Date int Day string DayOfWeek int Month string MonthName int Year } store_dims { int StoreId int StoreNumber string State } product_dims { int ProductId string Sku string Name string Category }

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

Snowflake Schemas

Star schemas with normalized dimension tables

Broader Topics Related to Star Schemas

Data warehouses

Data warehouses: Architecture, data flow, and related topics

Online Analytical Processing (OLAP)

A technique to create views and calculations from multi-dimensional data

Star Schemas Knowledge Graph