Logical DW Design: Star, Snowflake & OLAP SQL

01 · Model

Star & snowflake schemas

A warehouse is normally ROLAP — the multidimensional cube stored in relational tables (as opposed to MOLAP’s native, proprietary, sparse physical cube, or HOLAP’s hybrid). The de-facto ROLAP logical model is the star schema.

A star schema is:

Two design choices define the star:

• Surrogate keys. Dimension keys are generated ids, not the operational keys — smaller, so joins and the fact table are cheaper.
• Denormalization. Dimension tables are denormalized: product → type → category is a transitive dependency that a star keeps in one table. That is redundancy — but it means fewer joins, and since a warehouse has no updates, the usual anomaly risk does not bite.

A typical OLAP query joins the fact to its dimensions, filters, and groups:

SELECT   City, Week, Type, SUM(Quantity)
FROM     Week, Shop, Product, Sale
WHERE    Week.ID_Week = Sale.ID_Week AND Shop.ID_Shop = Sale.ID_Shop
         AND Product.ID_Product = Sale.ID_Product AND Product.Category = 'FoodStuff'
GROUP BY City, Week, Type

The snowflake schema normalizes the dimension tables, removing some transitive dependencies into secondary tables: a SHOP table keeps only ID_City as an external key, and CITY(ID_City, City, State) is separate — so a shop’s state is stored once per city, not once per shop. It saves space and helps queries on the fact plus primary dimension attributes, at the cost of extra joins to reach the outer levels.

Both schemas, drawn from the same SALE fact, so the only difference visible is the one that matters — where the hierarchy levels live, and what that costs to query:

02 · Exam-hot

OLAP SQL: the four Part II patterns

Every DW-design Part II ends with a set of SQL queries (usually four), and they draw from the same small toolkit. Master these four patterns and the query section writes itself; miss one and you drop the marks it carries.

-- Pattern 2: greatest-per-group (per series, the conference(s) with the most registrants)
CREATE VIEW ConfNum(ConferenceKey, Series, Year, Num) AS (
  SELECT C.ConferenceKey, C.Series, C.Year, SUM(R.NumOfRegistrants)
  FROM   Registrations R, Conference C
  WHERE  R.ConferenceKey = C.ConferenceKey
  GROUP BY C.ConferenceKey, C.Series, C.Year
);
SELECT C.Series, C.Year
FROM   ConfNum C
WHERE  C.Num = (SELECT MAX(C2.Num) FROM ConfNum C2 WHERE C2.Series = C.Series);

-- Pattern 3: weighted re-aggregation of an AVG measure (avg discount by continent & day)
SELECT   U.Continent, D.DayOfWeek,
         SUM(R.NumOfRegistrants * R.AvgDiscount) / SUM(R.NumOfRegistrants)
FROM     Registrations R, Country U, Date D
WHERE    R.UserCountry = U.CountryName AND R.Date = D.Date
GROUP BY U.Continent, D.DayOfWeek;

-- Pattern 4: bridge table — weighted vs impact
SELECT A.Author, SUM(S.Profit * B.Weight)   -- weighted: profit apportioned by weight
FROM   Authors A, Bridge B, Books Bk, Sales S
WHERE  A.Author_id = B.Author_id AND B.Book_id = Bk.Book_id AND Bk.Book_id = S.Book_id
GROUP BY A.Author;

Pattern 1 is the one that costs marks most often, so run the three operators against a real fact table and count what each emits — the distinction is not in their definitions, it is in their output:

SELECT   month, zone, product, SUM(quantity)
FROM     Sales
GROUP BY month, zone, product WITH CUBE

Here is a full DW-design exercise whose four SQL queries exercise CUBE/GROUPING-SETS, a bridge (actor) axis, an EXISTS filter, and a greatest-per-group — the whole toolkit at once:

03 · Optimization

Materialized views & the MD lattice

Aggregation is expensive, so a warehouse precomputes it: a materialized view is a fact table holding more-aggregated data. Views form a multidimensional lattice ordered by aggregation — $v_i \le v_j$ iff $v_i$ is less aggregated, i.e. $v_j$‘s data can be computed from $v_i$‘s. Whether you can roll a view up cheaply depends on the measure’s operator:

Given a distributive measure, which views are worth precomputing is a real trade-off with a counter-intuitive answer — and the smallest view is almost always the worst buy:

The logical-design workflow is four steps — choose the schema (star/snowflake) → translate the conceptual schema → choose materialized views → optimize — driven by the workload (dynamic, extemporaneous OLAP queries) and the data volume (distinct values × attribute size × number of events). Translating the fact schema follows fixed guidelines: descriptive attributes go in the dimension table of the attribute they hang off (or in the fact if they hang off the fact); optional attributes get nulls/ad-hoc values; cross-dimensional attributes get a new table keyed by the dimensional attributes they combine; shared hierarchies / convergence must not duplicate the dimension table (use roles); and multiple edges become a bridge table (or a new dimension, if you keep a star).