Watch Out for These Data Model Red Flags

Brick and Caleb discuss the importance of data modeling and good DAX in Power BI, highlighting common mistakes that can lead to poor report performance. For fast and efficient reporting, you need a sound data model that contains the right level of detail, dimensions, and relationships, avoids snowflake schemas, and uses simple (and standardized) naming conventions.

Watch this episode on our YouTube channel.

Blue Margin increases enterprise value for PE-backed, mid-market companies by serving as their fractional data team. We advise on, build, and manage data platforms. Our strategy, proven with over 300 companies to-date, expands multiples through data transformation, as presented in our book, The Dashboard Effect.

Subscribe here to get more episodes of The Dashboard Effect podcast on your favorite podcast app.

Visit Blue Margin's library of additional BI resources.

Transcript

Brick Thompson: 0:12

Welcome to The Dashboard Effect Podcast. I'm Brick Thompson.

Caleb Ochs: 0:15

I'm Caleb Ochs.

Brick Thompson: 0:16

Hey, Caleb, you're just back from vacation. We're gonna put your brain to work here today. We thought that we would talk specifically about how do you know that you have a sound data model based on how your Power BI reports are working? So, we'll try not to get too technical here, but I think it's kind of an interesting topic.

Caleb Ochs: 0:35

Sure, yeah. Brain is fresh and ready to rock.

Brick Thompson: 0:38

Not too jetlagged.

Caleb Ochs: 0:39

Yeah, right. Exactly. All right. Yeah, I think this is gonna be a good topic. And really, I'm thinking about this from an end user perspective. If you're working in a business that has Power BI, and maybe you have a suspicion that, I feel like this could be better. We're gonna hopefully tell you if you're thinking about it right. And if they could be better, or if what you've got is pretty good.

Brick Thompson: 1:05

Yeah, there's kind of standards of how a report should behave. It's not always the data model that's at fault if it's not performing well, it could be other stuff. But sometimes it is. Sometimes it just hasn't been designed well.

Caleb Ochs: 1:18

That's probably a good place to start. Yeah. So yeah, a sound data model goes a long ways to help with performance and things, but in other scenarios, too of things that you might want to do with your Power BI report, if you don't have a good data model behind it, it's not going to be able to do it. So one of those things would be, let's say you're looking at a sales number or something and you want to know more detail, like behind the scenes, how is this number coming together? Or what are the invoices that make this number up? Whatever that might be. And if you're not able to get to that level of detail, either in the same report or very easily with that same data model, then you've probably got a summarized model. Which is not awesome, because that is a problem. You cannot get to the detail that you need to get to.

Brick Thompson: 2:04

Right, you want to be able to get down to - most of the time - down to individual transactions, so that you can slice and dice it however you want to. And if someone has summarized as they're pulling the data into the data model, there's various reasons why that might happen. You can't. There's no drill down. There's no create me a new page that has the detail. Right? You're just kind of stuck.

Caleb Ochs: 2:26

Yeah, yeah. So then when you request that it takes three weeks...

Brick Thompson: 2:31

Because they're building a new data model.

Caleb Ochs: 2:34

And then you get it and maybe the numbers don't tie between the two. It's just like... yeah, it can be a big problem. Yeah. So if that's happening, that's probably because you don't have a great model that the reports are built upon.

Brick Thompson: 2:46

So having the right level of detail. This may be sort of related to another issue, which is how is your report performing?

Caleb Ochs: 2:56

Yep.

Brick Thompson: 2:56

So if you're, if you're clicking on things, if you're doing cross filters, or even just loading the report, and it's taking a long time. Could be the data model.

Caleb Ochs: 3:07

Yeah. Definitely could be the data model. It's interesting, because data modeling versus what you do in DAX is a little bit of a balance. But you have to have a good data model in order to write good, simple, fast DAX.

Brick Thompson: 3:21

Yeah.

Caleb Ochs: 3:22

You can still write really bad DAX on a good data model. So that could still cause you a performance issue because you just have some bad written DAX (that code that does the measurements and pulls the metrics together). So that could still be the case. But there's a couple of things, basically, what we're saying is if your Power BI reports seem to be very slow, you have something going on.

Brick Thompson: 3:47

And yeah, it's good chance it's in the data model. Are there a couple of simple examples of things that happen in data models that slow things down?

Caleb Ochs: 3:55

Yeah, one of the most common things that I see is when a model is put together, and it's not dimensional. So that means you have facts and dimensions, and it's making its best effort to be a star schema. Which means there aren't multiple layers of relationships between tables. So every time you have to traverse tables, like if you're doing Sales by Customer, you have a Customer table and you have a Sales table. That's one traverse. That's one step. If you have a Customer Group dimension that's connected to your Customer table, then your two steps. So if you're doing Sales by Customer Group, you now going to have two steps to go get to your Sales number from Customer Group. And those are very simple examples. But sometimes people will just kind of dump their ERP data into a model, and then you've got sometimes six or more steps to get from one table to another, and that will just murder your performance

Brick Thompson: 4:59

Ok. So you've got to denormalize the right amount. Now, if you took that to the extreme and just completely denormalized, and put all your dimensions in the same table as the fact table, does that hurt your performance too?

Caleb Ochs: 5:12

it's funny, that actually helps your performance. So this does get a little technical, but so if you have one table of data inside of your data model, in Power BI is what I'm talking about specifically, because I know that engine works. You're gonna get the best performance. However, if you have a lot of data and you do that, it's going to make your model size massive. Because you have so many rows and columns of data that's just repeated a lot.

Brick Thompson: 5:39

Yeah.

Caleb Ochs: 5:39

Power BI does a great job of compressing it. But still, if you got 100 million rows of customer names, and you've only got a million unique customers, having a million unique customers in a separate table with an ID in your fact table is going to be much more storage-friendly way to do that. So it's kind of a balance, you definitely want to do that. And you also run into other limitations, too, if you do that.

Brick Thompson: 6:03

I mean, the VertiPaq engine doesn't like super wide tables, does it? Or does it not care?

Caleb Ochs: 6:10

It depends on which context you're talking about. So when you're loading the table, it doesn't like it because it has to load all of those values into memory and then compress everything. So the more columns that you have, and the more values you have in those columns, the longer that takes. But when it's running DAX over that table, it likes it, because it doesn't have to go to different tables to compute. It's all just there.

Brick Thompson: 6:36

Interesting. Okay. All right. So then another way that you can know that a data model maybe is not as good as it should be for reporting is simply if people don't understand the data model. So if other people can't use that model to build an extension on that report, or a different version of that report, or just a different report based on the same data. It should be easy.

Caleb Ochs: 7:04

It should be easy. It should be intuitive. Columns and tables should be named in a really easy to understand way. And, you know, it didn't come into my earlier example of when people just dump things into a model. Sometimes those columns are really poorly named. If you don't have good naming conventions on your tables, you don't know which ones are connected to each other, which ones are related and which ones aren't. So pulling fields onto the canvas and trying to build a visual could just straight up give you an error. Or it can make your data look really weird. So you definitely want to have it super clean and intuitive to use.

Brick Thompson: 7:41

Yeah, and have standards within your company and your group. I mean, if you're referring to a foreign ID, name the column with a ID suffix on it, so that you know, it's an ID. You're not having to guess at that.

Caleb Ochs: 7:53

Exactly.

Brick Thompson: 7:54

And, you know, referring back to what you're talking about earlier, make sure that you don't do a snowflake schema by mistake. It just makes it harder to understand.

Caleb Ochs: 8:02

Right. Yeah, exactly. Take it a step further and hide the tables that you don't want people to see, or hide the columns you don't want people to see.

Brick Thompson: 8:08

Oh, yeah.

Caleb Ochs: 8:10

And, like you said, simple conventions. One of ours is the columns that you put into a table. A column should have no spaces, your measures should have spaces.

Brick Thompson: 8:22

Well, you mean in the names?

Caleb Ochs: 8:24

In the names.

Brick Thompson: 8:24

So you want the measures to be sort of human readable?

Caleb Ochs: 8:27

Exactly.

Brick Thompson: 8:28

The columns, you want to make sure that you don't have to put quotes around them in a SQL or a DAX query.

Caleb Ochs: 8:36

Yep.

Brick Thompson: 8:36

Good. All right. Any other quick things that people should look out for to know they maybe have a data model problem? Yeah.

Caleb Ochs: 8:43

Well, I mean, I guess the bottom line is if you think that there's a problem with your Power BI report, it's too slow, and you can't get the numbers that you want to get even though it seems like you should be able to, you probably do have a data model and /or DAX problem. It probably needs to be worked on.

Brick Thompson: 9:01

And if you can't hand the report to a different developer and they can understand it pretty easily. Same thing.

Caleb Ochs: 9:07

Exactly.

Brick Thompson: 9:08

Good. All right.

Caleb Ochs: 9:08

Cool. Yep.