BI systems lean on star schemas
- Microsoft’s Power BI guidance and Microsoft Fabric documentation both still recommend star schema models, with fact tables for measurements and dimension tables for descriptive context, as the default design for enterprise business intelligence. - Microsoft says DAX time-intelligence functions support period comparisons across days, months, quarters, and years, while training materials show year-to-date measures built on a marked date table for recurring finance reporting. - The pattern predates today’s AI tooling: Ralph Kimball’s dimensional modeling playbook still underpins Microsoft’s current warehouse and semantic-model guidance. (learn.microsoft.com)
Business intelligence systems still run on a simple shape: a central table of numbers, surrounded by tables that describe who, what, when, and where. That design is the star schema Microsoft still recommends for Power BI and Fabric. (learn.microsoft.com 1) (learn.microsoft.com 2) In that layout, the fact table holds measurements such as sales, units, or margin, and the dimension tables hold descriptive fields such as customer, product, date, or region. Microsoft says that structure improves usability and performance in Power BI semantic models. (learn.microsoft.com) The key modeling decision is grain, the exact level of detail stored in the fact table. Kimball’s dimensional modeling method treats grain as a first-order design choice because mixing monthly totals with line-item transactions in one fact table creates reporting errors. (kimballgroup.com) That is why teams still do extract, transform, and load work before dashboard design. Microsoft’s guidance says Power Query is used to transform and prepare source data, including harder warehouse tasks such as slowly changing dimensions. (learn.microsoft.com) Once the model is stable, business logic usually moves into measures, not raw columns. In Power BI, those measures are often written in Data Analysis Expressions, or DAX, so the same revenue, margin, or variance formula can be reused across every chart and table. (learn.microsoft.com 1) (learn.microsoft.com 2) Time intelligence is the finance workhorse in that layer. Microsoft says DAX includes functions for comparing periods across days, months, quarters, and years, and its training materials walk users through building a sales year-to-date measure after marking a proper date table. (learn.microsoft.com 1) (learn.microsoft.com 2) (microsoftlearning.github.io) That is the mechanics behind familiar executive dashboard rows: actuals, budget, variance, prior month, and year-to-date. Kimball’s catalog includes techniques for profit-and-loss fact tables, allocations, and year-to-date facts, which helps explain why finance reporting still maps cleanly onto dimensional models. (kimballgroup.com) Microsoft’s Fabric warehouse guidance makes the same point in newer language. It calls star schema the recommended design approach for relational warehouses, even as vendors add artificial-intelligence copilots and natural-language report builders. (learn.microsoft.com) The practical limit of AI here is that it can draft a report faster than a human, but it cannot fix a model that joins the wrong tables or mixes incompatible grains. A bad semantic model produces fast wrong answers. (learn.microsoft.com) (kimballgroup.com) So the old architecture remains the live one: clean data pipelines, a star-shaped model, and reusable DAX measures on top. The software stack has changed, but the reporting spine has not. (learn.microsoft.com) (learn.microsoft.com)
