1 00:00:00,040 --> 00:00:03,560 Welcome to the M365 FM podcast, where we explore the people, 2 00:00:03,560 --> 00:00:06,400 ideas and technologies shaping the future of work. 3 00:00:06,400 --> 00:00:09,880 Why a slow Power BI report is rarely a power BI problem? 4 00:00:09,880 --> 00:00:12,519 Your power BI report isn't slow because of a bug. 5 00:00:12,519 --> 00:00:15,080 It's slow because of a decision somebody made long before that 6 00:00:15,080 --> 00:00:17,839 report existed, and it's rarely the decision people blame. 7 00:00:17,839 --> 00:00:20,239 First, a slow report burns shared capacity. 8 00:00:20,239 --> 00:00:23,120 It makes people stop trusting the numbers, and it eats hours 9 00:00:23,120 --> 00:00:24,359 out of your team's week. 10 00:00:24,359 --> 00:00:25,960 Power BI ships with tools built 11 00:00:25,960 --> 00:00:27,399 for exactly this, and the most 12 00:00:27,399 --> 00:00:28,800 useful one is already sitting in 13 00:00:28,800 --> 00:00:30,179 your ribbon waiting for you to 14 00:00:30,179 --> 00:00:31,199 press record. 15 00:00:31,199 --> 00:00:34,640 Welcome to the M365 FM podcast, where we explore the people, 16 00:00:34,640 --> 00:00:37,200 ideas, and technologies shaping the future of work. 17 00:00:37,200 --> 00:00:38,320 By the end, you'll know the four 18 00:00:38,320 --> 00:00:39,759 layers where a report gets fast 19 00:00:39,759 --> 00:00:41,039 or slow, and the one that 20 00:00:41,039 --> 00:00:42,420 explains most of the slowness 21 00:00:42,420 --> 00:00:43,200 you're seeing. 22 00:00:43,200 --> 00:00:45,960 Most of us know the feeling from the other side of the screen. 23 00:00:45,960 --> 00:00:47,399 You open a dashboard, you move a 24 00:00:47,399 --> 00:00:48,920 filter, and the spinner sits 25 00:00:48,920 --> 00:00:51,140 there for 10s then twenty, you 26 00:00:51,140 --> 00:00:52,240 click again because maybe it 27 00:00:52,240 --> 00:00:53,359 didn't register and you've made 28 00:00:53,359 --> 00:00:54,079 it worse. 29 00:00:54,079 --> 00:00:56,399 Somewhere in the building, a refresh is grinding through the 30 00:00:56,399 --> 00:00:58,280 night, and by the time you arrive in the morning, the 31 00:00:58,280 --> 00:00:59,719 numbers still aren't ready. 32 00:00:59,719 --> 00:01:00,854 It's easy to assume that's a 33 00:01:00,854 --> 00:01:02,710 bug, or that power BI is slow by 34 00:01:02,710 --> 00:01:04,069 nature, it's almost never either 35 00:01:04,069 --> 00:01:05,829 one slowness in power BI comes 36 00:01:05,829 --> 00:01:07,030 from choices that were made 37 00:01:07,030 --> 00:01:08,390 somewhere along the way, and 38 00:01:08,390 --> 00:01:09,629 those choices sit at four 39 00:01:09,629 --> 00:01:10,750 different layers of the same 40 00:01:10,750 --> 00:01:11,230 system. 41 00:01:11,230 --> 00:01:12,510 Layer one is the data source. 42 00:01:12,510 --> 00:01:14,109 Wherever the numbers really live. 43 00:01:14,109 --> 00:01:15,510 Layer two is the model, which is 44 00:01:15,510 --> 00:01:17,030 how power BI sees the shape of 45 00:01:17,030 --> 00:01:18,030 that data. 46 00:01:18,030 --> 00:01:20,549 It holds the relationships and the calculations behind every 47 00:01:20,549 --> 00:01:21,510 number you see. 48 00:01:21,510 --> 00:01:24,109 Layer three is the report itself, the pages and the 49 00:01:24,109 --> 00:01:25,390 visuals you click on. 50 00:01:25,390 --> 00:01:28,310 Layer four is the environment, which means the shared computing 51 00:01:28,310 --> 00:01:31,409 power your whole workspace draws on and how far the data has to 52 00:01:31,409 --> 00:01:32,590 travel to reach it. 53 00:01:32,590 --> 00:01:35,469 When a report feels slow, people start at layer three because 54 00:01:35,469 --> 00:01:36,790 that's the part they can see. 55 00:01:36,790 --> 00:01:39,670 The real cause usually sits further down, and one of those 56 00:01:39,670 --> 00:01:42,510 four layers is behind most of the slow reports you will ever 57 00:01:42,510 --> 00:01:43,870 meet in your working life. 58 00:01:43,870 --> 00:01:44,989 By the end of this, you'll know 59 00:01:44,989 --> 00:01:46,790 which one what people mean by a 60 00:01:46,790 --> 00:01:47,989 performance optimizer. 61 00:01:47,989 --> 00:01:49,150 The name sounds like a product 62 00:01:49,150 --> 00:01:50,150 you could buy, and that's the 63 00:01:50,150 --> 00:01:51,549 first thing worth clearing up in 64 00:01:51,549 --> 00:01:52,069 power BI. 65 00:01:52,069 --> 00:01:54,469 A performance optimizer is a job rather than a button. 66 00:01:54,469 --> 00:01:57,230 It's a job somebody on your team is already doing, whether they 67 00:01:57,230 --> 00:01:58,189 know it or not. 68 00:01:58,189 --> 00:01:59,829 Think about what you'd hire someone to do. 69 00:01:59,829 --> 00:02:01,090 They would open your slow report 70 00:02:01,090 --> 00:02:02,109 and watch where the seconds 71 00:02:02,109 --> 00:02:02,790 land. 72 00:02:02,790 --> 00:02:03,750 Then they'd work out whether the 73 00:02:03,750 --> 00:02:04,909 problem is the query, the 74 00:02:04,909 --> 00:02:06,030 drawing of the chart, or the 75 00:02:06,030 --> 00:02:07,069 data underneath. 76 00:02:07,069 --> 00:02:09,110 Change one thing and measure again. 77 00:02:09,110 --> 00:02:12,430 That loop of measure, change and measure again is the entire job. 78 00:02:12,430 --> 00:02:13,789 Microsoft gives you tools for 79 00:02:13,789 --> 00:02:15,389 every stage of that loop, which 80 00:02:15,389 --> 00:02:16,789 is why the name gets attached to 81 00:02:16,789 --> 00:02:18,069 a toolkit instead of a single 82 00:02:18,069 --> 00:02:18,349 switch. 83 00:02:18,349 --> 00:02:20,469 Some of those tools are free and already installed. 84 00:02:20,469 --> 00:02:22,189 Some come with a paid capacity. 85 00:02:22,189 --> 00:02:24,509 All of them do the same thing in the end, which is to help you 86 00:02:24,509 --> 00:02:26,750 find the bottleneck and then prove you fixed it. 87 00:02:26,750 --> 00:02:29,469 Ownership is the part that saves you the most frustration, and 88 00:02:29,469 --> 00:02:32,310 some of these levers belong to whoever builds the report, which 89 00:02:32,310 --> 00:02:33,189 is often you. 90 00:02:33,189 --> 00:02:35,550 Some belong to whoever owns the model, which might be a 91 00:02:35,550 --> 00:02:38,509 completely different person, and some belong to whoever runs the 92 00:02:38,509 --> 00:02:41,069 capacity, which is frequently an admin you've never met. 93 00:02:41,069 --> 00:02:42,150 Knowing which lever is even 94 00:02:42,150 --> 00:02:43,509 yours to pull keeps you from 95 00:02:43,509 --> 00:02:44,669 fixing the wrong thing for a 96 00:02:44,669 --> 00:02:45,150 week. 97 00:02:45,150 --> 00:02:46,270 Picture a sales dashboard that 98 00:02:46,270 --> 00:02:47,349 feels slow in three different 99 00:02:47,349 --> 00:02:47,870 ways. 100 00:02:47,870 --> 00:02:49,430 The page takes ages to open, 101 00:02:49,430 --> 00:02:50,550 which points at the report 102 00:02:50,550 --> 00:02:51,150 layer. 103 00:02:51,150 --> 00:02:52,729 A single chart takes ages to 104 00:02:52,729 --> 00:02:54,310 update when you change a filter, 105 00:02:54,310 --> 00:02:55,870 which points at the model, and 106 00:02:55,870 --> 00:02:56,870 the whole thing crawls at four 107 00:02:56,870 --> 00:02:57,710 o'clock in the afternoon, but 108 00:02:57,710 --> 00:02:58,789 runs fine at eight in the 109 00:02:58,789 --> 00:02:59,110 morning. 110 00:02:59,110 --> 00:03:02,060 That points at the environment because four in the afternoon is 111 00:03:02,060 --> 00:03:05,099 when the entire company asks for exactly the same numbers at the 112 00:03:05,099 --> 00:03:06,099 very same moment. 113 00:03:06,099 --> 00:03:07,259 That's one dashboard with three 114 00:03:07,259 --> 00:03:08,300 different causes and three 115 00:03:08,300 --> 00:03:09,180 different owners. 116 00:03:09,180 --> 00:03:12,819 Which is exactly why blaming power BI never fixes anything. 117 00:03:12,819 --> 00:03:15,139 It's the map for the rest of this episode, and we'll walk it 118 00:03:15,139 --> 00:03:18,020 in order, starting with the part you can open today. 119 00:03:18,020 --> 00:03:20,020 The optimizer already sitting in your ribbon. 120 00:03:20,020 --> 00:03:22,900 If you use power BI desktop, you already have a real performance 121 00:03:22,900 --> 00:03:25,300 tool installed and it doesn't cost a thing. 122 00:03:25,300 --> 00:03:26,740 It lives on the optimize ribbon 123 00:03:26,740 --> 00:03:28,259 and the one to meet first is 124 00:03:28,259 --> 00:03:29,460 Performance Analyzer, which has 125 00:03:29,460 --> 00:03:30,699 been sitting there quietly the 126 00:03:30,699 --> 00:03:31,419 whole time. 127 00:03:31,419 --> 00:03:32,900 Performance analyzer is a recorder. 128 00:03:32,900 --> 00:03:34,780 You press start and then you use 129 00:03:34,780 --> 00:03:35,939 the report the way a normal 130 00:03:35,939 --> 00:03:36,500 person would. 131 00:03:36,500 --> 00:03:38,180 You click a slicer, you change a filter. 132 00:03:38,180 --> 00:03:39,340 You open a page. 133 00:03:39,340 --> 00:03:42,099 Every time a visual loads the recorder writes down how long it 134 00:03:42,099 --> 00:03:44,500 took and where the time went inside that load. 135 00:03:44,500 --> 00:03:46,699 It sorts that time into a few plain buckets. 136 00:03:46,699 --> 00:03:48,139 One bucket is the query, which 137 00:03:48,139 --> 00:03:49,460 is the time power BI spent 138 00:03:49,460 --> 00:03:50,340 asking the model for the 139 00:03:50,340 --> 00:03:51,099 numbers. 140 00:03:51,099 --> 00:03:54,020 Another is the visual display, which is the time spent drawing 141 00:03:54,020 --> 00:03:55,379 the chart on screen. 142 00:03:55,379 --> 00:03:56,780 A third catches everything else, 143 00:03:56,780 --> 00:03:58,419 like one visual waiting in line 144 00:03:58,419 --> 00:03:59,740 behind another one, while the 145 00:03:59,740 --> 00:04:01,020 rest of the page finishes its 146 00:04:01,020 --> 00:04:02,569 own work when a single bucket 147 00:04:02,569 --> 00:04:03,449 clearly dominates. 148 00:04:03,449 --> 00:04:04,729 You found your suspect. 149 00:04:04,729 --> 00:04:07,289 One thing to understand, and it's the part people get wrong. 150 00:04:07,289 --> 00:04:09,650 Performance analyzer tells you where the time goes. 151 00:04:09,650 --> 00:04:10,770 It doesn't tell you how to fix 152 00:04:10,770 --> 00:04:12,050 it, which is why the tool on its 153 00:04:12,050 --> 00:04:13,250 own has never rescued a slow 154 00:04:13,250 --> 00:04:13,810 report. 155 00:04:13,810 --> 00:04:16,610 That judgment is still yours or whoever owns the model. 156 00:04:16,610 --> 00:04:17,769 The ribbon also has something 157 00:04:17,769 --> 00:04:19,410 called pause visuals, and if you 158 00:04:19,410 --> 00:04:21,029 build reports, it will save you 159 00:04:21,029 --> 00:04:21,689 real time. 160 00:04:21,689 --> 00:04:24,149 Normally, every time you drag a field onto a chart, power BI 161 00:04:24,149 --> 00:04:25,129 runs a fresh query. 162 00:04:25,129 --> 00:04:27,529 Do that twenty times and you've waited on twenty queries. 163 00:04:27,529 --> 00:04:29,310 When you pause, the report stops 164 00:04:29,310 --> 00:04:31,129 asking for data, and each chart 165 00:04:31,129 --> 00:04:32,569 shows a note that it has pending 166 00:04:32,569 --> 00:04:33,009 changes. 167 00:04:33,009 --> 00:04:34,850 You make all your edits first, then refresh. 168 00:04:34,850 --> 00:04:35,170 Once. 169 00:04:35,170 --> 00:04:36,569 There are optimization presets 170 00:04:36,569 --> 00:04:38,009 too, and they amount to three 171 00:04:38,009 --> 00:04:39,629 moods query reduction suits 172 00:04:39,629 --> 00:04:40,689 reports that talk to a live 173 00:04:40,689 --> 00:04:42,009 database, and it turns off the 174 00:04:42,009 --> 00:04:43,250 automatic cross filtering that 175 00:04:43,250 --> 00:04:44,449 fires a new query on every 176 00:04:44,449 --> 00:04:44,850 click. 177 00:04:44,850 --> 00:04:47,730 Interactivity is the opposite, and it's the default because it 178 00:04:47,730 --> 00:04:48,810 keeps everything instant. 179 00:04:48,810 --> 00:04:51,850 For reports that hold their own copy of the data, customize 180 00:04:51,850 --> 00:04:54,370 opens the settings so you can pick and choose one more small 181 00:04:54,370 --> 00:04:55,290 trick while we're here. 182 00:04:55,290 --> 00:04:58,689 If a page has a pile of slices, you can add an apply All and a 183 00:04:58,689 --> 00:04:59,810 clear all button. 184 00:04:59,810 --> 00:05:00,970 People then set every filter 185 00:05:00,970 --> 00:05:02,290 they want and apply the lot in 186 00:05:02,290 --> 00:05:02,930 one click. 187 00:05:02,930 --> 00:05:05,649 That beats firing off a separate query for each change and 188 00:05:05,649 --> 00:05:06,970 waiting on every one of them. 189 00:05:06,970 --> 00:05:09,810 All of that is useful, and none of it is where most slowness 190 00:05:09,810 --> 00:05:12,449 comes from, because the ribbon only measures the problem. 191 00:05:12,449 --> 00:05:15,129 The recorder keeps pointing at something underneath the report, 192 00:05:15,129 --> 00:05:16,889 and that's where we go next. 193 00:05:16,889 --> 00:05:18,930 The biggest lever sits under the report. 194 00:05:18,930 --> 00:05:21,290 Everything you click in a report is served by the model 195 00:05:21,290 --> 00:05:24,290 underneath it, and a badly shaped model will defeat every 196 00:05:24,290 --> 00:05:26,009 attempt at tidying the report. 197 00:05:26,009 --> 00:05:27,209 A model, in plain terms, is 198 00:05:27,209 --> 00:05:28,370 power by his own view of your 199 00:05:28,370 --> 00:05:29,009 data. 200 00:05:29,009 --> 00:05:30,290 It holds the tables, the 201 00:05:30,290 --> 00:05:32,009 relationships between them, and 202 00:05:32,009 --> 00:05:33,610 the calculations that turn rows 203 00:05:33,610 --> 00:05:35,089 into numbers you care about, 204 00:05:35,089 --> 00:05:36,649 like total sales or last month's 205 00:05:36,649 --> 00:05:37,250 margin. 206 00:05:37,250 --> 00:05:38,689 Microsoft calls it a semantic 207 00:05:38,689 --> 00:05:40,129 model, and the name is the only 208 00:05:40,129 --> 00:05:41,089 fancy part. 209 00:05:41,089 --> 00:05:42,730 One idea makes most models fast 210 00:05:42,730 --> 00:05:44,250 or slow, and it comes down to 211 00:05:44,250 --> 00:05:45,529 the shape of the data rather 212 00:05:45,529 --> 00:05:46,810 than the charts sitting on top 213 00:05:46,810 --> 00:05:47,329 of it. 214 00:05:47,329 --> 00:05:48,689 Imagine your data as a single, 215 00:05:48,689 --> 00:05:50,389 enormous list of events every 216 00:05:50,389 --> 00:05:51,449 sale, every click, every 217 00:05:51,449 --> 00:05:52,889 invoice, all stacked in one 218 00:05:52,889 --> 00:05:53,569 table. 219 00:05:53,569 --> 00:05:56,209 On its own, that list is painful to work with because everything 220 00:05:56,209 --> 00:05:57,209 is jammed together. 221 00:05:57,209 --> 00:06:00,009 So you pull the descriptions out into their own small lists one 222 00:06:00,009 --> 00:06:02,279 for products, one for stores, one for dates. 223 00:06:02,279 --> 00:06:05,240 Now the big table holds mostly numbers and codes, and the small 224 00:06:05,240 --> 00:06:06,879 tables hold the explanations. 225 00:06:06,879 --> 00:06:08,319 That shape is called a star schema. 226 00:06:08,319 --> 00:06:10,920 It's the difference between a model that answers in a blink, 227 00:06:10,920 --> 00:06:13,279 and one that grinds away at every single click. 228 00:06:13,279 --> 00:06:14,800 Shape matters so much because of 229 00:06:14,800 --> 00:06:16,360 how much data power BI has to 230 00:06:16,360 --> 00:06:16,759 touch. 231 00:06:16,759 --> 00:06:20,199 For every click, a well shaped model finds what it needs by 232 00:06:20,199 --> 00:06:23,079 checking a small table first and then grabbing only the matching 233 00:06:23,079 --> 00:06:24,879 rows it needs from the big one. 234 00:06:24,879 --> 00:06:27,560 A poorly shaped model forces the engine to sweep the whole thing. 235 00:06:27,560 --> 00:06:30,319 Then there's the quiet budget Killer, which is the number of 236 00:06:30,319 --> 00:06:32,120 distinct values in a column. 237 00:06:32,120 --> 00:06:35,439 A column holding ten product categories costs almost nothing. 238 00:06:35,439 --> 00:06:36,620 A column holding a unique 239 00:06:36,620 --> 00:06:37,920 timestamp for every one of 240 00:06:37,920 --> 00:06:39,319 twenty million rows costs a 241 00:06:39,319 --> 00:06:40,120 great deal. 242 00:06:40,120 --> 00:06:42,319 The engine has to track every one of those values. 243 00:06:42,319 --> 00:06:44,079 You don't need to memorize the term for it. 244 00:06:44,079 --> 00:06:45,279 You just need to notice when a 245 00:06:45,279 --> 00:06:46,360 column has a huge number of 246 00:06:46,360 --> 00:06:47,519 different values in it, because 247 00:06:47,519 --> 00:06:48,480 that's where your memory is 248 00:06:48,480 --> 00:06:48,800 going. 249 00:06:48,800 --> 00:06:50,279 The other habit is filtering a 250 00:06:50,279 --> 00:06:51,519 table visual that tries to show 251 00:06:51,519 --> 00:06:52,879 everything, will drag an 252 00:06:52,879 --> 00:06:54,199 enormous number of rows into 253 00:06:54,199 --> 00:06:55,519 memory, and that data gets 254 00:06:55,519 --> 00:06:56,839 uncompressed and rebuilt on 255 00:06:56,839 --> 00:06:57,639 every refresh. 256 00:06:57,639 --> 00:07:00,560 The fix is to cap it, show the top ten thousand rows or the top 257 00:07:00,560 --> 00:07:03,470 one hundred, and the experience for the person reading barely 258 00:07:03,470 --> 00:07:05,829 changes while the memory use falls off a cliff. 259 00:07:05,829 --> 00:07:07,230 Here's where I have to be straight with you. 260 00:07:07,230 --> 00:07:09,829 Some of these fixes need someone who can edit the model and that 261 00:07:09,829 --> 00:07:10,550 might not be used. 262 00:07:10,550 --> 00:07:13,110 Some of them also take time and testing to get right. 263 00:07:13,110 --> 00:07:14,310 If you learn one thing about 264 00:07:14,310 --> 00:07:16,069 power BI performance, make it 265 00:07:16,069 --> 00:07:16,709 this. 266 00:07:16,709 --> 00:07:19,629 Ask what shape the model is in before you ever blame the report 267 00:07:19,629 --> 00:07:20,629 in front of you. 268 00:07:20,629 --> 00:07:22,910 Import direct query and direct Lake. 269 00:07:22,910 --> 00:07:25,670 Every model has to answer one question before anything else. 270 00:07:25,670 --> 00:07:26,910 And that question is where the 271 00:07:26,910 --> 00:07:28,449 data lives when somebody clicks 272 00:07:28,449 --> 00:07:28,990 a chart. 273 00:07:28,990 --> 00:07:30,189 There are three answers in power 274 00:07:30,189 --> 00:07:31,230 BI and they work like three 275 00:07:31,230 --> 00:07:32,189 delivery methods. 276 00:07:32,189 --> 00:07:33,509 The first is import and it 277 00:07:33,509 --> 00:07:34,709 behaves like stocking your own 278 00:07:34,709 --> 00:07:35,589 pantry. 279 00:07:35,589 --> 00:07:38,430 Power BI copies the data into its own file storage and reads 280 00:07:38,430 --> 00:07:41,350 from that copy, so every click is quick and predictable. 281 00:07:41,350 --> 00:07:42,949 What you pay for is a copy that 282 00:07:42,949 --> 00:07:43,990 has to be refreshed on a 283 00:07:43,990 --> 00:07:45,509 schedule, and it takes memory, 284 00:07:45,509 --> 00:07:47,149 which means a big model can get 285 00:07:47,149 --> 00:07:47,990 expensive. 286 00:07:47,990 --> 00:07:49,149 The second is direct query, 287 00:07:49,149 --> 00:07:50,350 which is closer to phoning the 288 00:07:50,350 --> 00:07:51,389 shop every time you need 289 00:07:51,389 --> 00:07:52,029 something. 290 00:07:52,029 --> 00:07:53,509 Power BI keeps no copy, and 291 00:07:53,509 --> 00:07:54,910 instead every click sends a 292 00:07:54,910 --> 00:07:55,790 question to the original 293 00:07:55,790 --> 00:07:56,910 database. 294 00:07:56,910 --> 00:07:57,990 Nothing is ever out of date, 295 00:07:57,990 --> 00:07:59,189 which matters for an operations 296 00:07:59,189 --> 00:08:00,310 board where the number has to be 297 00:08:00,310 --> 00:08:00,509 true. 298 00:08:00,509 --> 00:08:03,269 Right now the trade traders that the speed of your report becomes 299 00:08:03,269 --> 00:08:04,629 the speed of that database. 300 00:08:04,629 --> 00:08:07,589 And databases aren't usually built for thousands of people 301 00:08:07,589 --> 00:08:08,430 clicking at once. 302 00:08:08,430 --> 00:08:11,189 The third one is newer and it only exists in Microsoft Fabric. 303 00:08:11,189 --> 00:08:13,589 It's called Direct Lake, and the idea is clever. 304 00:08:13,589 --> 00:08:14,230 In fabric. 305 00:08:14,230 --> 00:08:15,470 Your data already sits in one 306 00:08:15,470 --> 00:08:16,790 Lake, which is Microsoft's 307 00:08:16,790 --> 00:08:18,350 single storage layer for all of 308 00:08:18,350 --> 00:08:19,629 the analytics data across the 309 00:08:19,629 --> 00:08:20,750 entire platform. 310 00:08:20,750 --> 00:08:23,350 Direct Lake reads straight from there because it uses the same 311 00:08:23,350 --> 00:08:25,069 fast engine that import uses. 312 00:08:25,069 --> 00:08:27,310 It behaves like a copy you never had to make. 313 00:08:27,310 --> 00:08:29,149 You just never pay the price of storing one. 314 00:08:29,149 --> 00:08:30,389 The refresh is the clever part. 315 00:08:30,389 --> 00:08:33,070 Instead of copying the data, it updates the pointers to the 316 00:08:33,070 --> 00:08:35,909 latest files, which can take seconds instead of hours. 317 00:08:35,909 --> 00:08:38,309 The limits here are real, so let's be straight about them. 318 00:08:38,309 --> 00:08:40,389 Direct Lake needs a fabric capacity, which is a paid 319 00:08:40,389 --> 00:08:43,389 subscription, and it needs your tables to be well organized. 320 00:08:43,389 --> 00:08:44,629 It also has guardrails that 321 00:08:44,629 --> 00:08:45,789 depend on the size of that 322 00:08:45,789 --> 00:08:46,509 capacity. 323 00:08:46,509 --> 00:08:49,909 So on a small one, you can hit a ceiling on rows or model size. 324 00:08:49,909 --> 00:08:51,110 And when it can't read a table 325 00:08:51,110 --> 00:08:52,590 directly, it can quietly fall 326 00:08:52,590 --> 00:08:54,190 back to the slower direct query 327 00:08:54,190 --> 00:08:54,590 path. 328 00:08:54,590 --> 00:08:55,549 That means the speed you were 329 00:08:55,549 --> 00:08:56,629 promised isn't always the speed 330 00:08:56,629 --> 00:08:57,350 you get. 331 00:08:57,350 --> 00:09:00,149 The choice usually comes down to how fresh the data has to be. 332 00:09:00,149 --> 00:09:01,345 A nightly sales report that 333 00:09:01,345 --> 00:09:02,899 nobody touches during the day is 334 00:09:02,899 --> 00:09:03,100 happy. 335 00:09:03,100 --> 00:09:05,779 An import a live operations board that has to show the 336 00:09:05,779 --> 00:09:08,259 current minute once direct query or direct lake. 337 00:09:08,259 --> 00:09:09,539 If you're already in fabric with 338 00:09:09,539 --> 00:09:10,860 a big lake house, direct Lake is 339 00:09:10,860 --> 00:09:11,500 worth testing. 340 00:09:11,500 --> 00:09:15,019 Just go in knowing it isn't free and it isn't automatic. 341 00:09:15,019 --> 00:09:16,860 Fewer visuals, tighter filters. 342 00:09:16,860 --> 00:09:18,019 Now we're back at the layer you 343 00:09:18,019 --> 00:09:19,179 can touch without asking 344 00:09:19,179 --> 00:09:20,100 anyone's permission. 345 00:09:20,100 --> 00:09:21,779 And it's the report itself. 346 00:09:21,779 --> 00:09:24,299 Every visual on a page is its own little worker. 347 00:09:24,299 --> 00:09:27,820 When the page opens, each chart sends its own query and they all 348 00:09:27,820 --> 00:09:29,059 queue up to be served. 349 00:09:29,059 --> 00:09:30,500 Ten visuals means ten queries 350 00:09:30,500 --> 00:09:31,299 competing for the same 351 00:09:31,299 --> 00:09:31,940 attention. 352 00:09:31,940 --> 00:09:33,259 Twenty means the page feels 353 00:09:33,259 --> 00:09:34,379 heavy no matter how good the 354 00:09:34,379 --> 00:09:35,580 model underneath it happens to 355 00:09:35,580 --> 00:09:35,940 be. 356 00:09:35,940 --> 00:09:37,100 The fix is uncomfortable for 357 00:09:37,100 --> 00:09:38,100 anyone who loves a packed 358 00:09:38,100 --> 00:09:39,980 dashboard, use fewer visuals per 359 00:09:39,980 --> 00:09:41,019 page and move the detail 360 00:09:41,019 --> 00:09:41,820 somewhere else. 361 00:09:41,820 --> 00:09:43,539 Power BI gives you two ways to do that. 362 00:09:43,539 --> 00:09:45,059 One is a drill through page, 363 00:09:45,059 --> 00:09:46,259 which is a separate page that 364 00:09:46,259 --> 00:09:47,500 opens only when someone clicks 365 00:09:47,500 --> 00:09:49,059 on a specific row of a specific 366 00:09:49,059 --> 00:09:49,659 chart. 367 00:09:49,659 --> 00:09:52,399 The other is a tooltip page, which appears when someone 368 00:09:52,399 --> 00:09:53,820 hovers over a chart. 369 00:09:53,820 --> 00:09:55,179 Both let you keep the depth 370 00:09:55,179 --> 00:09:56,539 without paying for it on every 371 00:09:56,539 --> 00:09:57,500 page load. 372 00:09:57,500 --> 00:09:58,820 Next, watch out for the visuals 373 00:09:58,820 --> 00:10:00,820 that show far too many rows a 374 00:10:00,820 --> 00:10:02,210 table with hundreds of thousands 375 00:10:02,210 --> 00:10:03,529 of rows is one of the most 376 00:10:03,529 --> 00:10:04,769 expensive things you can put on 377 00:10:04,769 --> 00:10:05,409 a page. 378 00:10:05,409 --> 00:10:08,289 All of that data has to be fetched, uncompressed and drawn. 379 00:10:08,289 --> 00:10:11,090 If the reader only ever looks at the top twenty rows, then the 380 00:10:11,090 --> 00:10:14,450 visual should only ever ask the model for the top twenty rows. 381 00:10:14,450 --> 00:10:16,250 That leads to the mistake almost everyone makes. 382 00:10:16,250 --> 00:10:19,450 At least once you build a table, you leave it unfiltered and it 383 00:10:19,450 --> 00:10:21,970 tries to show every single row you have at once. 384 00:10:21,970 --> 00:10:25,009 That works on your test data and falls over the moment real data 385 00:10:25,009 --> 00:10:25,889 shows up in it. 386 00:10:25,889 --> 00:10:27,250 The cure is to set a limit in 387 00:10:27,250 --> 00:10:28,730 the filter pane and set it a 388 00:10:28,730 --> 00:10:29,809 little higher than anyone would 389 00:10:29,809 --> 00:10:30,450 ever scroll. 390 00:10:30,450 --> 00:10:32,009 One warning about custom visuals 391 00:10:32,009 --> 00:10:33,169 because they look like a free 392 00:10:33,169 --> 00:10:33,610 upgrade. 393 00:10:33,610 --> 00:10:36,250 They aren't automatically fast, and a badly built one can drag 394 00:10:36,250 --> 00:10:38,629 down a whole page along with every other visual sitting on 395 00:10:38,629 --> 00:10:39,690 that page next to it. 396 00:10:39,690 --> 00:10:41,809 Test it with real data before you ship it. 397 00:10:41,809 --> 00:10:44,490 The limit here is that cutting visuals can hurt the story if 398 00:10:44,490 --> 00:10:45,730 you cut the wrong ones. 399 00:10:45,730 --> 00:10:48,529 The wrong one is usually the chart somebody opens every day. 400 00:10:48,529 --> 00:10:51,049 Measure before and after and let the recorder tell you whether 401 00:10:51,049 --> 00:10:52,529 you helped the layout. 402 00:10:52,529 --> 00:10:55,330 Nobody thinks about now for the layer nobody sees. 403 00:10:55,330 --> 00:10:58,210 And that can quietly undo everything you just fixed. 404 00:10:58,210 --> 00:11:00,570 This is the environment and it comes down to three things that 405 00:11:00,570 --> 00:11:03,370 are easy to ignore shared compute gateways and distance. 406 00:11:03,370 --> 00:11:06,250 Start with compute in power BI and fabric. 407 00:11:06,250 --> 00:11:09,090 Your workspace runs on something called a capacity, and that 408 00:11:09,090 --> 00:11:10,129 capacity is shared. 409 00:11:10,129 --> 00:11:11,690 Every report, every refresh, and 410 00:11:11,690 --> 00:11:13,169 every background job in it draws 411 00:11:13,169 --> 00:11:14,490 from the same pool of processing 412 00:11:14,490 --> 00:11:15,049 power. 413 00:11:15,049 --> 00:11:17,889 One enormous report can slow down everybody else, and your 414 00:11:17,889 --> 00:11:20,370 fast report can feel slow, simply because it happens to be 415 00:11:20,370 --> 00:11:21,809 sitting next to a heavy one. 416 00:11:21,809 --> 00:11:23,490 Fabric handles part of this for you. 417 00:11:23,490 --> 00:11:26,330 It spreads out the accounting for background work so that a 418 00:11:26,330 --> 00:11:28,690 single heavy job doesn't instantly blow the budget. 419 00:11:28,690 --> 00:11:31,049 It does that without changing how fast your jobs run. 420 00:11:31,049 --> 00:11:32,210 When demand stays above the 421 00:11:32,210 --> 00:11:33,809 limit for too long, the server 422 00:11:33,809 --> 00:11:35,330 starts delaying work to protect 423 00:11:35,330 --> 00:11:35,929 the rest. 424 00:11:35,929 --> 00:11:38,289 And that's the moment people start seeing timeouts. 425 00:11:38,289 --> 00:11:39,649 When a capacity comes under real 426 00:11:39,649 --> 00:11:40,690 strain, there are only three 427 00:11:40,690 --> 00:11:41,090 answers. 428 00:11:41,090 --> 00:11:42,409 You make the work lighter, you 429 00:11:42,409 --> 00:11:44,009 buy a bigger capacity, or you 430 00:11:44,009 --> 00:11:45,049 move some of the work onto a 431 00:11:45,049 --> 00:11:45,730 different one. 432 00:11:45,730 --> 00:11:47,049 Microsoft's own guidance lists 433 00:11:47,049 --> 00:11:48,490 exactly those three, and the 434 00:11:48,490 --> 00:11:49,370 third one is usually the 435 00:11:49,370 --> 00:11:50,409 cheapest, though it does take 436 00:11:50,409 --> 00:11:51,289 somebody willing to do the 437 00:11:51,289 --> 00:11:51,889 moving. 438 00:11:51,889 --> 00:11:54,809 The tool for seeing all of this is the Capacity Metrics app. 439 00:11:54,809 --> 00:11:55,970 It works as a dashboard about 440 00:11:55,970 --> 00:11:57,090 your dashboard and about 441 00:11:57,090 --> 00:11:57,850 everybody else's. 442 00:11:57,850 --> 00:11:58,769 At the same time. 443 00:11:58,769 --> 00:12:01,169 It shows which items are eating the compute and when. 444 00:12:01,169 --> 00:12:02,399 So you can find the report 445 00:12:02,399 --> 00:12:03,360 that's making everyone else 446 00:12:03,360 --> 00:12:04,000 miserable. 447 00:12:04,000 --> 00:12:06,679 Then there's distance, which sounds trivial and isn't. 448 00:12:06,679 --> 00:12:09,320 Your power BI tenant lives in a specific region, and every 449 00:12:09,320 --> 00:12:10,399 request routes there. 450 00:12:10,399 --> 00:12:11,720 If your data source sits on a 451 00:12:11,720 --> 00:12:12,799 server in another part of the 452 00:12:12,799 --> 00:12:14,279 world, then every click pays a 453 00:12:14,279 --> 00:12:15,120 travel tax. 454 00:12:15,120 --> 00:12:16,600 Your gateway is sitting somewhere else. 455 00:12:16,600 --> 00:12:18,000 Again, only makes it worse. 456 00:12:18,000 --> 00:12:19,519 Microsoft's advice is simple, 457 00:12:19,519 --> 00:12:20,639 which is to keep your data 458 00:12:20,639 --> 00:12:22,200 sources, your gateway, and your 459 00:12:22,200 --> 00:12:23,919 capacity as close together as 460 00:12:23,919 --> 00:12:25,360 you can, ideally in the same 461 00:12:25,360 --> 00:12:26,159 region. 462 00:12:26,159 --> 00:12:27,240 The awkward part about this 463 00:12:27,240 --> 00:12:28,399 layer is that it's usually an 464 00:12:28,399 --> 00:12:29,620 admins decision rather than 465 00:12:29,620 --> 00:12:30,240 yours. 466 00:12:30,240 --> 00:12:32,759 That's exactly why the case has to be made with numbers. 467 00:12:32,759 --> 00:12:34,879 Your job is often to measure carefully and hand over the 468 00:12:34,879 --> 00:12:37,000 evidence, which is far more persuasive than saying the 469 00:12:37,000 --> 00:12:40,519 dashboard feels slow and copilot as a first pass optimizer. 470 00:12:40,519 --> 00:12:42,120 Now for the newest lever, which 471 00:12:42,120 --> 00:12:43,480 is the one people ask about most 472 00:12:43,480 --> 00:12:44,720 and the one most likely to be 473 00:12:44,720 --> 00:12:45,519 oversold. 474 00:12:45,519 --> 00:12:48,559 In March of twenty twenty six, Microsoft made a striking claim. 475 00:12:48,559 --> 00:12:49,720 According to Microsoft, a 476 00:12:49,720 --> 00:12:51,240 developer with moderate power BI 477 00:12:51,240 --> 00:12:52,480 experience could now handle 478 00:12:52,480 --> 00:12:53,919 optimization work that normally 479 00:12:53,919 --> 00:12:55,879 takes sixteen to forty hours, 480 00:12:55,879 --> 00:12:57,000 with copilot doing the heavy 481 00:12:57,000 --> 00:12:57,399 lifting. 482 00:12:57,399 --> 00:12:59,799 That same work lands in under twenty minutes. 483 00:12:59,799 --> 00:13:02,230 Microsoft reported that model and refresh times dropped by 484 00:13:02,230 --> 00:13:05,110 forty five percent, and that import mode model sizes shrank 485 00:13:05,110 --> 00:13:06,389 by nearly eighty percent. 486 00:13:06,389 --> 00:13:07,950 Report Visuals, Microsoft said 487 00:13:07,950 --> 00:13:09,149 rendered twenty five percent 488 00:13:09,149 --> 00:13:09,669 faster. 489 00:13:09,669 --> 00:13:11,870 Those are big numbers, and here's the framing to keep 490 00:13:11,870 --> 00:13:12,870 attached to them. 491 00:13:12,870 --> 00:13:14,110 They come from Microsoft's own 492 00:13:14,110 --> 00:13:15,070 testing rather than an 493 00:13:15,070 --> 00:13:16,509 independent benchmark, and 494 00:13:16,509 --> 00:13:17,909 Microsoft is the company selling 495 00:13:17,909 --> 00:13:18,429 the tool. 496 00:13:18,429 --> 00:13:21,149 Treat them as a strong signal that this works, not as a 497 00:13:21,149 --> 00:13:23,830 promise about what you will see in your own tenant once real 498 00:13:23,830 --> 00:13:25,629 users start clicking. 499 00:13:25,629 --> 00:13:27,990 In practice, it reads, the report file spots the 500 00:13:27,990 --> 00:13:30,710 inefficiencies and hands you a step by step plan. 501 00:13:30,710 --> 00:13:33,990 That part is worth understanding because it means copilot is 502 00:13:33,990 --> 00:13:37,590 closer to a fast diagnostician than to an autopilot. 503 00:13:37,590 --> 00:13:39,830 It tells you what's wrong and what to change, and a person 504 00:13:39,830 --> 00:13:42,710 still has to review it and decide whether the change is 505 00:13:42,710 --> 00:13:44,389 right for the business. 506 00:13:44,389 --> 00:13:47,389 A governance layer arrived alongside it, and it matters if 507 00:13:47,389 --> 00:13:48,830 you work somewhere careful. 508 00:13:48,830 --> 00:13:51,909 Fabric IQ, which reached general availability in September of 509 00:13:51,909 --> 00:13:54,549 twenty twenty six, lets people ask business questions in 510 00:13:54,549 --> 00:13:56,629 copilot and get answers grounded in your governance. 511 00:13:56,629 --> 00:13:57,909 Power by models. 512 00:13:57,909 --> 00:14:00,750 Admins also gained a control to keep specific models out of 513 00:14:00,750 --> 00:14:02,110 those AI experiences. 514 00:14:02,110 --> 00:14:03,350 Each model carries its own 515 00:14:03,350 --> 00:14:04,830 setting for whether read only 516 00:14:04,830 --> 00:14:05,909 users can reach it through 517 00:14:05,909 --> 00:14:06,629 copilot. 518 00:14:06,629 --> 00:14:09,909 For a beginner, copilot is a useful first pass, especially 519 00:14:09,909 --> 00:14:12,509 for finding problems you'd never have spotted on your own. 520 00:14:12,509 --> 00:14:14,789 It isn't a substitute for understanding the four layers, 521 00:14:14,789 --> 00:14:16,950 because you still have to judge what it hands you. 522 00:14:16,950 --> 00:14:19,549 Judgment is the one thing it can't hand over. 523 00:14:19,549 --> 00:14:20,429 What to remember. 524 00:14:20,429 --> 00:14:21,710 And one question for you. 525 00:14:21,710 --> 00:14:23,029 Here's the short version. 526 00:14:23,029 --> 00:14:25,389 A power BI report gets fast or slow at four layers. 527 00:14:25,389 --> 00:14:27,990 The data source, the model, the report, and the environment. 528 00:14:27,990 --> 00:14:30,669 The tools to inspect them are already in your ribbon, and 529 00:14:30,669 --> 00:14:33,429 copilot can give you a head start on the diagnosis before 530 00:14:33,429 --> 00:14:34,350 you dig in yourself. 531 00:14:34,350 --> 00:14:36,309 If you remember one thing, remember this the model 532 00:14:36,309 --> 00:14:39,110 underneath the report is where most slowness is born, which is 533 00:14:39,110 --> 00:14:41,710 why the first question is never about the chart in front of you. 534 00:14:41,710 --> 00:14:43,549 It's about the shape of the data behind it. 535 00:14:43,549 --> 00:14:44,669 So here's what I want to know. 536 00:14:44,669 --> 00:14:46,070 And I read every reply. 537 00:14:46,070 --> 00:14:48,309 What's the slowest report in your world right now? 538 00:14:48,309 --> 00:14:51,309 Does it drag the moment the page loads when you click or when 539 00:14:51,309 --> 00:14:52,950 everybody logs on at once? 540 00:14:52,950 --> 00:14:55,230 Your answer tells you which layer you're standing on. 541 00:14:55,230 --> 00:14:56,629 If this made the topic feel less 542 00:14:56,629 --> 00:14:59,129 intimidating, subscribe to M365 543 00:14:59,129 --> 00:15:01,029 FM to master the tools and the 544 00:15:01,029 --> 00:15:02,590 mindset shaping the future of 545 00:15:02,590 --> 00:15:03,230 work.