Star Schema for Finance: Fact, Dimensions & the Calendar Table | Excel & Power BI S2 Ep3

📂 Download the AtlasParts dataset and the Episode 9 Power BI starter file: https://github.com/GoCelesteAI/excel-powerbi-for-finance Episode 3 of Season 2 of *Excel & Power BI for Finance* — star schema for finance. Episode 8 ended with eight Tables sitting in the Vertipaq model with no relationships drawn between them. The Model view canvas was a row of disconnected cards. We had loaded the data; we hadn't yet *modelled* it. This episode is the modelling. Star schema is the single most important concept in Power BI. Get it right and every measure you write feels inevitable. Get it wrong and every measure has a footnote ("works only when filtered by..."). We walk through why your GL extract alone isn't enough (filter granularity, additivity, compression), the difference between fact and dimension Tables, building a Calendar Table from a few lines of DAX, and drawing the four relationships that turn AtlasParts into a real data model — ready for DAX in Episode 4. What You'll Learn: - Why a single wide GL Table is the wrong model for Power BI — three failure modes: filter granularity (filters can't reach what they need to filter), aggregation correctness (DAX can't tell facts from dims), and compression (wide tables explode column cardinality and ruin Vertipaq). - The two-Table-types rule of star schema — Fact (things that happened: gl_journal, with measure columns + foreign keys) and Dimension (things that describe: customers, products, accounts, calendar — short Tables with unique keys + descriptive attributes). - Build a Calendar Table from scratch with DAX — `CALENDAR(DATE(2020,1,1), DATE(2026,12,31))` plus `ADDCOLUMNS` for Year, Quarter, Month, Fiscal Year, Day of Week, IsWeekend. Mark as Date Table or time intelligence breaks. - Draw the four relationships in Model view — gl_journal[customer_id] → customers, gl_journal[product_id] → products, gl_journal[account_code] → chart_of_accounts, gl_journal[posting_date] → Calendar[Date]. Cardinality one-to-many, filter direction single, every time. - Star vs snowflake — why denormalised is always better in Power BI even though snowflakes look prettier in textbooks. The Power BI rule: collapse the country/region/continent chain into one dim Table; one short hop from fact to attribute. - Pitfalls — many-to-many warnings (find the duplicate keys), both-direction filters (default to single, only flip when a measure visibly needs it), the difference between unconnected Tables and unused dims (it's fine to leave Tables out of the star). Timestamps: 0:00 - Intro — Star schema for finance 0:24 - What's in this episode 0:52 - Why your GL extract isn't enough — three reasons 2:04 - Fact + dimension — two kinds of Tables 3:10 - The AtlasParts star — one fact, four dims 3:58 - Build the Calendar Table — DAX, then mark as Date Table 5:05 - Draw the relationships — model view, 4 lines 6:10 - Snowflake vs star — denormalise 7:00 - Recap — three takeaways 7:35 - Up next — DAX Fundamentals Key Takeaways: 1. A star schema has exactly two kinds of Tables. Fact Tables hold *what happened* (transactions, postings, events) — measure columns plus foreign keys to dimensions. Dimension Tables hold *what describes* (customers, products, accounts, dates) — primary key plus descriptive attributes. Filter or group by it? Dim. Sum or average it? Fact (as a measure column). 2. Build a Calendar Table by hand. None of the AtlasParts CSVs include one, but DAX time intelligence (TOTALYTD, SAMEPERIODLASTYEAR, fiscal-quarter measures) needs every date — including the slow days with no transactions. Three lines of DAX get you a usable Calendar; Mark as Date Table is the step everyone forgets. 3. Relationships are one-to-many, single-direction. Drag from foreign key (fact-side) to primary key (dim-side). Filters flow from dimension to fact, always. Many-to-many is a smell — usually a duplicate key in your dim. Both-direction filters create DAX ambiguity; default to single. 4. Denormalise the dimensions. Snowflakes look pretty in textbooks but are slow in DAX, ambiguity-prone, and harder to reason about. Collapse customer.country.region into one customers Table with country and region as columns. We did exactly this with the Episode 8 Merge step — one short hop from fact to attribute is the goal. 5. Episode 8 did the loading. Episode 9 — this one — does the modelling. Episode 10 finally writes DAX measures against the model. Each episode needs the previous one's work to be done correctly. Star schema is foundational; rushing it makes every subsequent measure fight you. #PowerBI #StarSchema #DataModeling #FinanceAnalytics #DAX #PowerBIDesktop #ExcelToPowerBI #DataModel #FinancialReporting #BusinessIntelligence --- Generated by GoCelesteAI · part of the Excel & Power BI for Finance series