Downloads
Dimensional modeling

Retail Data Warehouse

A retail data warehouse built to teach modeling: a star schema with snowflaked product and store dimensions, customers that change over time (type 2), a price history, a month-end inventory snapshot, an order pipeline with a date for each stage, a factless promotion table, four currencies with daily exchange rates, and budgets at a different grain.

retailwhSQL ServerMariaDB 25 tables
fact_salesdim_productdim_customerdim_storedim_date

Read the guide

Retail data warehouse (retailwh): Star and snowflake schemas, slowly changing customers, price history, inventory and order-pipeline snapshots, a factless table, multi-currency and budgets, in one retail warehouse.

Good for

Star and snowflake schemasSlowly changing dimensionsSnapshots and semi-additive measuresModeling in Power BI and Excel

Questions to try

  • How did sales change by fiscal year and category?
  • What tier was a customer in when they bought?
  • Which promoted products never sold?
  • How long does an online order take to ship?

Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.

Connect to it

Server learnsql.cpetoday.com,1433, database retailwh, in Excel or Power BI (Import mode) or DBeaver. These tables are in the dbo schema, so once you have picked the database you can use plain names such as bridge_product_tag.

Download the Excel connection (.odc)

How to use it: download the file and open it. Excel opens it as a connection to retailwh and asks for the SQL Server username and password from your CPE Today course. The file holds no password. The first table is selected by default, and you can pick others from the Data window. Setup details for other tools are on Connect.

Tables (25)

Click a table to preview it in the Playground.

Data dictionary

What each column means, its type and keys, and for codes and statuses the values seen in this copy. 11 of 25 tables are described so far. Read it as Markdown.

Click a table in the diagram above to jump to it.

dim_channel one row per channel 3 columns

Sales channels.

ColumnTypeKeyMeaningValues seen
channel_key int PK Unique channel key. 1 1 2 1 3 1 4 1
channel_name varchar(20) Channel name. In-store 1 Marketplace 1 Online 1 Phone 1
is_digital bit 1 for digital channels. False 2 True 2
dim_customer one row per customer version 16 columns

Customers, one row per version: slowly changing history with valid_from, valid_to and is_current.

ColumnTypeKeyMeaningValues seen
customer_key int PK Unique row key (a customer can have several).
customer_id varchar(10) Business customer ID.
customer_name varchar(80) Name.
email varchar(100) Not described yet
segment varchar(16) Customer segment (see the values). Consumer 15613 Small business 2448 Wholesale 968
loyalty_tier varchar(10) Loyalty tier (see the values). Bronze 7753 Silver 6481 Gold 3559 Platinum 1236
region_key int FK to dim_region Region (dim_region). 2 3836 1 3802 3 3797 5 3070 6 2425 4 2099
city varchar(60) Not described yet
postal_code varchar(10) Not described yet
latitude decimal Not described yet
longitude decimal Not described yet
signup_date date Not described yet
valid_from date First date this version applies.
valid_to date Last date this version applies.
is_current bit 1 for the current version. True 12000 False 7029
change_reason varchar(20) Why this version was created. (blank) 7661 Tier change 5552 Signup 4339 Moved 1109 Segment change 368
dim_date one row per day 17 columns

One row per calendar day with calendar and fiscal labels.

ColumnTypeKeyMeaningValues seen
date_key int PK Date key.
calendar_date date The calendar date.
calendar_year smallint Calendar year. 2024 366 2023 365 2025 365 2026 365 2022 275 2027 90
calendar_quarter tinyint Not described yet
calendar_month tinyint Calendar month number. 1 155 3 155 5 155 7 155 8 155 10 155 12 155 4 150 6 150 9 150 11 150 2 141
month_name varchar(12) Not described yet
day_of_month tinyint Not described yet
day_of_week tinyint Not described yet
day_name varchar(12) Not described yet
is_weekend bit 1 on a weekend day. False 1304 True 522
iso_week tinyint Not described yet
fiscal_year smallint Fiscal year. 2024 366 2023 365 2025 365 2026 365 2027 365
fiscal_quarter tinyint Fiscal quarter. 2 460 3 460 1 455 4 451
fiscal_period tinyint Fiscal period. 2 155 4 155 5 155 7 155 9 155 10 155 12 155 1 150 3 150 6 150 8 150 11 141
fiscal_period_name varchar(12) Not described yet
is_holiday bit 1 on a holiday. False 1776 True 50
holiday_name varchar(40) Not described yet
dim_product one row per product 10 columns

Products: SKU, name, subcategory, brand, size and cost.

ColumnTypeKeyMeaningValues seen
product_key int PK Unique product key.
sku varchar(14) Stock keeping unit.
product_name varchar(80) Product name.
subcategory_key int FK to dim_subcategory Subcategory (dim_subcategory).
brand_key int FK to dim_brand Brand (dim_brand).
color varchar(20) Color. (blank) 38 Olive 34 Slate 34 White 33 Navy 30 Sand 30 Rust 29 Forest 27 Charcoal 25 Black 24 Sky 19
size_label varchar(12) Size label. (blank) 247 XL 20 XS 17 L 16 M 13 S 10
unit_cost_usd decimal Cost per unit in USD.
launch_date date Date the product launched.
discontinued_date date Date discontinued, if it has been.
dim_promotion one row per promotion 7 columns

Promotions with discount and dates.

ColumnTypeKeyMeaningValues seen
promotion_key int PK Unique promotion key.
promotion_name varchar(60) Name.
promotion_type varchar(20) Type of promotion (see the values). Seasonal 25 Holiday 10 Clearance 5 Category 4
discount_pct decimal Discount percent. 15.0 20 20.0 14 25.0 5 30.0 5
start_date date First day.
end_date date Last day.
category_key int FK to dim_category Not described yet
dim_store one row per store 14 columns

Stores and online channels, with location, size and open or close dates.

ColumnTypeKeyMeaningValues seen
store_key int PK Unique store key.
store_id varchar(8) Not described yet
store_name varchar(60) Store name.
store_type varchar(12) Type of store (see the values). Mall 12 Outlet 11 Street 9 Flagship 7 Online 4
region_key int FK to dim_region Region (dim_region). 2 9 1 8 3 8 4 6 5 6 6 6
city varchar(60) Not described yet
state_province varchar(30) Not described yet
postal_code varchar(10) Not described yet
latitude decimal Not described yet
longitude decimal Not described yet
opened_date date Opening date.
closed_date date Closing date, if closed. (blank) 40 2024-01-31 1 2025-02-28 1 2026-05-31 1
square_feet int Floor area.
is_online bit 1 for online stores. False 39 True 4
fact_budget one row per budget line 6 columns

Budgeted sales by fiscal period, category and region.

ColumnTypeKeyMeaningValues seen
budget_id int PK Unique budget row ID.
fiscal_year smallint Fiscal year. 2023 360 2024 360 2025 360 2026 360 2027 360
fiscal_period tinyint Fiscal period. 1 150 2 150 3 150 4 150 5 150 6 150 7 150 8 150 9 150 10 150 11 150 12 150
category_key int FK to dim_category Category (dim_category). 1 360 2 360 3 360 4 360 5 360
region_key int FK to dim_region Region (dim_region). 1 300 2 300 3 300 4 300 5 300 6 300
budget_usd decimal Budget in USD.
fact_inventory_snapshot one row per product, store and month end 8 columns

Stock by product and store at each month end.

ColumnTypeKeyMeaningValues seen
snapshot_id int PK Unique row ID.
month_end_date_key int FK to dim_date Month-end date (dim_date).
product_key int FK to dim_product Product (dim_product).
store_key int FK to dim_store Store (dim_store).
on_hand_units int Units on hand.
on_order_units int Units on order.
units_sold_in_month int Units sold in the month.
is_stockout bit 1 when the item was out of stock. False 189137 True 59865
fact_order_pipeline one row per order 14 columns

Each order's progress through fulfillment, with a date key for each step.

ColumnTypeKeyMeaningValues seen
pipeline_id int PK Unique row ID.
order_id varchar(14) Order.
customer_key int FK to dim_customer Customer (dim_customer).
store_key int FK to dim_store Store (dim_store). 40 38355 42 10110 43 8331 41 6986
channel_key int FK to dim_channel Channel (dim_channel). 2 44903 4 11334 3 7545
order_date_key int FK to dim_date Date ordered.
paid_date_key int FK to dim_date Date paid.
picked_date_key int FK to dim_date Not described yet
shipped_date_key int FK to dim_date Date shipped.
delivered_date_key int FK to dim_date Date delivered.
returned_date_key int FK to dim_date Date returned, if any.
order_value_usd decimal Order value in USD.
carrier varchar(20) Shipping carrier. FedEx 12517 UPS 12503 USPS 12434 DHL 11353 Canada Post 6832 Royal Mail 3358 Hermes 3355 (blank) 1430
status varchar(12) Where the order is now (see the values). Delivered 50044 Returned 12140 Cancelled 1321 Shipped 168 Processing 109
fact_returns one row per return 6 columns

Items returned against a sale line.

ColumnTypeKeyMeaningValues seen
return_id int PK Unique return ID.
sale_line_id int FK to fact_sales Sale line returned (fact_sales).
return_date_key int FK to dim_date Date of the return (dim_date).
quantity smallint Units returned. 1 22570 2 3115 3 952 4 588
refund_usd decimal Refund in USD.
reason varchar(30) Reason for the return (see the values). Changed mind 7997 Wrong size 7661 Defective 4980 Not as described 3926 Arrived late 2661
fact_sales one row per order line 18 columns

Sales in a star-schema warehouse: one row per order line, in local currency and in USD.

ColumnTypeKeyMeaningValues seen
sale_line_id int PK Unique line ID.
order_id varchar(14) Order the line belongs to.
line_number tinyint Line number within the order. 1 164204 2 119604 3 61681 4 23731 5 7309 6 1838 7 370 8 63
order_date_key int FK to dim_date Order date (dim_date).
ship_date_key int FK to dim_date Ship date (dim_date).
customer_key int FK to dim_customer Customer (dim_customer).
product_key int FK to dim_product Product (dim_product).
store_key int FK to dim_store Store (dim_store).
channel_key int FK to dim_channel Channel (dim_channel). 1 234538 2 101523 4 25638 3 17101
promotion_key int FK to dim_promotion Promotion applied (dim_promotion).
currency_key int FK to dim_currency Currency (dim_currency). 1 226560 3 60900 4 49583 2 41757
quantity smallint Units sold. 1 284094 2 64216 3 19116 4 11374
unit_price_local decimal Price per unit in local currency.
discount_local decimal Discount in local currency.
net_amount_local decimal Net sales in local currency.
fx_rate_to_usd decimal Exchange rate to USD on the order date.
net_amount_usd decimal Net sales in USD.
cost_usd decimal Cost in USD.
14 more tables (no notes yet)

← All databases