A data cube for planning: measures, dimensions, grain and the season calendar
The document describes which measures and dimensions buying planning needs, at which grain to store each measure and which rules to set in advance: conversion at the date, a week on a period boundary, the sell-through denominator without a calendar filter. The decision it supports: what to collect and how to label it so that the “Buying plan from revenue backwards” calculation is fed with facts.
Why this document
The plan calculation is only as good as its inputs. If the order, the delivery and the sale lie at different grain and in different currencies, sell-through and cancellation come out “roughly”. The document is read by the head of data, an analyst and a category manager when designing reporting. It is needed before reports are built: agreeing on measures and grain is cheaper than explaining later why the totals of different reports do not match and rebuilding the history of several seasons.
At a glance
- What is this document and why is it needed?
- The document describes which measures and dimensions buying planning needs, at which grain to store each measure and which rules to set in advance: conversion at the date, a week on a period boundary, the sell-through denominator without a calendar filter. The decision it supports: what to collect and how to label it so that the “Buying plan from revenue backwards” calculation is fed with facts.
- Who owns the document and how often is it rebuilt?
- The head of data or an analyst with an owner of each measure; the measures reference is reviewed once per season.
- What mistakes are the most common?
- Storing amounts in one currency without the original → the rate cannot be restored → two columns. Filtering the sell-through denominator by the calendar → the share is overstated → take the whole delivery. Mixing explicit and implicit discounts → the depth of the discount is unclear → different types.
For whom
- Brand
- Brand: measures of order, confirmation and delivery by factories and seasons; delivery dates by batches.
- Retail
- Retail: measures of sales, discounts and stock by stores and warehouses, defective and service storage separately.
What it consists of
Measure groups
- Budget and order: budget, ordered, confirmed, canceled. Receipt: delivered, transfer between places. Sales: quantity, amount, cost. Discounts: explicit and implicit. Stock: at the start and end of a period. Areas and markup: by places of sale.
- Each measure is recorded with grain, unit, currency rule and owner.
Dimensions
- Calendar (ordinary and season), product with a hierarchy, supplier, storage place (store, warehouse, defective, service), customer (retail, wholesale, foreign), discount (explicit and implicit), order type.
- A week on a period boundary goes to the period where more of its days fall, or is split by days: one rule for the whole storage.
Rules
- The amount is stored in the original and in the single currency; converted at the rate of the receipt or sale date.
- The sell-through denominator (delivered) is not filtered by the season calendar, otherwise the share is overstated.
- Dates of the first, half and last delivery are counted from the cumulative delivery at purchase prices.
- Returns are stored as a separate fact on the same keys to count net sales.
A slice example: season, category, delivery
- Take the autumn–winter season, the outerwear category and the second delivery. For this slice the cube must return four numbers: ordered, confirmed, delivered at purchase prices and sold at cost. If one number is stored by months and the others by weeks, the slice cannot be assembled honestly: the period would have to be cut by eye.
- An illustrative check in units: ordered 100, confirmed 95, delivered 92, sold 60. Cancellation is 1 − 92 / 100 = 8%, the share sold of the delivered is 60 / 92 ≈ 65%. The same numbers for each delivery separately show which batch pulls the season down.
What to do with the result
- A share sold of the delivered above 100%: look for a calendar filter in the denominator or a delivery in another currency without conversion, not for a “record season”.
- Weekly and monthly totals differ: check the rule for a week on a period boundary and whether it is applied in every load.
- Cancellation by order lines and by amount differs: that is normal, but state which measure it is counted on and do not compare the two versions.
- A new factory, store or category appears in the middle of a season: add it to the dimensions reference with a start date, otherwise the season history will be cut off and shares for earlier periods cannot be compared with the current ones.
How to roll it out
- Start with four plan metrics rather than all measures at once: add a new metric when the numerator, the denominator and the owner are clear.
- Run a pilot on one season and two categories: reconcile the cube totals with the accounting report; analyze a gap above the agreed threshold before expanding.
- Assign an owner to each measure by name and set a review date for the reference once per season; without an owner the reference goes stale within two seasons.
Where the data comes from
| Data source | Fields |
|---|---|
| Measures and dimensions reference | name, group, grain, unit, currency rule, source, owner |
| Rates table | date, currency pair, rate, source of the rate |
| Season calendar | date, season, period, season week, boundary rule |
How to build it step by step
- Step 1: List the plan metrics: revenue, cost, sell-through, cancellation, discount.
- Step 2: For each write the numerator and denominator and their grain.
- Step 3: Link measures to dimensions: what is needed for a slice.
- Step 4: Set the default season bounds and mark where yours differ.
- Step 5: Choose the rule for a week on a period boundary.
- Step 6: Set up the rates table and the conversion rule at the transaction date.
- Step 7: Separate explicit and implicit discounts by different types.
- Step 8: Check the completeness of the description with the “Measures and dimensions reference” template.
- Step 9: Assemble one control slice (season, category, delivery) and reconcile the four numbers with the accounting report.
- Step 10: Assign an owner of each measure and a review date for the reference; after the season compare the actual coverage of slices with the plan.
Formulas
| Measure | Formulas | Note |
|---|---|---|
| Sell-through | cost of goods sold in a period ÷ delivered at cost over the whole time of the season goods | Above 100% is impossible. |
Formulas in notation
- Cancellation
c = 1 - \frac{D}{O}
D: delivered, O: ordered.
Worked example
Which measure at which grain a metric needs (an illustrative breakdown)
| Metric | Measures | Grain |
|---|---|---|
| Sell-through | cost of goods sold, delivered | product × place × day |
| Cancellation | ordered, delivered | order line |
| Effective discount | revenue, cost, coefficient | product × day × discount type |
The grain of the denominator cannot be coarser than that of the numerator, otherwise the share loses the slice.
Figures
Data as a table
| Measure | Measures |
|---|---|
| Sell-through | 2 |
| Cancellation | 2 |
| Eff. discount | 3 |
How many measures a metric needs. An illustrative count: cancellation has two measures, the effective discount three.
- positive
- negative
- total
Data as a table
| Step | Change | Level |
|---|---|---|
| Ordered | +100 | 100 |
| Not confirmed | −5 | 95 |
| Not delivered | −3 | 92 |
| Sold and in stock | — | 92 |
From order to stock. An illustrative path: ordered 100, lost at confirmation 5 and at delivery 3, 92 reached the shelf.
Specifics by category
- Apparel
- Apparel: product down to the size.
- Footwear
- Footwear: size and width as a hierarchy level.
- Accessories
- Accessories: the style level is enough.
- Kids
- Kids: height as a level.
- Men
- Men: stable styles, long series.
- Women
- Women: more styles without history.
Common mistakes
- Storing amounts in one currency without the original → the rate cannot be restored → two columns.
- Filtering the sell-through denominator by the calendar → the share is overstated → take the whole delivery.
- Mixing explicit and implicit discounts → the depth of the discount is unclear → different types.
- Not agreeing on the boundary week → weekly totals do not match monthly → one rule.
- Loading only totals into the cube without order and delivery lines → the slice cannot be rebuilt → store lines at the grain of the operation.
- Changing the grain of a measure without a version mark → seasons are not comparable → keep a reference version and the date of change.
Russian specifics
- The method is managerial; storing customer personal data in a cube is subject to the personal data law: anonymize the customer before loading.