Downloads

Model the LearnSQL databases

Connection details and a star-schema recipe for each sample database.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Connect to each LearnSQL sample database from Power BI or Excel.
  • Build a star-schema model from the sample tables.
  • Check relationships with the model checklist before you write measures.
  • Choose the right sample database to practice each kind of model.

Connection details

Contoso (retail sales; the best DAX playground)

Tables in contoso: FactSales, DimDate, DimProduct, DimProductSubcategory, DimProductCategory, DimStores (the store table), DimChannel, DimGeography, DimPromotion, DimEntity, DimCurrency and DimCustomer, plus FactExchangeRate, FactBudget and FactInventory (see "Contoso extensions" below). On SQL Server FactSales is a 104,940-row sample for speed (every row where SalesKey % 10 = 0) and FactSalesFull holds all 1,048,575 rows (January 2016 to December 2018); on MariaDB FactSales holds all 1,048,575 rows and there is no FactSalesFull. Every key is a declared primary key and every relationship a declared, trusted foreign key, so Power BI and the Excel Data Model detect the relationships on their own. All keys are whole numbers (int), and DateKey is a true date. Ready-made views save the joins: vw_Product (product with subcategory and category), vw_Store (store with geography, coordinates and a quality flag), vw_Sales (the fact with date, product, store and channel names), vw_SalesFull (the same over the full table, SQL Server only) and vw_SalesByMonthCategory (monthly totals by category). Columns that matter: - FactSales: SalesKey, DateKey (date), channelKey (lower-case c), StoreKey, ProductKey, PromotionKey, CurrencyKey, UnitCost, UnitPrice, SalesQuantity, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, SalesAmount. - DimDate: Datekey (date), CalendarYear, CalendarHalfYear, CalendarQuarter, CalendarMonth (a yyyymm number such as 201603, so it sorts correctly), CalendarMonthLabel (the month name as text), CalendarWeek, CalendarDayOfWeek, FiscalYear, FiscalQuarter, FiscalMonth, IsWorkDay, IsHoliday, HolidayName, and label columns. It covers 2014-2020, so filter it to the data's 2016-2018 (or build your own Calendar in M). - DimStores: StoreKey and GeographyKey (both int), StoreName, StoreType, Status, OpenDate, CloseDate, EmployeeCount, SellingAreaSize, plus Latitude, Longitude and GeoQuality (the coordinates come from the store's original location data; where they were swapped or wrong they were repaired and flagged in GeoQuality). DimGeography: GeographyKey (int), ContinentName, RegionCountryName, StateProvinceName, CityName, Latitude, Longitude. DimChannel: ChannelKey, ChannelName (Store, Online, Catalog, Reseller). Model recipe (star schema): - Sales (from FactSales or FactSalesFull): keep SalesKey, Date, channelKey (rename ChannelKey), StoreKey, ProductKey, UnitCost, UnitPrice, SalesQuantity, ReturnQuantity, ReturnAmount, DiscountAmount, TotalCost, SalesAmount. Hide the key columns. - Calendar (from DimDate or built in M): Date, Year, Quarter, Month Number, Month (sorted by Month Number), Year-Month (sorted by a YearMonthKey integer). Mark as date table. - Product -> Subcategory -> Category (a snowflake): either flatten with merges or relate the chain (Sales[ProductKey] to Product, Product[ProductSubcategoryKey] to Subcategory, Subcategory[ProductCategoryKey] to Category; the category table's key is named ProductCategoryID, rename it to ProductCategoryKey if you like; the relationship is declared in the database). - Store (DimStores) related to Geography through GeographyKey; Channel (DimChannel). Control totals on the FULL fact table (FactSalesFull): Total Sales $3,826,462,615.76, Total Cost $1,629,711,680.67, Gross Margin % 57.4%; by year 2016 $1,440,347,077.25, 2017 $1,210,810,889.77, 2018 $1,175,304,648.74. The 10% sample gives roughly a tenth of each: amounts differ, ratios (margin %, share by channel) stay close. Always check which fact table a project loaded before comparing to answers, and recompute control totals with SQL on the table you loaded. Good first measures: Total Sales, Total Cost, Gross Margin, Gross Margin %, Units Sold, Average Unit Price, Online share of sales, Sales by channel, Returns %, Sales PY, YoY %.

Contoso extensions: customers, budget, currencies and inventory

Four additions make Contoso a fuller model. None of them changes a sales amount, so the control totals above still hold. - DimCustomer (30,000 customers): CustomerKey, FirstName, LastName, Gender, BirthDate, MaritalStatus, YearlyIncome, Occupation, Education, NumberChildrenAtHome, CustomerSegment (Value, Core, Premium), EmailPromotion, SignupDate, AcquisitionChannelKey (to DimChannel) and GeographyKey (the customer's home city, to DimGeography). FactSales (and FactSalesFull) now carry CustomerKey. Every Online and Catalog sale has a customer, about a quarter of Store sales do (a loyalty card), and Reseller sales have none, so the key is blank on purpose for the rest. No customer buys before their SignupDate. Use it for distinct customer counts, new versus returning customers, cohorts, RFM and age bands (build the age band yourself from BirthDate). - FactBudget: one row per month (the first of the month), store and product category, with BudgetSalesQuantity, BudgetSalesAmount and BudgetTotalCost. Budgets run from 90% to 110% of what was sold, so some cells beat plan and some miss. It is at a coarser grain than the sales, so relate both to the shared Date, Store and Category dimensions instead of to each other (create a month-start column on your calendar to relate on). On SQL Server compare it with FactSalesFull; the 10% FactSales sample is about a tenth of the budget. - Currencies: DimCurrency now lists 18 currencies. Sales amounts stay in US dollars (CurrencyKey 1); each store has a local CurrencyKey (euro-zone stores in EUR, and so on; stores in countries without a modelled currency use USD). FactExchangeRate holds a daily AverageRate and EndOfDayRate for 2016-2018, expressed as local currency per one US dollar, so local amount = US dollars x rate. Use it to practice converting in DAX, switching the reporting currency and a two-column relationship (date and currency). There are no rows for 29 February, because DimDate has none. - FactInventory: a month-end snapshot of OnHandQuantity, OnOrderQuantity, SafetyStockQuantity and InventoryValue for each physical store and product subcategory. A balance must never be added across months; use LASTNONBLANK, CLOSINGBALANCEMONTH or an average, and divide by sales for stock turn and days of supply. - Views: vw_Customer, vw_CustomerSummary (first and last purchase, sales per customer), vw_SalesLocalCurrency, vw_BudgetVsActual (budget next to actual with the variance) and vw_InventoryMonthly. vw_Sales and vw_SalesFull now include CustomerKey. Measures to try: Customers = DISTINCTCOUNT(Sales[CustomerKey]); New Customers (customers whose first purchase is in the period); Sales per Customer; Budget Variance % = DIVIDE([Sales] - [Budget], [Budget]); Sales (local currency) = SUMX(Sales, Sales[SalesAmount] * LOOKUPVALUE(FactExchangeRate[AverageRate], FactExchangeRate[DateKey], Sales[DateKey], FactExchangeRate[CurrencyKey], RELATED(Stores[CurrencyKey]))); Stock on Hand = CALCULATE(SUM(FactInventory[OnHandQuantity]), LASTDATE(Calendar[Date])).

classicmodels (orders for a model-car wholesaler, 2003-2005)

Tables: customers, employees, offices, orderdetails, orders, payments, productlines, products. Model: orders (header) and orderdetails (lines) with a LineTotal = quantityOrdered * priceEach; products -> productlines; customers; payments as a second fact (customerNumber, paymentDate, amount). Measures: Revenue = SUMX(orderdetails, quantityOrdered * priceEach), Orders = DISTINCTCOUNT(orders[orderNumber]), Payments, Credit utilisation. Two facts (orders and payments) both relate to customers and a shared Calendar; payments lag orders, a good time-intelligence and "AR" exercise. A good Excel Power Pivot first project: small and clean. Orders span 2003-2005, so build the Calendar for those dates.

northwind (small trading company, text keys)

Tables: Customers, Employees, Orders, Order Details, Products, Shippers, Suppliers, Invoices, Purchase Orders and Details, Inventory Transactions. Relationships are on text (Orders.Customer holds the customer's company name; Order Details.Product holds the product name), and there are only about 48 orders, so it is thin for DAX; good for Power Query, merges and SUMIFS in Excel. Primary and foreign keys have been declared on Orders, Order Details, Customers, Shippers and Products (names must match exactly).

dvdrental (DVD rentals)

Tables: payment, rental, customer, film, category, film_category, inventory, store, staff, address, city, country, actor, film_actor. A large-ish payments fact (about 16,000 rows) with rental dates; build Payments -> Rental -> Inventory -> Film -> Category. Measures: Revenue, Rentals, Average rental duration (DATEDIFF), Late returns, Revenue per film, Top categories, Customers with repeat rentals. Dates are 2005-2006: the Calendar must cover them.

world (countries and cities)

Tables: country, city, countrylanguage. No time dimension: use it to learn relationships (country Code to city CountryCode), hierarchies (Continent > Region > Country), Top N, and map visuals. Measures: Population = SUM(country[Population]), GDP per capita proxy = DIVIDE(SUM(country[GNP]) * 1000000, SUM(country[Population])), Life expectancy = AVERAGE.

adventureworks, xtreme, qbd, qbo, sage100, acme

Model checklist for these databases

  1. Pick the fact table(s) and the grain (one row per order line, per payment). 2. Choose dimensions. 3. Cast keys to Whole number. 4. Build or reuse a Calendar covering the data's years, sorted month names, marked as date table. 5. Relationships one-to-many, single direction. 6. Base measures, then everything else. 7. Check totals against a SQL query on the same database (SELECT SUM(...)) before trusting the report.

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.