Syntha Lab RU

Data model of standard reports

Brand history, the SKU report, carryover and season results: which facts and dimensions are needed, how much of the data already exists and what to close first.

both Processes and data

Open interactively →

About the topic

A standard report exists so that the same question gets an answer built the same way each season. Brand history at a client, a SKU report, the fate of carry-over goods, season results: if each such report is assembled from files by hand, numbers do not reconcile between seasons and the analyst spends time on assembly instead of analysis. This topic explains which small data model stands behind any standard report and how to tell whether your data is ready for them.

Why it matters to a brand or retailer. Reports support recurring decisions: what to keep in the line, which client to sell to and on what terms, how much to buy, how to assess a season. If the data is already in the right form, the report is built at the press of a button; if not, the decision is postponed. Here you determine: which report is needed first, which fields it lacks, which one to close first and how to store history so the past does not change.

The logic rests on a simple scheme of facts and dimensions. Facts are events with numbers: sales, closing stock of the week, receipts. Dimensions are what they are cut by: date (with the season week), product, store or channel, supplier, promotion. Almost any report is assembled from these two kinds, so it is important to agree on the row unit: what counts as one fact row. For example, a SKU per day in a store. If the unit is too coarse (month and category), small questions cannot be answered; if too fine, there is too much data and it is awkward to store.

The second key idea: dimensions change over time, and history must remain. If a model was moved from one category to another in March and you store only the current category, January sales also land in the new one. So price and category need values as of a date, not only current ones. For the same reason price and discount are stored separately: from the final price the discount can no longer be recovered.

Data readiness for a report is measured by fields: which are needed, how important each is and what share is already available. Then the decision what to close first becomes arithmetic rather than argument. Traps: building reports by hand every season, storing only the current price and category, mixing unsold leftovers with models deliberately kept in the line, failing to fix period, filters and row unit in the report specification.

Key ideas

Facts and dimensions

A fact: sold 2 units of SKU 4601234567893 in store 17 on 3 April. Dimensions: date (with season week), product (model, color, size, category), store (city, format), supplier, promotion. Any question, such as category sales by store format by season week, is obtained by grouping facts by dimensions, without new exports.

The row unit sets size and depth

2,754 SKUs, 30 stores. A row of SKU × day × store gives up to 2,754 × 365 × 30 ≈ 30.2 million rows a year, SKU × week × store about 4.3 million, seven times fewer. A weekly unit usually suits season reports, while a daily unit is needed where decisions are made by day (promotions, sales pace). Choose the unit by the question and fix it.

History of dimensions: price and category as of a date

In March a model was moved from Outerwear to Jackets. January sales 4 million roubles in the old category. If you store only the current category, January ends up in Jackets and the old category shows zero for January. A category year-on-year report will compare the incomparable. The solution: store the category with effective dates so the report can choose as it was or as it is now.

Price and discount are stored separately

List price 5,000 roubles, sold at 3,500. If only 3,500 is saved, you cannot tell whether it was a 30% discount or the item simply cost less. With the price and discount pair, reports compute maintained margin, depth and frequency of discounts and honestly assess a promotion; without it these metrics cannot be restored.

Data readiness is counted by fields with weights

The Season results report: sales weight 30, available 100%; closing stock of the week weight 25, 80%; receipts weight 20, 100%; price and discount weight 15, 50%; category as of a date weight 10, 0%. Readiness (30 + 20 + 20 + 7.5 + 0) ÷ 100 = 77.5%. The shortfall of a field is weight × (1 − availability): stock 5, price and discount 7.5, category 10. Close category history first.

How to work through it

  1. Choose the report needed first and write down which decision it supports.
  2. List the data fields it needs and classify them as facts and dimensions.
  3. Assign a weight and availability share to each field and compute readiness of the four standard reports.
  4. Close the field with the biggest shortfall: weight × (1 − availability).
  5. Fix the row unit (for example SKU × week × store) and the period, filters and columns in the specification.
  6. Store past values of price and category with effective dates; keep price and discount separate.
  7. Reconcile the report with the ledger and agree an explainable gap so numbers match for all users.

Where to start

1) Choose the report you need first. 2) List the data fields it needs. 3) Set the weight and availability of each field. 4) Calculate the readiness of the four reports. 5) Close the field with the highest priority. 6) Fix the row grain (SKU × day × store). 7) Keep the past values of price and category: they change over time. Calculations of the topic in order: “Data readiness for standard reports”. See also (their primary topic is another one): “Merchandise plan traffic light”.

Calculations in this topic

  • Data readiness for standard reports

    From a list of data fields with your weights and availability shows how much of the data exists for four reports (brand history, the SKU report, carryover, season results) and which field to close first.

    How much of the data for four reports already exists and what to close first.

  • Merchandise plan traffic light

    Compares actuals with the plan on key merchandise-plan metrics and colors each green, amber or red by your thresholds. Shows what to look at first. The default thresholds are neutral (“an example”): there is no single Russian benchmark, set them from your own past seasons and targets.

    Which plan metrics are already outside the tolerated deviation and where to look first.

Self-check questions

  • Which facts and dimensions do brand history, the SKU report, carryover and season results need?
  • What share of the needed fields already exists and which field should be closed first?
  • What counts as one row: SKU × day × store or SKU × week × store?
  • Are past price and category stored, or only the current one?
  • Are price and discount separated in the data?
  • How do you tell unsold leftovers from models deliberately kept in the line?
  • Does the report reconcile with the ledger and what explains the remaining gap?
  • Are the period, filters, row unit and report owner fixed in the specification?

Knowledge check

Multiple-choice questions. The correct answer and the explanation follow each question.

  1. What sits at the center of the star schema of standard reports?

    • A. The supplier reference book
    • B. The fact table: sales, stock at week end, receipts
    • C. The date table with season weeks
    • D. The finished report for the head

    Correct answer: B. The fact table: sales, stock at week end, receipts. Facts sit at the center, with dimensions around them (date with the season week, product, store or channel, supplier, promotion). Suppliers and dates are dimensions around the center, and a finished report is assembled from facts and dimensions, it is not the base of the model.

  2. The SKU report needs three fields: sales (weight 5, 90% filled), stock at week end (weight 4, 60% filled) and cost (weight 1, 100% filled). What is the readiness of the report?

    • A. 83.3%
    • B. 60%
    • C. 76.7%
    • D. 79%

    Correct answer: D. 79%. Readiness = Σ (weight × availability) ÷ Σ weights = (5 × 90 + 4 × 60 + 1 × 100) / 10 = 790 / 10 = 79%. 83.3% is the simple average without weights, 60% is the weakest field, and 76.7% comes from forgetting the field with weight 1 (690 / 9).

  3. The priority of closing a field = weight × (100 − availability) × the number of reports that need it. What should be closed first? Stock at week end: weight 5, 70% filled, needed by 4 reports. Price and discount separately: weight 4, 40%, 2 reports. Supplier code: weight 3, 20%, 1 report. Sales by SKU, day and store: weight 5, 90%, 4 reports.

    • A. Price and discount separately
    • B. Supplier code
    • C. Stock at week end
    • D. Sales by SKU, day and store

    Correct answer: C. Stock at week end. Priorities: stock 5 × 30 × 4 = 600; price and discount 4 × 60 × 2 = 480; supplier code 3 × 80 × 1 = 240; sales 5 × 10 × 4 = 200. The supplier code is the worst filled (20%), but only one report needs it and its weight is small; price and discount come second, only two reports use them; sales are 90% filled, there is almost nothing to close there.

  4. In spring the “Jackets” category was moved from “Outerwear” to “Light clothing”. How should the category be stored so that the last season’s results are not recalculated?

    • A. Store the category value with “from” and “to” dates: past values stay
    • B. Replace the category in the card with the new one: then reports see a single value
    • C. Store only the current category and write the former one in a note
    • D. Create a new SKU at each category change

    Correct answer: A. Store the category value with “from” and “to” dates: past values stay. A category is a slowly changing dimension: the value is stored with dates, and a season report takes the category that was in force then. A replacement would rewrite history: the last season’s results would be recalculated by the new scheme; a note takes no part in the calculation; a new SKU would break the sales history and the stock.

  5. A sales fact row is SKU × day × store. How many rows over 28 days if 200 SKUs sell in 12 stores every day?

    • A. 9,600
    • B. 5,600
    • C. 67,200
    • D. 2,400

    Correct answer: C. 67,200. 200 × 12 × 28 = 67,200 rows. 9,600 is the rows at a weekly grain (200 × 12 × 4); 5,600 comes without stores (200 × 28), 2,400 without days (200 × 12). Daily rows can be rolled up into weekly ones, not the other way round, so the finest grain available is stored.

Common mistakes

  • Mistake: Building reports from files by hand every season

    What it costs: Numbers do not reconcile between seasons

    How to avoid: A single model and specification.

  • Mistake: Storing only the current price and category

    What it costs: History is distorted

    How to avoid: Values as of a date.

  • Mistake: Mixing unsold leftovers with models deliberately kept in the line

    What it costs: False conclusions about carry-over

    How to avoid: A keep-decision flag.

  • Mistake: Not separating price and discount

    What it costs: The discount cannot be restored later and maintained margin is unavailable

    How to avoid: Two fields.

  • Mistake: Not fixing period, filters and row unit

    What it costs: Two analysts get different numbers

    How to avoid: A report specification.

Russia and the EAEU

If reports go to retail chains or platforms, they set the format and fields: build a mapping table between your fields and theirs and do not rebuild the model for every partner. A starting reference: history of at least two years by day, store and SKU; there is no unified Russian standard for history length. There are several reconciliation sources in Russia: online-till receipts, electronic documents (UPD) for shipments and receiving, and for labeled goods the movement of codes (entry into circulation, withdrawal at sale), which independently confirms sales and stock.

Retention periods for primary accounting and tax documents are set by the accounting law and the Tax Code; ask your accountant, because cleaning data too early deprives you of an evidence base. If a report contains customer data (a loyalty program), Federal Law 152-FZ applies: store it anonymized wherever personal data is not needed. Keep amounts in standard reports excl. VAT (standard rate 22% from 01.01.2026) and state the base so the report matches financial statements.

When entering other countries, two dimensions are added to the model of standard reports: country and currency. Each event is stored in the transaction currency and, separately, in your reporting currency at a rate you chose in advance (at the transaction date or the report date); recalculation on the fly changes past figures. VAT differs across union countries (reference as of 14.11.2026, check: VAT rates by country are on the page “EAEU and CIS countries”), so amounts excl. VAT are comparable and with VAT are not. Platforms and chains of different countries deliver data in their own formats and with different depth, so the field mapping table is kept per country. Holiday calendars differ, and the season week for comparing periods must account for that.

Categories and audiences

Apparel
In apparel the report row is most often at model and color level with a size breakdown. For season results the full-price share, stock and discounts matter; for carry-over the link to the ancestor model matters. The size curve of sales is needed as a separate report because it shows which size ran short.
Footwear
For footwear the size report is mandatory: the core sizes sell faster than the edges, and the season shows which pairs are left. Returns by size and last are needed in the same dimensions. The row unit is usually a SKU pair per week because a day gives too many zero rows.
Accessories
Accessories have no sizes, so the SKU equals the colorway and the report is simpler. But gift peaks and sets matter: a set and its content must be linked, otherwise sales of the set and its components are counted twice. Keep price and promotion history especially carefully because price strongly affects demand here.
Kids
For children’s goods the season-results report is split into the school window and the New Year one: comparing them with each other is meaningless. Age and height as dimensions show which groups are unsold. Stock by age is best shown in a separate table so the buyer can see where markdowns start earlier.
Men
The main report of the men’s line is built on carried-over models: which basics hold for years and which accumulate stock. It needs the link to the predecessor model and a row unit “style x color x size pair”, otherwise when a SKU changes the year-on-year comparison has to be corrected by hand.
Women
In the women’s line styles move between categories (a dress goes to “beach”) and promotions are frequent, so the report needs price and category history with effective dates: February sales are judged at the then price and the then category. The brand history for a client is built from the same facts, and client reports depend on it.

Questions this topic answers

  • Which facts and dimensions do the brand history, the SKU report, carryover and season results need?
  • What share of the needed fields exists and which field to close first?
  • What counts as one row: SKU × day × store?
  • How to keep the past price and category so history does not change?
  • How to reconcile a report with the books so that the numbers agree for everyone who uses it?

Key measures and terms

Study first

What next

Next: “KPI dictionary and common definitions”, “Data quality and data owners”, “The planning calendar”.

Documents on this topic

Open interactively →