Chapter 07

Conceptual DW Design: the Dimensional Fact Model

The conceptual half of every Part II warehouse exercise. The Dimensional Fact Model — facts, dimensions, measures, hierarchies and their special cases; how measures aggregate (flow, level, unitary); and the top-down method that turns an operational ER schema into a fact schema via the attribute tree, pruning and grafting, and the glossary.

Reading: ~45 min Interactive: 2 widgets Source: Polimi TIS 2025/26 — DW Conceptual Design 1, the DFM (deck 08) · Polimi TIS 2025/26 — DW Conceptual Design 2, ER → fact schema (deck 09)

01 · Model

The Dimensional Fact Model

Warehouse conceptual design needs its own model: the E/R model cannot be used for it, because a warehouse is multidimensional, not entity-relational. The Dimensional Fact Model (DFM) fills the gap — it describes a set of fact schemata out of four ingredients.

Q

The DFM — asked as theory, and the backbone of the Part II exercise

“Describe the Dimensional Fact Model” is a Part I question (2017-02-01), and the DFM is the conceptual half of the DW-design Part II exercise that appears in 9 of 16 sessions. So every construct below is both examinable theory and a tool you use under exam pressure.

Fact

A concept relevant to the decision process — a set of events (e.g. SALE). It becomes the root of the fact schema.

Measure

A numeric property of the fact (quantity, gross income, number of tickets).

Dimension

A fact property over a finite domain — an analysis coordinate (product, date, shop).

Hierarchy

A directed tree of dimensional attributes whose edges are n:1 associations, rooted at a dimension (date → month → trimester → year).

Three constraints define a legal fact schema: a fact expresses an N:M relationship among its dimensions; there is a functional dependency from the fact to its dimensions; and the dimensions of one fact schema must have distinct names.

Events come at two granularities. A primary event is a single occurrence of the fact — “on 10/10/2001, ten Brillo packets were sold at BigShop for 25 euros”. A secondary event aggregates primary events along a hierarchy — “in October 2001, 230 Brillo packets were sold at BigShop for 575 euros”. The root of a hierarchy is the finest granularity; moving up it groups events.

The DFM has a handful of constructs the exam loves to test by name:

Descriptive attribute

Extra information about a dimensional attribute, uniquely determined by it (a shop’s address, phone) — not itself an analysis coordinate.

Optional edge / dimension

An edge (or whole dimension) that only takes a value for some events — diet only for food; promotion amount only for products on promotion.

Cross-dimensional attribute

A value obtained by combining several dimensional attributes — VAT depends on product category and state.

Convergence

Two dimensional attributes linked by more than one directed path — shop → city → county → state and shop → sale-district → state both reach state.

Hierarchy sharing

A shorthand for a duplicated hierarchy portion; label the roles on incoming edges (a call’s caller number and called number share the phone-number hierarchy).

Multiple edge

A many-to-many link between a fact/attribute and a dimension (a book has several authors); drawn as a multiple edge, handled specially at logical design.

Every one of them has a graphical form you are expected to draw, so here is the notation itself — a legal fact schema on top, the six constructs beneath it:

A LEGAL FACT SCHEMA — SALE date month year product type category shop city state SALE quantity gross income unitary price nr-tickets dimensions are the accented circles · measures sit inside the fact · an empty fact defaults to COUNT THE SIX CONSTRUCTS THE EXAM ASKS FOR BY NAME 1 · DESCRIPTIVE ATTRIBUTE shop address Uniquely determined by it — never an analysis coordinate. 2 · OPTIONAL EDGE product diet Valued for some events only (diet applies to food alone). 3 · MULTIPLE EDGE book author Many-to-many. Becomes a bridge table with a WEIGHT. 4 · CONVERGENCE shop city district state Two paths, one destination — both are legal and must agree. 5 · CROSS-DIMENSIONAL ATTRIBUTE category state VAT Determined by two attributes jointly, by neither alone. 6 · HIERARCHY SHARING CALL caller called number One hierarchy reused twice — label the roles on the edges. a fact is an N:M relationship among its dimensions · fact → dimensions is a functional dependency · dimension names are distinct

A fact schema with no measures is empty — and its default measure is COUNT (e.g. course attendance).

2017-02-01-q12017Q01Dimensional Fact Modelmedium5 pts
Describe the dimensional fact model used in the data warehouse context and define its main elements. Provide a small example.

02 · Exam-hot

Measures: additivity & aggregability

Aggregation combines the measure values of many primary events into one value for a secondary event. The critical question is which operator is legal along which dimension — and that depends on the kind of measure.

A measure is additive w.r.t. a dimension iff SUM is freely applicable to it along that dimension. Not every measure is. The standard classification — a recurring Part I question — is:

Measure typeMeaningAdditive along…Aggregate with
FlowAccumulated over a time interval (quantity sold, revenue, number of orders)every dimension, including timeSUM
Level (stock)A snapshot at an instant (quantity in stock, account balance, inhabitants)every dimension except timeover time: AVG / MIN / MAX
UnitaryA per-unit ratio (unit price, exchange rate, % discount)no dimensionweighted average, or recompute from flows
Q

Flow, level & unitary measures — asked in 2017-07-21

“Describe flow, level and unitary measures with examples” is a Part I question (2017-07-21). Graders want the three types, an example of each, and — the discriminating point — which aggregations are legal: level measures are non-additive over time (summing Monday’s and Tuesday’s stock is meaningless) but fine with AVG/MIN/MAX; unitary measures are non-additive everywhere and must be re-aggregated as a weighted average.

tip

Two aggregability traps from the deck

Level measure (INVENTORY). Quantity in stock is a level measure: non-additive w.r.t. time, but the fact schema annotates the time arc with AVG, MIN to say those operators are allowed along it. Multiple-edge double counting. Number of tickets is non-additive w.r.t. product because ticket ↔ product is many-to-many: a ticket holding two products of type T1 is counted once per product, so SUM over product type over-counts (2 real tickets report as 3). This is exactly why multiple edges need special handling at logical design.

The rule is short enough to believe you have memorised it. What memorising does not give you is a sense of how wrong the illegal aggregation is — so the second panel below re-aggregates a stored average both ways, and the gap between them is the marks:

Hands-on

Additivity — which aggregation is legal

Pick a measure type and the dimension you are aggregating along. Time is singled out because it is the only dimension whose direction the classification cares about.

A snapshot at one instant. quantity in stock · account balance · inhabitants. Additive along every dimension except time.

SUM is illegal · Summing a stock over time double-counts: 100 items in stock in March and 100 in April is not 200 items, it is the same 100 counted twice. Along time use AVG, MIN or MAX.

useAVG · MIN · MAX

Re-aggregating a stored average

Each row is a group with its own weight and its own stored average discount. Roll them up to one number, two ways.

Groupcount (weight)avg discount %
Italy
France
AVG(avg)
15%
SUM(count * avg) / SUM(count)
19%
Error
4 pts

Unbalanced groups · The groups carry different weights, so AVG(avg) is off by 4 points. Weighting by count is not a refinement; it is the only correct answer.

Try thisSet both counts to 500. The error drops to zero — that is the hidden assumption behindAVG(avg), and the reason it looks right in a toy example. Now put them back to 100 and 900 and watch four points of discount reappear.
TakeawayFlow sums everywhere. Level sums everywhere except time — stock does not accumulate across instants. Unitary sums nowhere, so re-aggregate it as SUM(cnt · avg) / SUM(cnt) or rebuild it from the flows underneath. Multiple edges cause the same double-counting from the other direction.
2017-07-21-q22017Q02Flow, level & unitary measuresmedium5 pts
Describe flow, level, and unitary measures in the data warehouse context. Provide a set of examples.

03 · The exercise

From ER to fact schema

This is the exercise. Given an operational relational/ER schema, a fixed top-down method produces the fact schema. Run it as a procedure — it is graded step by step in every DW-design Part II.

Part II method The top-down conceptual-design method

1 · Fact choice (subject-oriented)

Pick the fact: an event archive that is frequently updated (SALE, DOWNLOAD, RENT). A fact can be an entity, a relationship among entities, or a relation R. Static archives (students) are bad facts — a warehouse studies dynamics. The chosen fact is the root.

2 · Attribute tree

Build the tree: the root is the fact’s primary key, and each node functionally determines its descendants (follow the functional dependencies / foreign keys outward from the fact). Obtained semi-automatically from the ER schema.

3 · Attribute-tree editing — pruning & grafting

Remove what the data mart does not need. Pruning a node deletes the whole subtree rooted at it. Grafting a node deletes the node but reconnects its children to its parent — keeping the descendants while dropping the intermediate level.

4 · Dimension choice

Dimensions are chosen among the children of the root. Time must always be a dimension — a historical source has it as an attribute; a snapshot source needs it added.

5 · Measure definition

Numeric attributes of the root become measures; more measures come from aggregate functions over tree attributes (SUM, AVG, MIN, MAX, COUNT).

6 · Glossary & fact-schema creation

Write the glossary (an expression per measure) and draw the fact schema: dimension hierarchies are the subtrees rooted at each dimension; the fact name is the chosen entity.

×

Pruning vs grafting — the move graders check

They are not the same. Pruning throws away a node and everything below it (the detail is gone). Grafting throws away only the node, lifting its children up to the parent (the detail survives, the level disappears). Grafting a middle level of a hierarchy you don’t need to group by — but whose finer children you still want — is the standard move; pruning it by mistake loses the children too.

Run the two moves on the lecture’s own SALE tree. Note where date and shop sit — under nrticket, not under the root — so the dimension set you are aiming for is only reachable one way:

Hands-on

Attribute tree — pruning vs grafting

This is the lecture's SALE tree as reverse engineering leaves it. Select a node, then decide: prune it (the node and everything below it goes) or graft it (the node goes, its children move up to its parent). Get to the dimensions product, date, shop.

Select a node in the tree below.

Dimensions
product, nrticket
Measures
quantity, unitaryprice
Nodes left
18

Not yet · Missing date, shop. A dimension that vanished was pruned along with its parent — grafting that parent would have kept it.

Try thisHit The classic mistake and read the dimension strip: pruning nrticket takes date and shop down with it, and a warehouse with no time dimension is not a warehouse. Undo, then graft the same node instead.
TakeawayPrune when you want the detail gone; graft when you want the level gone but the detail kept. The tell is whether the node has children you still need to group by — if it does, grafting is the only move that survives the grader.

Two special cases recur in the source schema and must be handled before the tree is clean: cyclic relationships (part–subpart, employee–manager) are broken after a chosen number of iterations; schema cycles are broken by keeping the most convenient link; ISA hierarchies become optional (0:1) relationships; a compound attribute becomes a vertex with its components as children.

Worked example The SALE example — glossary and fact schema

For the classic SALE fact (from Sale(product, nrticket, quantity, unitaryprice) and its surrounding schema), after pruning/grafting the dimensions are product, date, shop (each with its hierarchy — product → type → category → division; date → month → trimester → year; shop → city → county → state), and the measures come with their glossary expressions:

Quantity      = SUM(Sale.quantity)
Gross income  = SUM(Sale.quantity * Sale.unitaryprice)
Unitary price = AVG(Sale.unitaryprice)     -- a unitary measure: AVG, not SUM
Nr-tickets    = COUNT(*)

The glossary is what makes the measures computable: each expression says how to derive the measure, at any level of aggregation, from the source attributes. Note Unitary price is aggregated with AVG (it is a unitary measure — summing prices is meaningless), while Quantity and Gross income are flow measures and use SUM. The final fact schema is the tree redrawn with SALE at the centre, the three dimension hierarchies radiating out, and the four measures listed on the fact.

Here is a full DW-design exercise to run end to end — reverse-engineering, the attribute tree with pruning/grafting, the fact schema, the glossary, and the logical schema + SQL (the logical half is the next chapter):

2018-09-07-q32018Q03DW design (full pipeline)hard23 pts
PoliBeach owns a chain of beach resorts selling rental packages (each package has a daily price and is bought for a number of days; the resort director may apply a per-sale discount). Design a data warehouse to analyze the package sales. Operational database: - CITY(CityName, Country) - BEACHRESORT(BeachResortId, BeachResortName, Director, CityName) - PACKAGE(PackageId, Description, Category, DailyPrice) - AVAILABLERENTALOBJECT(ObjectName, Description) - PACKAGECOMPOSITION(PackageId, RentalObjectName, Quantity) - CUSTOMER(CustomerId, Surname, GivenName, BirthDate, CityOfResidence) - SALE(CustomerId, PackageId, BeachResortId, StartDate, Duration, DiscountPercentage) 1. (3 pts) Reverse-engineer into an ER schema. 2. For the useful fact(s): a. (3 pts) attribute tree with pruning/grafting; b. (3 pts) fact schema; c. (2.5 pts) glossary. 3. (3 pts) Logical schema. 4. SQL: a. (2 pts) total sold packages per package category, resort director and customer city, with all one- and two-attribute aggregations; b. (2.5 pts) average duration for Italian customers born in 1989 per beach-resort city and package id; c. (2 pts) total income by start date, month and year including the single aggregations; d. (2 pts) per country, the beach resort(s) with the greatest income.

Load-bearing ideas

  • The DFM models a fact (event, the root) analysed by dimensions (coordinates) organised in hierarchies, measured by measures. Constraints: fact = N:M among dimensions; FD fact → dimensions; distinct dimension names.
  • Constructs: descriptive attribute (extra info, uniquely determined), optional edge/dimension, cross-dimensional attribute (combined value), convergence (two paths to one attribute), hierarchy sharing (roles), multiple edge (many-to-many). Empty fact ⇒ default measure COUNT.
  • Measure types: flow (additive everywhere, SUM), level (non-additive over time; AVG / MIN / MAX), unitary (non-additive everywhere; weighted average). Multiple edges cause double-counting.
  • The method: fact choice → attribute tree (root = PK) → pruning (delete subtree) / grafting (lift children) → dimensions (children of root; time always) → measures → glossary + fact schema.
  • Exam radar: (1) define the DFM constructs and flow/level/unitary measures; (2) run the top-down method, keeping pruning vs grafting and the weighted re-aggregation of unitary measures straight — the two most common ways to lose marks on the Part II conceptual step.