And if you watch this video till the end, I'll give you a very special gift. This video teaches you how to audit an existing data model. But what if you were creating a new data model for a new project?.
How do you get it right to begin with? I'll send you my data modeling beginner's guide for free. Just comment the word guide below. I'm Avi Singh, a Power BI Microsoft MVP, and through my online program on learnpowerbi.com, I have helped.
Thousands of business users master Power BI and build beautiful reports in under 30 days. Let's get started with our audit. For one, you are right to care about the data model. Just the fact that you're watching this video means you're.
Far ahead of the average Power BI user who doesn't even know the concept of data model. They think that Power BI is a reporting tool. It actually isn't. It is a data modeling tool..
When I'm helping a client or student with a Power BI problem, I often ask them to show me the data model first. Because I want to figure out if this project is being run by people who know what they're doing, who understand the core concepts of Power BI, or they have no clue and they are.
Stumbling in the dark just trying to put reports together any way they can. So here we are in the model view of Power BI, and that's exactly the place to start for our model audit. So the first thing I look at is that how does it feel at first.
Glance? And unfortunately, a lot of models that I see look completely messy and it looks like somebody has thrown up on, you know, the data model and then they just pull every data, every table in and it's a big mess..
That's not the way to do Power BI. Power BI, you're supposed to do an agile manner while always following the core concepts, the core principles. So clean model will look something like this. And if they are organized in this way, then I'll know that.
They have very clear understanding of the look up tables, the hippos and the data tables, the giraffes, which represent the business processes. So here I can see that the tall ones are the giraffes and I can see the, you know, The Who, what, where and how the look up.
Posts Related:
- Tutorial Adobe Acrobat Sign automations with Make
- Transform Boring Presentations into Interactive Meetings Mentimeter
- Cadastrar Igreja - Planilha Cadastro de Membros e Controle de DÃzimos.
Tables are up top.
And these data tables are hooking in to the right table as, as they need. And of course, there can be a lot of a lot more data tables or giraffes here..So they can be as many business processes as that model is covering. So we have budget sales, there could be inventory support calls, warehousing, shipment, who know you can, you can keep adding and that's how a model grows..
Now, if it indeed does have a whole bunch of data tables, then the next thing I would expect to see is that it has layouts. So again, if you have lots of data tables which are tracking different business processes, then you want to create layouts so that you have a dedicated focus to you just to understand.
The the relationships and what's going on with that. So budget, we have just the budget and the related tables. And if you go here on sales, we have just the sales table and everything related to that. And again, if you had warehousing, inventory, shipment.
Support calls and so on so forth, I would expect there to be a layout for each one of them. The next thing I look at are the relationships. So the relationships by default should be one too many and you would see a one on this side and star on this side and 1.
Directional not by directional. The arrow should be one way. So that is the default and anything that is an exception it's OK. But I'll ask them about it..
So if I see a dotted line, as you see over here, there's a dotted line relationship here, you see a bi directional relationship here. And I'll ask about that. Now, of course, this one is the one I designed..
So the dotted line is because in sales we have two kind of dates. We have the order date and the ship date. Order date is the active relationship, that is this one. And the ship date is inactive, which can be activated in Dax, right?.
So that makes sense. So again, I'll ask for that explanation and here we have a bi directional relationship because this is a bridge table which is bridging essentially a many to many relationship. The key lesson is any exception, anything which is not the.
Standard plane vanilla, one to many, one directional, you got
To be able to explain it. Now, unfortunately, a lot of times when I'm reviewing people's models and I ask about the exceptions for the dotted line, bi directional and many to many, something like that, the.Best that I get is a shoulder shrug. It's like, I don't know, I dragged and dropped it and that's what Power BI created it. That is not the sign off of Power BI Pro, right? That is very amateuristic..
You got to be in control. You need to know the concepts, you need to follow that and build your model according to that. The next thing that I look at is maybe the specific data tables themselves..
And for this, I may switch over to the table view. And what I'm looking for is really the naming convention. So make sure they have good sensible names. Yeah, here, like all names make sense. And if I look at the table view, they don't seem to have.
Unnecessary columns. Sometimes people just import way too many columns. And folks, it's it's hard, if not impossible, if you start with too many tables, too many columns to later remove them, especially if you start publishing a report and other.
People start creating reports off of that. Trust me, you want to start iteratively in agile manner, bringing less than, then expand from there. Very intentionally by design, right? So modeling by design, not by accident..
All right. So, so again, the name should be clean. The columns should be what we need. And the last bonus thing that I look for is how the measures are organized..
Now these, I like seeing them in two different ways. The 1st way is a table just for the formulas, right? And in this case, we have two tables. We have the formula sales and we have formula budgets. And that's OK..
You can have, depending on how big or small the model is, you can have as many of these tables as you want. So, yeah, if I see that they're organizing their measures in these folders, I'm like, you know, respect, mate. That's, that's good..
They, they probably know what they're doing. And this is this is good stuff. Now, the next level to that is if they are organizing their measures in subfolders, so not separate tables. But you notice here, if you click on sales, in sales, they.
Have a subfolder called Measures that has all the measures organized and you can kind of collapse it and it goes away. And the same thing we have in budget, we have this Measures folder. So now you know how to audit a data model..
But if you are trying to create a data model from scratch, maybe you're starting on a new project, then what are the key data modeling principles to follow? If you like, I can send you my data modeling beginner's guide for free..
