Introduction to Power BI Desktop’s Model View

Create table relationships in Power BI Desktop’s Model View

In this video, Matt will walk you through Power BI Desktop’s Model view. You will learn about the ideal star schema data structure for a Power BI data model, as well as differences between fact and dimension tables, and how to create relationships between tables.

Hi, this is Matt with Playfair+. In this video, we’re going to discuss the star schema and why it’s important to structure your data this way whenever you’re starting a new project with multiple tables. Let’s dive in. We’re going to go into the Model view. So this is the next piece that we really want to cover, because it’s really important to show the star schema.

This is the data structure that we want to do. So we want to make sure we head back over to our slides so we get a really good view of the star schema. And what you’ll see is, we have a fact table in the middle. This is what all of our tables are connected to. And then we have the dimension tables around the outside.

So the dimension tables, you would think about as the ways that we’re slicing and dicing the data. And the fact table is going to be like what we’re aggregating. So I’m looking at a zoomed-in view of our Sales table. You’ll see things like Order Quantity, Profit, these are what we’re thinking of when we say, hey, I want to visualize the data.

I want to visualize the Profit, whether it’s over a month, whether it’s, you know, a year-over-year, I want to say, what were my quarterly sales for the last year? So we’re thinking the Sales table is our fact table, so it’s going to be at the center of our relationship. It is always the many side of the one-to-many or many-to-one relationships.

So if you think about, you’ve got many tables going into the fact table in the center, so that’s why it is the many side. So that’s data that’s going to be summarized on screen. And then around the outside, we have our dimension tables. So, if we’re looking at our slide, you can see I’ve kind of moved the fact table, the Sales table, out of the way.

So these are our dimension tables. They’re always on the one side of the one-to-many or many-to-one relationships. They support the filtering. So if you say, you know, I want to, you know, filter my customers by city. You think about you’re using the Customer table. It’s the dimension table. It’s on the outside of our star schema relationship.

So when you’re joining multiple tables together, think about them in that star schema, where you’ve got filters on the outside and you’re kind of all connecting to the center table. So alright, we’ll go back into our Power BI because there’s one table. That is actually not connected already. So most of these, you see, they have an active relationship.

So like if I clicked on SalesTerritory, you can see there is an active relationship. It runs from the SalesTerritoryKey to the SalesTerritoryKey from Sales and SalesTerritory. But let’s look at this Date table. Okay, so we have the Date table. It’s not connected to our Sales table, so we wouldn’t be able to do any time series analysis.

Well, we have a DateKey, and let’s join this to the several, we’ll join it to the OrderDateKey on Sales. You can just grab the DateKey and drag it over to OrderDateKey and then let go and you can see the new relationship. A window will pop up. So we’re going from Date to, and we want to make sure DateKey is actually what we’re pulling.

So we want to make sure we’re on DateKey, to OrderDateKey, you see these are going to line up. So the cardinality is one-to-many. So again, the Date table on the outside is going to be on the one side. The Sales table, our fact table in the center is going to be the many side of the relationship. We can go ahead and save that.

And you see the relationship connect. So you can click on it and it’ll show. You can also see the arrow pointing towards, from Date into the Sales table. So that is kind of the brief intro into our Model view, and again, the way that we’re going to connect our data together. 

So now that you’ve seen the star schema in action, I hope you consider it when you’re starting a multi-table project in Power BI.

It’s a great way to connect your data and transform, and make sure all your tables are related in a way that’s easy to use and easy to understand when you’re working through your visualizations. Thanks for watching.