Lesson 1Store and location objects: Store_ID, Store_Name, Region, Country, ChannelDefine store and location dimension objects for retail analysis. Learn how to model store identifiers, names, regions, countries, and channels, and how these attributes support geographic and channel performance reporting.
Store_ID as the store business keyStore_Name standards and cleansingRegion and country hierarchiesChannel classification and mappingLocation attributes for filteringLesson 2Keys, joins, and aliases: techniques for conformed dimensions and multiple joins to the same tableModel keys, joins, and aliases to support conformed dimensions. Learn how to join shared dimensions to multiple facts, avoid loops, and use table aliases to represent different roles or paths in the universe schema.
Natural versus surrogate keys in designInner and outer join choicesCreating and using table aliasesResolving join loops with contextsValidating join paths with sample queriesLesson 3Core universe objects: Sales_Revenue (sum), Units_Sold (sum), Gross_Margin (sum), Discount_Amount (sum)Design core sales fact measures that users rely on. Learn how to model revenue, units, margin, and discounts as additive measures, define aggregation behaviour, and document business rules behind each metric in the universe.
Business definition of Sales_RevenueUnits_Sold measure and aggregation rulesGross_Margin calculation and validationDiscount_Amount sourcing and logicMeasure formatting and number scalingLesson 4Derived and calculated objects: variables for Margin_Pct, Stock_Turnover, Days_of_Inventory, Slow_Mover_FlagCreate derived and calculated objects that encapsulate business logic. Learn how to build margin percentage, stock turnover, days of inventory, and slow mover flags while keeping formulas maintainable and well documented.
Margin_Pct formula and rounding rulesStock_Turnover calculation optionsDays_of_Inventory business definitionSlow_Mover_Flag thresholds and logicValidating derived metrics with samplesLesson 5Additional universe objects: Selling_Price (detail), Cost_of_Goods_Sold (detail), Stock_Level (snapshot), Stock_Value (calculated)Model additional detail and snapshot measures that enrich analysis. Learn how to expose selling price, cost of goods sold, stock level, and stock value, and understand when to use detail versus aggregated objects in reports.
Selling_Price as a detail objectCost_of_Goods_Sold sourcing and rulesStock_Level as a snapshot measureStock_Value as a calculated measureChoosing detail versus aggregated objectsLesson 6Handling multiple fact tables: join types, contexts, and aliases to prevent fan traps and chasm trapsHandle multiple fact tables safely within one universe. Learn join strategies, contexts, and aliases to avoid fan and chasm traps, ensuring that combined sales and stock reports return accurate, nonduplicated results.
Identifying fan and chasm trap patternsJoin strategies for multiple fact tablesUsing contexts to isolate fact combinationsAliases to separate incompatible joinsTesting combined sales and stock queriesLesson 7Dimension objects: Product_ID, SKU, Product_Category, Product_Subcategory, BrandDesign robust product dimension objects for analysis. Learn how to expose IDs, SKUs, categories, subcategories, and brands, manage slowly changing attributes, and ensure consistent product rollups across all fact tables.
Product_ID as primary business keySKU granularity and uniquenessProduct_Category hierarchy designProduct_Subcategory relationshipsBrand attributes and reporting useLesson 8Avoiding double counting: defining clear grain, use of aggregate-aware contexts, and semi-additive measures explanationUnderstand how to prevent double counting in aggregated reports. Learn to define a clear fact grain, use aggregate-aware objects and contexts, and correctly handle semi-additive measures such as stock and balances over time.
Defining a clear and consistent fact grainAggregate-aware measures and objectsDesigning and using universe contextsSemi-additive measures across timeTesting reports for hidden double countingLesson 9Time objects: Calendar_Date, Fiscal_Year, Fiscal_Period, Week, Month_To_Date_FlagDesign time dimension objects for flexible period analysis. Learn how to expose calendar dates, fiscal years, fiscal periods, weeks, and flags such as month-to-date, enabling consistent time-based filters and comparisons.
Calendar_Date as the base time keyFiscal_Year and Fiscal_Period mappingWeek and month attributes for groupingMonth_To_Date_Flag logic and usageHandling holidays and special periodsLesson 10Fact grain and modelling: defining transaction-level sales fact vs stock snapshot fact, grain implicationsDefine and document fact grain for each table. Learn the difference between transaction-level sales facts and stock snapshot facts, and how grain choices affect aggregations, drill paths, and report performance.
Transaction-level sales fact definitionStock snapshot fact grain and timingGrain alignment across related factsImpact of grain on aggregationsDocumenting grain for report designersLesson 11Identify subject areas: Sales fact, Stock fact, Product master, Store master, Calendar dimensionDefine the business subject areas that drive the universe design. Learn how sales, stock, product, store, and calendar data map to fact and dimension tables, and how this separation supports flexible, consistent reporting.
Sales fact subject area definitionStock fact subject area definitionProduct master as a conformed dimensionStore master and location coverageCalendar dimension business requirementsLesson 12Auditing and lineage fields: Data_Source, Load_Timestamp, Record_Status for troubleshooting and reconciliationIntroduce auditing and lineage fields into the universe. Learn how Data_Source, Load_Timestamp, and Record_Status support troubleshooting, reconciliation, and user trust, and how to expose them without confusing end users.
Purpose of Data_Source in reportingUsing Load_Timestamp for recency checksRecord_Status for active or deleted rowsDesigning audit objects for power usersReconciliation techniques using audit data
