Big data: manufacturer orders (mfgorders)
25 million order lines from a manufacturer to wholesalers and retailers, stored as compressed columnstore on SQL Server and computed on the fly on MariaDB; plus a bill of materials.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Query 25 million order lines on SQL Server columnstore, and on MariaDB with a limit.
- Explain why the big table takes almost no disk, and how it is built.
- Write queries that stay fast at this size.
- Use the bill of materials to read how a manufacturer's products are built.
mfgorders is a manufacturer's order history: 25,165,824 order lines (6.3 million orders, about $6.3 billion) from January 2023 to 24 October 2026, sold to wholesalers, retail chains and independent retailers in eleven countries and made in eight plants. It is here to practice querying large tables, and it adds the one thing the other databases lack: manufacturing, with a three-level bill of materials and a customer hierarchy in which retailers belong to wholesalers.
All customers, products and amounts are invented.
How 25 million rows take almost no disk
Nothing big sits in the project folder. The rows are not stored in a data file you load: SQL Server builds them on the server, and MariaDB computes them when you ask.
| Engine | What order_lines is |
Disk | Speed |
|---|---|---|---|
| SQL Server | A real table with a clustered columnstore index: each column is stored and compressed separately | About 380 MB for all 25 million rows (an ordinary table would need several gigabytes) | Seconds. Every full-table query below runs in under four seconds |
| MariaDB | A view over the virtual seq_1_to_25165824 table: each row is calculated from its number when you query it |
None | Roughly five microseconds a row, so a full scan takes minutes. Limit the rows with order_line_id |
Both engines use the same integer arithmetic and no random numbers, so the data is identical. Because order dates grow with the order number, an order_line_id range is also a date range: the first two million lines are January through April 2023, and the last lines are October 2026.
On MariaDB, add a limit to the examples below. Put o.order_line_id <= 1000000 AND at the start of the WHERE clause (or use o.order_line_id > 25000000 for the latest lines). The queries are written for SQL Server and run unchanged there.
Connect to it
- SQL Server: server
learnsql.cpetoday.com,1433, databasemfgorders, tables in thedboschema. See Connect Excel and Power BI. - In the browser: the database page. The SQL editor works on both engines.
- Fiscal year: November to October, named by the year it ends (
dim_date.fiscal_year).
The tables
| Table | Rows | What it holds |
|---|---|---|
order_lines |
25,165,824 | One row per order line: order_id, line_number, order_date, ship_date, customer, product, plant, channel, quantity, unit_price, discount_pct, net_amount, cost_amount |
dim_customer |
20,000 | 500 wholesalers, 1,000 retail chains, 18,500 retailers; parent_customer_id links a retailer to its wholesaler; coordinates |
dim_product |
2,800 | 1,500 finished goods, 500 subassemblies and 800 components, with list price, standard cost, make or buy and lead time |
bom |
about 8,500 | The bill of materials: parent, child, quantity per, scrap percent |
dim_product_family, dim_plant, dim_order_channel |
12, 8, 4 | Families, plants (with coordinates) and order channels |
dim_date |
1,461 | Calendar with fiscal columns |
Views: vw_bom_explosion (every component of a finished good at every level, with cost) and vw_customer_hierarchy.
The big table has no enforced foreign keys, which is normal for columnstore fact tables, so the schema explorer shows nothing connected to it. Join on the ids.
Questions to try
How big is it? The whole table in a few seconds:
SELECT COUNT(*) AS order_lines, COUNT(DISTINCT o.order_id) AS orders, COUNT(DISTINCT o.customer_id) AS customers,
MIN(o.order_date) AS first_order, MAX(o.order_date) AS last_order, ROUND(SUM(o.net_amount) / 1000000.0, 1) AS sales_millions
FROM order_lines o
WHERE o.order_date >= '2023-01-01';
Sales and margin by fiscal quarter and customer type. A join to two dimensions across all 25 million lines:
SELECT d.fiscal_year, d.fiscal_quarter, c.customer_type, COUNT(*) AS line_count, ROUND(SUM(o.net_amount) / 1000000.0, 1) AS sales_millions,
ROUND(100.0 * (SUM(o.net_amount) - SUM(o.cost_amount)) / SUM(o.net_amount), 1) AS margin_pct
FROM order_lines o
JOIN dim_customer c ON c.customer_id = o.customer_id
JOIN dim_date d ON d.calendar_date = o.order_date
WHERE o.order_date >= '2023-01-01'
GROUP BY d.fiscal_year, d.fiscal_quarter, c.customer_type
ORDER BY d.fiscal_year, d.fiscal_quarter, c.customer_type;
Wholesalers get larger quantities at a 25 percent discount, so their margin is lower than retailers'.
Which products sell the most, and what share of the total is each? A window function over the grouped result:
SELECT p.sku, p.product_name, ROUND(SUM(o.net_amount), 0) AS sales, ROUND(100.0 * SUM(o.net_amount) / SUM(SUM(o.net_amount)) OVER (), 2) AS pct_of_total FROM order_lines o JOIN dim_product p ON p.product_id = o.product_id WHERE o.order_date >= '2023-01-01' GROUP BY p.sku, p.product_name ORDER BY sales DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
Is it a Pareto? Rank the customers, then band them by rank:
WITH c AS (SELECT o.customer_id, SUM(o.net_amount) AS s FROM order_lines o WHERE o.order_date >= '2023-01-01' GROUP BY o.customer_id),
r AS (SELECT customer_id, s, ROW_NUMBER() OVER (ORDER BY s DESC) AS rn, SUM(s) OVER () AS total FROM c)
SELECT CASE WHEN rn <= 200 THEN '1 top 1% (200 customers)' WHEN rn <= 1000 THEN '2 next 4%' ELSE '3 other 95%' END AS band,
COUNT(*) AS customers, ROUND(SUM(s) / 1000000.0, 1) AS sales_millions, ROUND(100.0 * SUM(s) / MAX(total), 1) AS pct_of_sales
FROM r
GROUP BY CASE WHEN rn <= 200 THEN '1 top 1% (200 customers)' WHEN rn <= 1000 THEN '2 next 4%' ELSE '3 other 95%' END
ORDER BY band;
The top 1 percent of customers (200 of about 20,000) place roughly a fifth of the sales, and the top 5 percent roughly 40 percent.
How do monthly sales trend? A three-month moving average with a window frame:
WITH m AS (SELECT d.calendar_year AS yr, d.calendar_month AS mo, SUM(o.net_amount) AS sales
FROM order_lines o JOIN dim_date d ON d.calendar_date = o.order_date
WHERE o.order_date >= '2023-01-01'
GROUP BY d.calendar_year, d.calendar_month)
SELECT yr, mo, ROUND(sales / 1000000.0, 1) AS sales_millions,
ROUND(AVG(sales) OVER (ORDER BY yr, mo ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) / 1000000.0, 1) AS moving_avg_3_months
FROM m
ORDER BY yr, mo;
Where is the margin, by plant and product family?
SELECT pl.plant_name, f.family_name, ROUND(SUM(o.net_amount) / 1000000.0, 1) AS sales_millions,
ROUND(100.0 * (SUM(o.net_amount) - SUM(o.cost_amount)) / SUM(o.net_amount), 1) AS margin_pct
FROM order_lines o
JOIN dim_product p ON p.product_id = o.product_id
JOIN dim_product_family f ON f.family_id = p.family_id
JOIN dim_plant pl ON pl.plant_id = o.plant_id
WHERE o.order_date >= '2023-01-01'
GROUP BY pl.plant_name, f.family_name
ORDER BY sales_millions DESC
OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;
Each product is made mostly in its home plant (85 percent of lines), so plants differ by product family.
How many lines have not shipped yet? Open lines are in the latest weeks:
SELECT ch.channel_name, COUNT(*) AS line_count, SUM(CASE WHEN o.ship_date IS NULL THEN 1 ELSE 0 END) AS not_yet_shipped,
ROUND(100.0 * SUM(CASE WHEN o.ship_date IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_not_shipped
FROM order_lines o
JOIN dim_order_channel ch ON ch.channel_id = o.channel_id
WHERE o.order_date >= '2026-09-01'
GROUP BY ch.channel_name
ORDER BY ch.channel_name;
Which wholesalers sell the most once you include their retailers? The customer hierarchy is a self-join on parent_customer_id:
SELECT w.customer_name AS wholesaler, w.country, COUNT(DISTINCT c.customer_id) AS accounts_in_hierarchy, ROUND(SUM(o.net_amount) / 1000000.0, 2) AS sales_millions FROM order_lines o JOIN dim_customer c ON c.customer_id = o.customer_id JOIN dim_customer w ON w.customer_id = COALESCE(c.parent_customer_id, c.customer_id) WHERE w.customer_type = 'Wholesaler' AND o.order_date >= '2023-01-01' GROUP BY w.customer_name, w.country ORDER BY sales_millions DESC OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;
Sales by region and customer type for one month:
SELECT c.region, c.customer_type, COUNT(*) AS line_count, ROUND(SUM(o.net_amount) / 1000000.0, 1) AS sales_millions FROM order_lines o JOIN dim_customer c ON c.customer_id = o.customer_id WHERE o.order_date >= '2026-01-01' AND o.order_date < '2026-02-01' GROUP BY c.region, c.customer_type ORDER BY sales_millions DESC OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;
Because order_date grows with the row number, SQL Server skips the compressed row groups that cannot contain January 2026 (segment elimination), so a one-month query reads a small part of the table.
SQL Server only: approximate distinct counts
On a very large table an exact COUNT(DISTINCT ...) is expensive. APPROX_COUNT_DISTINCT is much cheaper and is typically within a few percent:
SELECT APPROX_COUNT_DISTINCT(order_id) AS approx_orders, COUNT(DISTINCT order_id) AS exact_orders FROM order_lines;
Manufacturing: the bill of materials
vw_bom_explosion is a recursive query. It starts at each finished good and repeatedly follows bom down to subassemblies and components, multiplying the quantities. The full explosion of one finished good, with every level:
SELECT finished_good_sku, bom_level, item_sku, item_name, item_type, make_or_buy, quantity_per_finished_good, ROUND(extended_cost_usd, 2) AS extended_cost_usd FROM vw_bom_explosion WHERE finished_good_sku = 'FG-00001' ORDER BY bom_level, item_sku;
Rolling the component cost up and comparing it with the product's standard cost shows where materials alone exceed the standard cost, which is a margin problem to chase:
SELECT x.finished_good_sku, p.standard_cost_usd AS standard_cost, ROUND(x.component_cost, 2) AS rolled_up_component_cost,
ROUND(x.component_cost - p.standard_cost_usd, 2) AS gap
FROM (SELECT finished_good_sku, SUM(CASE WHEN item_type = 'Component' THEN extended_cost_usd ELSE 0 END) AS component_cost
FROM vw_bom_explosion GROUP BY finished_good_sku) x
JOIN dim_product p ON p.sku = x.finished_good_sku
ORDER BY ABS(x.component_cost - p.standard_cost_usd) DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;
What makes a table like this fast, and what to do in Power BI
- Columnstore compresses each column on its own and processes about 900 rows at a time (batch mode). Narrow, repetitive columns such as ids and codes compress best.
- Sorted loading. The rows are inserted in date order, so each row group covers a narrow date range and a date filter skips most of them.
- Filter early, select few columns. Avoid
SELECT *: a columnstore only reads the columns you name. - Pre-aggregate for reports. A report rarely needs 25 million rows. Summarize to day, product and customer type (about a million rows) in SQL Server, or keep the detail in DirectQuery and the summary in Import, using Power BI aggregations. In
retailwh,fact_sales_period_aggshows the same idea on a small scale. - Import is limited by model size, not row count. Order numbers and exact timestamps have a very high number of distinct values and compress poorly; drop what no report uses.
- DirectQuery sends a query to SQL Server per visual, which columnstore answers quickly, but one query returns at most a million rows. Excel cannot use DirectQuery.
- Incremental refresh loads only the recent partitions of a big table.
See Data structures and Power BI limits for the limits that apply to models of this size.
Things worth knowing
- Skew. A few wholesalers and chains place a large share of the orders, and a few products outsell the rest, which makes top-N queries interesting.
- Open lines.
ship_dateis null for lines that would ship after 24 October 2026. - Hierarchy. About 70 percent of retailers have a wholesaler as
parent_customer_id; the rest buy directly. - The build. SQL Server builds the table in 24 batches of 1,048,576 rows (one compressed row group each), then compresses and shrinks the file. A rebuild takes about ten minutes; MariaDB takes seconds because it stores nothing.
Try it on the data
Open the mfgorders database page, browse it in the Playground, write your own queries in the SQL editor, see how the tables connect in the schema explorer, or ask an AI assistant.