Facts, dimensions and cubes, the five OLAP operations, and the star, snowflake and fact constellation schemas.
3.1 Concept
The multidimensional data model views data as facts that are analysed along dimensions. A fact is a numeric measure, such as a sales amount, and a dimension is a point of view, such as time, product or place. Each dimension can also have a concept hierarchy, which arranges its values from detailed to general.
ExampleAt ShopMart, the fact is the sales amount, and its dimensions are Time, Product and Store. The Time dimension has the hierarchy day, month, quarter and year. The Store dimension has the hierarchy store, city, state and region.
ExplanationA question such as "What was the sales amount for each product category, in each quarter, in Uttar Pradesh?" simply chooses one level on each dimension. The model can therefore answer many different questions without a new table being built for each one.
3.2 Data cubes
A data cube is an n-dimensional view of the facts, in which each cell holds an aggregated measure for one combination of dimension values.
ExampleShopMart builds a three-dimensional cube with Time (in quarters), Product (by category) and Store (by city). The table below shows one two-dimensional slice of this cube for the city of Lucknow, with amounts in lakhs of rupees.
| Category | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| Electronics | 120 | 135 | 150 | 210 |
| Clothing | 80 | 95 | 70 | 140 |
| Grocery | 200 | 205 | 210 | 220 |
ExplanationThe cell for Electronics, Q4 and Lucknow holds 210, which is the total of every electronics bill in Lucknow's stores during the fourth quarter. A cube with three dimensions and no hierarchies can be summarised in 2³ = 8 ways, and each of these summaries is called a cuboid. The base cuboid {time, product, store} holds the most detail. Three cuboids use two dimensions each ({time, product}, {time, store} and {product, store}), and three use one dimension each ({time}, {product} and {store}). The apex cuboid {} holds a single number, the grand total of all sales.
3.3 OLAP operations on cubes
Five standard operations let a user move around a data cube. The table below describes each operation and applies it to the Lucknow cube.
| Operation | What it does | Example on the Lucknow cube |
|---|---|---|
| Roll-up | Aggregates up a concept hierarchy | Moving from city to state adds the sales of Lucknow, Kanpur and Agra into one figure for Uttar Pradesh |
| Drill-down | Moves down to finer detail | The Q4 electronics figure of 210 is split into October 60, November 90 and December 60 |
| Slice | Fixes one dimension at a single value | Fixing Time at Q4 gives a Product × Store table for Q4 only |
| Dice | Selects ranges on two or more dimensions | Choosing Time in {Q1, Q2} and Category in {Electronics, Clothing} gives a smaller sub-cube |
| Pivot | Rotates the axes of the view | Quarters are shown as rows and categories as columns |
Example (roll-up)In Q4, electronics sales were ₹210 lakh in Lucknow, ₹160 lakh in Kanpur and ₹130 lakh in Agra. Rolling up from city to state gives one figure for Uttar Pradesh: 210 + 160 + 130 = ₹500 lakh.
Example (drill-down)The Q4 electronics figure for Lucknow, ₹210 lakh, is drilled down from quarter to month. It splits into October ₹60 lakh, November ₹90 lakh and December ₹60 lakh, and these add back to 60 + 90 + 60 = ₹210 lakh.
Example (slice)Fixing Time = Q4 leaves a two-dimensional table of category by city for Q4 only. For Lucknow, that table holds Electronics ₹210 lakh, Clothing ₹140 lakh and Grocery ₹220 lakh, so Lucknow's Q4 total is 210 + 140 + 220 = ₹570 lakh.
Example (dice)Choosing Time in {Q1, Q2} and Category in {Electronics, Clothing} for Lucknow gives a sub-cube of four cells: Electronics Q1 ₹120 lakh, Electronics Q2 ₹135 lakh, Clothing Q1 ₹80 lakh and Clothing Q2 ₹95 lakh. The total of this sub-cube is 120 + 135 + 80 + 95 = ₹430 lakh.
Example (pivot)Pivoting the Lucknow table turns its three category rows into columns and its four quarter columns into rows. The same twelve numbers are shown, only in a different arrangement.
ExplanationRoll-up and drill-down move up and down a hierarchy, and the totals always agree between levels, as the drill-down example shows. Slice and dice cut the cube into smaller pieces. Pivot changes only the way the data is displayed and does not change the data itself. In practice, a manager combines these operations: for example, she may slice the cube to Q4, drill down to months and then pivot the result to prepare a report.
3.4 Star schema
In a star schema, one central fact table holds the measures together with a foreign key for each dimension. The fact table is linked to one denormalised table for each dimension, so the diagram of the schema looks like a star.
In the figure, every dimension table joins directly to the fact table. As a result, a query needs only one join for each dimension it uses.
ExampleThe following SQL query finds the total sales amount for each product category in 2025.
SELECT p.category, SUM(f.amount)
FROM sales_fact f
JOIN product_dim p ON f.product_key = p.product_key
JOIN time_dim t ON f.time_key = t.time_key
WHERE t.year = 2025
GROUP BY p.category;Suppose the fact table for 2025 contains only the five rows below, already joined to the Product table to show each product's category.
| time_key (2025) | product_key | Category | amount (₹) |
|---|---|---|---|
| 20250105 | 11 | Electronics | 45,000 |
| 20250212 | 27 | Clothing | 3,200 |
| 20250318 | 11 | Electronics | 30,000 |
| 20250420 | 54 | Grocery | 1,800 |
| 20250511 | 27 | Clothing | 2,600 |
The query groups these rows by category and adds the amounts. It returns Electronics = 45,000 + 30,000 = ₹75,000, Clothing = 3,200 + 2,600 = ₹5,800 and Grocery = ₹1,800.
ExplanationThe Product table stores the category and the brand in the same row as the product, which means it is denormalised. The word "Electronics" is therefore repeated for every electronic product. This repetition costs some storage space, but it keeps the number of joins small, so queries run quickly.
3.5 Snowflake schema
A snowflake schema is a star schema in which the dimension tables are normalised, meaning that each dimension is split into smaller related tables.
In the figure, the Product table now points to a separate Category table, and the Store table points to a separate City table.
ExampleIn the snowflake version of ShopMart's schema, the Product table holds product_key, name and category_key, and it points to a Category table that holds category_key, category_name and department. In the same way, the Store table holds store_key and city_key, and it points to a City table that holds city_key, city and state.
ExplanationSuppose there are 5,000 electronic products. In the star schema, the 11-character word "Electronics" is stored in every one of their rows, which takes 5,000 × 11 = 55,000 bytes. In the snowflake schema, the word is stored once (11 bytes), and each product row holds only a 2-byte category key, which takes 5,000 × 2 = 10,000 bytes. The total is about 10,011 bytes, so the snowflake schema saves about 45,000 bytes for this one column, and renaming the category needs only one update instead of 5,000. However, the query in section 3.4 now needs one more join, from Product to Category, so it runs more slowly.
3.6 Fact constellation (galaxy schema)
A fact constellation, which is also called a galaxy schema, contains several fact tables that share some of their dimension tables.
ExampleShopMart adds a Shipping fact table with the columns time_key, product_key, from_warehouse_key, to_store_key, units_shipped and cost. This new fact table shares the Time and Product dimensions with the existing Sales fact table.
For March 2026, the Shipping fact table shows that 520 units of a 43-inch LED television were shipped to stores, while the Sales fact table shows that 410 units were sold.
ExplanationBecause both fact tables use the same Time and Product keys, the two figures can be joined and compared directly. The excess stock is 520 − 410 = 110 units, which is 110 ÷ 520 ≈ 21 per cent of what was shipped, so this product is being overstocked. This kind of schema suits large enterprise warehouses that model many business processes.
3.7 Measures
The measures stored in a cube fall into three groups, according to how they can be calculated from partial results. The table below summarises them.
| Type | Rule | Examples |
|---|---|---|
| Distributive | Partial results can be combined directly | Count, sum, minimum and maximum |
| Algebraic | Computed from a fixed number of distributive values | Average and standard deviation |
| Holistic | Need all of the raw data | Median, mode and rank |
ExampleStore A sold items worth 2, 4 and 9 thousand rupees, and store B sold items worth 1 and 8 thousand rupees. The sum for store A is 15 and the sum for store B is 9, so the total for both stores is simply 15 + 9 = 24, which shows that sum is distributive. The average for both stores is (15 + 9) ÷ (3 + 2) = 4.8, which can be calculated from the two sums and the two counts, so average is algebraic. The median of store A is 4 and the median of store B is 4.5, but the median of all five values {1, 2, 4, 8, 9} is 4, and in general it cannot be worked out from the two separate medians, so median is holistic.
ExplanationDistributive and algebraic measures can be pre-computed for each cell of a cube and then rolled up cheaply. Holistic measures need the raw data, so cubes either compute them approximately or calculate them only when they are asked for.
Exam points
- A cube with n dimensions and no hierarchies has 2n cuboids; the base cuboid is the most detailed and the apex cuboid is the grand total.
- Roll-up climbs a concept hierarchy; drill-down goes to finer detail.
- Slice fixes one dimension; dice selects ranges on two or more dimensions; pivot only rotates the view.
- A star schema has one fact table joined to denormalised dimension tables.
- A snowflake schema normalises dimension tables, saving space but adding joins.
- A fact constellation (galaxy schema) has several fact tables sharing dimension tables.
- Sum and count are distributive, average is algebraic, and median is holistic.
Independent study notes for learning purposes. Not affiliated with or endorsed by any university, publisher or course provider. Product and company names are trademarks of their respective owners.