Data Modelling in Power BI: What It Is and Why It Matters

If your Power BI solutions are slow, confusing or overly complicated, it’s very possible your data model has a part in it.

A well-designed Power BI data model is a must. This is so much the case, that I like to say:

Following a star schema data model approach helps you avoid challenges that haven’t even come up yet.

Don’t know what a star schema is? Well don’t worry, as we will go through this and a lot more in this blog.

What is a Data Model?

A data model is a structured representation of how data is stored, organised and related within a system.

Simply put for Power BI, it’s the tables, columns and the relationships between those tables.

What is Data Modelling?

Data modelling is the process of creating a data model. It involves understanding data requirements and defining elements such as entities (tables), attributes (columns), relationships (how tables relate to each other) and constraints (such as unique identifiers or rules).

That said, I want to emphasise that data modelling is not just a technical task that helps in structuring and storing the data, but a way to understand the business. Through this process, you ask the right questions to uncover how the organisation operates, what its core processes are, who is involved and how different elements connect. For me, this is one of those exercises that I find as a way to truly discover the organisation I’m working with.

So, all data professionals, not just those working with Power BI, should be comfortable and understand the fundamentals of data modelling.

In Power BI Desktop, data modelling lets you create data models that form the backbone of your Power BI report.

Types of Data Models

When working with Power BI, how you structure your data plays a huge role in performance, usability and how the solution scales. So, let’s walk through some of the most common types of data models.

Relational Model

This type of model is traditionally used in transactional systems (OLTP), so those built to handle the day to day stuff like inserting, updating or deleting individual records. Think of things like a CRM or an ERP system.

Why does the relational model work here? Because these transactional systems need data to be stored with high accuracy, minimal duplication and strict consistency. Think of a CRM system, you need every customer, contact, interaction and communication history to be properly linked and validated. That’s where a relational model shines.

You might also hear this referred to as an ERD model, short for Entity Relationship Diagram (this sure brings back memories for me). But, to be clear, the relational model is the actual structure (tables, keys, etc.) whilst the ERD is the visual design used to model that structure. People often use the terms interchangeably, and that’s okay, just remember that one is more suited to describe the design and the other the implementation.

But here's the thing, this is not the type of model we want to build in Power BI. Why? Because relational models are highly normalised, meaning data is broken into many smaller related tables to reduce duplication. That’s perfect for transactional systems, but not so much for reporting and analysis purposes.

You’ve probably come across those messy Power BI models with 30+ tables, often referred to as spaghetti models. You know the ones I am talking about, full of tangled relationships crossing over each other. That’s usually what happens when we just try to "make it work" without spending enough time thinking through the model. These types of models often look similar to a relational model. Lots of small, tightly connected tables but there's an important difference. Relational models are intentionally designed that way for transactional systems. Many of the Power BI spaghetti models I have come across were set up that way because no one stopped to structure the data appropriately and just kept adding to it. Point is, big difference between the two.

Dimensional Modelling

Now we’re talking Power BI’s language. Dimensional modelling is the go-to approach for analytical engines like Power BI and data warehouses. This is what we should aim for when building our reporting solutions and dashboards - to ensure we have a good data model. It’s built around two core components:

• Fact Tables: Store the numerical columns that can be measured. Think sales, donations or stock.
• Dimension Tables: Store the descriptive columns that provide context. Think product, customer or date.

Don’t worry, the above was just a quick intro and we’ll dive deeper into fact and dimension tables further below. But before we go further, there’s one thing I really want to call out.

Power BI can work with any data structure.

Think flat files, relational models, dimensional models, even messy Excel exports. You can build reports on top of all of them. Now, for those experienced in Power BI, you may be thinking… "but Laz, you always talk about best practices, WHY are you saying this?".

It’s because over the years of training people in Power BI, I’ve heard a recurring concern: "I thought you needed something called a star schema (dimensional model) to use Power BI."

Not true! Power BI was originally designed to empower business users through self-service. Most people using it day to day won’t know what normalisation is, the difference between Inmon and Kimball and of course a star schema. And honestly, they shouldn’t need to. But that is why I’m calling this out. If you’re new to Power BI, don’t feel like dimensional modelling is some mandatory technique you need to master and apply before you can start creating any reports.

Now, with the above out of the way… just because you can structure your data in any way and get the solution to work… should you? Absolutely not! Spend time on your data model and if you are creating a solution that goes beyond your own personal use, I highly recommend the dimensional model (more so, Star Schema which we will explore further below).

When we was discussing the relational model above, we said its highly normalised. So, what about the dimensional model? Well, the fact tables are normalised in the sense that they contain only what’s needed, such as the foreign keys and numeric columns. The dimension tables though are denormalised as they are nice and wide, combining all columns into logical tables. For example, everything relating to product will be in a single product table, not split into three tables: Product Subcategory, Product Category and Product.

At this point, a common question may be "Were dimensional models created for Power BI?" Absolutely not! This data structure has been around since the 1990s and was created by someone called Ralph Kimball. As a data professional, I would highly recommend you check out the book The Data Warehouse toolkit. Also, if you are really curious, have a look at Inmon vs Kimball and understand the differences in approaches.

So, unlike the relational models which are great for transactions like inserts and updates, the dimensional model is optimised for querying and analysis.

Flat File Model

Alright - I know I’ll get some heat for this.

Technically speaking, a flat file isn’t a type of data model… I get it! However, in the real world of Power BI? It’s everywhere. If anything, think of it more as a data structure approach rather than a formal modelling technique.

But what is it and why call it out? Well, this is when you import data into one table, often coming from a single Excel or CSV export. Think of all the columns being ingested all together as a single table. It might contain columns relating to product (product name, product colour, product category, product size, etc.), region (region name, manager name, location, etc.), customer, sales amount, sales quantity and so on. Everything in one BIG single table.

This approach is simple and quick, and hey, sometimes that’s fine! But as you’ll see in the rest of this blog, flat files don’t scale well. They’re harder to maintain, more prone to errors and can limit your ability to slice, filter and analyse your data effectively.

Power BI will let you build reports on top of flat files… but if you want performance, simplicity and flexibility, go with the star schema.

The Different Stages of Data Modelling: Conceptual, Logical and Physical

Now that we’ve explored the different types of data models, I want to highlight a few stages we often go through when designing one. Keep in mind, this applies to both relational and dimensional models.

Conceptual Model: This is your high-level view. I like to think of it as a bubble diagram. It shows the main entities (like Customer, Product, Sales) and how they relate to each other. No fields, no column names, no tech. Just a clear view of what data should exist in the solution and how it connects. It’s more of a business model, something that's easier for stakeholders to understand.

Logical Model: This one goes a bit deeper. Over the years (going back 10+ now), I’ve often heard this called the fully-attributed model. That’s because at this stage, we show the fields that will sit under each entity, but still without thinking about how it will be built. At this point though, it might start becoming harder for non-technical users to follow.

Physical Model: This is where it gets real. The physical model shows exactly how the solution will be built. It includes table names, column names, data types and all the other details needed to implement it in Power BI, SQL or whatever platform you’re using.

Just a quick side note (blog for another time)... A common question is "I get the different model types and the design stages, but what do I actually model? Do I model everything for the entire business?". Well, when it comes to dimensional models, I always suggest designing around a core business event or process. If this is something you want to go deeper into, I highly recommend the book Agile Data Warehouse Design by Lawrence Corr and Jim Stagnitto. Plus, I am writing a more detailed blog on approaches to data modelling.

Data Model in Power BI - What is a Star Schema?

We introduced star schemas earlier, as they fall under dimensional modelling. But why do we like them so much for Power BI? Well, compared to relational models, star schemas encourage us to work with fewer tables and fewer relationships.

That’s a big win for tools like Power BI, everything is closer together, simpler to work with and much faster to query. With fewer joins and less relationship complexity, data doesn’t have to jump through as many hoops to get where it needs to go. The data propagates more efficiently, hence faster query results.

Have a look below at an example star schema so you can visualise how it all comes together. We have one central fact table, surrounded by multiple dimension tables. Each dimension has a primary key that uniquely identifies each row. That same key appears in the fact table as a foreign key and this is how relationships are created.

Notice the arrows on the diagram (relationships in Power BI), they point from the dimension to the fact table. That’s because we promote 1 to Many relationships, with one on the dimension side, many on the fact side. Makes sense, right?

Take the Customer dimension… Each customer exists only once in the dimension table and has a unique ID. But that same customer can make multiple purchases, so their ID shows up many times in the fact table.

Components of a Star Schema: Facts, Dims and Relationships

Let’s now break down the three core components of a star schema - the model we should aim for in Power BI. These are: Fact Tables, Dimension Tables and the Relationships that connect them.

Fact Tables: The Numbers (Your WHAT)

Fact tables store the numerical columns you want to measure, count, sum, add, average, etc. So, they answer your WHAT type questions such as:

• What are my total sales?
• What are my donations?
• What is my stock availability?

In addition to numerical columns, they also contain something called foreign keys. These allow us to establish a relationship to the dimension table. For example, we may have the Customer ID Foreign Key in the fact table, whilst in the Customer Dimension table we have a Customer ID Primary Key. We create a join based on these two columns.

When it comes to size, fact tables typically have far more rows and tend to grow over time. And when you think about it, that makes perfect sense. Take a Sales fact table, every row represents a single transaction. Naturally, as you make more sales, the number of rows increases.

Now compare that to a Store dimension table, where each row represents one of your stores. If you have 100 stores, that table will stay fairly static as you’re not adding new stores daily, weekly or even monthly. So, it’s expected that fact tables (like Sales) grow rapidly, while dimension tables (like Store) remain relatively small.

It’s more logical for new rows to be constantly added to your Sales fact table (as transactions happen), than to your Store dimension table or any other dimension table.

Dimension Tables: The Context (Your BY)

Dimension tables store the descriptive columns which give your numbers meaning. Think of them as the characteristics of the measures, the way you break them down. So, they answer your BY type questions such as:

• What are my sales BY product category?
• What are my donations BY fundraiser?
• What is my stock availability BY warehouse?

If we take the first question above "What are my sales by product category?" the sales part comes from the fact table and the product category comes from the dimension table. In Power BI, we would drag the sales measure from the Sales fact table onto the canvas and drag the Product Category column from the Product dimension to break it down.

In addition to the descriptive columns that provide context, dimension tables also contain something called primary keys. These keys uniquely identify each row and are used to link the dimension to the fact table. This is essential, every row in a dimension table must have a unique primary key. Of course, there are different types of dimensions, but for now, we’re keeping things simple. Just remember this: every dimension table needs a primary key.

Primary keys allow us to create relationships to the fact table. For example, in the Customer dimension table, we have a Customer ID primary key that uniquely identifies each customer. In the Sales fact table, we have a corresponding Customer ID foreign key. We create a join between these two columns, allowing Power BI to connect the facts (sales) with the customer context.

Below is an image I put together to summarise everything we’ve covered so far.

Why use a Star Schema?

So, now that we know the components of a star schema and how it looks, the natural question is… why use it? We’ve touched on it already, but let me now walk you through four key reasons why I always recommend a star schema approach when working with Power BI.

Simpler

With a star schema, I like to think all measures and columns are grouped into logical containers, facts and dimensions. This makes everything easier to find, especially when building a self-service data model (semantic model in PBI). Just imagine giving users a flat file data model, with one massive table… they’ll be scrolling up and down or hunting with the search bar for a column they don’t even know the name of.

I also like making the model visible. When it's a star schema, it’s far easier for them to make sense of. Compare that to a tangled relational model or those messy spaghetti-style Power BI models and the difference is night and day.

Performance

Because a star schema has fewer tables and fewer relationships, it improves query performance. Everything’s closer together and more efficient to retrieve. There’s also less repeated data in your fact table, when compared to flat file models. That means better compression and faster processing. And yes, your refresh times will usually be quicker too.

Scalable

Star schemas are easier to scale. If the solution grows or you need to add more to it later, it’s much simpler when your model is built around clear facts and dimensions. Since we’ve already defined the grain of the fact and built dimensions with purpose, extending it becomes more easy and less risky. You’re not dumping more onto a giant flat file, you’re evolving a well-structured model.

Capability Enabled

Star schemas unlock a whole bunch of features in Power BI, things like drill down, drill through, better hierarchies and more. You can build cleaner, more flexible experiences for users. DAX also becomes way easier. Honestly, some of the most confusing and complex DAX formulas I’ve come across sit on top of badly designed data models. Your model dictates your DAX, clean model, clean logic.

Also, with a star schema you’ll almost always have a proper Date or Calendar dimension. Once marked as a date table in Power BI, you unlock all the time intelligence features like year-to-date, prior period and rolling average… things that just don’t work properly without it.

Final Thoughts

So there you have it, everything you need to know to get started with data modelling in Power BI, and more specifically, why I keep banging on about star schemas.

Your data model isn’t just some backend technical task. It’s what defines how easy it is to build reports, data visualizations, how fast things run, how to model different data sources and how clean your DAX. Get the model right, and everything else becomes easier.

If you're building for self-service, aiming for performance or just want to avoid those dreaded spaghetti models... then the star schema is your best friend.

And hey, if you ever want a second pair of eyes on your model, or you're not sure where to start, feel free to get in touch. Happy to help.