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 strong relationships dat preserve referential integrity and enable flexible analytics.
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 dat standardizes dates across all facts. Learn which attributes to include, how to support fiscal calendars, and how holidays and periods drive time intelligence.
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 dem interact in di BI model.
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 behavior data aligns wid business facts. Learn key fields, grain choices, and how to capture channels, devices, and engagement metrics reliably.
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 strong Products dimension dat supports merchandising, pricing, and margin analysis. Learn key attributes, hierarchies, and links to suppliers and categories for flexible slicing.
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 dat 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 lifecycleModeling refunds and partial returnsLesson 7Inventory and Fulfillment tables: InventorySnapshot, Shipments, Returns with example fieldsDesign inventory and fulfillment tables dat support stock tracking, shipment performance, and return analysis. Learn key fields, table roles, and how dese 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 dat unifies spend, channels, and performance. Learn key fields, date ranges, and how to connect campaigns to sessions and orders for attribution.
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.
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 dat supports segmentation, retention, and lifetime value analysis. Learn essential attributes, derived metrics, and privacy considerations for analytics.
Customer identifiers and deduplicationDemographic and location attributesSignup, lifecycle, and activity datesCustomer segment and VIP flagsLifetime value and churn indicators
