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
- SQL Server (best for Excel, Power Pivot and Power BI): server
learnsql.cpetoday.com,1433(a comma before the port), SQL Server authentication (login supplied with the CPE Today course), Import mode. Accept the certificate prompt (public teaching server, self-signed). In Excel: Data > Get Data > From Database > From SQL Server Database. In Power BI Desktop: Get data > SQL Server. In M:Sql.Database("learnsql.cpetoday.com,1433", "contoso")thenSource{[Schema="contoso", Item="FactSales"]}[Data]. - The schema name equals the database name on SQL Server (contoso.FactSales, northwind.Orders, classicmodels.orders), not dbo. (Exceptions: sage100, xtreme and the dbo tables use dbo, qbd uses QuickBooks, qbo uses QuickBooksOnline, and adventureworks puts the original schema in the table name, e.g. adventureworks.Production_Product.)
- MariaDB/MySQL: server
learnsql.cpetoday.com, port 3306, databases acme, northwind, world and the mirrored sample databases. Power BI Desktop uses the MySQL database connector (needs the MySQL driver); Excel uses From MySQL Database (needs the connector installed). SQL Server is the master copy; use it when you have the choice. - Read-only: you can only query. The Web API (
https://learnsql.cpetoday.com/api/mssql/<db>/<table>?limit=100) needs no login and can be loaded with Power Query From Web. - Always choose Import, not DirectQuery, for teaching: it is fast and works offline once loaded. Filter and select columns in Power Query before loading.
- Repeated failed logins block an address for a while; copy the password from the course materials.
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
adventureworks(SQL Server) is the OLTP version of AdventureWorks (82 tables named likeProduction_Product,Person_Person,HumanResources_Employee,Sales_SalesOrderHeader,Sales_SalesOrderDetail): a normalized transactional model, so build a star schema from SalesOrderHeader/Detail, Product, ProductSubcategory, ProductCategory, Customer and SalesTerritory before using DAX.xtreme(dbo): Customer, Orders,Orders Detail, Product,Product Type, Supplier, Purchases, Employee,Employee Addresses, Credit, Financials,Xtreme Info. A compact order-entry model that is good for a first star schema.qbd(schemaQuickBooks, 84 tables) andqbo(schemaQuickBooksOnline, 47 tables): QuickBooks Desktop-style and QuickBooks Online-style accounting data (Customers, Invoices and InvoiceLineItems, Bills, Payments, Accounts, JournalEntries ...). Excellent for AR/AP aging, P&L by class and customer profitability exercises. Use the schema name in the M navigation:Source{[Schema="QuickBooks", Item="Invoices"]}[Data].sage100(dbo, hundreds of tables with Sage 100 names such as AR_Customer, AP_InvoiceHeader): a real accounting-system shape; use only the handful of tables you need.acmeexists on MariaDB only for now; connect with the MySQL connector.
Model checklist for these databases
- 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.