Would take you from beginner to Power BI Pro and creating incredible dashboards in just 30 days. And you can see lots of our member success stories on our website. If you have any questions, send me a message on LinkedIn or.
E-mail me at avi@avising.com. Let's talk about relationships and Power BI modeling. So let's start with what is a model? Well, this is how the dictionary defines it. It's a representation of a person or a thing, typically on.
A smaller scale than the original. You know, that's a good enough definition because that is quite similar to what a Power BI model is. It is a representation of the real world out there. And that's what a Power BI model does it as best as it can..
It tries to represent, it tries to capture the real world into the structure that it understands. And these are the two main structures. So in a model, you're going to find entities or tables and you're going to find relationships like how are these.
So let's stay there for a little bit. So entities as tables, as you already know, can be of two types. We can have data tables or we can have lookup tables. Now data tables typically represent your business process.
And right? So they capture data about a specific business process and it could be anything. It could be something related to your production sales order, a shipment, a support call, a hiring, or any other process in.
Your business. And business processes are the heartbeat of the business. This is what's important to us. This is what keeps the ship afloat. We care about this stuff, we love this stuff, and that's why.
We want to measure it. We want to capture data around us so we can understand it and improve it. So that's our data table, which represents the business process. So as it runs, as it executes, every time it executes, we.
Capture data around that. The lookup tables are The Who, what, where one, how. And these are the ones which help us analyze and slice and dice and really understand the business process. Look at it from a lot of different perspectives..
Posts Related:
- Excel 2021 Pivot Tables Tutorial
- Seamlessly generate cloud flow insights
- Is Power BI Free or Paid The Good News and Bad News + Tips on Getting Started
Of course, you've seen an example.
So sales is a business process, right? So that's that's a business process. We were capturing in that data table and our look up tables who, what, where and how in this case have been customer,.Product, territory and calendar. Let's kind of recap everything that we have learned in earlier modules and so far about data table and look up table. So data table, they typically represent a business process, whereas the look up tables represents The Who, what, where,.
When, how. Data tables are lanky, think giraffes, tall and thin, right? So they have lots and lots of rows and data table tend to grow, right? So sales, you can have a million sales transactions every year,.
Even every day, depending on the size of your business, right? So it's so they they really grow fast and and they're thin. They don't have that many columns. They're not supposed to be very wide. So that's a data table for you..
On the other hand, look up tables are squat, they're short and they're wide tank hippos. All right, so so yeah, so for imagine data table with sales, the look up table is going to be customer. And if you have a million sales transactions, we're not going to.
Have a million customer or a million products, right. I mean, you know, I don't know, maybe we'll have a few 1000 customers and few 100 products. So they're, they're short, they're not that tall and they don't grow as fast, but they can be wide..
They're rich in these attributes which, you know, we can use to slice and dice the data and that can add up and make it wide, right? So it can have a lot of columns. Now, data tables are rich in numbers..
When you look up a data table, you should see a lot of numbers in there, right? So numbers which are capturing the information about the business process and numbers which can also be keys which connected to lookup tables..
And again this is not a hard and fast rule. I'm not saying that you cannot have words or alphanumeric stuff in the data table, we often do. But as a general rule, data table are number heavy. On the other hand, lookup tables are word heavy, right?.
So if you look at that, there should be a lot of these rich
Kind of text attributes because that's what's helpful for us to kind of slice and dice and analyze data and that's what the lookup tables bring us. It should look very word heavy..Data tables are generally for the machines in the sense that, of course, in our case, the machine is Power BI. And really how we leverage that machine is by writing DACS, measures and DACS, which we'll cover in later module in detail. But you've already seen the power and the strength of it,.
Right? Then how it can take massive amounts of data and just crunch and slice and dice and operate it. And if you contrast that with what humans can do, I mean, it's hard for us to absorb 100 numbers, right?.
I mean, we've talked about this. You put 100 numbers up on the wall, I probably wouldn't be able to make sense of them and but if you write a measure, you know, some average or something, then I can make sense of it, right?.
So data tables generally are operated upon by machine, by the Power BI machine, in our case via DAX measures. So that's where we expect to be writing most of our measures and calculations and numbers, right? On the other hand, look up tables are generally for humans.
Because these would have the fields which humans are going to use to slice and dice and visualize data and consume it and understand it, right? So, so we have the the number, the sales amount crunched from the data table, but then we want to look at by product category.
And sub category and the customer occupation and so on and so forth. So that's kind of a comparison in a look at data table and look up table. So let's talk a little bit about how you should go about creating.
A Power BI model. Now, a common mistake is starting at this button. Now I know it's tempting. I know, you know, it feels like Power BI is an amazing tool. We've already seen query editor..
It's so awesome. And you feel like I should just click this, get it, get it in there. Now, I would say advise you to step away from get data button. In fact, a good idea might be to step away from the power behind.
Desktop and your computer. And once you have had the discussion with your clients and again, your clients can be internal within your same company or external clients. Once you've had some discussions with them, when you have and.
Once you have explored the data enough, then you should step away from the computer and go to a whiteboard or take out a piece of paper. That's the best place to design your Power BI model. So start there and think about what is the key business process.
That you're trying to capture and write that down. And once you have that written down and say, well, what's The Who, what, where one, how right and and fill those boxes and then see what comes up for you. And once you have that, then you launch the query editor..
Once you have the image of what you want to create, you use the query editor, kind of bring it to life to kind of sculpt that out of the data source you're connected to. And one reason why this is important is because often if you directly go to the get data route, people often just simply.
End up creating a model which looks like the way the data existed in the source. But you don't have to. And doing this kind of encourages you, nudges you in the right direction..
Now, once you do have the data all loaded into Power BI, then you've heard me talk about this before, but again, I feel this is really crucial. So I'm going to emphasize this once more that make sure you carefully layout the model..
And again, there's modeling by accident and modeling by design. And if you lay it out like this, it's very apparent to you or anybody else looking at the model that this model has been designed with care. It's not a model by accident where you just, you know, dumped.
A bunch of data on there. So the best practices, again in line with everything we have discussed is to place the data table near the bottom, right. So, you know, place them at the bottom and make the data table tall..
And I do that even if sometimes when data table only has two or three columns, so there's not much there, but I still drag and drop and kind of make it make it larger, even if it has just two or three columns, just to remind me every single time that this is a data table, right?.
So visually, I make it tall. And then you're going to place your lookup tables near the top. And these ones, I make them squat. So I make them short. And again, obviously they typically have a large number of.
Columns, but I make them squat to kind of remind them of this fact that these are the lookup tables. Now there's another thing when I'm looking at a relationship view, another thing that I look at is that the relationship should be by default one too many at single directional..
Don't worry too much about that. We will go into a lot more detail there. Now, there is one tip that I would like to share though. So this layout that I just talked about. Now, this is great when you're just starting out and you have.
Just a few fact tables, a few data tables. But once you know your model grows over time and you keep adding data tables because you're capturing more and more information about additional business processes, right? So you typically keep growing the model that way..
And sometimes it becomes really challenging to follow what I just the best practice that I just described. It just becomes too messy and it doesn't work. And notice here that I didn't call it a complex model. Words have power, my friends..
And let's call it an awesome model, right? So again, this shouldn't be something that you end up on day one. If you if that happened and there's something wrong, you're not following the agile approach..
But over time, as you build a Power BI model, it is possible that you would end up with 50 or even 100 tables. And again, it's going to happen over time. But how do you handle that? How do you still follow these best practices that we talked.
About? Well, the tip for you is that you can create additional layout out in Power BI. Let me show you how. So here is our Power BI model, and admittedly this doesn't have.
That many tables yet, but we're just going to, I'm just going to show you how it's going to work. So again, if you go into this model or relationship view and if you look at the bottom, notice that there's a plus button here..
And if you hover over that, I don't know if you can read it, but it says new layout. And this is what it does. So again, it doesn't look like much as, oh, set U your layout. And if you expand your fields now what you can do is create a.
New layout. This is basically think of like a diagram view, right? So it doesn't change anything in the model. It lets you just create like a new visual picture. And hey, a picture is worth 1000 words, right?.
