<!-- https://learnsql.cpetoday.com/analyze/big-orders -->
# Big data: manufacturer orders (mfgorders)

`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`, database `mfgorders`, tables in the `dbo` schema. See [Connect Excel and Power BI](/connect).
- **In the browser:** [the database page](/databases/mfgorders). 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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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?**

```sql
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:

```sql
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`:

```sql
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:**

```sql
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:

```sql
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:

```sql
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:

```sql
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_agg` shows 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](/analyze/data-structures) 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_date` is 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.
