From questions to a reporting model
Every Monday, Mangrove Market's leadership sees three different "sales" numbers for the same week. This first lesson of the course starts the rebuild of their weekly trading dashboard at the bottom: you turn the decisions people make into questions, and the questions into a reporting model of facts, dimensions and a declared grain that any later definition can be computed from.
By the end you can
- Turn a decision into questions with a measure, "by" words, a period and a comparison
- Declare the grain of a fact table and keep every column true at that grain
- Choose between a transaction, a periodic snapshot and an accumulating snapshot fact table
- Share conformed dimensions with surrogate keys, and keep history with slowly changing dimensions
- Write SQL that joins a fact to its dimensions and sums it at the grain the question needs
The everyday picture
Picture a woman who runs a fruit and vegetable stall in a busy market. Every sale gets a small paper slip: the date, the time, the item code, the quantity and the price. The slips go into a box. On the wall hangs one card per item code, with the name and the category (fruit, vegetables, herbs), and a calendar where she marks holidays and rainy days.
Her nephew keeps the accounts. He writes one line per day in a notebook: the date and the day's total. Every total in it is correct.
One evening she asks: how much fruit did we sell on rainy Saturday mornings this year? The notebook cannot answer. It has no items, no hours and no weather. The box of slips can. She pulls the Saturday morning slips, keeps the fruit by checking the wall cards, checks the calendar for rain, and adds up.
That is a reporting model. The slips are a fact table: one row per thing that happened, at a level of detail she decided once. The wall cards and the calendar are dimensions: the descriptions she filters and groups by. The item code on each slip is the key that joins them. The nephew's notebook is a summary: any question it was not built for is lost for good.
Start from the decisions
Every Monday at 9 am, Mangrove Market's leadership team reviews last week's trading. Three reports are on the screen, and each shows a different "sales" number for the same week. Finance says about $35.2 million. Store operations says about $31.4 million. The app team says about $7.9 million. The first twenty minutes go on arguing about which is right. You and Tomás, Mangrove's data analyst, have been asked to rebuild the weekly trading dashboard: sales, out-of-stock losses and delivery times for 320 stores and the app.
The tempting first step is to open the warehouse and start drawing charts. Good BI teams start earlier, with the people in the meeting and the decisions they make each week. A dashboard earns its place only if it changes what someone does on Monday afternoon.
For each person, write down the decision, then the questions that feed it. A useful question has four parts: a measure (the number), the "by" words (how to slice it), a period, and a comparison.
- Head of stores decides which stores get a visit or extra staff this week. Question: "Sales by store, last week, against the same week last year."
- Head of supply decides which products and distribution centres (DCs) to fix first. Question: "Sales lost to empty shelves by category and DC, last week, against the 4-week average."
- App operations lead decides where to add riders. Question: "Median minutes from order placed to delivered, by city and hour, last week, against the 30-minute promise."
Now read each question like a grammar exercise. The numbers you add up (sales, units, minutes) are facts, also called measures. The words after "by", "per" and "for each" (store, category, DC, city, hour, week) point to dimensions. And each measure comes from a business process: selling at a till, taking an app order, counting stock on a shelf. One business process usually becomes one fact table.
This is the core of dimensional modelling, described by Ralph Kimball and Margy Ross in The Data Warehouse Toolkit. The Kimball Group's design process has four steps: select the business process, declare the grain, identify the dimensions, identify the facts.
You designed tables for an app in Modeling data in tables. A reporting model is built for a different job, so it makes different choices:
| App tables (sd-07) | Reporting model | |
|---|---|---|
| Built for | Many small writes, one receipt at a time | Large reads that add up millions of rows |
| Shape | Normalised: each fact in one place, many joins | A star: one fact table and a few wide dimensions |
| History | The current state, updated in place | Kept: new rows are added, old ones stay |
| Readers | The app's own code | Analysts, dashboards and BI tools |
Quick check
The head of stores asks, "Which stores sold less fresh produce before noon last week than in the same week last year?" Which split is right?
The number being added up is sales. Everything used to filter or group it (store, the "fresh" category, before noon, last week and the same week last year) is a dimension attribute. A fact table per store would make comparisons across stores harder; stores are rows in one dimension.
Declare the grain, then the facts
The grain is a sentence that says what one row of a fact table means. Write it before you choose a single column: "one row per sale line", meaning one product line on one till receipt or one app order. Everything else follows from it. A column belongs in the fact only if it has exactly one true value at that grain.
Choose the lowest grain the source system records, called the atomic grain. You can always add rows up; you can never split a row that was stored already added up. Finance's ledger keeps one row per store per day per account. It is correct, and it can never tell you how much fresh produce sold before noon, because the product and the hour were gone before the data arrived. Mangrove's stores ring up about 400,000 baskets a day with roughly 4 lines each, so a sale-line fact grows by about 1.6 million rows a day, close to 600 million a year. A modern analytical warehouse handles that size; no computing power can recover detail the grain threw away.
With the grain fixed, add one key column per dimension and the numbers as fact columns. The result is a star schema.
city and week_start, and sums gross_cents. Article and channel stay unused until a question needs them. A customer dimension joins the same way.Every query against a star has the same shape: join the fact to the dimensions you need, filter and group on dimension columns, and sum fact columns. "Units of bakery sold through the app in East city" uses three dimensions instead of two, with no new tables. Because the shape never changes, BI tools such as Power BI, Looker and Metabase can write the query for you when you drag a field onto a chart. Microsoft's Power BI guidance puts it this way: dimension tables are for filtering and grouping, fact tables for summarising.
Store facts that add up. qty, gross_cents, discount_cents and tax_cents are additive: sum them across stores, products and days and the total still means something. A unit price is not. So store the line's extended amount (quantity times price) and compute an average price later as total cents divided by total units. Ratios such as average basket work the same way: store the parts, divide at the end. Stock on hand is a third kind, which adds up across stores but not across days. Metrics everyone agrees on, later in this course, deals with those kinds in depth.
Two more habits keep the fact usable. Keep the receipt number and line number on the fact with no table of their own, so anyone can trace a row back to the till. Kimball calls such a column a degenerate dimension. And never leave a key empty. The customer is known only when someone scans a loyalty card or orders in the app. An empty key silently drops rows from an inner join, so point those lines at a default dimension row, such as customer_key = -1, "Unknown customer".
Quick check
A line-grain fact has a column basket_total_cents copied from the receipt onto every line. A dashboard sums it to show "sales". What goes wrong?
The basket total is true for the receipt, not for each line, so summing it at line grain multiplies it by the number of lines. A store filter doesn't help, because the duplication happens inside every receipt. Sum the line amounts instead, or keep basket totals in a receipt-grain table.
Three kinds of fact table
The dashboard's three measures come from three business processes with different rhythms. Kimball names three basic kinds of fact table, and the dashboard needs one of each.
A transaction fact table has one row per event, written when the event happens and never changed. Sale lines are the classic case. Nothing is recorded when nothing happens: a product that sold no units on Tuesday has no rows for Tuesday.
That gap is why out-of-stock losses need a periodic snapshot: one row per store per article per day, recording stock on hand at closing, written every day whether or not anything moved. An empty shelf is a fact you can only see in a table that records the shelf on the days nothing sold. If a store stocks about 8,000 of Mangrove's 25,000 articles, that is about 2.6 million rows a day across 320 stores. Snapshots are dense on purpose.
Delivery times need the third kind. An app order passes through steps: placed, picked, dispatched, delivered (or cancelled). An accumulating snapshot has one row per order with a column for each milestone. The row is inserted when the order is placed and updated each time the order reaches the next step. Durations such as "minutes from placed to delivered" become plain fact columns, so a median by city and hour is one query. About 60,000 app orders a day means 60,000 new rows a day, each updated a few times in its first hour.
When you are unsure which kind you need, ask what the question counts. Events that happen (sales, refunds, deliveries received) suggest a transaction fact. A level that you want to see at regular points (stock, account balances, open tickets) suggests a periodic snapshot. A process with a known start, known steps and an end (an order, a delivery to a store, a job application) suggests an accumulating snapshot.
Dimensions everyone shares, and how they keep history
The trading dashboard puts sales, out-of-stock losses and delivery times on one page and filters them all with one "City" selector. That works only if the three fact tables use the same store dimension and the same date dimension. A dimension shared like this, with the same keys, names and meanings for every fact, is called conformed. If the app team keeps its own store list and a calendar whose week starts on Sunday, "East city, week 42" means two different things on one page.
Kimball's planning tool for this is the bus matrix: one row per business process, one column per dimension, and a mark where they meet. Each column with more than one mark is a dimension that must be built once and shared. The Do it section below builds one for the trading dashboard.
The date dimension is the one every fact uses. It has one row per day, with columns for the week start, month, fiscal period and festival weeks, so "the same week last year" becomes a join and a filter. One date dimension can also play several roles. A sale line has a sale date and a posting date (the day finance books it), so the fact carries two date keys that point at the same dim_date. Kimball calls this a role-playing dimension.
Dimensions get their own surrogate keys, created by the warehouse. You met surrogate keys in sd-07 as protection against changing codes. The warehouse adds two reasons. Data arrives from several systems: the POS knows a store as S118, the app's order system has its own store ids, and both map to one store_key. And the warehouse keeps history, so one store may need several rows. Keep the source code as an ordinary column; it is the store's durable key, the same in every version.
History is the last design choice. Store attributes change: a manager leaves, or head office redraws its regions. A dimension whose attributes change now and then is a slowly changing dimension, and for each attribute you choose a rule. The Kimball Group lists types 0 to 7; two cover most needs.
- Type 1: overwrite. Update the value in place. Every past fact now shows the new value. Right for corrections, such as a misspelled store name.
- Type 2: add a row. Close the current row (set
valid_toandis_current = false) and insert a new row with a new surrogate key. Facts loaded before the change keep pointing at the old row, and new facts get the new key. Right for changes that matter to analysis, such as the region a store reports to.
Type 2 gives you history "as it was" for free: join the fact to the dimension on the surrogate key. Sometimes a manager wants history "as it is", every past sale under today's region, to compare like with like. Then join through the durable key to the current version of the same store, as the second practice exercise does.
Quick check
A store's name was entered as "Harbour Raod" by mistake. Which rule should fix it?
A typo is a correction, so overwriting is right; nobody wants a report split between "Harbour Raod" and "Harbour Road". Type 2 is for real changes that analysis must respect. Deleting and reloading would orphan every fact that points at the old key.
Do it
Write the first version of the reporting model for the trading dashboard: questions, a bus matrix, grain statements, the sales star in SQL, and two checks that prove it.
Write the decisions and questions
One row per person in the Monday meeting. If a chart can't be traced to a decision here, it doesn't go on the dashboard.
Markdown# Weekly trading dashboard: decisions and questions | Who | Decision this week | Measure | By | Compare with | |----------------|-----------------------------|--------------------|--------------------|----------------------| | Head of stores | Which stores to visit/staff | Sales | store, week | same week last year | | Head of supply | Which products/DCs to fix | Out-of-stock loss | category, DC, week | 4-week average | | App operations | Where to add riders | Minutes to deliver | city, hour | 30-minute promise | Definitions of "sales" and "out-of-stock loss": open, agreed in bi-03.Draw the bus matrix
Rows are business processes, columns are dimensions. Every column with two or more marks is a conformed dimension you build once.
Markdown| Business process (fact) | Date | Store | Article | Channel | Customer | DC | Rider | |-------------------------------|------|-------|---------|---------|----------|----|-------| | Sales: fact_sales_line | X | X | X | X | X | | | | Stock: fact_stock_daily | X | X | X | | | X | | | App orders: fact_app_order | X | X | | X | X | | X |Write one grain sentence per fact
If two people read the sentence and picture different rows, rewrite it.
Markdownfact_sales_line: one row per line on a till receipt or app order, at the moment of sale. fact_stock_daily: one row per store per article per day, as counted at store closing. fact_app_order: one row per app order, updated at each milestone until delivered or cancelled.Create the sales star
Surrogate keys on every dimension, the store code kept as the durable key, type 2 columns on the store, and the grain written as the fact's primary key.
dim_article,dim_channelanddim_customerfollow the same pattern asdim_store.SQLCREATE TABLE dim_date ( date_key integer PRIMARY KEY, -- 20261012 full_date date NOT NULL UNIQUE, week_start date NOT NULL, -- the Monday of that week month_start date NOT NULL, is_festival boolean NOT NULL DEFAULT false ); CREATE TABLE dim_store ( store_key bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- warehouse key store_code text NOT NULL, -- durable key from the POS: 'S118' name text NOT NULL, city text NOT NULL, region text NOT NULL, -- type 2: a change adds a row valid_from date NOT NULL, valid_to date NOT NULL DEFAULT '9999-12-31', is_current boolean NOT NULL DEFAULT true );SQLCREATE TABLE fact_sales_line ( channel_key integer NOT NULL REFERENCES dim_channel (channel_key), sale_id bigint NOT NULL, -- degenerate dimension: receipt or order line_no integer NOT NULL, sale_date_key integer NOT NULL REFERENCES dim_date (date_key), posting_date_key integer NOT NULL REFERENCES dim_date (date_key), -- same dimension, second role store_key bigint NOT NULL REFERENCES dim_store (store_key), article_key bigint NOT NULL REFERENCES dim_article (article_key), customer_key bigint NOT NULL REFERENCES dim_customer (customer_key), -- -1 = unknown order_status text NOT NULL, -- 'completed', 'cancelled', 'returned' qty integer NOT NULL, gross_cents bigint NOT NULL, -- qty x price, before discount discount_cents bigint NOT NULL DEFAULT 0, tax_cents bigint NOT NULL DEFAULT 0, PRIMARY KEY (channel_key, sale_id, line_no) -- the grain, written down );Prove the grain and reconcile to the source
Many cloud warehouses accept a primary key but do not enforce it, so test the grain with a query. Then check that the fact adds up to the source lines day by day (
saleandsale_linefrom the POS). Both queries should return no rows; any row is a bug to fix before anyone sees a chart.SQL-- 1. The grain holds: no two rows for the same line SELECT channel_key, sale_id, line_no, count(*) FROM fact_sales_line GROUP BY channel_key, sale_id, line_no HAVING count(*) > 1; -- 2. Store-channel facts add up to the POS lines, day by day WITH src AS ( SELECT (s.sold_at AT TIME ZONE 'Asia/Dhaka')::date AS day, -- the stores' zone sum(l.qty * l.price_cents) AS source_cents FROM sale s JOIN sale_line l ON l.sale_id = s.id GROUP BY 1 ), fact AS ( SELECT d.full_date AS day, sum(f.gross_cents) AS fact_cents FROM fact_sales_line f JOIN dim_date d ON d.date_key = f.sale_date_key JOIN dim_channel c ON c.channel_key = f.channel_key WHERE c.channel = 'store' GROUP BY 1 ) SELECT * FROM src FULL JOIN fact USING (day) WHERE source_cents IS DISTINCT FROM fact_cents;
Bring it to your team
Ask these whenever someone proposes a new report table or dashboard.
- Which decision will this change, and who makes it each week?
- What is the grain of this fact table, in one sentence?
- Is every column true at that grain, or is something copied down from the order or the day?
- Do all our fact tables use the same store and date dimensions?
- When a store changes region, do its old sales stay in the old region or move? Which do we want, and where is that written down?
- Does the fact reconcile to the source system every day, and who looks when it doesn't?
Red flags
- Dashboards built straight on the app's tables, with a different hand-written query per chart.
- Facts stored already summed by store and day "to save space".
- Order-level amounts repeated on every line row.
- Each team keeping its own store list or its own calendar.
- A store's region or manager overwritten with no history.
- Unit prices or averages stored in a fact and then summed.
Practice
Sum a star at the grain the question needs
A small sales star: fact_sales_line(sale_id, line_no, date_key, store_key, article_key, channel_key, qty, gross_cents), with dim_date(date_key, full_date, week_start), dim_store(store_key, store_code, name, city), dim_article(article_key, name, category) and dim_channel(channel_key, channel). Return gross sales and units by city and category for the week starting 12 October 2026, both channels. Columns: city, category, gross_cents, units, one row per city and category.
CREATE TABLE dim_date (date_key int PRIMARY KEY, full_date date NOT NULL, week_start date NOT NULL); CREATE TABLE dim_store (store_key int PRIMARY KEY, store_code text NOT NULL, name text NOT NULL, city text NOT NULL); CREATE TABLE dim_article (article_key int PRIMARY KEY, name text NOT NULL, category text NOT NULL); CREATE TABLE dim_channel (channel_key int PRIMARY KEY, channel text NOT NULL); CREATE TABLE fact_sales_line (sale_id int NOT NULL, line_no int NOT NULL, date_key int NOT NULL, store_key int NOT NULL, article_key int NOT NULL, channel_key int NOT NULL, qty int NOT NULL, gross_cents int NOT NULL, PRIMARY KEY (sale_id, line_no)); INSERT INTO dim_date VALUES (20261012, '2026-10-12', '2026-10-12'), (20261015, '2026-10-15', '2026-10-12'), (20261018, '2026-10-18', '2026-10-12'), (20261019, '2026-10-19', '2026-10-19'); INSERT INTO dim_store VALUES (1, 'S101', 'Harbour Road', 'East'), (2, 'S102', 'Lake View', 'East'), (3, 'S201', 'Old Market', 'North'); INSERT INTO dim_article VALUES (1, 'Bananas 1kg', 'Fresh'), (2, 'White bread', 'Bakery'), (3, 'Rice 5kg', 'Grocery'); INSERT INTO dim_channel VALUES (1, 'store'), (2, 'app'); INSERT INTO fact_sales_line VALUES (1001, 1, 20261012, 1, 1, 1, 2, 240), (1001, 2, 20261012, 1, 2, 1, 1, 180), (1002, 1, 20261015, 2, 1, 1, 3, 360), (1003, 1, 20261015, 2, 3, 2, 1, 650), (1004, 1, 20261018, 3, 1, 1, 1, 120), (1004, 2, 20261018, 3, 2, 1, 2, 360), (1005, 1, 20261018, 1, 2, 2, 2, 360), (1008, 1, 20261012, 3, 3, 1, 1, 650), (1006, 1, 20261019, 1, 1, 1, 5, 600), (1007, 1, 20261019, 3, 3, 1, 2, 1300);
-- Gross sales and units by city and category, week starting 2026-10-12
SELECT s.city,
sum(f.gross_cents) AS gross_cents
FROM fact_sales_line f
JOIN dim_store s ON s.store_key = f.store_key
-- 1. join dim_article (for category) and dim_date (for week_start)
-- 2. keep only the week starting 2026-10-12
-- 3. add category and units (the sum of qty), and group by city and category
GROUP BY s.city;[
{"city": "East", "category": "Fresh", "gross_cents": 600, "units": 5},
{"city": "East", "category": "Bakery", "gross_cents": 540, "units": 3},
{"city": "East", "category": "Grocery", "gross_cents": 650, "units": 1},
{"city": "North", "category": "Fresh", "gross_cents": 120, "units": 1},
{"city": "North", "category": "Bakery", "gross_cents": 360, "units": 2},
{"city": "North", "category": "Grocery", "gross_cents": 650, "units": 1}
]
Show a solution
SELECT s.city,
a.category,
sum(f.gross_cents) AS gross_cents,
sum(f.qty) AS units
FROM fact_sales_line f
JOIN dim_store s ON s.store_key = f.store_key
JOIN dim_article a ON a.article_key = f.article_key
JOIN dim_date d ON d.date_key = f.date_key
WHERE d.week_start = '2026-10-12' -- filter on the dimension, not on date_key ranges
GROUP BY s.city, a.category;Every star query has this shape: join the dimensions the question names, filter and group on their columns, sum the fact. The two sales on 19 October belong to the next week, so the week filter drops them. Channel isn't in the question, so dim_channel isn't joined and both channels are counted.
History as it is today
On 1 July 2026 Mangrove moved store S118 from the North region to the new Central region, and dim_store is type 2, so S118 has two rows. fact_store_month(month_start, store_key, gross_cents) holds monthly sales from May to August. The Central regional manager wants every month restated to today's regions, so she can compare May and June with July and August like for like. The starter joins on store_key, which counts each month under the region the store had at the time. Change it so every month counts under the store's current region. Columns: region, gross_cents.
CREATE TABLE dim_store (store_key int PRIMARY KEY, store_code text NOT NULL, name text NOT NULL, region text NOT NULL,
valid_from date NOT NULL, valid_to date NOT NULL, is_current boolean NOT NULL);
CREATE TABLE fact_store_month (month_start date NOT NULL, store_key int NOT NULL, gross_cents int NOT NULL);
INSERT INTO dim_store VALUES
(1, 'S118', 'Canal Street', 'North', '2024-01-01', '2026-06-30', false),
(2, 'S118', 'Canal Street', 'Central', '2026-07-01', '9999-12-31', true),
(3, 'S120', 'Mill Lane', 'North', '2024-01-01', '9999-12-31', true),
(4, 'S131', 'Station Road', 'Central', '2024-01-01', '9999-12-31', true);
INSERT INTO fact_store_month VALUES
('2026-05-01', 1, 40000000), ('2026-06-01', 1, 40000000), ('2026-07-01', 2, 40000000), ('2026-08-01', 2, 40000000),
('2026-05-01', 3, 38000000), ('2026-06-01', 3, 38000000), ('2026-07-01', 3, 38000000), ('2026-08-01', 3, 38000000),
('2026-05-01', 4, 42000000), ('2026-06-01', 4, 42000000), ('2026-07-01', 4, 42000000), ('2026-08-01', 4, 42000000);
-- Gross sales by region, May to August 2026.
-- Today this joins each month to the store version current at the time ("as it was").
-- Change it so each month counts under the store's region today ("as it is"):
-- join a second copy of dim_store on store_code, keeping only the current row.
SELECT s.region,
sum(f.gross_cents) AS gross_cents
FROM fact_store_month f
JOIN dim_store s ON s.store_key = f.store_key
GROUP BY s.region;[{"region": "Central", "gross_cents": 328000000}, {"region": "North", "gross_cents": 152000000}]
Show a solution
SELECT cur.region,
sum(f.gross_cents) AS gross_cents
FROM fact_store_month f
JOIN dim_store s ON s.store_key = f.store_key -- the version at the time of sale
JOIN dim_store cur ON cur.store_code = s.store_code -- every version of the same store
AND cur.is_current -- keep only today's version
GROUP BY cur.region;The starter gives North $2.32 million and Central $2.48 million, because S118's May and June sales stay with North. Restated to today's regions, all four of S118's months count for Central: $3.28 million against $1.52 million. Both answers are correct; they answer different questions, so a dashboard must say which one it shows. The durable key (the store code) is what lets one type 2 dimension answer both.
Model the other two measures
The trading dashboard also needs (a) which articles were on an empty shelf at closing, by store and DC, every day, and (b) minutes from placed to delivered for each app order, by city and hour, including how many orders were cancelled. For each, write the fact table type, the grain sentence, the dimensions and the fact columns. Don't define "out-of-stock loss" yet; just make sure the model can compute it later.
- (a) is a periodic snapshot with the grain "one row per store per article per day"
- (a) notes that stock on hand adds up across stores but not across days
- (b) is an accumulating snapshot with one row per order and a time or date key for each milestone
- (b) stores durations in minutes as fact columns and keeps cancelled orders with a status
- Both reuse the same store and date dimensions as the sales fact
Show a model answer
(a) fact_stock_daily: periodic snapshot.
Grain: one row per store per article per day, as counted at store closing.
Dimensions: date, store, article, supplying DC.
Facts: on_hand_qty (semi-additive: sum across stores, never across days),
is_out_of_stock (1 or 0, so a sum counts empty shelves),
expected_daily_units (for the loss estimate defined later).
(b) fact_app_order: accumulating snapshot.
Grain: one row per app order, updated at each milestone.
Dimensions: placed date and hour, store, city (through the store),
customer, rider, order status.
Milestones: placed_at, picked_at, dispatched_at, delivered_at, cancelled_at.
Facts: minutes_to_pick, minutes_to_deliver (empty until delivered),
order_value_cents, is_cancelled.Both use the conformed dim_store and dim_date, so one city filter works across sales, stock and delivery times on the same page.
Check yourself
The app operations lead wants the median minutes from order placed to delivered, by city and hour, and a count of orders stuck at "picked" right now. Which fact table fits best?
An order is a process with known steps, so one row per order with a column per milestone gives both answers: durations for delivered orders and the current step for open ones. A row written only at delivery can never show orders stuck at "picked". A daily city snapshot loses the hour, and delivery minutes are not true at the line grain.
In June, finance's board pack showed North region sales of $41 million for January to June. In August the same report, for the same months, shows $38 million. No sales were loaded or deleted. What is the most likely cause?
If past sales disappear from a region with no change to the facts, the dimension rewrote history. Type 2 keeps each sale with the region of its time, and a join through the durable key can still restate to today's regions when someone asks. A mixed grain would inflate the number, not shrink it, and a week-start change can't move half-year totals.
Finance offers its ledger table (one row per store per day per account, net amounts) as the base for the whole trading dashboard. The buying team will ask for fresh produce sales before noon. What do you say?
You can add up a fine grain, never split a coarse one. A ledger row for a store's day has no products or hours in it, and adding a product column later doesn't bring back data that was summed away. The ledger is valuable as the check the line-grain fact must match. Averaging two different definitions produces a number nobody can explain.
Flashcards
Key takeaways
- Start from the decisions people make; each question gives you a measure, "by" words that become dimensions, a period and a comparison.
- Declare the grain in one sentence before choosing columns, choose the lowest grain the source records, and keep every column true at that grain.
- Events go in transaction facts, levels in periodic snapshots, and processes with milestones in accumulating snapshots.
- Share conformed date and store dimensions with warehouse surrogate keys; use type 2 for changes that matter to history and type 1 for corrections.
- Mangrove's three Monday numbers come from three grains and three dates; one line-grain fact with every amount and both dates lets one definition be agreed later.
Sources and further reading
- Four-Step Dimensional Design Process, Kimball Group
- Dimensional Modeling Techniques (fact table types, conformed dimensions, bus matrix, slowly changing dimension types 0 to 7), Kimball Group
- Understand star schema and the importance for Power BI, Microsoft Learn
- Ralph Kimball and Margy Ross, The Data Warehouse Toolkit, 3rd edition, Wiley, 2013