Lesson 1Relationships and keys: Orders→Customers (CustomerID), Orders→Products (OrderLine table), Orders→Calendar (OrderDate→DateID), Sessions→Campaigns (UTM)Understand how primary and foreign keys connect orders, customers, products, dates, and campaigns. Learn to design robust relationships that preserve referential integrity and enable flexible analytics in your systems.
Natural keys vs surrogate keysOrders to Customers via CustomerIDOrder lines linking Orders and ProductsOrders to Calendar via DateIDSessions to Campaigns using UTM keysLesson 2Calendar/Date dimension: fields (Date, Day, Week, Month, Quarter, Year, IsHoliday, FiscalPeriod)Build a reusable calendar dimension that standardises dates across all facts. Learn which attributes to include, how to support fiscal calendars, and how holidays and periods drive time intelligence effectively.
Defining the date dimension grainStandard calendar attributesFiscal year and fiscal period fieldsHoliday and special event flagsSupporting multiple time hierarchiesLesson 3Core fact and dimension distinctions: Orders fact, Sessions/Events fact, Inventory factDifferentiate fact and dimension tables and classify core facts for orders, sessions, and inventory. Understand how each fact supports specific KPIs and how they interact in the BI model for better insights.
Facts vs dimensions in BI modelsOrders fact and revenue analyticsSessions and events fact usageInventory fact and stock movementsConformed dimensions across factsLesson 4Sessions/Events table (for web analytics): fields (SessionID, VisitDate, UserID, Channel, LandingPage, Device, SessionsMetrics, Events)Model sessions and events for web analytics so behaviour data aligns with business facts. Learn key fields, grain choices, and how to capture channels, devices, and engagement metrics reliably and accurately.
Session vs event level grainCore session identifiers and datesChannel, source, and landing page fieldsDevice and platform attributesSession metrics and event countsLesson 5Products table: sample fields (ProductID, SKU, Name, Category, Brand, Price, Cost, Weight, SupplierID, CategoryHierarchy)Design a robust Products dimension that supports merchandising, pricing, and margin analysis. Learn key attributes, hierarchies, and links to suppliers and categories for flexible slicing in dashboards.
Choosing the product dimension grainCore product identifiers and SKUsCategory and hierarchy attributesPricing, cost, and margin fieldsSupplier and brand relationshipsLesson 6Orders table: sample fields (OrderID, OrderDate, CustomerID, ShippingAddressID, OrderStatus, PaymentMethod, Subtotal, Discounts, Shipping, Tax, Total, RefundAmount)Define a clean, analytics-ready Orders fact table. Explore essential fields, data types, and calculation rules so that revenue, discounts, tax, and refunds are consistent across dashboards and reports.
Choosing the grain of the Orders tableCore identifiers and date fieldsMonetary fields and calculation rulesHandling order status and lifecycleModelling refunds and partial returnsLesson 7Inventory and Fulfillment tables: InventorySnapshot, Shipments, Returns with example fieldsDesign inventory and fulfilment tables that support stock tracking, shipment performance, and return analysis. Learn key fields, table roles, and how these structures feed accurate operational and BI reporting.
InventorySnapshot table purpose and grainKey InventorySnapshot fields and typesShipments table structure and metricsReturns table structure and metricsLinking inventory to orders and productsLesson 8Marketing Campaigns table: fields (CampaignID, Channel, Source, Medium, StartDate, EndDate, Spend, Budget, CampaignName)Create a Marketing Campaigns dimension that unifies spend, channels, and performance. Learn key fields, date ranges, and how to connect campaigns to sessions and orders for attribution in analytics.
Campaign identifiers and naming rulesChannel, source, and medium fieldsBudget, spend, and pacing metricsCampaign start and end date handlingLinking campaigns to sessions and ordersLesson 9Design patterns: star schema, slowly changing dimensions (SCD Type 1/2), grain definition and its importanceApply proven dimensional design patterns to your schema. Learn star schemas, slowly changing dimensions, and how clear grain definitions prevent ambiguity and inconsistent BI calculations in your work.
Star schema vs snowflake tradeoffsDefining fact table grain preciselyGrain alignment across related factsSlowly changing dimensions Type 1Slowly changing dimensions Type 2Lesson 10Customers table: sample fields (CustomerID, Name, Email, SignupDate, Country, Region, CustomerSegment, LifetimeValue, AcquisitionChannel, IsVIP)Model a Customers dimension that supports segmentation, retention, and lifetime value analysis. Learn essential attributes, derived metrics, and privacy considerations for analytics in e-commerce.
Customer identifiers and deduplicationDemographic and location attributesSignup, lifecycle, and activity datesCustomer segment and VIP flagsLifetime value and churn indicators
