Behind the Scenes in Power BI: Tips to Streamline Data Modeling
Apr 11, 2024Episode 125
Brick Thompson, Jon Thompson, Caleb Ochs
This week, Brick and Caleb get a bit technical and discuss how to improve Power BI report performance through effective semantic modeling and minimizing data transformation with Power BI. They unpack the challenges of managing data transformations directly within Power BI and highlight the advantages of using serverless SQL views instead. Their insights will help Power BI report developers avoid common pitfalls, streamline data processing, and improve Power BI report performance.
Jump to a Section: 0:40 How Blue Margin approaches semantic modeling with serverless SQL views 1:45 Power BI is not an ETL tool, so use SQL instead 2:34 How tight does governance around semantic models need to be? 5:23 What is the optimal level of complexity for a semantic model?
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.
This week, Brick and Caleb get a bit technical and discuss how to improve Power BI report performance through effective semantic modeling and minimizing data transformation with Power BI. They unpack the challenges of managing data transformations directly within Power BI and highlight the advantages of using serverless SQL views instead. Their insights will help Power BI report developers avoid common pitfalls, streamline data processing, and improve Power BI report performance.
Jump to a Section: 0:40 How Blue Margin approaches semantic modeling with serverless SQL views 1:45 Power BI is not an ETL tool, so use SQL instead 2:34 How tight does governance around semantic models need to be? 5:23 What is the optimal level of complexity for a semantic model?
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.
Welcome back to The Dashboard Effect podcast. I'm Brick Thompson.
Caleb Ochs:
I'm Caleb Ochs.
Brick Thompson:
Caleb, we wanted to talk this episode about the semantic model that you create when you're creating a Power BI report. This can be a model on top of a model, actually, it could be just the only semantic model connecting directly to some data source. And there's definitely some considerations around this. This might be a little technical, but I think it'll be an interesting discussion.
Caleb Ochs:
Yeah.
Brick Thompson:
So how do you think about that report writer creating their own semantic model? And what are some best practices or things to think about in dealing with that?
Caleb Ochs:
Yeah. So just to set the stage. So the way that we do it, the way we build a data lake, and then we put a semantic layer on top of it, and that semantic layer is typically SQL views. It's molding the data.
Brick Thompson:
Serverless SQL.
Caleb Ochs:
Yes, serverless SQL views just makes the data - the raw data - look in a way that's understandable and easy to work with and easy to model with facts and dimensions. Power BI itself has a thing called Power Query in it. And you can go like to a bunch of different sources, you can pull data, and you can do essentially what we do in these SQL views inside of Power BI itself. You could. You can also just consume those SQL views and do other things outside of them in Power BI. So Power BI has its own little like semantic model engine. So
anyway, to your point:
best practices. The way that we do it, you really want to have as little transformation done in Power BI as possible.
Brick Thompson:
So, why?
Caleb Ochs:
Good question. Good clarification. Power BI inherently has a lot of great things about it. I mean you can connect a lot of different data sources, you can connect to many different data sources in one data model. However, it's not an ETL tool. So I could get into some real technical stuff here, but basically, if you do too much in the in that ETL portion of Power BI, it can just cause you problems in terms of refresh speed and memory problems, it just takes way too much.
Brick Thompson:
And just dealing with that M language in Power Query is a nightmare.
Caleb Ochs:
Yeah. It's a lot to deal with. So anyway, SQL is much better at feeding data and shaping it. And then you let Power BI do the aggregations and crunching of those numbers.
Brick Thompson:
Yeah. Okay. So you said, as you were explaining that, that ideally, you provide a good enough semantic layer for your report writer, so that they're just connecting to that, and maybe adding some DAX and KPIs, and so on. But they're not redoing relationships between tables or that type of thing. But invariably, someone's gonna have a different way they want to look at it. They're gonna have a spreadsheet they want to bring in, or they're going to connect to Power BI Metrics to get goals or something, and you have to relate them. So how do you empower people to do that in a good way? Do you need to have governance around that? Or do you just kind of let people do their thing? And as we talked about in our last episode, if it turns out to be a report that's going to be broadly disseminated, then you put it through some kind of a governance process to make sure it's good.
Caleb Ochs:
Yeah. Yeah, I mean, the key thing here that I want to stress to people is that building that semantic model in Power BI is not something that you need to have super tight governance over. It's not something that's going to totally impact IT. You don't need to have IT involved in them building themselves a semantic model in Power BI. So it's really up to them to build that the way that they need it built. And it might include some of those things like pulling in an Excel sheet or whatever. And then, another thing to think about is, you when you have a model, a Power BI model, Power BI, if you're newer to it, you don't quite grasp the concept of a model and a report being different. It's just one thing. Because that's the experience in the desktop. Once you get further along in your Power BI journey, you start to realize that a model can be one thing, and I can have a bunch of reports pointed at it. But most of the time, I think the broad cases that we've seen, the majority of the cases is people are one report to a data model. Power BI data model.
Brick Thompson:
And you see that when you publish the report out to the Power BI Service, and you'll see the model in there. The report in the model, but other people can connect to that model and create their own reports if they want to.
Caleb Ochs:
Yeah, they could.
Brick Thompson:
But it doesn't seem to happen that much.
Caleb Ochs:
Right. So I think for most companies, what they're going to find is that it's just fine to have people do a one report to one data model strategy.
Brick Thompson:
And don't worry about it.
Caleb Ochs:
Yeah, don't worry about it.
Brick Thompson:
Don't feel like okay, wait, hold up, we have to go back to the original semantic layer and get that incorporated there.
Caleb Ochs:
Yeah, exactly. Right.
Brick Thompson:
Don't sweat it. So probably in your mind, there's an optimal level of complexity for that main semantic layer over the data lake. Sort of like we used to think about normalization for databases, I mean, analogous, it's not the same. But now I guess there's a right level of complexity and completeness, in that in that model, to to enable people to create their own reports to extend that model. But don't go to trying to create a perfect sort of Kimball data warehouse-like semantic layer. Is that right?
Caleb Ochs:
An all-encompassing model that's going to be able to do everything for everyone. Don't do that. You definitely want to have, to your point, one that does most things, but then let there be those edge cases. And that's okay. That's okay.
Brick Thompson:
Yeah, in fact...well, we'll talk about in a later episode, but I've been thinking a lot about how important it is to take an iterative approach to BI. And for many years, people felt like, "Okay, I need to do a complete... don't even start creating reports until I've spent 18 months creating this canonical data model." And what we found, and we've talked about before in previous episodes, is that almost invariably once you publish a report, and people start using it, they realize what they really wanted. Or they realize this other little thing that would make it super useful. And you think, okay, maybe that happens once or twice a report. No. It keeps happening because the business is changing, there are new problems that need to be solved, people are thinking of different ways to go after the decisions and the things that they want to figure out. And so you don't want to stymie people by saying, "Hey, we have super tight governance, you can only use this data model." You actually want to let them do that, so that they can iterate. And when good ideas come out of that great. You can incorporate them in the main semantic layer. Actually probably have multiple main semantic layers, but in the right place there.
Caleb Ochs:
Right.
Brick Thompson:
But realize there's going to be this meta semantic layer - almost always.
Caleb Ochs:
Yeah, right. Right. Exactly. And that's a really good point. Definitely don't misunderstand. There is a use case for having a primary model that does most of your stuff. But you know, don't make it a requirement to put whatever you're trying to do into that model in order to be able to report on it. Just build it separately.
Brick Thompson:
You're gonna slow things down. Frustrate people.
Caleb Ochs:
And it might actually wreck your main model, too. Because it may not actually fit there well.
Brick Thompson:
Yeah. Interesting. I like it. Alright. Well, we'll come back to this in a later episode because I want to talk about that iteration and sort of what we've learned over the last few years of delivering tons of BI projects and how flexible you need to be. Or when to not be flexible and when to
be flexible. Spoiler alert:
mostly be really flexible. Enable that as much as possible. We probably should have put a technical warning at the front of this episode. I feel like we had a lot of terms in there. Hopefully, people got some interesting thoughts out of this. And I guess with that, we'll wrap it.