Downloads
Global retail chain

Contoso Retail

A retail sales star schema with stores, channels, products, promotions, 30,000 customers, budgets, 18 currencies, monthly inventory and three years of sales. The best database for DAX and time intelligence.

contosoSQL ServerMariaDB 16 tables
FactSalesDimCustomerDimProductDimStoresFactBudget

Good for

Star schemasDAX and time intelligencePower BI dashboards

Questions to try

  • What are sales and margin by channel and year?
  • Which categories have the highest margin %?
  • Which stores sell the most?

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 contoso, in Excel or Power BI (Import mode) or DBeaver. In queries use the schema-qualified name shown for each table (for example contoso.DimChannel).

Download the Excel connection (.odc)

How to use it: download the file and open it. Excel opens it as a connection to contoso 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 (16)

Click a table to preview it in the Playground.

Practice files for this database

Intermediate45 KB

Contoso: Monthly Sales by Channel and Category

Three years of retail sales summarized by month, channel and category. A realistic starting point for PivotTables, slicers and margin analysis.

Download
Advanced53 KB

Power BI: Advanced DAX Patterns

Calculation groups, field parameters, running totals, a user-defined function and quick measures.

Download
Intermediate42 KB

Power BI: CALCULATE and Filter Context

CALCULATE with Boolean filters, REMOVEFILTERS, ALLSELECTED, KEEPFILTERS and FILTER.

Download
Beginner48 KB

Power BI: DAX Basics

SUM, COUNTROWS, DIVIDE, calculated columns, variables and the first measures.

Download
Intermediate38 KB

Power BI: Iterators (SUMX, RANKX, TOPN)

SUMX, AVERAGEX, RANKX, TOPN and row context versus filter context.

Download
Beginner41 KB

Power BI: Relationships and RELATED

Relationships, RELATED, RELATEDTABLE, inactive relationships and USERELATIONSHIP.

Download
Intermediate76 KB

Power BI: Report Design

Drillthrough and tooltip pages, navigation buttons, conditional formatting, dynamic titles, mobile layout.

Download
Advanced35 KB

Power BI: Row-Level Security

Row-level security roles (static and USERPRINCIPALNAME) plus an object-level security script.

Download
Intermediate47 KB

Power BI: Time Intelligence

Date table, YTD, prior year, year-over-year, rolling 12 months and fiscal periods.

Download
Beginner62 KB

Power BI: Visual Gallery

Every core visual type once, on the Contoso star schema.

Download

Model it for DAX

The modeling guide has the table-by-table recipe for this database, and the Power BI projects use Contoso.

Data dictionary

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

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

DimChannel one row per channel 7 columns

Sales channels, such as store or online.

ColumnTypeKeyMeaningValues seen
ChannelKey int PK Unique channel key. 1 1 2 1 3 1 4 1
ChannelLabel nvarchar(100) Channel label. 1 1 2 1 3 1 4 1
ChannelName nvarchar(20) Channel name. Catalog 1 Online 1 Reseller 1 Store 1
ChannelDescription nvarchar(50) Description. Catalog 1 Online 1 Reseller 1 Store 1
ETLLoadID int Not described yet
LoadDate datetime2 Not described yet
UpdateDate datetime2 Not described yet
DimCurrency one row per currency 3 columns

Currencies.

ColumnTypeKeyMeaningValues seen
CurrencyKey int PK Unique currency key.
CurrencyCode char(3) ISO currency code.
CurrencyName nvarchar(50) Currency name.
DimCustomer one row per customer 17 columns

Customers: name, demographics and where they live.

ColumnTypeKeyMeaningValues seen
CustomerKey int PK Unique customer key.
CustomerLabel nvarchar(20) Not described yet
FirstName nvarchar(50) First name.
LastName nvarchar(50) Last name.
Gender char(1) Gender. F 15592 M 14408
EmailAddress nvarchar(100) Email address.
BirthDate date Not described yet
MaritalStatus char(1) Marital status. M 18248 S 11752
YearlyIncome int Yearly income.
Occupation nvarchar(50) Not described yet
Education nvarchar(50) Not described yet
NumberChildrenAtHome int Children living at home. 0 13826 1 5479 2 5452 3 2657 4 2586
CustomerSegment nvarchar(20) Customer segment. Core 13943 Value 12110 Premium 3947
EmailPromotion int Not described yet
SignupDate date Not described yet
AcquisitionChannelKey int FK to DimChannel Not described yet
GeographyKey int FK to DimGeography Location (DimGeography).
DimDate one row per day 29 columns

One row per calendar day with calendar and fiscal labels. Join FactSales.DateKey to Datekey.

ColumnTypeKeyMeaningValues seen
Datekey date PK Date key (a date).
FullDateLabel nvarchar(20) Not described yet
DateDescription nvarchar(20) Not described yet
CalendarYear int Calendar year. 2014 365 2015 365 2016 365 2017 365 2018 365 2019 365 2020 365
CalendarYearLabel nvarchar(20) Not described yet
CalendarHalfYear int Not described yet
CalendarHalfYearLabel nvarchar(20) Not described yet
CalendarQuarter int Quarter.
CalendarQuarterLabel nvarchar(20) Not described yet
CalendarMonth int Month number.
CalendarMonthLabel nvarchar(20) Month label, such as Jan. August 217 December 217 January 217 July 217 March 217 May 217 October 217 April 210 June 210 November 210 September 210 February 196
CalendarWeek int Not described yet
CalendarWeekLabel nvarchar(20) Not described yet
CalendarDayOfWeek int Not described yet
CalendarDayOfWeekLabel nvarchar(10) Not described yet
FiscalYear int Fiscal year. 2014 365 2015 365 2016 365 2017 365 2018 365 2019 365 2020 365
FiscalYearLabel nvarchar(20) Not described yet
FiscalHalfYear int Not described yet
FiscalHalfYearLabel nvarchar(20) Not described yet
FiscalQuarter int Not described yet
FiscalQuarterLabel nvarchar(20) Not described yet
FiscalMonth int Not described yet
FiscalMonthLabel nvarchar(20) Not described yet
IsWorkDay nvarchar(20) 1 on a working day. WorkDay 1824 WeekEnd 731
IsHoliday int 1 on a holiday.
HolidayName nvarchar(20) Not described yet
EuropeSeason nvarchar(50) Not described yet
NorthAmericaSeason nvarchar(50) Not described yet
AsiaSeason nvarchar(50) Not described yet
DimProduct one row per product 32 columns

Products with name, brand, category links, color, size and prices.

ColumnTypeKeyMeaningValues seen
ProductKey int PK Unique product key.
ProductLabel nvarchar(255) Not described yet
ProductName nvarchar Product name.
ProductDescription nvarchar Not described yet
ProductSubcategoryKey int FK to DimProductSubcategory Subcategory (DimProductSubcategory).
Manufacturer nvarchar(50) Manufacturer. Contoso, Ltd 560 Proseware, Inc. 177 Southridge Video 170 Fabrikam, Inc. 163 The Phone Company 152 Wide World Importers 133 A. Datum Corporation 132 Adventure Works 128 Litware, Inc. 48 Northwind Traders 27
BrandName nvarchar(50) Brand. Contoso 560 Proseware 177 Southridge Video 170 Fabrikam 163 The Phone Company 152 Wide World Importers 133 A. Datum 132 Adventure Works 128 Litware 48 Northwind Traders 27
ClassID nvarchar(10) Not described yet
ClassName nvarchar(20) Not described yet
StyleID nvarchar(10) Not described yet
StyleName nvarchar(20) Not described yet
ColorID nvarchar(10) Not described yet
ColorName nvarchar(20) Color.
Size nvarchar(50) Not described yet
SizeRange nvarchar(50) Size range.
SizeUnitMeasureID nvarchar(20) Not described yet
Weight float Not described yet
WeightUnitMeasureID nvarchar(20) Not described yet
UnitOfMeasureID nvarchar(10) Not described yet
UnitOfMeasureName nvarchar(40) Not described yet
StockTypeID nvarchar(10) Not described yet
StockTypeName nvarchar(40) Not described yet
UnitCost decimal Cost per unit.
UnitPrice decimal List price per unit.
AvailableForSaleDate datetime2 Not described yet
StopSaleDate datetime2 Not described yet
Status nvarchar(7) Product status (see the values). On 1395 295
ImageURL nvarchar(150) Not described yet
ProductURL nvarchar(150) Not described yet
ETLLoadID int Not described yet
LoadDate datetime2 Not described yet
UpdateDate datetime2 Not described yet
DimPromotion one row per promotion 14 columns

Promotions with discount, type and dates.

ColumnTypeKeyMeaningValues seen
PromotionKey int PK Unique promotion key.
PromotionLabel nvarchar(100) Not described yet
PromotionName nvarchar(100) Promotion name. Asian Holiday Promotion 3 Asian Spring Promotion 3 Asian Summer Promotion 3 European Back-to-Scholl Promotion 3 European Holiday Promotion 3 European Spring Promotion 3 North America Back-to-School Promotion 3 North America Holiday Promotion 3 North America Spring Promotion 3 No Discount 1
PromotionDescription nvarchar(255) Not described yet
DiscountPercent float Discount percent. 0.1 9 0.2 9 0.05 3 0.07 3 0.15 3 0.0 1
PromotionType nvarchar(50) Type of promotion (see the values). Seasonal Discount 27 No Discount 1
PromotionCategory nvarchar(50) Category. Store 27 No Discount 1
StartDate datetime2 First day.
EndDate datetime2 Last day.
MinQuantity int Not described yet
MaxQuantity int Not described yet
ETLLoadID int Not described yet
LoadDate datetime2 Not described yet
UpdateDate datetime2 Not described yet
DimStores one row per store 30 columns

Stores: name, type, location, size and status.

ColumnTypeKeyMeaningValues seen
ID int Not described yet
StoreKey int PK Unique store key.
GeographyKey int FK to DimGeography Location (DimGeography).
StoreManager int Store manager (ID).
StoreType nvarchar(255) Type of store (see the values). Store 299 Online 3 Reseller 3 Catalog 1
StoreName nvarchar(255) Store name.
StoreDescription nvarchar(255) Not described yet
Status nvarchar(255) Store status (see the values). On 294 Off 12
OpenDate datetime2 Opening date.
EntityKey int FK to DimEntity Not described yet
ZipCode nvarchar(255) Not described yet
ZipCodeExtension nvarchar(255) Not described yet
StorePhone nvarchar(255) Not described yet
StoreFax nvarchar(255) Not described yet
AddressLine1 nvarchar(255) Not described yet
AddressLine2 nvarchar(255) Not described yet
CloseReason nvarchar(255) Not described yet
EmployeeCount int Employees.
SellingAreaSize int Selling area.
LastRemodelDate datetime2 Not described yet
GeoLocation nvarchar(255) Not described yet
Geometry nvarchar(255) Not described yet
ETLLoadID int Not described yet
LoadDate datetime2 Not described yet
UpdateDate datetime2 Not described yet
CloseDate date Closing date, if closed. (blank) 294 2009-07-12 5 2009-05-12 2 2008-09-22 1 2008-12-12 1 2009-02-12 1 2009-04-12 1 2009-07-02 1
Latitude decimal Not described yet
Longitude decimal Not described yet
GeoQuality varchar(10) Not described yet
CurrencyKey int FK to DimCurrency Not described yet
FactBudget one row per store, category and date key 6 columns

Planned sales targets by store and product category, by month.

ColumnTypeKeyMeaningValues seen
DateKey date PK FK to DimDate Date (DimDate).
StoreKey int PK FK to DimStores Store (DimStores).
ProductCategoryKey int PK FK to DimProductCategory Product category (DimProductCategory). 3 10812 4 10615 5 10610 2 10581 6 9991 1 9975
BudgetSalesQuantity int Budgeted units.
BudgetSalesAmount decimal Budgeted sales.
BudgetTotalCost decimal Budgeted cost.
FactExchangeRate one row per currency and date 4 columns

Daily currency exchange rates.

ColumnTypeKeyMeaningValues seen
DateKey date PK FK to DimDate Date (DimDate).
CurrencyKey int PK FK to DimCurrency Currency (DimCurrency).
AverageRate decimal Average rate for the day.
EndOfDayRate decimal Closing rate for the day.
FactInventory one row per store, subcategory and date 7 columns

Inventory position by store and product subcategory on each date.

ColumnTypeKeyMeaningValues seen
DateKey date PK FK to DimDate Date (DimDate).
StoreKey int PK FK to DimStores Store (DimStores).
ProductSubcategoryKey int PK FK to DimProductSubcategory Subcategory (DimProductSubcategory).
OnHandQuantity int Units on hand.
OnOrderQuantity int Units on order.
SafetyStockQuantity int Minimum stock to keep.
InventoryValue decimal Value of the stock on hand.
FactSales one row per product sold in a transaction 20 columns

The sales fact table: one row per product sale with quantities, prices, costs and the links to date, store, product and customer.

ColumnTypeKeyMeaningValues seen
SalesKey int PK Unique sales row ID.
DateKey date FK to DimDate Sale date (DimDate.Datekey).
channelKey int FK to DimChannel Sales channel (DimChannel). 1 70057 2 16626 4 12242 3 6015
StoreKey int FK to DimStores Store (DimStores).
ProductKey int FK to DimProduct Product (DimProduct).
PromotionKey int FK to DimPromotion Promotion applied (DimPromotion).
CurrencyKey int FK to DimCurrency Currency of the amounts (DimCurrency).
UnitCost decimal Cost per unit.
UnitPrice decimal Price per unit.
SalesQuantity int Units sold.
ReturnQuantity int Units returned. 0 90479 1 14097 2 355 3 7 4 2
ReturnAmount decimal Value of the returns.
DiscountQuantity int Not described yet
DiscountAmount decimal Discount given.
TotalCost decimal Total cost of the units sold.
SalesAmount decimal Sales value after discount.
ETLLoadID int Not described yet
LoadDate datetime2 Not described yet
UpdateDate datetime2 Not described yet
CustomerKey int FK to DimCustomer Customer (DimCustomer).
5 more tables (no notes yet)

← All databases