Skip to main content

Power BI and Databricks: Practical Best Practices

Summary

  • Best Practices Guide: This video walks through 51 proven practices for optimizing Power BI and Databricks integration across data layers, warehousing, and analytics.
  • Architecture Deep Dive: Learn holistic approaches spanning data preparation, SQL warehouse serving, semantic modeling, reporting, and Databricks service optimization.
  • Practical Toolkit: Attendees receive a comprehensive one-page cheat sheet summarizing all best practices for immediate reference and ongoing use.

Power BI and Databricks: Practical Best Practices

Watch: Power BI and Databricks: Practical Best Practices
Talk by Hobbs Hobbs, Sr. Solution Architect, Databricks
Power BI has long been the dominant BI tool in the market. In this video, we'll discuss how to get the most out of PowerBI and Databricks, beginning with high-level architecture and moving down into detailed how-to guides for troubleshooting common failure points. At the end, you'll receive a cheat-sheet which summarizes those best practices into an easy-to-reference format.
Intelligent analytics for everyone: https://www.databricks.com/product/business-intelligence
BI meets AI: https://www.databricks.com/resources/ebook/business-intelligence-meets-ai
Explore the only unified platform for agent systems: https://www.databricks.com/product/artificial-intelligence
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

FAQs

How many best practices are covered in this video?

This video covers 51 best practices for Power BI and Databricks integration, spanning data optimization, SQL warehouse setup, semantic modeling, report design, and service configuration. The presenter delivers this comprehensive content in a 40-minute session with visual demonstrations.

What topics does this Power BI and Databricks session cover?

This video addresses the full analytics stack: data layer optimization, SQL warehouse serving, Power BI querying and modeling, report design and settings, Power BI service optimization, and best practices for maintaining high performance across the entire platform.

Is there a downloadable summary from this video?

Yes, this video includes a link to a downloadable one-page summary of all 51 best practices. Attendees can access this cheat sheet through the slide deck link provided at the end of this video for easy reference.

Who should watch this Power BI and Databricks video?

This video is ideal for BI architects, data engineers, and Power BI practitioners implementing the Databricks Data and AI platform. The presenter draws from extensive experience in BI architecture and consulting to provide practical, immediately applicable guidance.

Full transcript

0:00 Introduction and agenda

Good morning, ladies and gentlemen. You picked me over breakfast, so I am honored. Thank you for being here. Some of you have come across time zones and continents to come to this conference. So, I'm sorry you're tired. My experience with these conferences, there's almost an unlimited amount of coffee sitting around. Just every corner has a new coffee station. So help yourself and hopefully we'll get through this. All right.
I'm a senior solution architect with data bricks before this. I was in business intelligence architecture. I was a PowerBI architect for a long time. I was a consultant. So I'd come in with companies. I'd help them design their BI infrastructure and fine-tune their semantic models and roll out training plans and all those kinds of things. So that's what we're talking about today. My goal is that we would get through 51 best practices in the next 40 minutes.
This is only going to work though if we can see my screen. 51 best practices in 40 minutes. So that means I'm going to have to go really fast. You don't have to take pictures with your phone. You don't have to take notes because what's going to happen is at the end uh there'll be a link in the slide deck and the slide deck will be published and you click the link and it takes you to a blog post and at the bottom of the blog post is a download which has a one-page summary of all of the best practices that I'm about to share.
We're going to try and be really holistic as we go through this. So we're going to go through and we're going to begin uh at the bottom with the data itself. How do you get the data ready? Then how do you get the model the excuse me the SQL warehouse serving piece ready? then the semantic model ready, then the report itself ready, and then everything that goes into the PowerBI service ready. So, those are kind of the steps that we're going to walk through together today.
Okay. So, when I presented at my first conference, my I got my uh bachelor's degree in public speaking and then I went out into the world and found that you cannot get a job with a bachelor's degree in public speaking and I ended up in it. So, this is kind of here I am now. But the first conference I was ever at, I got a selfie with the audience and I said, "Would you please pretend to be unreasonably excited to be in my presentation so that I can show my manager and then he'll send me to more conferences."
So, I'm going to take a picture and if you would just be like I know it's 8 a. m. , right? Just unable. All right. Unreasonably excited to be here. Three, two, one. Thank you very much. I appreciate you. I don't get to come out to these things unless I can tell my manager that I'm doing a good job. So, if we can't get the slide deck to work, I'll just walk through it verbally. Yeah. And we'll walk through because really I'm just like there's no fluff in this in these slides.
It's just a list of things that you should do. When I was putting this together, I was trying to put myself in the shoes of what I would want to hear at a technical session. And I thought, I don't want the marketing stuff, right? Just tell me all of the useful things that I can do. So, I'm going to go ahead and get started then and we'll walk through and uh make as much progress as we can. So, we're going to begin at the beginning with the data layer itself.
All right. So, to get through 51, I have to go kind of fast. I have 38 minutes left. Down at the bottom, data bricks is not actually working with tables. Yeah, it's kind of a yes, victory. All right. So, we're not actually working with tables. We're working with these things called files. Yeah. So, there's all of these files. And the files, data bricks has some wizardry and it takes them and it thinks of them as a table.

3:12 Data layer optimization

But because you have these individual files and every change, every update, every uh delete, all of that becomes a new file, it is worth thinking about this briefly. There are some really nice uh sweet easy buttons to go through. This used to be a complex process, but now you can turn on something called liquid clustering. Liquid clustering can be set at the level of a table and you say monitor my table and automatically partition it based off the way the users are querying it.
So this is nice right because you used to have to think about partition policies and how you would break things up. This says just monitor run some data science figure out the best possible grouping of my files so that queries respond as fast as possible. And as query patterns change liquid clustering adapts to them. It's it's always ever so slightly behind. Right? So if you get a real significant change in your usage, give it a couple of days for the clusters to be rebuilt.
If you're not comfortable being that handsoff, right, if you want to be a little more hands-on, then you're going to run something called Zorder. Zorder will go through and do a similar process. You're just manually telling it, pick these things to order and partition by. Also set up predictive optimization. This sits at the level of a catalog or schema. You're going to turn it on for your gold layer, the serving layer.
And this says um go out clean up unused data and go through and make sure everything is optimized or again if you want to be more hands-on you can run those two commands vacuum and optimize yourself. Statistics happen automatically um if you've got predictive optimization turned on. Otherwise run statistics on your tables so that you've got some metadata about their performance about the number of files and their sizes.
And if you do that the file piece is taken care of. You can forget about it. don't need to think about file storage anymore. So then there's the data itself, right? So files are gone. Now we have tables. Think of tables in your mind. What are things we want to avoid? Um if you can avoid wide data types do, this is a small one. Like you're not going to get a lot of bang for your buck for this, but if you have the choice between having a decimal with a 100,000 decimal places being saved or just three, right?
Pick the smaller, narrower data type if you can. Don't save things as strings unless you really need to have them as strings. If you have columns that you can drop, and this is just classic Kimble Inmon modeling stuff, right? If you have high cardality columns, you can avoid when we get to Vertipac compression in a little bit with semantic models, it doesn't like high cardality columns. So, avoid those as best you can.
Um, take those things out where you are able. If your gold zone is views, how many of you are using views to serve your end users? Yeah, this is fine. If those views take a long time to compute and that's being run at the moment that the reporting layer needs it, that's waiting. Yeah. So, not ideal. You can also set up something like a materialized view which will premputee those results for you and save them. Now, there's a bunch of caching and stuff that happens uh in data bricks that we'll talk about and in PowerBI.
So, don't worry about this one too much, but if you can move to materialized views, do so. And again, if you can get away from uh really granular data to something more aggregate, now is the time to do so. You can also do those things in PowerBI, but let reporting be reporting and let the warehouse be the warehouse. Try and do this work up front. Logical modeling. Okay, so you have files, those are taken care of. You have the data itself.
It's in as good a shape as it's going to get. Now we get to the model in which those tables communicate with one another. We suggest that you go through layers of improvement from bronze to silver to gold. Those are the words we use. You can call it whatever you want, right? It can be staging to raw to clean to change data capture to business logic like I don't care. Just do your work in layers. Save the outputs and then reference the output in the next layer.
Don't present things to business users until it's done. So whatever you call your last layer, that's when you want to get the business involved. I'm going to pause here real quick because this one's important. Look in my eyes. You have to use a star schema. I'm sorry. It's a lot of work to build a good star schema, but you are not going to out optimize a terrible relational logical model. I'm sorry. This is just the way the world works.
You might be able to get away with some snowflake modeling, that would probably be okay, right? If you're hub and spoke, right, data vault style, that's going to be a little tricky, but you can make it work. But really, the star schema is still king. So, put in the work to make a good quality star schema. If you have five fact tables with many to many relationships between them and a dimension which flows from fact table one through to fact table 5, nothing else you do after this matters anymore, right?
That's so bad it's going to undermine everything else we try and do. So make sure you've got things in a star schema. Inside of data bricks, you can declare primary and foreign keys and if you then publish, which we'll talk about, from data bricks to PowerBI, it will honor those and pre-build those relationships for you. Yeah. So it'll automatically relate based off of the constraints that you've put in place. And if you use the word rely when you set up those constraints, um, data bricks will avoid expensive logic on top of those because it will assume out of the box that those values are unique.
So if you run a distinct count of a primary key or excuse me, if you run distinct over a primary key, it should be distinct. If you have used rely applying that constraint, it will now say I don't need to run that logic. I know I am relying on the fact that this is unique. So your queries will run faster if you put in the word rel. If you need surrogate keys, right? Um you can build those a few different ways in PowerBI, excuse me, in data bricks.
Uh and then pass them over to PowerBI. Data is done. We're not going to talk about data anymore. Next step, right, is you're going to have to serve that data through a thing. We generally call this a SQL warehouse, though it doesn't have to be a SQL warehouse. So for your SQL warehouses can technically serve your business users PowerBI reports through all-purpose compute. Please don't do that. It is um it is slightly cheaper so you will save a little bit of money but generally speaking it is slower because it's not optimized for SQL and BI.

9:11 SQL warehouse serving

So where you can use a SQL warehouse. How many of you guys are using serverless SQL warehouses for serving your PowerBI stuff? Okay. Very few of you are classic SQL warehouses for most of you. Something else. You're apathetic. Yeah. Okay. All right. Well, try and use SQL. Um, serverless isn't useful everywhere, but I do find it really useful here. The reason for that is the spin-up time. So, the first person who hits a SQL warehouse, uh, they're going to have to wait for it to turn on.
If you've got a serverless SQL warehouse, that's like 5 seconds. If you've got a classic SQL warehouse, it's like 5 minutes. So either you're like running a job to spin it up and you're warming it up in advance, right? Or if you're serless, you can just let it happen. They also can have spin down of I set mine to like 10 minutes. If nobody has hit my SQL warehouse in a 10-minute period, I wanted to turn itself off so I'm not spending money on it.
And because the spin-up time is so low, I don't really care that, right, who gets in after that 10-minute period, he's got to wait an extra 5 seconds. That's rough. But once he's waited that 5 seconds, everything after that should flow through really nicely. So use a serverless SQL warehouse. If you're going to roll out multiple warehouses for multiple people or groups, think about the cache. It's not all that complex, but hold it in your brain.
Um, serverless SQL warehouses have a 24-hour cache. Any query which comes in in a 24-hour period is stored in cache in memory in that SQL warehouse. even if it turns off, right? When it turns itself back on, it will re-reference that cache for 24 hours. So, group people based off of their query patterns and send them to the same SQL warehouse so they can benefit from caching as much as possible. I set my stop um I jumped ahead, right?
Auto stop for 10 minutes wherever you can. Um if you have large data, you've got hundreds of millions of rows or billions of rows that you're reporting on, you probably need to go up in size. I always start with the two extra small and I know my management is going to be displeased because that's the cheapest option, but most of the time it does fine for me, right? Start with a 2x small serverless SQL warehouse and try it.
And if it works for you and the queries run in, I don't know 2, three, four, five seconds, whatever that might be, stay at 2x small. If you're noticing your queries run a long time and you have done everything I already described, right, and then it really is just like, no, no, it's optimized. it just takes this long, right? Then you go up and you go up to a small or a medium or a large accordingly. If instead of long running queries you have many queries, then you go wide, right?
You're going to autoscale. So you take your 2x small SQL warehouse and you say minimum of one, maximum of 10. Yeah. Um I was talking with the product team who owns SQL warehouses and uh they told me that there is no particular limit for how many queries enter a SQL warehouse. node before they get uh cued and passed to the next one. My experience which is not official and they told me that I can't give you an official number because it's dynamic and it's uh intelligent and all of these things is it's like it's like 10 to 15.
Okay, so like 10 to 15ish queries will come in and then they're going to get cued for a single node. So that's when you would want to spin up a second one to handle the backlog. So if you have hundreds of queries coming at the same second, right? You get this big backlog, eight extra nodes are going to spin up in a five to six second period. If you're using serless, they get distributed to all of those. If you have a 10-minute turnoff period, after 10 minutes, any of those nodes that are not being used, they turn themselves off one at a time.
So this lets you handle bursts really nicely. And then you're sort of setting like your um like your minimum. The minimum you can scale on to zero if you want or down to one, but think about what you want the minimum to be. And what is the most your company would be willing to pay to give people good report response times? That's the maximum amount you're willing to spin up. Right? That's imagine you all 10 are up and running at 100%.
This is the cost I'm willing to pay to meet my SLA. That's how to think about that problem. Once all of that is in place, you can monitor your warehouse. You can set up alerts on top of it to say if I have spun up to nine nodes and they're all running at 90% and that's been true for four hours, I probably need to know about that because I might need to review the query patterns I'm expecting from my users. So, set up alerts on there. think about the minimum that you want to use.
And you can always go back into the uh the query history for your SQL warehouse. And inside of there, especially if you're doing things through direct query, which we'll talk about in a little bit, you'll see you can see some of the user information there, who passed the query across. Um, and now you can do cool things like chargeback. I'm a big fan of chargeback. um it doesn't it doesn't play in all organizations. So think about that however you like.
But the ability to track things to business units and say instead of being um just a cost center to be like look you got x amount of value from this tool. I know these 300 people are using me on a regular basis. Here's what we paid in order to provide that to you. Right? So those conversations are useful. All right we're going to step out of data bricks now. Right? Those two layers are data bricks layers. And now we have to move over to the PowerBI side of the equation.

14:47 Power BI querying and modeling

So we'll begin with Power Query. Um and slightly prior to Power Query is you're going to have to think about how you want to do your security. Yeah, if you are able to use direct query mode, which again I'll talk about in four points here, right? You can use data bricks row security policies and your PowerBI users will it'll be applied to them. you have to turn on single sign on and direct query inside of that semantic model and then that user information gets passed across and you can have security in one spot.
This is nice. It's not always feasible. So if it's not feasible then you're going to have it in two spots, right? You're going to have a warehouse level security policy and a reporting and semantic model level security policy. So those decisions require some thought. You're going to want to parameterize your Power Query code. How many of you are using parameters to switch between databases and environments? Oh, guys, come on.
You got to do this. This is good stuff. Okay, so go into your Power Query, right? If you're hitting data bricks, you can take the data bricks environment and the SQL warehouse and the catalog and the schema. You're going to build parameters for those four objects, right? And then all of your tables should reference those parameters. This means once it's published up to the PowerBI service, you can go to the semantic model, you can see the parameters and you can change the parameters in the PowerBI service without republishing, right?
So you can take it from environment A to environment B if you're using uh pipelines inside of PowerBI, right? They've got the ability to also edit those parameters. So you can say this model points at dev and this model points at test and this model points at prod. I don't do any republishing. I just swap out a parameter and refresh the thing. So parameterize your Power Query code. The only thing you should ever ever ever do in Power Query is select star from a table.
That's it. I don't ever want to see any ETL logic in Power Query ever again as long as I live. Power Query is great and it gives um average people the ability like it's like a superpower, right? They can do anything, right? Once you have PowerBI, you can do anything. You're saying it, I don't need you, right? get out. Just give me access to the data. I'll handle everything else myself. So, this is good and empowering and I love the democratization of data.
But if you're doing valuable data cleansing, do it in the warehouse layer, right? Take the live you just like take the code you're doing and give it back to a data engineer and say please do this as a view or a materialized view in data bricks for me. That way other people get to benefit from the cleaning that you are doing. Also, Power Query data cleansing is slow. You compare the same cleansing done in SQL versus Power Query, and it's going to be like my experience, it's like 10 times slower to do things in Power Query.
So, only select star inside of Power Query. Anytime you can limit data to only what's useful, do so. Limit rows and columns. That was kind of a cheap shot when I was a consultant. Um, just because it's hard because people want everything, right? They want all of the stuff. And so then I come in, I'm like, well, you have to limit the columns available. And they're like, but we want all of the columns. Do the best you can, right?
Work with your business people, figure out what's really valuable, but as you can limit rows and columns. So the semantic model itself, we're out of power query. We've loaded our tables in whatever storage format, and now we're dealing with the semantic model. I'm going to encourage you to try direct query mode. I never would have said this six years ago when I was working as a PowerBI architect because uh direct query mode mostly sucks.
It's mostly awful, right? You're going to get 30 to 60 second load times and every click does some new thing and it's awful and I never suggested it. There are two platforms now that um on the on the serving side do a good enough job that it's worth revisiting direct query. One of them is our competitor Snowflake. They do a great job with uh uh small BI queries responding in just like really short periods of time. Yeah.
So if if you're using Snowflake, please come to data bricks. But if you're if you're using Snowflake, right, you should consider direct query mode because you really can get down to a good SLA for your reports. So then you're like, okay, why like why would I do this thing? There are all these benefits that we've uh when I was a PowerBI person, I just wanted I was like, get out of my way and give me the stuff and I'll solve my own problems.
I don't need you. Right? This was my attitude. Um, and I find this in many PowerBI, we are extremely driven and passionate and we don't like roadblocks. So, we just like give me the stuff and I'll solve my own problem. Um, what happens though is that in doing this, we embrace all of these extra steps that we didn't necessarily have to embrace. So, we embrace things embrace embrace things like refresh schedules. If you're in direct query mode, you don't have to have a refresh schedule, right?
That's not a thing you have to worry about. We embrace building our own rowle security policies. If you're in direct query mode, you don't have to rebuild your own role level security policy. We embrace copying data between systems, right? Like what is wrong with us? But we we do. We're just like, all right, we're just going to take this data and I want a copy of it and I want a copy of it over here that I have to maintain forever now and will be a competing source of truth because I did my own ETL on top of this semantic model and you don't have that in your warehouse layer.
So I can't look at that. Right? So we have embraced all of these things in order to have that autonomy. So it is worth considering though it's a hard cell, right? Consider direct query mode. What kind of SLA are you going to get? You're going to get like 3 to 8 seconds. Yeah, it is never going to be as fast as import mode. It's not going to happen. If your people want that and it's really important to them, like don't go down this road.
But if they're willing to sit for 5 seconds to get their report, it's worth getting all of this baggage out of the system in order to do so. You can always switch any table is in direct query mode. You just click a button and now it's in import mode, right? So it's easy to go from direct query to import. It doesn't work the other way. If you start in import, you cannot switch it to be a direct query table. So start in direct query mode.
Try some composite modeling where you've got your dimensions in um in excuse me in import mode and your fact tables in direct query mode. And if that doesn't work, go full import. That's fine, too. You can do userdefined aggregations, but again, try and do that in the warehouse layer whenever you can. Make sure that your relationships are solid. Use one to one, one to many. There was a I was at a early PowerBI conference and one of the guys there, he had a thick Italian accent and he said to everyone in the room, he said, "If you use a many to many relationship, you will go to hell."
So um take it from the wise don't use many to many relationships if you can possibly avoid them. Make sure that uh once you have those relationships there single directional is better than birectional right there should be only limited use cases we're using birectional relationships you can click this button especially in direct query mode that says assume referential integrity. If you click that button, you will move um to doing inner joins, right?
And so you get a drastically smaller final result SQL statements. So the performance goes up quite a bit. Okay, DAX logic. I have 19 minutes left in DAX, right? Try and write efficient DAX. What does efficient DAX mean? It's it's honestly kind of difficult online to like find good examples of what efficient DAX looks like. if you really need to dive into something. I was helping a client the other day. They had some time intelligence stuff that was going on in their DAX uh and they wanted to move to direct query mode, but they were getting 30 60c load times.
Um so you're going to want to get used to tools like DAX Studio. Anyone using DAX Studio? Small handful. Okay. So DAX Studio will allow you to take a DAX statement, run it, and see where the bottlenecks are. Right? So get used to this. Uh and again um PowerBI uses this engine called the Vertipac compression in so Vertipac is is just magic right it's incredible what they have built that's why it's proprietary right and it has a proprietary format that almost nothing can read but it's so good and it's so fast and it loads everything into memory vertipac compression requires this special way of thinking about calculations so they're columner instead of row based and all of this stuff all that to say right um get used to some of these third party tools tools which will help you improve this.
Vertipac will will um will muscle through most things with no problem. You can write really inefficient DAXs on many semantic models and nobody will ever notice, right? It's just going to run in half a second and it'll be fine. But as you scale up, if you're getting into really large semantic models, 10 20 gigabyte after compression semantic models, it's going to be really important that you get used to these things.
You should never make calculated columns or tables, right? Why? Because that's what the warehouse is there for, right? If the column is valuable, move it back into the warehouse. Let the reporting tool do the reporting and free that business value up for people other than just the PowerBI semantic model. Yeah. Don't make calculated columns. Don't make calculated tables. They're usually very simple things. They can be done row-wise.
They don't have to be columner because it's a column output, right? So you can do it in SQL. And then again, DAX studio and the query history for debugging these things. So we're going to step out of the semantic model. We talked about the data itself, the file structure, the physical objects that we're working with, how to optimize that. We talked about the serving layers. All right, so we have our data and we've got our um tables and what should those look like?

24:31 Report design and settings

And then that goes through a SQL warehouse and we've optimized our SQL warehouse for our servers. Then we came over and we talked about what Power Query should and should not do, right? Okay. So, now we moved over to the semantic models. We talked through some relational best practices there. Um, and some of the things that you want to do in your DAX code, how to think about direct query versus import. And now we're actually over now into the report itself.
I don't know what that sound is. This one is fairly straightforward. your report design. Um, you're going to want to keep your charts to like less than 10 on a page for the most part, especially in direct query mode, but in import mode as well, because um I I have a totally separate talk track where I talk about design just design practices like graphic design stuff and most people the questions they want answered, you should keep it as limited as possible to some subset of all of the available questions with the ability to take an action and see more.
So that landing page try and keep it a less than 10 charts. Use PowerBI for its purpose. This has been a losing battle in my lifetime. Um so what is PowerBI good at? Right? PowerBI is great at visualized highly aggregated reporting. This is what it's designed for from the ground up. This is what it's built for. How many of you in your reports are mostly making tables and matrixes? No. Oh, I'm so glad things have changed.
In my experience, most people just want something like Excel to run in PowerBI, right? They want the granular, non-aggregated, nonvisisualized data. This was my experience. It sounds like things have gotten better. So, praise the Lord, right? Things are better than they were when I was doing this. But use PowerBI for what it's good at, where you can aggregate and visualize stuff and be bullish about it and push people to think this way about their data. if they need Excel, that's fine.
Excel is always going to be here. Excel is Excel is like the uh big muscular lumberjack grandfather of PowerBI. And that like PowerBI people want to own stuff. No, no, no. The Excel people want to own stuff, right? Excel is like all of the data tools you could possibly need on your laptop workable by you with no one's help, right? You can do all of your CRUD operations. You can alter things and change things. You don't need anybody's permission to do anything in Excel.
That's very appealing and very powerful used. Well, so we're never going to get away from that, right? That matters. It matters to people to be able to solve their own problems. Let them have their Excel stuff. But if they want it in Excel, be like, "Good, use Excel." And if they come to you, say, "That's not what I do. This is what I do." Right? So, I'm going to help you by doing this thing over here. There are some settings.
Uh uh and again some of these I'm going to focus on direct query mode. Um you can set up query reduction. There's five or six different settings inside of the actual PowerBI desktop tool which will allow you to do this. So you can do things like say um don't apply changes until I click this button over here. Take all of my filters in. Let me change them and then apply them all at once. So there's some things you can do to reduce those queries getting passed into the background.
If you set column to is nullable. It helps with some of the underlying logic to make some of these things faster as well. There are some default limits to the number of queries which will run in parallel against any data source. So you can go into query parallelization which is a hard word to say parallelization. Uh and you can set that number to something higher because I'll tell you that data bricks can handle it, right?
If you want to pass in a 100 queries at once, you can. Um so set that query paralization high. It's designed that default limit is is designed for things like older SQL server environments where you really don't want to overload the underlying system and have it shut down on you. Um, but with a lot of cloud infrastructure, you can do this. Now, if you have really large fact tables, make sure that you are doing incremental loads.
You can set this up directly in desktop or if you have to use data flows, you can use data flows. I've never been a big fan of data flows. They feel like I just didn't understand why, right? Why am I not doing this over here in the warehouse layer, why am I doing it in a data flow? There are some places it does make sense. If you have a table, it's really large, you want it to be loaded incrementally and it is referenced by 10 different semantic models the road.
Sure, do that with a data flow. That's like that use case makes sense to me. But you can do this directly inside of your report as well. Okay, so we started at the bottom with files and then we moved to data structures and then we moved to logical models and now we've talked about the SQL warehouse serving layer and then we talked about power query and now we've talked about some semantic modeling best practices. We talked about report design.

29:19 Power BI service optimization

Keep it simple, right? Don't over complicate things. Try not to be Excel if you can possibly avoid it. Excel is awesome but let it be it itself and you be your own thing. And then eventually we publish all of this up into the final step which is the PowerBI service. If you want to pass role level of security through from data bricks you need to be using direct query mode right otherwise this is your time to begin thinking about how you're going to manage roll of security policies directly inside of PowerBI.
There's some great tools there you can use them right just realize that there's going to be some duplicate duplication going on between these two spots. If you have large models or high amounts of traffic, by high amounts of traffic, I mean thousands of users. If you have thousands of users and they're going through a gateway, you need to strongly consider setting up a gateway cluster. This will automatically route users through the gateway that is most available.
So, you can set up five or 10 of these gateways and users will flow through the correct path. It's not all that often in my experience that the gateway is the bottleneck. But if you are experiencing network lag that you can't explain, right? Like my warehouse responded really fast and then I know when PowerBI received it and there's a gap in that middle section. This is where you need to start thinking about gateways and making sure that those are well tuned.
If you're inside of data bricks, you can publish directly um from your data bricks environment to the PowerBI service. Uh, this comes with some caveats and some cool features. So, you can go to a catalog or a schema. Let's say it's your gold zone. You're in finance. There's finance gold. So, you go down into finance gold and you look at the schema. It's got all the tables there for your semantic model and you've set up all of your relationships with primary and foreign keys.
There's a button there which will publish that to the PowerBI workspace. When you do that, every table you can say direct query importer or dual. What storage mode do you want for these things? and it will take the metadata definition of that model and push it to the PowerBI service. One of the cool things this does is in data bricks you can have column level comments right where you describe the column in business terms you say this is also known as these five other things in Oracle where I poured this from originally like it was called this 37 character nonsense right and so this is how you should think about this so those comments will be passed over into your PowerBI environment if you do this this is nice because it's it's inline documentation every table every column now has the comments from the warehouse and the people who built it on how to think about these values.
And when you're a desktop or you're a semantic modeler and you hover over a column or a table, that comment pops up. Now, so that's cool, right? You can publish straight from there. You can pick which tables you want. They'll honor the relationships. There is almost always extra work to be done if you take this approach because it doesn't have any measures in it, right? It doesn't have any DAXs present. It's using the XMLA endpoints.
How many of you know what the XMLA endpoints are? Okay, not many of you. So, back in the olden days of uh SQL Server Analysis Services, S SAS, they built these endpoints for pushing metadata out to these cubes. PowerBI uses the same thing. There's one caveat. If you use these endpoints to do stuff, so if when I say a metadata publish, what I mean is the definition of the model gets pushed, which is a really small file, and then you can put data inside of it.
So it's like an empty container, right? You've defined the container and all of its boundaries and you put it in the PowerBI service. This takes half a second to run and I can say, "Wait, I want to change this container slightly. Push that out and I don't need to push all the data." You then refresh this object to fill the container up with useful information. Most large PowerBI deployments I worked on, you're going to end up using these endpoints, but once you do, you are locked out of desktop as an editing tool for your semantic model.
Some people are really comfortable with that. I worked in tabular editor for almost all of the serious work that I did, right? But if you're not comfortable with that, right, this is the time to not do this thing because you're going to be locked out of using PowerBI desktop if you push through this method. Okay, I'm running out of I have seven minutes left. So, uh, you can publish. You get the value of comments. You can set up automatic aggregations.
Again, I have a I think often about direct query mode because I spend a lot of my time helping clients set that up. So, you can go into semantic model and you can say, I want you to cache common questions um, and queries that come across directly in the PowerBI service and then refresh them if they seem to be out of date. So, this is called automatic aggregations. It happens in the background. It's a black box. There are no settings.
There are no levers. There are no toggles, right? You're just saying PowerBI service, please set up some caching for me in the background to handle these things. Then you used to be able to set a percentage. You said, I wanted x% of all queries to be pre-cached for me. And the larger the cache, right, the more memory space it took up to store all of these things. So, you can set those up if you need to make those things faster.
Um, you can set the max number of connections to a given data source. This is just a admin setting that you can turn on to allow more traffic through to the underlying systems. Make sure you have if you're how many of you guys are premium PowerBI premium. Those terms are going away now, right? As we move into the world of fabric, so it's got a new skew and everything, but it used to be called PowerBI premium. Um, you can you're going to there's a monitoring app that you can download which will help you keep track of memory usage and a few other things because running out of memory in your PowerBI premium environment is awful. you don't want to do it.
It's so hard to troubleshoot and it's really hard to like kill runaway processes and stuff. So, make sure monitoring here as well. Okay, last thing that I want to talk about is making sure that um from a CI/CD perspective, you're doing a few things. It has been a long bitter road working with PowerBI and CI/CD. There were not a lot of built-in mechanisms for this when they rolled it out. They wanted business people. They sold it through Microsoft Office, right?
That's how you got your PowerBI license was through Microsoft Office. So average business person doesn't care about this. As you move into more robust deployments, you kind of have to start caring about it. So again, make sure you're using parameters in your Power Query codes so that you can just swap them out and hit refresh to move between environments. Um, same thing for those. Oh, sorry. in datab bricks if you are doing data engineering at the end of a workflow you can now pass a refresh to a semantic model in PowerBI so you've got your tables that you're going to use inside your semantic model you build them through your pipeline and then the last thing it does is it says either publish this to the PowerBI service or refresh it right so this is nice because you used to just have schedule stuff and hope that nothing took too long right you'd say I'm going to schedule this to run at 12 and hopefully my data engineering stuff is done by then and if not then I miss my window and I get no data this time.
So this is a true just waterfall when the table is loaded the very next thing that happens is it automatically refreshes your semantic models for you. There are some new file formats that have been pushed out over the last year. Have any of you begun working with the TBIDS? I don't remember the actual acronyms but there's a new uh file format for semantic models and for the reporting layer. And I think going forward, Microsoft is going to kind of double down on this and begin introducing, they've already done a good job introducing some git type integration with the workspaces.
So looking forward, right, be thinking about these things. And finally, if you have not touched tabular editor, it is a lifesaver for really large models, right? Get out there and get used to this tool. That's it. That is 51 best practices that we just went through. Um, before you go, if you would please on your app, if you liked this and you want me to come back, then I need you to I don't know when you get the link, if it's later or now or what, but go in there and be like, "Hobs was awesome.

37:20 Conclusion and resources

He's really tall and attractive and you should give him a raise and it'll like whatever you want. Um, put something in there. I would appreciate that." There is uh a link. You obviously can't click on it, but when we publish these decks, there's a blog post. It's the databicks PowerBI cheat sheet. Databicks PowerBI cheat sheet. It's a blog. Skip the blog. Just go to the bottom. At the bottom of the blog is a downloadable link to a PDF.
This PDF has all of the best practices I talked about and all of the links to all of the documentation that supports all of those best practices. Yeah. So, that's why I said you don't need to take pictures. Just go out there and steal this thing and keep it because it is a gold mine. It's like 30 years of experience from a wide variety of people put into a single document. Thank you for your time. We have three minutes for some Q&A.
If anyone has any questions and I'll be around um so come and find me, but we got a question there back and he's going to come hand you a microphone. Yeah, you guys if you can you can definitely leave. Thank you for your time.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.