<!-- https://learnsql.cpetoday.com/analyze/retail-warehouse -->
# Retail data warehouse (retailwh)

`retailwh` is a data warehouse for a fictional retailer with 43 locations in the United States, Canada, the United Kingdom and Germany, plus an online, phone and marketplace "store" for each country. It is not an application database: it is organized the way a warehouse is, with **fact tables** (what happened) around **dimension tables** (who, what, where, when), so it is the place to learn modeling patterns before you build them in Power BI or Excel. April-March fiscal year, FY2023 through 24 October 2026 (the start of FY2027), about 787,000 rows in 25 tables.

> All customers, products, brands, suppliers and amounts are invented. Region boundaries are deliberately rough rectangles.

## Connect to it

- **SQL Server:** server `learnsql.cpetoday.com,1433`, database `retailwh`, tables in the `dbo` schema. See [Connect Excel and Power BI](/connect).
- **In the browser:** open [the database page](/databases/retailwh) for the Playground, the SQL editor and the schema explorer. The schema explorer draws every declared relationship, which makes the star and snowflake shapes easy to see.
- **Fiscal year:** April to March, named by the year it ends, so FY2026 is April 2025 to March 2026 and fiscal period 1 is April. `dim_date` has the fiscal columns; there are no fiscal columns on the facts, because a warehouse joins to the date dimension.

## The tables

| Pattern | Tables | What they hold |
|---|---|---|
| Dates and money | `dim_date`, `dim_currency`, `fx_rates` | A daily calendar with fiscal columns and holidays; four currencies and a daily rate to USD |
| Places | `dim_country`, `dim_region`, `dim_store` | 8 countries, 6 selling regions (with a rough boundary polygon), 43 locations with latitude and longitude |
| Product snowflake | `dim_category`, `dim_subcategory`, `dim_product`, `dim_brand`, `dim_supplier`, `dim_product_price`, `dim_tag`, `bridge_product_tag` | 323 products in 17 subcategories and 5 categories, 24 brands, 14 suppliers, a price history and tags |
| Customers (type 2) | `dim_customer` | 12,000 customers as about 19,000 rows: a new row when a customer moves, changes tier or changes segment |
| Other dimensions | `dim_channel`, `dim_promotion` | In-store, online, phone and marketplace; 44 promotions |
| Sales | `fact_sales`, `fact_returns` | About 379,000 order lines at the grain of one product on one order, and the lines that came back |
| Inventory | `fact_inventory_snapshot` | Month-end stock by product and store |
| Orders in flight | `fact_order_pipeline` | One row per online, phone or marketplace order, with a date for each stage |
| Promotions | `fact_promo_coverage` | Which products were on offer in which stores for which promotion (a factless table) |
| Plan and summary | `fact_budget`, `fact_sales_period_agg` | Budgets by fiscal period, category and region; and actual sales summarized to the same grain |

Five views flatten the common joins: `vw_sales_detail` (the whole star in one row), `vw_customer_current`, `vw_inventory_latest`, `vw_order_cycle_time` and `vw_budget_vs_actual`.

## How the tables join

```
dim_date ───────────┐   (role-playing: order date, ship date, and six dates on the pipeline)
dim_customer ───────┤
dim_channel ────────┤
dim_promotion ──────┼──< fact_sales >── dim_product >── dim_subcategory >── dim_category      (snowflake)
dim_currency ───────┤        │               └─────── dim_brand >── dim_supplier >── dim_country >── dim_currency
dim_store >── dim_region >── dim_country >── dim_currency
                    fact_returns >── fact_sales          dim_product >──< dim_tag  (via bridge_product_tag)
fact_order_pipeline, fact_inventory_snapshot, fact_promo_coverage: other facts on the same dimensions
fact_budget and fact_sales_period_agg: facts at a coarser grain, joined on category and region
```

## Each pattern, with a query

### Star schema: one fact, many dimensions

`vw_sales_detail` joins the star for you. Sales and margin by category for the 2026 fiscal year:

```sql
SELECT fiscal_year, category_name, ROUND(SUM(net_amount_usd), 0) AS sales_usd, ROUND(SUM(margin_usd) * 100.0 / SUM(net_amount_usd), 1) AS margin_pct
FROM vw_sales_detail
WHERE fiscal_year = 2026
GROUP BY fiscal_year, category_name
ORDER BY sales_usd DESC;
```

### Snowflake: follow the chain

Product reaches its category through the subcategory, and its supplier country through brand and supplier. Nothing is wrong with this, but each hop is another join and another relationship in Power BI:

```sql
SELECT cat.category_name, sub.subcategory_name, b.brand_name, sup.supplier_name, co.country_name AS supplier_country,
       COUNT(DISTINCT p.product_key) AS products, ROUND(SUM(s.net_amount_usd), 0) AS sales_usd
FROM fact_sales s
JOIN dim_product p ON p.product_key = s.product_key
JOIN dim_subcategory sub ON sub.subcategory_key = p.subcategory_key
JOIN dim_category cat ON cat.category_key = sub.category_key
JOIN dim_brand b ON b.brand_key = p.brand_key
JOIN dim_supplier sup ON sup.supplier_key = b.supplier_key
JOIN dim_country co ON co.country_key = sup.country_key
GROUP BY cat.category_name, sub.subcategory_name, b.brand_name, sup.supplier_name, co.country_name
ORDER BY sales_usd DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;
```

### Slowly changing dimension (type 2)

When a customer changes, `dim_customer` gets a new row instead of changing the old one. Each version has `valid_from`, `valid_to` (9999-12-31 for the current one), `is_current` and a `change_reason`. Here are six customers' histories:

```sql
SELECT customer_id, customer_name, loyalty_tier, segment, city, valid_from, valid_to, is_current, change_reason
FROM dim_customer
WHERE customer_id IN ('C000010', 'C000011', 'C000012', 'C000013', 'C000014', 'C000015')
ORDER BY customer_id, valid_from;
```

`fact_sales.customer_key` points at the version that was true **on the order date**, so a sale keeps the tier and segment the customer had at the time. That is what `tier_at_sale` in the view means:

```sql
SELECT tier_at_sale, COUNT(DISTINCT customer_id) AS customers, COUNT(DISTINCT order_id) AS orders, ROUND(SUM(net_amount_usd), 0) AS sales_usd,
       ROUND(SUM(net_amount_usd) / COUNT(DISTINCT order_id), 2) AS avg_order_usd
FROM vw_sales_detail
WHERE fiscal_year = 2026
GROUP BY tier_at_sale
ORDER BY sales_usd DESC;
```

To count customers rather than versions, count `DISTINCT customer_id`. To report on today's attributes, join through `customer_id` to the row where `is_current = 1` (see `vw_customer_current`).

### Price history with validity dates

`dim_product_price` is a type 2 table too: a list price with the dates it applies. Prices change most Aprils, and electronics get cheaper:

```sql
SELECT p.sku, p.product_name, pp.valid_from, pp.valid_to, pp.list_price_usd
FROM dim_product p
JOIN dim_product_price pp ON pp.product_key = p.product_key
WHERE p.product_key IN (SELECT product_key FROM dim_product_price GROUP BY product_key HAVING COUNT(*) >= 4)
ORDER BY p.sku, pp.valid_from
OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;
```

### Multi-currency

Each sale line keeps the local amount, the exchange rate on the day, and the USD amount. The effective rate is not the average of the daily rates, because it is weighted by sales:

```sql
SELECT cur.currency_code, COUNT(*) AS line_count, ROUND(SUM(s.net_amount_local), 0) AS sales_local, ROUND(SUM(s.net_amount_usd), 0) AS sales_usd,
       ROUND(SUM(s.net_amount_usd) / SUM(s.net_amount_local), 4) AS effective_rate, ROUND(MIN(s.fx_rate_to_usd), 4) AS lowest_rate, ROUND(MAX(s.fx_rate_to_usd), 4) AS highest_rate
FROM fact_sales s
JOIN dim_currency cur ON cur.currency_key = s.currency_key
GROUP BY cur.currency_code
ORDER BY sales_usd DESC;
```

### Periodic snapshot: do not add a balance across time

`fact_inventory_snapshot` records stock at each month-end. You can add it across products and stores, but not across months, because the same units appear in every month. Look at one month at a time, take the last month, or average:

```sql
SELECT dd.fiscal_period_name, SUM(i.on_hand_units) AS on_hand_units_all_stores, SUM(i.units_sold_in_month) AS units_sold, SUM(CAST(i.is_stockout AS INT)) AS stockouts
FROM fact_inventory_snapshot i
JOIN dim_date dd ON dd.date_key = i.month_end_date_key
WHERE dd.fiscal_year = 2026
GROUP BY dd.fiscal_period_name
ORDER BY dd.fiscal_period_name;
```

`units_sold_in_month` is additive across months; `on_hand_units` is not.

### Accumulating snapshot: an order moving through stages

`fact_order_pipeline` has one row per order and a date for each stage; a stage the order has not reached is null. `vw_order_cycle_time` turns the dates into days:

```sql
SELECT ch.channel_name, od.fiscal_year, COUNT(*) AS orders, ROUND(AVG(o.days_to_ship * 1.0), 2) AS avg_days_to_ship, ROUND(AVG(o.days_to_deliver * 1.0), 2) AS avg_days_to_deliver
FROM vw_order_cycle_time o
JOIN dim_channel ch ON ch.channel_name = o.channel_name
JOIN dim_date od ON od.calendar_date = o.order_date
WHERE o.status IN ('Delivered', 'Returned') AND od.fiscal_year >= 2025
GROUP BY ch.channel_name, od.fiscal_year
ORDER BY ch.channel_name, od.fiscal_year;
```

### Factless fact: what was offered, and what did not sell

`fact_promo_coverage` has no measures. It records that a product was on promotion in a store. Joining it to the sales shows the offers nobody took, which a sales table alone can never show:

```sql
SELECT pr.promotion_name, COUNT(*) AS offered_combinations,
       SUM(CASE WHEN x.line_count IS NULL THEN 1 ELSE 0 END) AS never_sold,
       ROUND(100.0 * SUM(CASE WHEN x.line_count IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_never_sold
FROM fact_promo_coverage c
JOIN dim_promotion pr ON pr.promotion_key = c.promotion_key
LEFT JOIN (SELECT promotion_key, product_key, store_key, COUNT(*) AS line_count
           FROM fact_sales WHERE promotion_key IS NOT NULL
           GROUP BY promotion_key, product_key, store_key) x
       ON x.promotion_key = c.promotion_key AND x.product_key = c.product_key AND x.store_key = c.store_key
WHERE pr.start_date >= '2025-01-01' AND pr.start_date < '2026-01-01'
GROUP BY pr.promotion_name
ORDER BY pct_never_sold DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;
```

### Facts at different grain: budget against actual

> **Watch out.** The budget is by fiscal period, category and region; sales are by order line. Compare them at the budget's grain.

The warehouse keeps a pre-summarized `fact_sales_period_agg` for this, which is also the standard fix when a view over a large fact table is slow:

```sql
SELECT category_name, SUM(budget_usd) AS budget_usd, SUM(actual_usd) AS actual_usd, ROUND(SUM(actual_usd) * 100.0 / SUM(budget_usd), 1) AS pct_of_budget
FROM vw_budget_vs_actual
WHERE fiscal_year = 2026
GROUP BY category_name
ORDER BY pct_of_budget DESC;
```

### Many-to-many with a bridge table

A product can have several tags and a tag many products. Summing sales by tag counts a product once for each of its tags, so the totals do not add up to total sales. That is correct for this question, but be careful when you use tags as a filter:

```sql
SELECT t.tag_name, COUNT(DISTINCT p.product_key) AS products, ROUND(SUM(s.net_amount_usd), 0) AS sales_usd
FROM fact_sales s
JOIN dim_product p ON p.product_key = s.product_key
JOIN bridge_product_tag b ON b.product_key = p.product_key
JOIN dim_tag t ON t.tag_key = b.tag_key
GROUP BY t.tag_name
ORDER BY sales_usd DESC;
```

### Role-playing dimension: one date table, several jobs

`fact_sales` has an order date and a ship date, both pointing at `dim_date`. Join the date table twice with different aliases. This finds December 2025 orders that shipped in January:

```sql
SELECT od.month_name AS order_month, sd.month_name AS ship_month, COUNT(*) AS line_count
FROM fact_sales s
JOIN dim_date od ON od.date_key = s.order_date_key
JOIN dim_date sd ON sd.date_key = s.ship_date_key
WHERE od.calendar_year = 2025 AND od.calendar_month = 12 AND sd.calendar_month <> od.calendar_month
GROUP BY od.month_name, sd.month_name;
```

### Geography: points and polygons

Stores and customers have latitude and longitude, which is enough for a bubble map. `dim_region.boundary_geojson` holds a rough GeoJSON polygon for each region, to practice a shape map. Stores by region:

```sql
SELECT r.region_name, co.country_name, COUNT(*) AS stores, SUM(st.square_feet) AS square_feet
FROM dim_store st
JOIN dim_region r ON r.region_key = st.region_key
JOIN dim_country co ON co.country_key = r.country_key
WHERE st.is_online = 0
GROUP BY r.region_name, co.country_name
ORDER BY stores DESC;
```

## Modeling it in Power BI and Excel

- **Load the star.** Relate each fact key to its dimension, one-to-many with single-direction filtering, hide the keys, and write measures on the fact tables. Flatten the snowflake chains in Power Query unless you need them.
- **Handle the customer versions.** Relate `fact_sales[customer_key]` to `dim_customer[customer_key]` to get attributes at the time of sale. Count customers with a distinct count on `customer_id`.
- **One date table, several relationships.** Make `order_date_key` active and `ship_date_key` inactive, and use `USERELATIONSHIP` in a measure. See [time intelligence](/analyze/time-intelligence) and mark `dim_date` as the date table; the fiscal columns give a April-March year.
- **Snapshots.** For on-hand units, write a measure that takes the last month in the filter (`LASTNONBLANK` or `CLOSINGBALANCEMONTH`), not a sum.
- **Budget against actual.** Relate `fact_budget` and `fact_sales_period_agg` to shared category, region and fiscal-period dimensions, not to each other.
- **Many-to-many tags.** Use a bridge table with a filter from the tag to the product; expect double counting across tags.
- **Maps.** Put `latitude` and `longitude` in a map visual, and use `boundary_geojson` as a custom shape. See [Maps and fiscal calendars](/analyze/maps-and-fiscal).

More structures, and what Power BI cannot do with them, are in [Data structures and Power BI limits](/analyze/data-structures).

## Things that happen in the data

- Four stores open (Austin, Ottawa, Bristol, Cologne) and three close (Pittsburgh, Kansas City, Birmingham), so `opened_date` and `closed_date` matter.
- Each store carries about a third of the products; the online locations carry all of them.
- About 2 percent of non-store orders are cancelled and have a pipeline row but no sales lines.
- Returns are most common for apparel and for orders that were shipped.
- Exchange rates drift around their base values; the Canadian dollar, pound and euro each drift by several percent over the period.
- Promotions apply only where `fact_promo_coverage` says the product was offered in that store.
