Star Schema (Data Warehouse)

Definition. A star schema is the simplest dimensional-modelling scheme used to implement a data warehouse. It consists of one central fact table, which stores the business measures (numeric facts) plus foreign keys, and several dimension tables placed around it, which store the descriptive, contextual attributes. Because the fact table sits at the centre with the dimensions radiating out, the resulting ER diagram looks like a star.

Example - retail sales warehouse

• Fact table: Sales(Time_key, Product_key, Store_key, Promo_key, Quantity, Amount)
• Dimensions: Time(Time_key, Date, Month, Quarter, Year), Product(Product_key, Name, Category, Brand), Store(Store_key, City, Region, Manager), Promo(Promo_key, Type, Discount)

              Time
               |
  Product --- Sales --- Store
               |
              Promo

Key characteristics

1. The fact table is large and narrow (keys + measures); dimension tables are small and fully denormalised.
2. Queries need only a few simple joins (star-join optimisation), so performance is high and the structure is easy for business users to understand.
3. Well suited to OLAP operations such as roll-up, drill-down, slice and dice.
4. The snowflake schema is the normalised variant in which dimension hierarchies are split into additional tables: it saves storage but adds joins and slows queries. Star schema is therefore preferred for performance and simplicity.