Why data modelling matters more than visuals
Most Power BI beginners spend their time on visuals — choosing between bar charts and donut charts, adjusting colours, formatting labels. That is understandable. Visuals are visible. The data model is invisible until something goes wrong.
The data model is the foundation that everything else sits on. A poorly structured model produces slow reports, incorrect calculations, and DAX measures that are difficult to write and harder to maintain. A well-structured model does the opposite: reports load fast, measures are simple, and the whole thing scales cleanly as data volumes grow.
The star schema is the data modelling pattern that Power BI is specifically optimised for. Understanding it is the single highest-leverage skill you can develop as a Power BI practitioner.
What is a star schema
A star schema is a data model structure where one central fact table is surrounded by multiple dimension tables, each connected to the fact table by a relationship. When you draw it out, it looks like a star — the fact table in the centre, dimension tables radiating outward.
The name comes from its shape, but the logic is more important than the geometry. The schema works because it organises data the way analytical queries naturally work: you want to measure something (bookings, revenue, room nights) broken down by various dimensions (time, geography, agent, supplier, hotel). The fact table holds the measurements; the dimension tables hold the breakdown categories.
Fact tables vs dimension tables
A fact table contains the events or transactions you want to analyse. Each row represents one occurrence of something — one booking, one sale, one session. Fact tables tend to be tall (many rows) and relatively narrow (fewer columns). The columns are either numeric measures (booking amount, room nights, revenue) or foreign keys that link to dimension tables.
A dimension table contains the descriptive attributes of the entities involved in your facts. It answers the "who, what, when, where" questions. A Dates dimension tells you the day of week, month, quarter, and fiscal period for each date. An Agents dimension tells you the agent name, city, country, and branch. Dimension tables tend to be short (fewer rows) and wider (more descriptive columns).
A real-world example
In a travel platform business intelligence project, the data model is built around a central Bookings Data fact table. Each row in this table represents one booking transaction, with columns for booking amount in INR and USD, room nights, booking date, and foreign keys linking to each surrounding dimension.
The dimension tables surrounding it are: a DateTable (with date, day of week, month, quarter, month number), an Agents table (agent code, agent name, city, country, branch), a Countries table (country code, name, nationality, region), a Branch-Market table (branch, market), a Supplier Details table (supplier code, group, name, type), and a Currency table (currency code, date, INR per unit, USD per unit).
Each dimension connects to the Bookings fact table through a single relationship — typically a one-to-many relationship where one row in the dimension matches many rows in the fact table. This structure allows you to slice booking data by any combination of dimensions: bookings by agent city and month, revenue by supplier type and branch, room nights by destination country and quarter.
Setting up relationships in Power BI
In Power BI Desktop, relationships are set up in the Model view. You drag a field from one table and drop it onto the matching field in another table. Power BI creates the relationship and shows it as a line between the two tables.
Power BI's Model view — a generic star schema with one fact table connected to four dimension tables via one-to-many relationships.
For a star schema, the pattern is consistent: connect the foreign key in the fact table to the primary key in each dimension table. The DateTable connects via a date field. The Agents dimension connects via agent code. The Supplier dimension connects via supplier code. The Countries dimension connects via country code.
Power BI will automatically detect the cardinality (one-to-many vs many-to-many) based on whether the connecting field has unique values in one of the tables. You want to confirm that your dimension table's key field is unique — each agent code appears exactly once in the Agents dimension — so the relationship is correctly identified as one-to-many.
Cardinality and cross-filter direction
Cardinality describes the nature of the relationship between two tables. In a star schema, the standard cardinality is many-to-one from the fact table to each dimension — many booking rows can have the same agent code, but each agent code appears only once in the Agents dimension.
Cross-filter direction controls how filters flow between tables. In a standard star schema, filters flow from dimension tables to the fact table — when you select a branch in a slicer, the filter flows from the Branch-Market dimension into the Bookings fact table and returns only the matching rows. This is single direction filtering and it is the default, correct setting for most scenarios.
Bidirectional filtering (where filters flow in both directions) should be used cautiously. It can create ambiguous filter paths and produce incorrect results in certain DAX calculations. The general guidance: use single direction filtering as your default and only switch to bidirectional when you have a specific, understood reason to do so.
Common mistakes to avoid
The first and most common mistake is connecting dimension tables directly to each other instead of routing everything through the fact table. This creates a snowflake schema, which adds complexity and can cause filter ambiguity. Keep all dimension-to-dimension connections out of your model.
The second mistake is using date columns directly from the fact table for time intelligence calculations instead of building a dedicated DateTable dimension. Power BI's time intelligence functions (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD) require a continuous, contiguous date dimension to work correctly. Always build a dedicated DateTable that covers every date in your data range.
The third mistake is importing more columns than you need. Every column in your model consumes memory. Before loading data, remove columns in Power Query that your reports will never use. A leaner model loads faster and is easier to navigate.
How this improves your DAX
The most immediate benefit of a clean star schema is simpler DAX. When your model is correctly structured, most of the filtering logic that would otherwise need to be written explicitly in DAX is handled automatically by the relationships.
A measure like total booking amount by branch, in a well-structured star schema, is simply: Total Bookings = SUM(BookingsData[BookingAmount]). When a user selects a branch in a slicer, the filter flows from the Branch-Market dimension into the fact table automatically. You do not need to write filter conditions in the measure itself.
Compare this to a flat, denormalised table where all columns live in one wide table. In that structure, every measure needs explicit filter conditions to return the correct result for a given selection. The measures become longer, more error-prone, and harder to maintain. The star schema removes that complexity at the model level so your measures stay clean.
Summary
The star schema is not advanced Power BI — it is foundational Power BI. Every professional report built on Power BI should start with this structure: one fact table surrounded by dimension tables, each connected through a single one-to-many relationship, with a dedicated DateTable marked for time intelligence.
Get this right before you write a single DAX measure or choose a single visual. The model is the foundation. Everything else follows from it.
In the next post in this series, we will go one level deeper into the transformation layer — how Power Query and M-Query prepare your raw data before it reaches the model.
`