# Contoso Retail data dictionary

Sample database `contoso` on learnsql.cpetoday.com (SQL Server login: CPE Today course). Each table lists its columns with the type, keys, what the column means, and, for codes and statuses, the values seen in the sample copy (with row counts when the dictionary was written).
11 of 16 tables are described. Undocumented tables are listed at the end.

## DimChannel

Sales channels, such as store or online.

Grain: one row per channel

| 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 |  |  |  |
| `LoadDate` | datetime2 |  |  |  |
| `UpdateDate` | datetime2 |  |  |  |

## DimCurrency

Currencies.

Grain: one row per currency

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `CurrencyKey` | int | PK | Unique currency key. |  |
| `CurrencyCode` | char(3) |  | ISO currency code. |  |
| `CurrencyName` | nvarchar(50) |  | Currency name. |  |

## DimCustomer

Customers: name, demographics and where they live.

Grain: one row per customer

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `CustomerKey` | int | PK | Unique customer key. |  |
| `CustomerLabel` | nvarchar(20) |  |  |  |
| `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 |  |  |  |
| `MaritalStatus` | char(1) |  | Marital status. | M (18248), S (11752) |
| `YearlyIncome` | int |  | Yearly income. |  |
| `Occupation` | nvarchar(50) |  |  |  |
| `Education` | nvarchar(50) |  |  |  |
| `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 |  |  |  |
| `SignupDate` | date |  |  |  |
| `AcquisitionChannelKey` | int | FK to DimChannel |  |  |
| `GeographyKey` | int | FK to DimGeography | Location (DimGeography). |  |

## DimDate

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

Grain: one row per day

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Datekey` | date | PK | Date key (a date). |  |
| `FullDateLabel` | nvarchar(20) |  |  |  |
| `DateDescription` | nvarchar(20) |  |  |  |
| `CalendarYear` | int |  | Calendar year. | 2014 (365), 2015 (365), 2016 (365), 2017 (365), 2018 (365), 2019 (365), 2020 (365) |
| `CalendarYearLabel` | nvarchar(20) |  |  |  |
| `CalendarHalfYear` | int |  |  |  |
| `CalendarHalfYearLabel` | nvarchar(20) |  |  |  |
| `CalendarQuarter` | int |  | Quarter. |  |
| `CalendarQuarterLabel` | nvarchar(20) |  |  |  |
| `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 |  |  |  |
| `CalendarWeekLabel` | nvarchar(20) |  |  |  |
| `CalendarDayOfWeek` | int |  |  |  |
| `CalendarDayOfWeekLabel` | nvarchar(10) |  |  |  |
| `FiscalYear` | int |  | Fiscal year. | 2014 (365), 2015 (365), 2016 (365), 2017 (365), 2018 (365), 2019 (365), 2020 (365) |
| `FiscalYearLabel` | nvarchar(20) |  |  |  |
| `FiscalHalfYear` | int |  |  |  |
| `FiscalHalfYearLabel` | nvarchar(20) |  |  |  |
| `FiscalQuarter` | int |  |  |  |
| `FiscalQuarterLabel` | nvarchar(20) |  |  |  |
| `FiscalMonth` | int |  |  |  |
| `FiscalMonthLabel` | nvarchar(20) |  |  |  |
| `IsWorkDay` | nvarchar(20) |  | 1 on a working day. | WorkDay (1824), WeekEnd (731) |
| `IsHoliday` | int |  | 1 on a holiday. |  |
| `HolidayName` | nvarchar(20) |  |  |  |
| `EuropeSeason` | nvarchar(50) |  |  |  |
| `NorthAmericaSeason` | nvarchar(50) |  |  |  |
| `AsiaSeason` | nvarchar(50) |  |  |  |

## DimProduct

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

Grain: one row per product

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ProductKey` | int | PK | Unique product key. |  |
| `ProductLabel` | nvarchar(255) |  |  |  |
| `ProductName` | nvarchar |  | Product name. |  |
| `ProductDescription` | nvarchar |  |  |  |
| `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) |  |  |  |
| `ClassName` | nvarchar(20) |  |  |  |
| `StyleID` | nvarchar(10) |  |  |  |
| `StyleName` | nvarchar(20) |  |  |  |
| `ColorID` | nvarchar(10) |  |  |  |
| `ColorName` | nvarchar(20) |  | Color. |  |
| `Size` | nvarchar(50) |  |  |  |
| `SizeRange` | nvarchar(50) |  | Size range. |  |
| `SizeUnitMeasureID` | nvarchar(20) |  |  |  |
| `Weight` | float |  |  |  |
| `WeightUnitMeasureID` | nvarchar(20) |  |  |  |
| `UnitOfMeasureID` | nvarchar(10) |  |  |  |
| `UnitOfMeasureName` | nvarchar(40) |  |  |  |
| `StockTypeID` | nvarchar(10) |  |  |  |
| `StockTypeName` | nvarchar(40) |  |  |  |
| `UnitCost` | decimal |  | Cost per unit. |  |
| `UnitPrice` | decimal |  | List price per unit. |  |
| `AvailableForSaleDate` | datetime2 |  |  |  |
| `StopSaleDate` | datetime2 |  |  |  |
| `Status` | nvarchar(7) |  | Product status (see the values). | On (1395),  (295) |
| `ImageURL` | nvarchar(150) |  |  |  |
| `ProductURL` | nvarchar(150) |  |  |  |
| `ETLLoadID` | int |  |  |  |
| `LoadDate` | datetime2 |  |  |  |
| `UpdateDate` | datetime2 |  |  |  |

## DimPromotion

Promotions with discount, type and dates.

Grain: one row per promotion

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `PromotionKey` | int | PK | Unique promotion key. |  |
| `PromotionLabel` | nvarchar(100) |  |  |  |
| `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) |  |  |  |
| `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 |  |  |  |
| `MaxQuantity` | int |  |  |  |
| `ETLLoadID` | int |  |  |  |
| `LoadDate` | datetime2 |  |  |  |
| `UpdateDate` | datetime2 |  |  |  |

## DimStores

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

Grain: one row per store

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | int |  |  |  |
| `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) |  |  |  |
| `Status` | nvarchar(255) |  | Store status (see the values). | On (294), Off (12) |
| `OpenDate` | datetime2 |  | Opening date. |  |
| `EntityKey` | int | FK to DimEntity |  |  |
| `ZipCode` | nvarchar(255) |  |  |  |
| `ZipCodeExtension` | nvarchar(255) |  |  |  |
| `StorePhone` | nvarchar(255) |  |  |  |
| `StoreFax` | nvarchar(255) |  |  |  |
| `AddressLine1` | nvarchar(255) |  |  |  |
| `AddressLine2` | nvarchar(255) |  |  |  |
| `CloseReason` | nvarchar(255) |  |  |  |
| `EmployeeCount` | int |  | Employees. |  |
| `SellingAreaSize` | int |  | Selling area. |  |
| `LastRemodelDate` | datetime2 |  |  |  |
| `GeoLocation` | nvarchar(255) |  |  |  |
| `Geometry` | nvarchar(255) |  |  |  |
| `ETLLoadID` | int |  |  |  |
| `LoadDate` | datetime2 |  |  |  |
| `UpdateDate` | datetime2 |  |  |  |
| `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 |  |  |  |
| `Longitude` | decimal |  |  |  |
| `GeoQuality` | varchar(10) |  |  |  |
| `CurrencyKey` | int | FK to DimCurrency |  |  |

## FactBudget

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

Grain: one row per store, category and date key

| 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. |  |

## FactExchangeRate

Daily currency exchange rates.

Grain: one row per currency and date

| 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. |  |

## FactInventory

Inventory position by store and product subcategory on each date.

Grain: one row per store, subcategory and 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. |  |

## FactSales

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

Grain: one row per product sold in a transaction

| 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 |  |  |  |
| `DiscountAmount` | decimal |  | Discount given. |  |
| `TotalCost` | decimal |  | Total cost of the units sold. |  |
| `SalesAmount` | decimal |  | Sales value after discount. |  |
| `ETLLoadID` | int |  |  |  |
| `LoadDate` | datetime2 |  |  |  |
| `UpdateDate` | datetime2 |  |  |  |
| `CustomerKey` | int | FK to DimCustomer | Customer (DimCustomer). |  |

## Undocumented tables

`DimEntity`, `DimGeography`, `DimProductCategory`, `DimProductSubcategory`, `FactSalesFull`
