1
00:00:00,040 --> 00:00:03,839
Welcome to the M365 FM podcast,
where we explore the people,

2
00:00:03,839 --> 00:00:06,639
ideas and technologies shaping
the future of work.

3
00:00:06,639 --> 00:00:09,439
Your power BI refresh isn't slow
because your report is big.

4
00:00:09,439 --> 00:00:11,039
It's slow because power BI keeps

5
00:00:11,039 --> 00:00:12,279
pulling back years of data that

6
00:00:12,279 --> 00:00:13,160
hasn't changed.

7
00:00:13,160 --> 00:00:16,239
Every table starts as one giant
block, so each refresh drags the

8
00:00:16,239 --> 00:00:17,519
whole thing across the network.

9
00:00:17,519 --> 00:00:19,940
Again, there's a setting that
fixes this and it comes down to

10
00:00:19,940 --> 00:00:21,719
two date markers and one rule.

11
00:00:21,719 --> 00:00:22,839
Get the rule wrong though, and

12
00:00:22,839 --> 00:00:24,519
your refresh does more work, not

13
00:00:24,519 --> 00:00:25,800
less.

14
00:00:25,800 --> 00:00:27,920
What incremental refresh
actually does?

15
00:00:27,920 --> 00:00:30,600
Incremental refresh is a setting
you switch on for a table.

16
00:00:30,600 --> 00:00:34,240
So power BI stops rebuilding the
whole thing on every refresh.

17
00:00:34,240 --> 00:00:36,439
The service splits that table
into smaller pieces and

18
00:00:36,439 --> 00:00:38,320
refreshes only the newest ones.

19
00:00:38,320 --> 00:00:41,000
Microsoft calls those pieces
partitions, and you never see

20
00:00:41,000 --> 00:00:43,679
them in the normal power BI
screens, because the service

21
00:00:43,679 --> 00:00:45,960
keeps them out of sight and
manages them for you.

22
00:00:45,960 --> 00:00:48,840
A model, in case the word is new
is the set of tables,

23
00:00:48,840 --> 00:00:51,880
relationships, and calculations
sitting behind your report.

24
00:00:51,880 --> 00:00:52,840
When people talk about the

25
00:00:52,840 --> 00:00:54,320
semantic model, they mean the

26
00:00:54,320 --> 00:00:55,359
engine that answers your

27
00:00:55,359 --> 00:00:56,640
report's questions.

28
00:00:56,640 --> 00:00:58,200
Think about a book you have
already read.

29
00:00:58,200 --> 00:00:59,200
If you only want to check the

30
00:00:59,200 --> 00:01:00,469
last page, you don't start again

31
00:01:00,469 --> 00:01:01,869
from page one and you don't

32
00:01:01,869 --> 00:01:03,189
reread every chapter to get

33
00:01:03,189 --> 00:01:03,670
there.

34
00:01:03,670 --> 00:01:06,790
Incremental refresh gives power
by that same shortcut, keeping

35
00:01:06,790 --> 00:01:09,349
the recent pages fresh while it
leaves the earlier chapters

36
00:01:09,349 --> 00:01:10,750
exactly where they were.

37
00:01:10,750 --> 00:01:12,150
This isn't a new data source,

38
00:01:12,150 --> 00:01:13,510
and it isn't a separate tool you

39
00:01:13,510 --> 00:01:13,950
install.

40
00:01:13,950 --> 00:01:15,150
It's a policy attached to a

41
00:01:15,150 --> 00:01:16,750
table, and the service does the

42
00:01:16,750 --> 00:01:17,269
heavy lifting.

43
00:01:17,269 --> 00:01:20,469
Once you publish, you set it up
in power BI desktop, publish

44
00:01:20,469 --> 00:01:22,909
your model and the service takes
over from there.

45
00:01:22,909 --> 00:01:25,069
The word incremental is doing
real work here.

46
00:01:25,069 --> 00:01:27,150
Each refresh adds and updates
only.

47
00:01:27,150 --> 00:01:30,269
What changed since the last one,
and it trusts that the older

48
00:01:30,269 --> 00:01:33,430
data is already correct and
already sitting in your model.

49
00:01:33,430 --> 00:01:34,950
That trust is the whole trick,

50
00:01:34,950 --> 00:01:36,109
and it's also where the mistakes

51
00:01:36,109 --> 00:01:37,209
come from, which we'll get to

52
00:01:37,209 --> 00:01:37,750
later.

53
00:01:37,750 --> 00:01:40,590
The payoff is straightforward
refreshes finish faster, they

54
00:01:40,590 --> 00:01:43,390
use less memory, and your model
can grow without dragging the

55
00:01:43,390 --> 00:01:45,709
entire history across the
network every night.

56
00:01:45,709 --> 00:01:48,790
Your visuals stay exactly the
same, and the report looks no

57
00:01:48,790 --> 00:01:51,510
different because the change
happens underneath and how much

58
00:01:51,510 --> 00:01:53,189
work each refresh has to do.

59
00:01:53,189 --> 00:01:54,189
Two things improve at once.

60
00:01:54,189 --> 00:01:55,510
When this is set up well, the

61
00:01:55,510 --> 00:01:57,030
refresh gets faster and the

62
00:01:57,030 --> 00:01:58,390
model can hold less data than

63
00:01:58,390 --> 00:01:59,469
the source because you're

64
00:01:59,469 --> 00:02:00,689
keeping only what your reports

65
00:02:00,689 --> 00:02:01,549
actually need.

66
00:02:01,549 --> 00:02:03,989
That second part is easy to
miss, and it's where the memory

67
00:02:03,989 --> 00:02:05,150
savings come from.

68
00:02:05,150 --> 00:02:07,909
The first refresh after you
publish is the slow one, because

69
00:02:07,909 --> 00:02:10,509
the service has to build the
pieces and load the history.

70
00:02:10,509 --> 00:02:13,909
After that refreshes, only touch
the recent window.

71
00:02:13,909 --> 00:02:15,349
Why hitting refresh stops

72
00:02:15,349 --> 00:02:16,710
working when you publish a

73
00:02:16,710 --> 00:02:18,310
model, every table starts life

74
00:02:18,310 --> 00:02:19,629
as one single block, and that

75
00:02:19,629 --> 00:02:21,069
one block holds every row in the

76
00:02:21,069 --> 00:02:21,590
table.

77
00:02:21,590 --> 00:02:24,590
This is the default, and for a
small table, it works fine.

78
00:02:24,590 --> 00:02:25,870
Now picture a sales table that

79
00:02:25,870 --> 00:02:27,189
grows by thousands of rows a

80
00:02:27,189 --> 00:02:27,629
day.

81
00:02:27,629 --> 00:02:30,229
After a couple of years, it
holds millions of rows and only

82
00:02:30,229 --> 00:02:32,550
a tiny slice of them change on
any given day.

83
00:02:32,550 --> 00:02:34,069
Power BI doesn't know that yet,

84
00:02:34,069 --> 00:02:35,669
so on every refresh, it reaches

85
00:02:35,669 --> 00:02:37,030
back to the source and pulls the

86
00:02:37,030 --> 00:02:38,710
whole table again, reloading

87
00:02:38,710 --> 00:02:39,789
rows that haven't moved in

88
00:02:39,789 --> 00:02:40,469
months.

89
00:02:40,469 --> 00:02:41,750
That's what makes a refresh
slow.

90
00:02:41,750 --> 00:02:43,550
It isn't the report and it isn't
the visuals.

91
00:02:43,550 --> 00:02:45,590
It's the reload of data that was
already correct.

92
00:02:45,590 --> 00:02:46,949
There's a memory cost to power

93
00:02:46,949 --> 00:02:48,270
BI keeps a copy of your model in

94
00:02:48,270 --> 00:02:49,789
memory while a refresh runs, so

95
00:02:49,789 --> 00:02:51,110
a full refresh can need roughly

96
00:02:51,110 --> 00:02:52,150
double the memory your model

97
00:02:52,150 --> 00:02:53,310
normally uses.

98
00:02:53,310 --> 00:02:54,629
Multiply that by a few large

99
00:02:54,629 --> 00:02:55,590
tables, refreshing on a

100
00:02:55,590 --> 00:02:56,949
schedule, and you start to feel

101
00:02:56,949 --> 00:02:58,699
it in the capacity A scheduled

102
00:02:58,699 --> 00:03:00,139
refresh runs on a timer, so this

103
00:03:00,139 --> 00:03:02,000
cost repeats if a full reload

104
00:03:02,000 --> 00:03:03,580
takes ninety minutes at midnight

105
00:03:03,580 --> 00:03:04,699
and another ninety minutes at

106
00:03:04,699 --> 00:03:05,979
noon, that's three hours of

107
00:03:05,979 --> 00:03:07,539
capacity time every day spent

108
00:03:07,539 --> 00:03:08,580
moving data.

109
00:03:08,580 --> 00:03:09,539
That hasn't changed.

110
00:03:09,539 --> 00:03:10,580
Nothing about the numbers in

111
00:03:10,580 --> 00:03:11,759
your report gets better for all

112
00:03:11,759 --> 00:03:12,379
that work.

113
00:03:12,379 --> 00:03:14,020
This is the wall most people
hit.

114
00:03:14,020 --> 00:03:16,659
The report was fine last year,
and this year the refresh takes

115
00:03:16,659 --> 00:03:19,860
three hours or it times out or
it fails without a clear reason.

116
00:03:19,860 --> 00:03:21,139
The data isn't the problem.

117
00:03:21,139 --> 00:03:24,259
And the way power BI loads the
data is part of the reason comes

118
00:03:24,259 --> 00:03:25,460
down to import mode.

119
00:03:25,460 --> 00:03:28,379
Power BI keeps its own copy of
your data, so that copy has to

120
00:03:28,379 --> 00:03:29,939
be refreshed to stay current.

121
00:03:29,939 --> 00:03:32,419
Incremental refresh doesn't
change that rule, and it just

122
00:03:32,419 --> 00:03:36,620
makes the copy smarter about
what actually needs updating.

123
00:03:36,620 --> 00:03:40,060
The two date markers behind
every refresh power BI needs a

124
00:03:40,060 --> 00:03:42,699
way to know which rows are new
and which rows are old.

125
00:03:42,699 --> 00:03:45,259
It figures that out with two
values you create yourself,

126
00:03:45,259 --> 00:03:47,139
named Rangestart and Rangeend.

127
00:03:47,139 --> 00:03:49,699
Those names are reserved in case
sensitive, so the spelling has

128
00:03:49,699 --> 00:03:50,539
to be exact.

129
00:03:50,539 --> 00:03:52,300
Rangestart is the earliest date

130
00:03:52,300 --> 00:03:53,819
in your window and range end is

131
00:03:53,819 --> 00:03:54,539
the latest.

132
00:03:54,539 --> 00:03:57,810
Both have to be the date time
type and you then filter your

133
00:03:57,810 --> 00:03:58,650
table's date column.

134
00:03:58,650 --> 00:04:02,050
So it keeps only the rows that
fall between those two markers.

135
00:04:02,050 --> 00:04:03,569
A parameter is just a named
value.

136
00:04:03,569 --> 00:04:05,129
You can drop into a query.

137
00:04:05,129 --> 00:04:06,409
You're not typing dates into a

138
00:04:06,409 --> 00:04:07,650
formula every time because power

139
00:04:07,650 --> 00:04:09,129
BI fills them in for you while

140
00:04:09,129 --> 00:04:10,169
you're still in power BI

141
00:04:10,169 --> 00:04:10,610
desktop.

142
00:04:10,610 --> 00:04:12,129
The values you type only control

143
00:04:12,129 --> 00:04:13,490
the small sample loaded into

144
00:04:13,490 --> 00:04:14,770
your local file, which keeps the

145
00:04:14,770 --> 00:04:15,930
preview light.

146
00:04:15,930 --> 00:04:19,290
The service ignores those values
later and sets its own.

147
00:04:19,290 --> 00:04:22,089
One detail here trips up a lot
of people put the equal sign on

148
00:04:22,089 --> 00:04:24,370
only one of the two markers,
never on both.

149
00:04:24,370 --> 00:04:27,529
If Rangestart uses greater than
or equal to and range end also

150
00:04:27,529 --> 00:04:30,649
uses less than or equal to a row
sitting exactly on a boundary

151
00:04:30,649 --> 00:04:31,810
can satisfy two windows.

152
00:04:31,810 --> 00:04:33,370
At once that row gets counted

153
00:04:33,370 --> 00:04:34,610
twice and your totals come out

154
00:04:34,610 --> 00:04:35,129
wrong.

155
00:04:35,129 --> 00:04:36,850
The two markers work as a pair.

156
00:04:36,850 --> 00:04:39,850
Rangestart opens the window and
range end closes it, and

157
00:04:39,850 --> 00:04:42,250
everything in between is fair
game for the refresh.

158
00:04:42,250 --> 00:04:45,129
When the pairing is clean, power
BI can divide your history into

159
00:04:45,129 --> 00:04:47,529
windows that don't overlap,
which is exactly what keeps the

160
00:04:47,529 --> 00:04:48,810
row counts honest.

161
00:04:48,810 --> 00:04:51,490
If your table doesn't store a
real date and instead uses a

162
00:04:51,490 --> 00:04:54,649
number like twenty, twenty four
or one, you're not stuck.

163
00:04:54,649 --> 00:04:57,279
You write a small function that
turns the date marker into that

164
00:04:57,279 --> 00:04:58,360
same number format.

165
00:04:58,360 --> 00:05:01,120
The reason you convert the
marker and not the column is

166
00:05:01,120 --> 00:05:03,720
that converting the column
itself would break the next step

167
00:05:03,720 --> 00:05:04,800
we're about to cover.

168
00:05:04,800 --> 00:05:07,800
Once you publish, you stop
managing these dates by hand.

169
00:05:07,800 --> 00:05:10,540
The service overwrites both
markers on every refresh to

170
00:05:10,540 --> 00:05:13,439
build the exact window it needs
for each piece of the table, and

171
00:05:13,439 --> 00:05:15,519
it slides that window forward
from then on.

172
00:05:15,519 --> 00:05:17,680
The filter you write is the
bridge between the markers and

173
00:05:17,680 --> 00:05:20,519
your data, telling power BI to
load the rows inside the window

174
00:05:20,519 --> 00:05:22,480
and to skip everything else.

175
00:05:22,480 --> 00:05:25,120
The rolling window that keeps
your model small.

176
00:05:25,120 --> 00:05:27,160
The policy has two numbers that
matter most.

177
00:05:27,160 --> 00:05:29,699
The first is the store period,
which decides how much history

178
00:05:29,699 --> 00:05:30,879
your model keeps.

179
00:05:30,879 --> 00:05:31,920
The second is the refresh

180
00:05:31,920 --> 00:05:33,120
period, which decides how much

181
00:05:33,120 --> 00:05:34,560
recent data gets reloaded on

182
00:05:34,560 --> 00:05:35,480
every refresh.

183
00:05:35,480 --> 00:05:38,040
Microsoft describes the result
as a rolling window.

184
00:05:38,040 --> 00:05:39,240
Recent data lives in small

185
00:05:39,240 --> 00:05:40,560
slices, like days or months

186
00:05:40,560 --> 00:05:41,480
because that's where change

187
00:05:41,480 --> 00:05:42,240
happens.

188
00:05:42,240 --> 00:05:43,680
As time moves forward, those

189
00:05:43,680 --> 00:05:45,420
small slices get merged into

190
00:05:45,420 --> 00:05:46,879
bigger ones like quarters and

191
00:05:46,879 --> 00:05:47,399
years.

192
00:05:47,399 --> 00:05:48,800
When the slice finally falls

193
00:05:48,800 --> 00:05:50,439
outside your store, period, it's

194
00:05:50,439 --> 00:05:52,160
removed from the model entirely

195
00:05:52,160 --> 00:05:53,959
and your model stays a sensible

196
00:05:53,959 --> 00:05:55,759
size while still holding years

197
00:05:55,759 --> 00:05:56,839
of history.

198
00:05:56,839 --> 00:05:58,519
A worked example makes it click.

199
00:05:58,519 --> 00:06:01,120
Say your table adds about ten
thousand rows a day and you set

200
00:06:01,120 --> 00:06:02,079
a two day window.

201
00:06:02,079 --> 00:06:03,519
The first refresh loads roughly

202
00:06:03,519 --> 00:06:05,199
twenty thousand rows, not the

203
00:06:05,199 --> 00:06:06,600
millions sitting in the source,

204
00:06:06,600 --> 00:06:07,959
and from then on, each refresh

205
00:06:07,959 --> 00:06:09,480
only touches that small recent

206
00:06:09,480 --> 00:06:10,240
window.

207
00:06:10,240 --> 00:06:13,079
Those narrow slices also make
the refresh cheaper to run.

208
00:06:13,079 --> 00:06:14,399
A small window means fewer rows

209
00:06:14,399 --> 00:06:16,000
to move, fewer rows to compress,

210
00:06:16,000 --> 00:06:17,040
and less work for the engine

211
00:06:17,040 --> 00:06:18,160
when it rebuilds the affected

212
00:06:18,160 --> 00:06:18,720
pieces.

213
00:06:18,720 --> 00:06:20,040
Compare that with a full
refresh.

214
00:06:20,040 --> 00:06:22,800
A full refresh pulls every row
you have ever collected from the

215
00:06:22,800 --> 00:06:24,560
source, every single time.

216
00:06:24,560 --> 00:06:26,120
Incremental refresh asks for the

217
00:06:26,120 --> 00:06:27,680
last couple of days and trusts

218
00:06:27,680 --> 00:06:29,000
that yesterday's numbers haven't

219
00:06:29,000 --> 00:06:29,720
changed.

220
00:06:29,720 --> 00:06:32,279
The store period is the part
beginners overlook.

221
00:06:32,279 --> 00:06:34,439
You might keep five years of
history in the model and still

222
00:06:34,439 --> 00:06:36,160
refresh only the last few days.

223
00:06:36,160 --> 00:06:38,519
Those two numbers are
independent, and getting them

224
00:06:38,519 --> 00:06:40,160
right is most of the tuning.

225
00:06:40,160 --> 00:06:42,279
Set the window too wide and you
lose the benefit.

226
00:06:42,279 --> 00:06:45,040
Set it too narrow and you risk
missing rows that arrive late

227
00:06:45,040 --> 00:06:46,800
and still need to be picked up.

228
00:06:46,800 --> 00:06:48,199
A good window covers the stretch

229
00:06:48,199 --> 00:06:49,560
of data that can realistically

230
00:06:49,560 --> 00:06:50,240
still change.

231
00:06:50,240 --> 00:06:52,279
That's why the feature is called
incremental.

232
00:06:52,279 --> 00:06:53,875
Refresh is a small addition to

233
00:06:53,875 --> 00:06:55,269
something that is already there,

234
00:06:55,269 --> 00:06:56,829
rather than a fresh rebuild from

235
00:06:56,829 --> 00:06:57,509
nothing.

236
00:06:57,509 --> 00:07:00,069
The service handles the merging
and the cleanup on its own, so

237
00:07:00,069 --> 00:07:03,069
you don't schedule it and you
don't manage the slices.

238
00:07:03,069 --> 00:07:04,750
The setting that decides
everything.

239
00:07:04,750 --> 00:07:06,670
Here's the rule the opening
warned you about.

240
00:07:06,670 --> 00:07:09,629
It's called query folding, and
it decides whether incremental

241
00:07:09,629 --> 00:07:12,149
refresh helps you or quietly
makes things worse.

242
00:07:12,149 --> 00:07:14,670
Query folding means your date
filter gets passed down to the

243
00:07:14,670 --> 00:07:17,670
data source instead of power by
asking for the whole table and

244
00:07:17,670 --> 00:07:18,990
then throwing rows away.

245
00:07:18,990 --> 00:07:21,750
It asks the source for only the
rows inside the window.

246
00:07:21,750 --> 00:07:24,430
The source does the filtering,
and it sends back a small slice,

247
00:07:24,430 --> 00:07:26,350
which is exactly what you want.

248
00:07:26,350 --> 00:07:28,470
When folding fails, the opposite
happens.

249
00:07:28,470 --> 00:07:30,029
Power BI asks the source for the

250
00:07:30,029 --> 00:07:31,790
entire table, pulls every row

251
00:07:31,790 --> 00:07:33,350
back, and filters on its own

252
00:07:33,350 --> 00:07:34,149
machine.

253
00:07:34,149 --> 00:07:36,829
The source never learns that you
only wanted two days of data.

254
00:07:36,829 --> 00:07:37,670
This is the trap.

255
00:07:37,670 --> 00:07:39,389
A refresh that can't fold can be

256
00:07:39,389 --> 00:07:40,910
slower than a plain full refresh

257
00:07:40,910 --> 00:07:43,050
because it adds filtering work

258
00:07:43,050 --> 00:07:44,430
on top of moving the same amount

259
00:07:44,430 --> 00:07:45,189
of data.

260
00:07:45,189 --> 00:07:46,389
It can also exhaust the memory

261
00:07:46,389 --> 00:07:47,829
in the service or the gateway,

262
00:07:47,829 --> 00:07:48,910
and then the whole refresh

263
00:07:48,910 --> 00:07:49,550
fails.

264
00:07:49,550 --> 00:07:52,310
At that point, incremental
refresh is doing real harm.

265
00:07:52,310 --> 00:07:54,860
The good news is that power BI
desktop usually warns you when

266
00:07:54,860 --> 00:07:58,220
it can't confirm folding for SQL
based sources like a SQL

267
00:07:58,220 --> 00:07:59,540
database or Azure Synapse.

268
00:07:59,540 --> 00:08:01,660
That check is reliable for other
sources.

269
00:08:01,660 --> 00:08:02,500
You may have to trace the

270
00:08:02,500 --> 00:08:03,779
queries to see what's actually

271
00:08:03,779 --> 00:08:04,339
being sent.

272
00:08:04,339 --> 00:08:06,720
If you're unsure whether folding
is happening, there's a quick

273
00:08:06,720 --> 00:08:09,699
test load a very small window
with only a few thousand rows.

274
00:08:09,699 --> 00:08:11,019
If that still takes a long time

275
00:08:11,019 --> 00:08:12,420
and hammers the machine, the

276
00:08:12,420 --> 00:08:13,779
filter isn't reaching the source

277
00:08:13,779 --> 00:08:15,060
and you should fix it before you

278
00:08:15,060 --> 00:08:15,860
publish.

279
00:08:15,860 --> 00:08:18,779
The most common reason folding
breaks is a type mismatch.

280
00:08:18,779 --> 00:08:19,920
Your date column and the two

281
00:08:19,920 --> 00:08:21,680
markers both need to be the date

282
00:08:21,680 --> 00:08:22,800
time type, and they need to

283
00:08:22,800 --> 00:08:23,300
match.

284
00:08:23,300 --> 00:08:24,459
If the column is a number and

285
00:08:24,459 --> 00:08:25,939
the marker is a date, the filter

286
00:08:25,939 --> 00:08:27,300
can't be pushed down and folding

287
00:08:27,300 --> 00:08:27,899
stops.

288
00:08:27,899 --> 00:08:29,980
Folding isn't unique to
incremental refresh.

289
00:08:29,980 --> 00:08:32,940
It's a general idea in Power
Query, and it shows up any time.

290
00:08:32,940 --> 00:08:34,100
Power BI would rather let the

291
00:08:34,100 --> 00:08:35,220
source work than do the work

292
00:08:35,220 --> 00:08:35,899
itself.

293
00:08:35,899 --> 00:08:38,399
Incremental refresh just makes
it matter a great deal, because

294
00:08:38,399 --> 00:08:41,259
a refresh now fires one query
per slice of the table.

295
00:08:41,259 --> 00:08:43,980
If none of those queries fold,
you've multiplied the problem

296
00:08:43,980 --> 00:08:45,019
instead of solving it.

297
00:08:45,019 --> 00:08:46,100
So the rule is short.

298
00:08:46,100 --> 00:08:47,179
If your filter can't reach the

299
00:08:47,179 --> 00:08:48,700
source, incremental refresh

300
00:08:48,700 --> 00:08:49,860
can't help you check folding

301
00:08:49,860 --> 00:08:50,980
before you switch anything on

302
00:08:50,980 --> 00:08:52,340
because everything else depends

303
00:08:52,340 --> 00:08:53,019
on it.

304
00:08:53,019 --> 00:08:54,620
What it costs and where it runs.

305
00:08:54,620 --> 00:08:55,539
The next question is the

306
00:08:55,539 --> 00:08:56,940
practical one what does this

307
00:08:56,940 --> 00:08:58,500
cost and where can you even run

308
00:08:58,500 --> 00:08:59,379
it?

309
00:08:59,379 --> 00:09:01,059
The first answer is reassuring.

310
00:09:01,059 --> 00:09:02,779
Incremental refresh works on

311
00:09:02,779 --> 00:09:04,620
power BI, Pro on premium, on

312
00:09:04,620 --> 00:09:05,980
premium per user, and on

313
00:09:05,980 --> 00:09:06,580
embedded.

314
00:09:06,580 --> 00:09:09,379
It isn't a premium only feature,
which is a common assumption.

315
00:09:09,379 --> 00:09:10,500
You also don't need a premium

316
00:09:10,500 --> 00:09:12,019
capacity just to get started,

317
00:09:12,019 --> 00:09:13,179
which surprises people who

318
00:09:13,179 --> 00:09:14,679
assume every useful power BI

319
00:09:14,679 --> 00:09:15,980
feature sits behind the bigger

320
00:09:15,980 --> 00:09:16,659
license.

321
00:09:16,659 --> 00:09:18,779
The part that needs the bigger
license is real time.

322
00:09:18,779 --> 00:09:21,899
If you want the very newest rose
to appear live that uses a

323
00:09:21,899 --> 00:09:25,059
direct query partition, and that
option is limited to premium

324
00:09:25,059 --> 00:09:26,700
premium per user and embedded.

325
00:09:26,700 --> 00:09:29,700
The standard scheduled refresh
on a window works on Pro.

326
00:09:29,700 --> 00:09:30,659
Then come the limits, and

327
00:09:30,659 --> 00:09:32,100
they're worth knowing before you

328
00:09:32,100 --> 00:09:33,740
build on shared capacity, which

329
00:09:33,740 --> 00:09:34,980
is where a plain Pro license

330
00:09:34,980 --> 00:09:35,500
lives.

331
00:09:35,500 --> 00:09:36,620
You get eight scheduled

332
00:09:36,620 --> 00:09:38,340
refreshes a day, and that same

333
00:09:38,340 --> 00:09:39,820
cap applies to refreshes you

334
00:09:39,820 --> 00:09:41,419
trigger through the API on

335
00:09:41,419 --> 00:09:42,779
premium, premium per user or

336
00:09:42,779 --> 00:09:43,860
fabric capacity.

337
00:09:43,860 --> 00:09:46,740
You can schedule up to forty
eight refreshes a day through

338
00:09:46,740 --> 00:09:48,139
the Xmla endpoint.

339
00:09:48,139 --> 00:09:50,059
There's effectively no daily cap
at all.

340
00:09:50,059 --> 00:09:51,490
Time limits work the same way.

341
00:09:51,490 --> 00:09:53,009
A refresh on shared capacity has

342
00:09:53,009 --> 00:09:54,409
to finish within two hours, and

343
00:09:54,409 --> 00:09:56,049
premium stretches that to five

344
00:09:56,049 --> 00:09:56,690
hours.

345
00:09:56,690 --> 00:09:58,210
The Xmla endpoint removes the

346
00:09:58,210 --> 00:09:59,649
time limit, which matters when

347
00:09:59,649 --> 00:10:01,049
your first load is genuinely

348
00:10:01,049 --> 00:10:01,450
huge.

349
00:10:01,450 --> 00:10:02,850
Keep those limits in mind when

350
00:10:02,850 --> 00:10:04,409
you plan the schedule, because a

351
00:10:04,409 --> 00:10:05,769
refresh that goes over them

352
00:10:05,769 --> 00:10:07,610
doesn't simply run late on a

353
00:10:07,610 --> 00:10:08,570
shared capacity.

354
00:10:08,570 --> 00:10:10,330
Extra refreshes can be refused,

355
00:10:10,330 --> 00:10:11,730
and on a premium capacity that's

356
00:10:11,730 --> 00:10:13,289
under heavy load, a cycle can be

357
00:10:13,289 --> 00:10:14,289
skipped altogether.

358
00:10:14,289 --> 00:10:15,889
One more thing sits in the
advanced pile.

359
00:10:15,889 --> 00:10:19,129
If you want to refresh a single
slice by hand or break a giant

360
00:10:19,129 --> 00:10:22,690
first load into batches, you
need a premium capacity with the

361
00:10:22,690 --> 00:10:24,330
Xmla endpoint switched on.

362
00:10:24,330 --> 00:10:26,009
You'll also need a tool like SQL

363
00:10:26,009 --> 00:10:27,570
Server Management Studio or

364
00:10:27,570 --> 00:10:28,690
Tabular Editor.

365
00:10:28,690 --> 00:10:29,789
You don't need any of that to

366
00:10:29,789 --> 00:10:31,090
start, and it's there when a

367
00:10:31,090 --> 00:10:32,250
model gets big enough to need

368
00:10:32,250 --> 00:10:32,889
it.

369
00:10:32,889 --> 00:10:34,250
The mistakes that bite people.

370
00:10:34,250 --> 00:10:35,129
Most of the pain with

371
00:10:35,129 --> 00:10:36,409
incremental refresh comes from

372
00:10:36,409 --> 00:10:37,929
five mistakes, and every one of

373
00:10:37,929 --> 00:10:39,210
them is avoidable once you know

374
00:10:39,210 --> 00:10:40,490
it's coming.

375
00:10:40,490 --> 00:10:41,690
The first is changing the date

376
00:10:41,690 --> 00:10:43,389
in the column you partitioned on

377
00:10:43,389 --> 00:10:44,929
incremental refresh reads a date

378
00:10:44,929 --> 00:10:46,250
change as a delete plus an

379
00:10:46,250 --> 00:10:46,850
insert.

380
00:10:46,850 --> 00:10:49,769
If that delete lands in your old
history, the feature never picks

381
00:10:49,769 --> 00:10:53,000
it up, and you can end up with
duplicate values or a refresh

382
00:10:53,000 --> 00:10:54,399
that fails on a conflict.

383
00:10:54,399 --> 00:10:56,039
Treat the date column as fixed.

384
00:10:56,039 --> 00:10:58,440
The second is expecting deleted
rows to vanish.

385
00:10:58,440 --> 00:11:00,000
Change detection only notices

386
00:11:00,000 --> 00:11:01,559
soft deletes where the row is

387
00:11:01,559 --> 00:11:02,639
still in the source, but marked

388
00:11:02,639 --> 00:11:03,600
as deleted.

389
00:11:03,600 --> 00:11:06,480
A row that's hard deleted just
stays in your model, quietly

390
00:11:06,480 --> 00:11:07,840
inflating your numbers.

391
00:11:07,840 --> 00:11:09,679
The third is republishing the
file.

392
00:11:09,679 --> 00:11:11,000
Once a model with incremental

393
00:11:11,000 --> 00:11:12,600
refresh is live, publishing the

394
00:11:12,600 --> 00:11:13,799
same file again wipes the

395
00:11:13,799 --> 00:11:15,080
partitions and the history you

396
00:11:15,080 --> 00:11:16,440
built, and it forces a full

397
00:11:16,440 --> 00:11:17,279
refresh.

398
00:11:17,279 --> 00:11:19,879
You also can't download the
model back as a file after it's

399
00:11:19,879 --> 00:11:20,840
published on premium.

400
00:11:20,840 --> 00:11:21,879
You add changes through a

401
00:11:21,879 --> 00:11:23,360
metadata only deployment so the

402
00:11:23,360 --> 00:11:24,480
history survives.

403
00:11:24,480 --> 00:11:26,799
The fourth is a window that's
too big for the source.

404
00:11:26,799 --> 00:11:28,200
Some sources cap a single query

405
00:11:28,200 --> 00:11:29,320
at sixty four megabytes of

406
00:11:29,320 --> 00:11:30,679
compressed data.

407
00:11:30,679 --> 00:11:32,200
Log analytics and application

408
00:11:32,200 --> 00:11:33,759
insights are the usual culprits,

409
00:11:33,759 --> 00:11:35,080
and they don't always throw an

410
00:11:35,080 --> 00:11:35,559
error.

411
00:11:35,559 --> 00:11:37,480
They just quietly truncate what
comes back.

412
00:11:37,480 --> 00:11:40,000
So smaller refresh and store
periods are the fix.

413
00:11:40,000 --> 00:11:41,159
The fifth is pulling from more

414
00:11:41,159 --> 00:11:42,519
than one data source in the same

415
00:11:42,519 --> 00:11:43,000
table.

416
00:11:43,000 --> 00:11:44,240
All partitions have to come from

417
00:11:44,240 --> 00:11:46,159
a single source, or the number

418
00:11:46,159 --> 00:11:47,720
of queries fired per refresh

419
00:11:47,720 --> 00:11:49,149
multiplies and your carefully

420
00:11:49,149 --> 00:11:50,509
tuned, refresh slows right back

421
00:11:50,509 --> 00:11:51,029
down.

422
00:11:51,029 --> 00:11:52,429
None of these are exotic.

423
00:11:52,429 --> 00:11:54,830
They are the everyday reasons a
policy that looked fine in

424
00:11:54,830 --> 00:11:57,149
testing falls over once it runs
in the service.

425
00:11:57,149 --> 00:11:59,350
The pattern behind all five is
the same.

426
00:11:59,350 --> 00:12:02,350
Incremental refresh assumes the
old data is settled and correct,

427
00:12:02,350 --> 00:12:05,289
so anything that quietly changes
the past will find a way to

428
00:12:05,289 --> 00:12:07,830
surprise you when not to use it.

429
00:12:07,830 --> 00:12:10,590
Incremental refresh is powerful,
and that doesn't mean you should

430
00:12:10,590 --> 00:12:11,710
switch it on everywhere.

431
00:12:11,710 --> 00:12:14,590
There are times it's the wrong
tool if your table is small.

432
00:12:14,590 --> 00:12:16,950
This adds moving parts for very
little gain.

433
00:12:16,950 --> 00:12:19,389
A few hundred thousand rows
refresh fine on their own, and

434
00:12:19,389 --> 00:12:22,309
the extra setup only gives you
something else to maintain.

435
00:12:22,309 --> 00:12:24,909
A source that can't fold queries
means this feature can't help

436
00:12:24,909 --> 00:12:26,190
you, and it may hurt.

437
00:12:26,190 --> 00:12:29,429
Test folding first before you
commit to a policy when you need

438
00:12:29,429 --> 00:12:31,389
the data correct to the second.

439
00:12:31,389 --> 00:12:32,629
The real time option is the

440
00:12:32,629 --> 00:12:34,309
answer, and that needs a premium

441
00:12:34,309 --> 00:12:35,110
capacity.

442
00:12:35,110 --> 00:12:37,710
It also changes the shape of
your model, because the newest

443
00:12:37,710 --> 00:12:41,029
rows come from a live query
rather than the stored copy.

444
00:12:41,029 --> 00:12:42,470
If the dates in your data change

445
00:12:42,470 --> 00:12:44,269
or rows get hard deleted, plan

446
00:12:44,269 --> 00:12:45,590
for that before you build the

447
00:12:45,590 --> 00:12:46,070
policy.

448
00:12:46,070 --> 00:12:49,190
Incremental refresh assumes the
past is settled and when the

449
00:12:49,190 --> 00:12:51,629
past moves, the future doesn't
handle it for you.

450
00:12:51,629 --> 00:12:52,990
And if your report already runs

451
00:12:52,990 --> 00:12:54,269
on direct query or a live

452
00:12:54,269 --> 00:12:55,629
connection, there's no stored

453
00:12:55,629 --> 00:12:56,870
copy to refresh in the first

454
00:12:56,870 --> 00:12:57,470
place.

455
00:12:57,470 --> 00:13:00,070
There's nothing to make
incremental because power BI

456
00:13:00,070 --> 00:13:02,990
isn't holding the data, so the
shape of a good fit is clear.

457
00:13:02,990 --> 00:13:05,909
A large growing import mode
table with a date column that

458
00:13:05,909 --> 00:13:09,230
stays put on, a source that can
fold, and a refresh window small

459
00:13:09,230 --> 00:13:10,429
enough to stay cheap.

460
00:13:10,429 --> 00:13:11,710
None of this makes incremental

461
00:13:11,710 --> 00:13:13,389
refresh a bad tool, and it just

462
00:13:13,389 --> 00:13:14,710
means the fit matters more than

463
00:13:14,710 --> 00:13:15,830
the feature.

464
00:13:15,830 --> 00:13:17,470
A quick recap in one question.

465
00:13:17,470 --> 00:13:18,950
So that's incremental refresh.

466
00:13:18,950 --> 00:13:21,750
It keeps a small recent window
of data fresh and leaves the

467
00:13:21,750 --> 00:13:24,870
settled history alone, so power
BI stops reloading years of data

468
00:13:24,870 --> 00:13:25,870
that hasn't changed.

469
00:13:25,870 --> 00:13:28,470
The setting that decides whether
it works is query folding, and

470
00:13:28,470 --> 00:13:30,590
that's the one to check before
you build anything.

471
00:13:30,590 --> 00:13:31,629
Here's my question for you.

472
00:13:31,629 --> 00:13:34,590
How big is your biggest table
and how long does a full refresh

473
00:13:34,590 --> 00:13:35,590
take right now?

474
00:13:35,590 --> 00:13:38,190
That number tells you whether
this is worth the effort, and I

475
00:13:38,190 --> 00:13:39,990
would genuinely like to know
what you're working with.

476
00:13:39,990 --> 00:13:41,309
If this made the topic feel less

477
00:13:41,309 --> 00:13:43,929
intimidating, subscribe to M365

478
00:13:43,929 --> 00:13:45,710
FM to master the tools and the

479
00:13:45,710 --> 00:13:47,389
mindset shaping the future of

480
00:13:47,389 --> 00:13:48,029
work.
