Lesson 1Store an location objects: Store_ID, Store_Name, Region, Country, ChannelDefine store an location dimension objects fi retail analysis. Learn how fi model store identifiers, names, regions, countries, an channels, an how dese attributes support geographic an 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, an aliases: techniques fi conformed dimensions an multiple joins to di same tableModel keys, joins, an aliases fi support conformed dimensions. Learn how fi join shared dimensions to multiple facts, avoid loops, an use table aliases fi represent different roles or paths inna 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 weh users rely pon. Learn how fi model revenue, units, margin, an discounts as additive measures, define aggregation behavior, an document business rules behind each metric inna di universe.
Business definition of Sales_RevenueUnits_Sold measure and aggregation rulesGross_Margin calculation and validationDiscount_Amount sourcing and logicMeasure formatting and number scalingLesson 4Derived an calculated objects: variables fi Margin_Pct, Stock_Turnover, Days_of_Inventory, Slow_Mover_FlagCreate derived an calculated objects weh encapsulate business logic. Learn how fi build margin percentage, stock turnover, days of inventory, an slow mover flags while keeping formulas maintainable an 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 an snapshot measures weh enrich analysis. Learn how fi expose selling price, cost of goods sold, stock level, an stock value, an understand when fi use detail versus aggregated objects inna 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, an aliases fi prevent fan traps an chasm trapsHandle multiple fact tables safely within one universe. Learn join strategies, contexts, an aliases fi avoid fan an chasm traps, ensuring combined sales an 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 fi analysis. Learn how fi expose IDs, SKUs, categories, subcategories, an brands, manage slowly changing attributes, an ensure consistent product rollups cross 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, an semi-additive measures explanationUnderstand how fi prevent double counting inna aggregated reports. Learn fi define a clear fact grain, use aggregate-aware objects an contexts, an correctly handle semi-additive measures such as stock an 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 fi flexible period analysis. Learn how fi expose calendar dates, fiscal years, fiscal periods, weeks, an flags such as month-to-date, enabling consistent time-based filters an 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 an modeling: defining transaction-level sales fact vs stock snapshot fact, grain implicationsDefine an document fact grain fi each table. Learn di difference between transaction-level sales facts an stock snapshot facts, an how grain choices affect aggregations, drill paths, an 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 di business subject areas weh drive di universe design. Learn how sales, stock, product, store, an calendar data map to fact an dimension tables, an how dis separation support 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 an lineage fields: Data_Source, Load_Timestamp, Record_Status fi troubleshooting an reconciliationIntroduce auditing an lineage fields into di universe. Learn how Data_Source, Load_Timestamp, an Record_Status support troubleshooting, reconciliation, an user trust, an how fi expose dem 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
