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.
DownloadStart here
Lessons and reference
Look things up
Start here
SQL
Power Query
Data modeling
Power BI and DAX
Finance playbooks
Run queries
Build and check
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.
Write each as a query in the SQL editor, or ask an AI assistant to walk you through 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.
Click a table to preview it in the Playground.
Three years of retail sales summarized by month, channel and category. A realistic starting point for PivotTables, slicers and margin analysis.
DownloadCalculation groups, field parameters, running totals, a user-defined function and quick measures.
DownloadCALCULATE with Boolean filters, REMOVEFILTERS, ALLSELECTED, KEEPFILTERS and FILTER.
DownloadSUM, COUNTROWS, DIVIDE, calculated columns, variables and the first measures.
DownloadSUMX, AVERAGEX, RANKX, TOPN and row context versus filter context.
DownloadRelationships, RELATED, RELATEDTABLE, inactive relationships and USERELATIONSHIP.
DownloadDrillthrough and tooltip pages, navigation buttons, conditional formatting, dynamic titles, mobile layout.
DownloadRow-level security roles (static and USERPRINCIPALNAME) plus an object-level security script.
DownloadDate table, YTD, prior year, year-over-year, rolling 12 months and fiscal periods.
DownloadEvery core visual type once, on the Contoso star schema.
DownloadThe modeling guide has the table-by-table recipe for this database, and the Power BI projects use Contoso.
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.
Sales channels, such as store or online.
| Column | Type | Key | Meaning | Values 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 |
Currencies.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
CurrencyKey |
int | PK | Unique currency key. | |
CurrencyCode |
char(3) | ISO currency code. | ||
CurrencyName |
nvarchar(50) | Currency name. |
Customers: name, demographics and where they live.
| Column | Type | Key | Meaning | Values 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). |
One row per calendar day with calendar and fiscal labels. Join FactSales.DateKey to Datekey.
| Column | Type | Key | Meaning | Values 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 |
Products with name, brand, category links, color, size and prices.
| Column | Type | Key | Meaning | Values 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 |
Promotions with discount, type and dates.
| Column | Type | Key | Meaning | Values 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 |
Stores: name, type, location, size and status.
| Column | Type | Key | Meaning | Values 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 |
Planned sales targets by store and product category, by month.
| Column | Type | Key | Meaning | Values 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. |
Daily currency exchange rates.
| Column | Type | Key | Meaning | Values 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. |
Inventory position by store and product subcategory on each date.
| Column | Type | Key | Meaning | Values 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. |
The sales fact table: one row per product sale with quantities, prices, costs and the links to date, store, product and customer.
| Column | Type | Key | Meaning | Values 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). |