Why Serious Power Apps Should Never Run on Excel Files

1
00:00:00,000 --> 00:00:02,560
Excel is not a database, stop pretending it is.

2
00:00:02,560 --> 00:00:05,240
When you wire power apps to a spreadsheet and call it production,

3
00:00:05,240 --> 00:00:08,960
you're building a glass elevator and acting shocked when it shatters under traffic.

4
00:00:08,960 --> 00:00:10,560
Here's what's actually at stake.

5
00:00:10,560 --> 00:00:15,560
Data loss that nobody notices until quarter-end concurrency chaos that rewrites history

6
00:00:15,560 --> 00:00:18,640
and a governance black hole auditors can drive a truck through.

7
00:00:18,640 --> 00:00:21,640
The truth, you're confusing a calculator with the control system.

8
00:00:21,640 --> 00:00:25,240
I'll tear down the myth, show the correct path, and hand you a migration plan.

9
00:00:25,240 --> 00:00:29,240
And there's one decision that prevents 80% of failures will get to that.

10
00:00:29,240 --> 00:00:32,440
First, why this matters before your next outage?

11
00:00:32,440 --> 00:00:33,560
Why this matters?

12
00:00:33,560 --> 00:00:34,880
Cost of confusion.

13
00:00:34,880 --> 00:00:39,560
OK, so basically the cost of confusion starts with pretending flexibility equals reliability.

14
00:00:39,560 --> 00:00:42,480
Excel is flexible because it lets anyone do anything.

15
00:00:42,480 --> 00:00:44,800
Data versus reliable because it refuses to.

16
00:00:44,800 --> 00:00:46,160
That refusal saves you money.

17
00:00:46,160 --> 00:00:47,000
Why this matters?

18
00:00:47,000 --> 00:00:50,680
Every time a spreadsheet stands in for a database, you import risk.

19
00:00:50,680 --> 00:00:55,280
Hidden risk, missed decimals, copied headers, rogue formulas, small errors that propagate like kuzu.

20
00:00:55,280 --> 00:00:59,560
You don't see the cost until you reconcile reports and discover two versions of truth.

21
00:00:59,560 --> 00:01:02,000
The average user shrugs, finance does not.

22
00:01:02,000 --> 00:01:03,880
The confusion tag shows up three ways.

23
00:01:03,880 --> 00:01:06,400
Data loss, concurrency, and governance.

24
00:01:06,400 --> 00:01:07,960
Data loss isn't always dramatic.

25
00:01:07,960 --> 00:01:09,320
It's often boring.

26
00:01:09,320 --> 00:01:12,480
Someone pests values over formulas to speed it up.

27
00:01:12,480 --> 00:01:17,800
Truncates a column because the view is narrow, or saves a filtered subset and emails it as

28
00:01:17,800 --> 00:01:20,200
the latest silent corruption.

29
00:01:20,200 --> 00:01:22,280
The system doesn't scream because Excel isn't a system.

30
00:01:22,280 --> 00:01:23,560
It's a grid with math.

31
00:01:23,560 --> 00:01:25,680
Concurrency is where the comedy turns expensive.

32
00:01:25,680 --> 00:01:30,040
Shared workbook, multiple editors, one drive, sync, jitter, last, save wins.

33
00:01:30,040 --> 00:01:31,800
Two people fix the same customer row.

34
00:01:31,800 --> 00:01:34,440
Credit terms updated here address corrected there.

35
00:01:34,440 --> 00:01:37,240
And the final file, blesses whichever save landed last.

36
00:01:37,240 --> 00:01:39,200
That's not a transaction that's roulette.

37
00:01:39,200 --> 00:01:43,560
And your power app connected to that file, it serves stale data, then overrides new data,

38
00:01:43,560 --> 00:01:45,000
then acts surprised.

39
00:01:45,000 --> 00:01:47,720
Acid transactions aren't optional in multi-user apps.

40
00:01:47,720 --> 00:01:50,120
They're the guard rails that keep reality coherent.

41
00:01:50,120 --> 00:01:51,760
Governance is the black hole.

42
00:01:51,760 --> 00:01:56,320
No schema enforcement means columns morph from text to number at the speed of a paste.

43
00:01:56,320 --> 00:01:59,640
No required fields means blanks stroll right into your pipeline.

44
00:01:59,640 --> 00:02:03,040
No relationships means you retype foreign keys and hope typos behave.

45
00:02:03,040 --> 00:02:07,160
No audit trail means you can't answer the simplest question who changed what, when, and

46
00:02:07,160 --> 00:02:08,160
why.

47
00:02:08,160 --> 00:02:09,160
Regulators love that.

48
00:02:09,160 --> 00:02:11,400
So do internal fraudsters and well-meaning chaos agents.

49
00:02:11,400 --> 00:02:12,520
Here's what most people miss.

50
00:02:12,520 --> 00:02:15,440
The tool you love for analysis is terrible as a backbone.

51
00:02:15,440 --> 00:02:17,040
Excel is the ancestor.

52
00:02:17,040 --> 00:02:19,160
Fast, familiar, forgiving.

53
00:02:19,160 --> 00:02:23,040
Dataverse is the spine, structured, relational, transactional.

54
00:02:23,040 --> 00:02:25,080
It's not just a different storage option.

55
00:02:25,080 --> 00:02:26,400
It's a different philosophy.

56
00:02:26,400 --> 00:02:28,320
Excel assumes you're a careful human.

57
00:02:28,320 --> 00:02:31,640
Dataverse assumes you're busy, distracted and occasionally wrong.

58
00:02:31,640 --> 00:02:33,200
And it builds fences accordingly.

59
00:02:33,200 --> 00:02:34,960
The costs scale fast.

60
00:02:34,960 --> 00:02:37,840
Reporting integrity collapses when column types drift.

61
00:02:37,840 --> 00:02:39,160
Compliance risks spikes.

62
00:02:39,160 --> 00:02:43,920
When sensitive data lives in a desktop file, then in an email, then in a shared folder with

63
00:02:43,920 --> 00:02:45,560
everyone access.

64
00:02:45,560 --> 00:02:49,280
One time isn't just an outage.

65
00:02:49,280 --> 00:02:52,720
It's the hours your team spends reconciling duplicated lists, patching broken formulas, and

66
00:02:52,720 --> 00:02:56,600
explaining to leadership why the dashboard changed by itself.

67
00:02:56,600 --> 00:02:57,600
It didn't.

68
00:02:57,600 --> 00:02:58,920
You did that.

69
00:02:58,920 --> 00:03:02,720
Everything clicked when I realized Excel is a calculator with a grid, you model, you explore,

70
00:03:02,720 --> 00:03:03,720
you prototype.

71
00:03:03,720 --> 00:03:04,720
Fantastic.

72
00:03:04,720 --> 00:03:08,520
But the moment multiple users enter, edit and rely on that data operationally, you need

73
00:03:08,520 --> 00:03:11,120
a system that enforces rules even when you're tired.

74
00:03:11,120 --> 00:03:12,560
In other words, dataverse.

75
00:03:12,560 --> 00:03:14,680
The aha moment, a database doesn't trust you.

76
00:03:14,680 --> 00:03:16,360
It validates constraints and logs.

77
00:03:16,360 --> 00:03:18,640
It forces relationships instead of trusting lookup.

78
00:03:18,640 --> 00:03:22,120
It gives you role-based access control so finance can update credit limits but can't

79
00:03:22,120 --> 00:03:23,400
see HR notes.

80
00:03:23,400 --> 00:03:26,880
It writes an audit trail because memory is not a control.

81
00:03:26,880 --> 00:03:28,680
Excel gives you none of that by design.

82
00:03:28,680 --> 00:03:29,680
It's why you love it.

83
00:03:29,680 --> 00:03:30,680
It's why it fails.

84
00:03:30,680 --> 00:03:31,880
The stakes, real money.

85
00:03:31,880 --> 00:03:33,640
Data loss forces rework.

86
00:03:33,640 --> 00:03:37,240
Concurrency creates contradictory records that erode customer trust.

87
00:03:37,240 --> 00:03:41,320
Governance gaps turn into fines, breach notifications, and board level meetings you do not want.

88
00:03:41,320 --> 00:03:45,920
The counter-intuitive part is that moving to dataverse feels slower at first.

89
00:03:45,920 --> 00:03:50,800
You define tables, relationships, choices, business rules, you set security roles in auditing,

90
00:03:50,800 --> 00:03:52,760
you think all I wanted was a simple app.

91
00:03:52,760 --> 00:03:54,080
And yes, that's the point.

92
00:03:54,080 --> 00:03:56,680
Simple is a user experience, not a data strategy.

93
00:03:56,680 --> 00:03:59,120
So before your next outage, accept the premise.

94
00:03:59,120 --> 00:04:00,120
Excel can track.

95
00:04:00,120 --> 00:04:01,360
Dataverse must govern.

96
00:04:01,360 --> 00:04:02,360
Hold that thought.

97
00:04:02,360 --> 00:04:05,200
Now we'll step on the three failures you keep stepping on and then replace them with

98
00:04:05,200 --> 00:04:07,960
something that actually holds under pressure.

99
00:04:07,960 --> 00:04:09,240
The three fatal failures.

100
00:04:09,240 --> 00:04:11,240
Data loss, concurrency, governance.

101
00:04:11,240 --> 00:04:12,680
This is what most people miss.

102
00:04:12,680 --> 00:04:15,760
Data loss in Excel rarely looks like a dramatic explosion.

103
00:04:15,760 --> 00:04:17,600
It looks like a polite quiet subtraction.

104
00:04:17,600 --> 00:04:20,960
The decimal that vanishes because the cell was formatted as text.

105
00:04:20,960 --> 00:04:24,960
The import that truncates are 255 characters because someone picked the wrong data type

106
00:04:24,960 --> 00:04:25,960
in a wizard.

107
00:04:25,960 --> 00:04:30,560
The column name that changes from customer ID to customer ID because someone hates underscores.

108
00:04:30,560 --> 00:04:32,720
And suddenly every formula points at air.

109
00:04:32,720 --> 00:04:34,320
Excel doesn't validate your reality.

110
00:04:34,320 --> 00:04:35,960
It nods, smiles, and proceeds.

111
00:04:35,960 --> 00:04:37,280
The simple version is this.

112
00:04:37,280 --> 00:04:40,800
There's no enforced schema, no constraints, no referential integrity.

113
00:04:40,800 --> 00:04:43,520
Think of a database as a bouncer checking IDs.

114
00:04:43,520 --> 00:04:46,120
Excel is the side door propped open with a chair.

115
00:04:46,120 --> 00:04:49,280
You can paste nine digits into a field that's supposed to hold 10.

116
00:04:49,280 --> 00:04:52,840
You can delete a parent row and strand children that still reference it.

117
00:04:52,840 --> 00:04:56,760
You can import a CSV with more rows than your clever range noticed and your app stops

118
00:04:56,760 --> 00:04:58,360
seeing 12% of the world.

119
00:04:58,360 --> 00:04:59,760
No alarms, no errors.

120
00:04:59,760 --> 00:05:01,720
Just the slow, invisible drift of truth.

121
00:05:01,720 --> 00:05:03,000
Here's the weird part.

122
00:05:03,000 --> 00:05:05,960
Even when you're careful, Excel's convenience is your enemy.

123
00:05:05,960 --> 00:05:08,120
Copypaste values only to speed things up.

124
00:05:08,120 --> 00:05:12,360
You just froze a living calculation, rename a sheet tab, your power query steps break three

125
00:05:12,360 --> 00:05:13,840
layers downstream.

126
00:05:13,840 --> 00:05:16,360
Use Find and Replace to fix a code pattern.

127
00:05:16,360 --> 00:05:17,360
Congratulations.

128
00:05:17,360 --> 00:05:21,080
You also change the middle of every part number containing that sequence.

129
00:05:21,080 --> 00:05:22,080
That's not Mellis.

130
00:05:22,080 --> 00:05:25,120
That's the absence of a contract between your intent and the data.

131
00:05:25,120 --> 00:05:26,680
Let's make it painfully concrete.

132
00:05:26,680 --> 00:05:28,320
Two users fix the same row.

133
00:05:28,320 --> 00:05:32,960
One updates the credit limit from 10,000 to 15,000 after a phone call with sales.

134
00:05:32,960 --> 00:05:36,240
The other corrects the shipping address to include the missing suite number.

135
00:05:36,240 --> 00:05:39,320
Save, last save wins blesses whichever edit landed last.

136
00:05:39,320 --> 00:05:41,040
The other edit obliterated.

137
00:05:41,040 --> 00:05:44,920
In your app, the record now has the new address and the old credit limit or vice versa depending

138
00:05:44,920 --> 00:05:46,240
on the sync lottery.

139
00:05:46,240 --> 00:05:50,400
There's no merge, no row level locking, no transaction that bundles both edits as a single

140
00:05:50,400 --> 00:05:51,400
truth.

141
00:05:51,400 --> 00:05:53,480
But we turned on co-authoring in one drive.

142
00:05:53,480 --> 00:05:54,480
Cute.

143
00:05:54,480 --> 00:05:58,240
That's text level reconciliation, not acid guarantees, then yes, acid matters.

144
00:05:58,240 --> 00:06:00,920
Atomicity means both edits commit or neither does.

145
00:06:00,920 --> 00:06:03,960
Consistency means the data obeys rules before and after.

146
00:06:03,960 --> 00:06:07,200
Consistency means simultaneous edits don't bleed into each other.

147
00:06:07,200 --> 00:06:10,640
Durability means once it's saved, it's day saved, Excel offers none of that, it offers

148
00:06:10,640 --> 00:06:11,640
good luck.

149
00:06:11,640 --> 00:06:14,480
Ba Saa Cene, he Sa Saa Somasasal.

150
00:06:14,480 --> 00:06:19,320
The power app connected to that file inherits the chaos, stale reads, conflicting rights,

151
00:06:19,320 --> 00:06:22,720
and override races that turn history into a choose your own adventure.

152
00:06:22,720 --> 00:06:26,040
Now governance, the black hole where accountability goes to die.

153
00:06:26,040 --> 00:06:28,120
No schema means columns can morph by accident.

154
00:06:28,120 --> 00:06:31,720
No relationships means there's nothing forcing a valid customer on an order.

155
00:06:31,720 --> 00:06:35,360
No required fields means phone can be empty until you need to call.

156
00:06:35,360 --> 00:06:38,760
No audit means you can't answer who changed this without walking around asking humans

157
00:06:38,760 --> 00:06:39,760
to remember.

158
00:06:39,760 --> 00:06:44,400
No role-based access means the intern can see salary notes because the file has one permission.

159
00:06:44,400 --> 00:06:45,760
Whoever got the link.

160
00:06:45,760 --> 00:06:49,360
And do not waive version history at me like it's an audit log.

161
00:06:49,360 --> 00:06:52,680
A blob level version is not a field level change record.

162
00:06:52,680 --> 00:06:54,520
Compliance exposure is not theoretical.

163
00:06:54,520 --> 00:06:58,320
Sensitive data in a spreadsheet travels by email, gets saved to desktop, reappears

164
00:06:58,320 --> 00:06:59,560
in a personal one drive.

165
00:06:59,560 --> 00:07:01,680
DLP doesn't see it until it's already everywhere.

166
00:07:01,680 --> 00:07:05,520
Try convincing an auditor that your customer list with birth dates in a tab called old

167
00:07:05,520 --> 00:07:09,160
was adequately protected because the folder had a read only sign.

168
00:07:09,160 --> 00:07:12,240
Excel doesn't implement policy, it politely ignores it.

169
00:07:12,240 --> 00:07:16,040
Nobody explains this right but the governance failure creates a culture of personal copies,

170
00:07:16,040 --> 00:07:17,120
just in case.

171
00:07:17,120 --> 00:07:21,760
So now you have the mothership file, three personal forks, a CSV used for a monthly import

172
00:07:21,760 --> 00:07:26,560
and a sharepoint copy for reporting, which one is real, all of them until something breaks,

173
00:07:26,560 --> 00:07:27,400
then none of them.

174
00:07:27,400 --> 00:07:31,480
This is how reporting integrity erodes, even if each person was careful.

175
00:07:31,480 --> 00:07:35,800
The truth treating Excel like a database is not frugality, it's deferred pain.

176
00:07:35,800 --> 00:07:39,080
Data loss is a feature of the medium, concurrency is a coin flip.

177
00:07:39,080 --> 00:07:43,040
Governance is theater, enter, that averse, not because Microsoft like selling you new nouns,

178
00:07:43,040 --> 00:07:47,720
but because you need a spine that enforces contracts, mediates edits and remembers everything.

179
00:07:47,720 --> 00:07:49,480
We'll build that next.

180
00:07:49,480 --> 00:07:52,160
Enter data verse what it is, why it wins.

181
00:07:52,160 --> 00:07:54,440
Enter data verse, not a nicer spreadsheet.

182
00:07:54,440 --> 00:07:58,600
A data platform that behaves like an adult is the spine your apps attached to so the body

183
00:07:58,600 --> 00:08:01,120
stands up even when users lean on it.

184
00:08:01,120 --> 00:08:04,360
This governance first because that's where Excel politely bowed out.

185
00:08:04,360 --> 00:08:08,880
In data verse you define tables, columns, data types, relationships and validation once

186
00:08:08,880 --> 00:08:11,040
and the platform enforces them every time.

187
00:08:11,040 --> 00:08:15,320
You want an account table and a contact table with one to many relationship?

188
00:08:15,320 --> 00:08:18,720
You don't beg users to keep IDs consistent, you create a look up.

189
00:08:18,720 --> 00:08:22,120
You want a choice field so status can only be new, active or closed?

190
00:08:22,120 --> 00:08:23,520
You define it and lock it.

191
00:08:23,520 --> 00:08:28,200
You want business rules like credit limit must be greater than zero if status is active.

192
00:08:28,200 --> 00:08:30,080
You don't write a memo, you write a rule.

193
00:08:30,080 --> 00:08:31,520
The difference is stark.

194
00:08:31,520 --> 00:08:34,640
Data verse is a contract, not a suggestion.

195
00:08:34,640 --> 00:08:36,640
Security isn't who has the file link.

196
00:08:36,640 --> 00:08:40,640
It's a role-based access control that maps to actual responsibilities, build security roles,

197
00:08:40,640 --> 00:08:45,400
sales finance support with table level permissions, row ownership and field level visibility.

198
00:08:45,400 --> 00:08:50,440
Make salary notes readable by HR only while everyone else sees a neat blank column.

199
00:08:50,440 --> 00:08:55,240
Layer teams over roles so access follows organizational structure instead of improvisation.

200
00:08:55,240 --> 00:08:59,600
Add column level security for truly sensitive attributes and yes enable audit logs that

201
00:08:59,600 --> 00:09:03,160
capture who changed what when from what to what.

202
00:09:03,160 --> 00:09:05,360
That's not a retroactive version history.

203
00:09:05,360 --> 00:09:09,080
That's accountability baked in data quality becomes proactive.

204
00:09:09,080 --> 00:09:13,400
Use required fields, reject style constraints and calculated columns to keep data consistent

205
00:09:13,400 --> 00:09:16,600
at entry, not in a cleanup day, six months later.

206
00:09:16,600 --> 00:09:21,200
The duplication rule stop you from creating contoso LLC, contoso LLC and contoso as three

207
00:09:21,200 --> 00:09:24,240
different customers because someone was in a hurry.

208
00:09:24,240 --> 00:09:29,560
Power automate brings discipline, run validation flows, send approvals, kick off notifications,

209
00:09:29,560 --> 00:09:34,360
and apply the same rule a thousand times without relying on, remember to do it.

210
00:09:34,360 --> 00:09:37,880
Scalability excel gasps at volume and concurrency, data verse expects it.

211
00:09:37,880 --> 00:09:42,720
It's built for multi-user high velocity rights with server side logic, indexes and query optimization.

212
00:09:42,720 --> 00:09:48,240
Your canvas apps can still paint the custom UI you crave, but the engine underneath is model-driven.

213
00:09:48,240 --> 00:09:51,760
Relationships enforced, forms generated, processes orchestrated.

214
00:09:51,760 --> 00:09:55,320
And yes, model-driven apps are responsive out of the box so you don't spend a week

215
00:09:55,320 --> 00:09:59,160
in nudging pixel-perfect layouts that collapse on mobile.

216
00:09:59,160 --> 00:10:03,600
Facilability changes the culture, flip on auditing at the table or column level and you get a time-stamped

217
00:10:03,600 --> 00:10:04,600
ledger of changes.

218
00:10:04,600 --> 00:10:08,600
Need to know who changed credit limit from 15,000 to 5,000 at 312 pm?

219
00:10:08,600 --> 00:10:09,600
It's there.

220
00:10:09,600 --> 00:10:11,120
Need change history for compliance?

221
00:10:11,120 --> 00:10:12,120
Also there.

222
00:10:12,120 --> 00:10:15,400
Enable change tracking to power incremental loads and integrations without repulling the

223
00:10:15,400 --> 00:10:19,440
universe and when inevitably someone asks for rollback, point to backups and point in

224
00:10:19,440 --> 00:10:20,440
time restore.

225
00:10:20,440 --> 00:10:24,040
Memory isn't a control, data verse logs are.

226
00:10:24,040 --> 00:10:26,440
Minimizing downtime during migration is not magic.

227
00:10:26,440 --> 00:10:28,680
It's planning supported by platform features.

228
00:10:28,680 --> 00:10:33,600
You can run faced migrations, stage the schema, load historical data, dual run for a period,

229
00:10:33,600 --> 00:10:35,920
then cut over with a timed freeze.

230
00:10:35,920 --> 00:10:40,000
If the cutover goes sideways, have rollback criteria defined and tested.

231
00:10:40,000 --> 00:10:43,560
You're not hoping the spreadsheet is close enough.

232
00:10:43,560 --> 00:10:49,120
You're executing a plan with guardrails, environments for dev, test and prod, solutions

233
00:10:49,120 --> 00:10:53,360
for transport and a clear promotion path that doesn't rely on right-click copy.

234
00:10:53,360 --> 00:10:56,520
Here's the truth, the marketing slides almost say out loud.

235
00:10:56,520 --> 00:10:59,800
Virtual driven strength plus canvas flexibility is the sweet spot.

236
00:10:59,800 --> 00:11:03,960
Use model driven for the core data interactions that demand consistency.

237
00:11:03,960 --> 00:11:06,960
Standard forms, related records, business process flows.

238
00:11:06,960 --> 00:11:10,880
Layer canvas pages for the oddball UI where speed and specificity matter.

239
00:11:10,880 --> 00:11:14,600
A barcode scanner, a dashboard, a kiosk style intake screen.

240
00:11:14,600 --> 00:11:16,760
One data brain, data verse.

241
00:11:16,760 --> 00:11:20,800
Feeding both experiences so you never play, which version is real.

242
00:11:20,800 --> 00:11:22,040
Again.

243
00:11:22,040 --> 00:11:24,520
Security and compliance certifications exist for a reason.

244
00:11:24,520 --> 00:11:28,520
You get encryption, address and in transit, data loss prevention policies across tenants

245
00:11:28,520 --> 00:11:32,640
and environments, and integration with Microsoft Entra for identity.

246
00:11:32,640 --> 00:11:34,920
That's not just comfort, it's audit evidence.

247
00:11:34,920 --> 00:11:38,720
Map that to your governance policy and stop pretending a shared folder with do not touch counts

248
00:11:38,720 --> 00:11:40,200
as control.

249
00:11:40,200 --> 00:11:41,440
Integration is adult too.

250
00:11:41,440 --> 00:11:45,280
Use virtual tables to surface external data without duplicating it or use data flows and

251
00:11:45,280 --> 00:11:47,920
power query to shape and import on a schedule.

252
00:11:47,920 --> 00:11:53,040
Trigger power automate flows on create, update, delete, so downstream systems stay in sync

253
00:11:53,040 --> 00:11:58,440
without a ritual of CSV exports and when analytics scale questions arrive data verse doesn't demand

254
00:11:58,440 --> 00:11:59,760
monastic purity.

255
00:11:59,760 --> 00:12:03,520
It extends export to fabric lake house for big analytics.

256
00:12:03,520 --> 00:12:08,440
Keep operational rights in data verse and let power be I speak to both without duct tape.

257
00:12:08,440 --> 00:12:11,200
Everything here exists to fix the failures we outlined.

258
00:12:11,200 --> 00:12:15,320
Data verse enforces contracts, mediates edits with transactions and remembers changes with

259
00:12:15,320 --> 00:12:16,320
an audit trail.

260
00:12:16,320 --> 00:12:19,200
It's not faster to set up than dragging a file into a connector.

261
00:12:19,200 --> 00:12:20,400
It's faster to live with.

262
00:12:20,400 --> 00:12:24,240
It's the difference between a hobby project and an operational system and because I can

263
00:12:24,240 --> 00:12:28,120
feel you about to ask yes, when your current world lives in excel, there's a path out

264
00:12:28,120 --> 00:12:30,920
that doesn't torture week will take it next.

265
00:12:30,920 --> 00:12:34,160
The correct migration strategy from excel to data verse.

266
00:12:34,160 --> 00:12:38,440
Here's the path out structured predictable and blessed boring will keep your users working

267
00:12:38,440 --> 00:12:43,560
while we move the data from clever spreadsheet to actual system preparation first inventory

268
00:12:43,560 --> 00:12:49,080
the chaos list every excel file feeding your app location owner consumers refresh cadence

269
00:12:49,080 --> 00:12:54,680
and how it's used reference entry or reporting classify sensitivity public internal confidential

270
00:12:54,680 --> 00:12:58,840
restricted note row counts and tabs that are actually separate entities masquerading as

271
00:12:58,840 --> 00:13:03,560
sheets identify calculated columns you'll replace with data verse calculated fields or business

272
00:13:03,560 --> 00:13:04,560
rules.

273
00:13:04,560 --> 00:13:10,560
Then pick a scope you can win one business process not your entire company modeling next translate

274
00:13:10,560 --> 00:13:15,840
sheets into tables columns and relationships one entity per real world now account contact

275
00:13:15,840 --> 00:13:17,520
order product.

276
00:13:17,520 --> 00:13:21,960
Use proper data types numbers as numbers dates estates choices for controlled vocabularies

277
00:13:21,960 --> 00:13:27,520
lookups for relationships avoid over normalization two or three levels deep is fine building

278
00:13:27,520 --> 00:13:32,720
an academic ERD museum is not ad required fields where reality demands them and define alternate

279
00:13:32,720 --> 00:13:37,400
keys for natural identifiers you'll use during imports now choose your import path if you

280
00:13:37,400 --> 00:13:42,280
need transformations splitting columns mapping codes fixing types use data flows with power

281
00:13:42,280 --> 00:13:46,800
query it's repeatable scheduleable and keeps your logic in one place if the spreadsheet

282
00:13:46,800 --> 00:13:51,080
is already clean and flat direct import works for a first load if users insist on working

283
00:13:51,080 --> 00:13:55,480
in excel during a transition the data verse add in let's them push changes under rules

284
00:13:55,480 --> 00:13:59,520
instead of emailing attachments the simple version pick the path that moves logic out of

285
00:13:59,520 --> 00:14:04,440
human hands and into a pipeline before you touch production validate profile the data

286
00:14:04,440 --> 00:14:10,160
blanks were required invalid formats duplicates by key often that reference nonexistent parents

287
00:14:10,160 --> 00:14:14,320
build the duplic logic standardized casing trim white space normalize punctuation then

288
00:14:14,320 --> 00:14:19,000
define match rules that won't eat cousins for twins load into a dev environment first

289
00:14:19,000 --> 00:14:24,040
reconcile counts and check sums spot check critical records then break it on purpose try to insert

290
00:14:24,040 --> 00:14:28,600
a bad value violate a relationship or bypass a required field the system should say no

291
00:14:28,600 --> 00:14:34,040
loudly security setup is not optional homework it's the core deliverable define roles for

292
00:14:34,040 --> 00:14:39,400
who can read create write a pen and delete by table layer teams so access follows org structure

293
00:14:39,400 --> 00:14:45,920
apply column level security to sensitive attributes salary ss and payment tokens use owner and business

294
00:14:45,920 --> 00:14:51,480
units go to corral role level visibility and yes default to least privilege the give everyone

295
00:14:51,480 --> 00:14:56,120
everything shortcut is how you re import risk turn on the memory enable auditing at the

296
00:14:56,120 --> 00:15:01,120
table and column level for the fields that matter status amounts identities dates enable

297
00:15:01,120 --> 00:15:05,880
change tracking to support downstream syncs without full reloads configure automatic backups

298
00:15:05,880 --> 00:15:10,840
and validate point in time restore if you can't prove you can go back you didn't finish the job

299
00:15:10,840 --> 00:15:15,200
plan your cutover like an operation with instruments counted face migration beats heroics

300
00:15:15,200 --> 00:15:19,960
step one deploy the schema to dev test and prod using solutions step two pre-load historical

301
00:15:19,960 --> 00:15:25,600
data into prod while your excel apps still runs step three schedule a freeze window communicated

302
00:15:25,600 --> 00:15:31,440
during which excel is view only step four delta load recent changes step five switch your

303
00:15:31,440 --> 00:15:36,640
apps data source to data verse and dual run for a defined period with read only access to all data

304
00:15:36,640 --> 00:15:42,480
step six declare success or rollback based on objective criteria data reconciliation thresholds

305
00:15:42,480 --> 00:15:47,320
performance in error rates user sign offs if you don't predefined rollback triggers you'll

306
00:15:47,320 --> 00:15:52,440
invent them mid panic communication cadence matters tell people what will change when and

307
00:15:52,440 --> 00:15:57,160
how they'll work during the freeze provider one page what moved wear guide and a hotline

308
00:15:57,160 --> 00:16:01,680
for the first week train the few who need to adjust habits like entering a required field

309
00:16:01,680 --> 00:16:06,920
that used to be optional people can adapt surprises cannot to quick tactics to avoid pain first

310
00:16:06,920 --> 00:16:11,320
keep identifier stable introduce a durable surrogate key in data verse and map excels natural

311
00:16:11,320 --> 00:16:16,200
key to an alternate key for upsets that prevents duplicates during staged loads second retire

312
00:16:16,200 --> 00:16:21,120
the spreadsheet deliberately archive it to a secure location lock it and publish a clear

313
00:16:21,120 --> 00:16:26,720
statement data verse is the source of truth excel extracts are read only an ephemeral the

314
00:16:26,720 --> 00:16:31,120
pay off your app now writes to a platform that enforces rules mediates edits and remembers

315
00:16:31,120 --> 00:16:36,480
changes you didn't move a file you replace the habit with a system and if you're about to

316
00:16:36,480 --> 00:16:41,640
ask how this fits the broader data landscape operational verses analytical will expand the

317
00:16:41,640 --> 00:16:47,200
architecture next when data verse isn't enough fabric lake house and sickle the truth data

318
00:16:47,200 --> 00:16:51,480
verse is your operational backbone not your planetary brain when the questions shift from

319
00:16:51,480 --> 00:16:55,680
update this record safely to model two years of click stream against revenue by region

320
00:16:55,680 --> 00:16:59,960
you've crossed into analytics land different physics stop trying to bench press a warehouse

321
00:16:59,960 --> 00:17:04,200
with an app database start with a simple decision tree if the workload is operational

322
00:17:04,200 --> 00:17:10,240
crud workflows approvals near real time views for teams data verse is home if the workload

323
00:17:10,240 --> 00:17:14,840
is analytical big joins historical snapshots large aggregates across millions or billions

324
00:17:14,840 --> 00:17:19,840
of rows enter Microsoft fabric and the lake house if the workload is specialized or LTP

325
00:17:19,840 --> 00:17:24,680
with extreme transaction rates or legacy app dependencies SQL server or Azure SQL belongs

326
00:17:24,680 --> 00:17:29,200
with data verse optionally as the app facing layer data verse plus fabric lake house is

327
00:17:29,200 --> 00:17:34,400
the clean pattern keep master data and operational truth in data verse use one lake to land curated

328
00:17:34,400 --> 00:17:39,520
governed exports for analytics at scale power be I sits natively on the lake house chewing

329
00:17:39,520 --> 00:17:43,600
through history without throttling your app compare that to overworking data verse with

330
00:17:43,600 --> 00:17:49,160
giant reports you'll throttle API limits annoy users and still deliver sluggish dashboards

331
00:17:49,160 --> 00:17:54,480
operational data serves today analytical data serves trends integration patterns matter

332
00:17:54,480 --> 00:17:58,960
virtual tables let data verse read from external stores without duplicating data use them to

333
00:17:58,960 --> 00:18:04,280
surface reference data or legacy system records inside your app while keeping the source authoritative

334
00:18:04,280 --> 00:18:09,480
for analytics avoid round tripping push data out on a schedule via data flows synapse link

335
00:18:09,480 --> 00:18:15,640
for data verse or fabric pipelines into one lake then model semantic layers in power be I

336
00:18:15,640 --> 00:18:19,600
that's push for analytics virtual eyes for operational context pulling massive facts back into

337
00:18:19,600 --> 00:18:24,920
data verse is how you recreate excel just Claudia SQL has a seat but not the throne use Azure

338
00:18:24,920 --> 00:18:29,600
school when you need stored procedures doing tight transactional work deterministic performance

339
00:18:29,600 --> 00:18:34,280
for line of business systems or when an existing apps schema is immovable you can front

340
00:18:34,280 --> 00:18:40,120
it with model driven apps via virtual tables or connect canvas apps directly the key is architectural

341
00:18:40,120 --> 00:18:46,240
intent data verse for unified security auditing and low code app ergonomics SQL for specialized

342
00:18:46,240 --> 00:18:51,560
transactional logic fabric for scale out analytics three tools one playbook hybrid architecture is

343
00:18:51,560 --> 00:18:56,680
adult supervision master data in data verse with relationships validation and are back facts

344
00:18:56,680 --> 00:19:01,400
streamed or batch loaded into fabric lake house for history enrichment and machine learning

345
00:19:01,400 --> 00:19:07,320
certain subsystems pricing engines MES trading run on SQL for deterministic transactional throughput

346
00:19:07,320 --> 00:19:11,880
glue it with power automate and fabric pipelines then document ownership and lineage so you

347
00:19:11,880 --> 00:19:16,760
don't grow shadow excel empire on the side who owns customer data verse who owns 12 months

348
00:19:16,760 --> 00:19:22,360
of sales detail fabric who owns pricing algorithm state SQL write it down and force it and stop

349
00:19:22,360 --> 00:19:26,920
arguing in meetings guardrails or excel doesn't reemerge wearing a power be I badge first

350
00:19:26,920 --> 00:19:32,320
no ad hoc cs v's as data sources in production they can be staging at best short lived auditable

351
00:19:32,320 --> 00:19:38,600
and deleted second centralized semantic models in power be I with certified data sets outlaw

352
00:19:38,600 --> 00:19:43,960
personal data models feeding executive dashboards third lineage or it didn't happen every data set

353
00:19:43,960 --> 00:19:49,640
maps back to data verse tables pipelines or SQL sources with documented transforms finally governance

354
00:19:49,640 --> 00:19:55,880
policies dlp for environments one lake access via security groups and c i cd for pipelines and models

355
00:19:55,880 --> 00:20:00,680
if you're still emailing extracts just for a quick check you've already backslid everything here

356
00:20:00,680 --> 00:20:07,000
keeps the promise data verse governs operations fabric scales inside SQL handles specialized transactions

357
00:20:07,000 --> 00:20:11,880
use the spine were a spine belongs and the warehouse where gravity is different your 10 step migration

358
00:20:11,880 --> 00:20:17,000
plan you can draft today you've got the y and the what now we make it unavoidable with a plan you

359
00:20:17,000 --> 00:20:23,400
can start drafting before your next meeting ends 10 steps no heroics just adult supervision step one

360
00:20:23,400 --> 00:20:28,840
map current excel assets owners consumers prioritized by risk inventory is not glamorous it's

361
00:20:28,840 --> 00:20:34,200
necessary list every spreadsheet that feeds or pretends to feed production where it lives who owns it

362
00:20:34,200 --> 00:20:40,040
who uses it and what decisions depend on it no row counts tabs that are really entities refresh

363
00:20:40,040 --> 00:20:46,120
cadence and whether it's reference data entry or reporting tax sensitivity public internal

364
00:20:46,120 --> 00:20:51,000
confidential restricted then ranked by blast radius which file if wrong or unavailable hurts you

365
00:20:51,000 --> 00:20:56,280
the most that's your starting q step two define entities keys relationships decide choices versus

366
00:20:56,280 --> 00:21:02,520
lookups translate the business nouns into tables accounts contacts orders products spare me the

367
00:21:02,520 --> 00:21:08,440
we are unique myth for each table define a primary key use guides as surrogate keys in data verse

368
00:21:08,440 --> 00:21:13,720
and identify natural keys as alternate keys for upsets like account number map relationships

369
00:21:13,720 --> 00:21:19,080
explicitly one too many from account to contact many too many were reality demands it use choices

370
00:21:19,080 --> 00:21:25,320
for control vocabularies status region category use lookups for relationships no a text field that

371
00:21:25,320 --> 00:21:31,480
usually matches is not a relationship step three security model sketch roads teams column security

372
00:21:31,480 --> 00:21:37,000
for sensitive fields design access like an adult create roles aligned to functions sales finance

373
00:21:37,000 --> 00:21:43,480
ops hr with create read write append append to and delete rights scope per table use teams to mirror

374
00:21:43,480 --> 00:21:49,080
org structure so people inherit access by membership not by hope apply column level security to fields

375
00:21:49,080 --> 00:21:55,160
like salary ssn credit card tokens or confidential notes decide ownership model user owned

376
00:21:55,160 --> 00:22:00,280
versus organization owned based on whether records should be private by default least privilege is

377
00:22:00,280 --> 00:22:05,480
not optional it's the baseline step four data quality rules required fields patterns the

378
00:22:05,480 --> 00:22:11,000
logic referential checks move the rules out of human memory and into the platform mark must have

379
00:22:11,000 --> 00:22:16,360
fields as required at the table level at format constraints for IDs emails phone numbers if it

380
00:22:16,360 --> 00:22:21,080
should match a pattern say so define the duplication rules for accounts and contacts that consider

381
00:22:21,080 --> 00:22:26,200
normalized names emails and phone numbers trim spaces standardized casing remove punctuation

382
00:22:26,200 --> 00:22:31,240
before comparing in force referential integrity with relationship rules so you cannot create an

383
00:22:31,240 --> 00:22:36,440
order without a valid account the system should reject nonsense at entry not after a quarterly cleanse

384
00:22:36,440 --> 00:22:42,760
step five build dataverse schema in dev seed with sample data create your tables columns

385
00:22:42,760 --> 00:22:47,480
relationships choices business rules and calculated fields in a development environment don't do

386
00:22:47,480 --> 00:22:52,680
this live seed with a small representative sample dozens or hundreds not thousands so you can

387
00:22:52,680 --> 00:22:58,520
see how forms render how look ups behave and where your rules bite this is where you find out that

388
00:22:58,520 --> 00:23:05,400
notes needs 1000 characters not 200 and that status should be a choice not free text adjust now

389
00:23:05,400 --> 00:23:12,600
not doing cut over step six choose import path configure data flows power query transformations

390
00:23:12,600 --> 00:23:17,400
pick the pipe that matches the mess if your excel needs transformation split columns fix types map

391
00:23:17,400 --> 00:23:23,480
codes use data flows with power query make the steps explicit and repeatable scheduled if needed if

392
00:23:23,480 --> 00:23:28,040
the sheet is already clean and flat use direct import for the initial load if users must keep

393
00:23:28,040 --> 00:23:33,000
entering data in excel during transition use the dataverse add in so their changes flow through

394
00:23:33,000 --> 00:23:37,960
the rules not around them the adult rule logic belongs in a pipeline not in someone's quick tips

395
00:23:37,960 --> 00:23:44,600
posted step seven validate loads reconciliation reports spot check audit and permissions load into

396
00:23:44,600 --> 00:23:50,040
dev reconcile counts per table and per key run exception reports for duplicates or offense and invalid

397
00:23:50,040 --> 00:23:55,080
formats then fix the source or the transform promote to a test environment and validate again

398
00:23:55,080 --> 00:24:01,400
produce a reconciliation report that a non-technical stakeholder can read we expected 18,942 accounts

399
00:24:01,400 --> 00:24:07,160
we loaded 18,941 one duplicates suppressed by rule spot check audit logs to confirm changes

400
00:24:07,160 --> 00:24:13,400
attract test security by logging in as each role see only what you should and fail where you must

401
00:24:14,360 --> 00:24:20,280
step eight pilot with a model driven skeleton canvas where you ex demands it stand up a model driven

402
00:24:20,280 --> 00:24:24,680
app against your dataverse schema you'll get forms views and related records out of the box that

403
00:24:24,680 --> 00:24:29,800
respect your relationships and rules use that for core operations add canvas pages only where you

404
00:24:29,800 --> 00:24:36,600
ex specificity matters scanning dashboards kiosk intake resist the urge to redesign everything pixel by

405
00:24:36,600 --> 00:24:41,640
pixel on day one function first fancy later the pilot proves that the data model is right because the

406
00:24:41,640 --> 00:24:48,520
app behaves even under people who don't read instructions step nine cut over plan freeze window dual run

407
00:24:48,520 --> 00:24:53,400
rollback communication script write the playbook before you play announce a freeze window where excel

408
00:24:53,400 --> 00:24:58,120
becomes read only pre-load historical data to product ahead of time during the freeze delta load

409
00:24:58,120 --> 00:25:03,640
new changes switch the apps data source to dataverse dual run for defined period with read only

410
00:25:03,640 --> 00:25:09,160
access to the old file for comparison define objective rollback criteria data variance thresholds

411
00:25:09,160 --> 00:25:13,960
error rates performance baselines user sign offs if you hit a red line rollback no heroics no

412
00:25:13,960 --> 00:25:19,400
sunk cost pride also script the combs what's changing when what users do differently and where to

413
00:25:19,400 --> 00:25:27,720
get help surprises are failures of planning not fate step 10 post migration monitoring backups audit

414
00:25:27,720 --> 00:25:33,240
review excel access deprecation you're not done at cut over you're done when operations are boring

415
00:25:33,800 --> 00:25:40,760
monitor key metrics record creation rates error logs api consumption and performance confirm automatic

416
00:25:40,760 --> 00:25:46,520
backups are running test point in time restore in a sandbox so you can sleep review audit logs weekly

417
00:25:46,520 --> 00:25:51,400
at first spot outliers confirm sensitive fields aren't being edited by the wrong roles deprecate

418
00:25:51,400 --> 00:25:56,360
excel access intentionally archive the old files to a secure locked location publish an access

419
00:25:56,360 --> 00:26:01,880
policy that states the source of truth is that averse exports are temporary and read only remove

420
00:26:01,880 --> 00:26:06,200
old connections from apps and flows to nobody silently backslides close loop the one decision that

421
00:26:06,200 --> 00:26:11,480
prevents 80% of failures centralized the data model in dataverse first not in a canvas app not in

422
00:26:11,480 --> 00:26:16,040
power query not in a well-meaning spreadsheet the model tables relationships rules and security

423
00:26:16,040 --> 00:26:20,840
lives in dataverse everything else plugs into that reality make that decision and most of the chaos

424
00:26:20,840 --> 00:26:26,120
you've normalized simply can't occur you know what to do next draft the inventory draw the model

425
00:26:26,120 --> 00:26:31,320
pick the pipeline and schedule the freeze you're replacing a habit with a system that's how grown-ups

426
00:26:31,320 --> 00:26:38,440
do data conclusion the spine not the spreadsheet the takeaway is simple excel tracks dataverse governs

427
00:26:38,440 --> 00:26:43,640
and governance is what keeps truth intact when humans get busy if this saved you from another quick fix

428
00:26:43,640 --> 00:26:49,160
disaster lock in your upgrade path subscribe turn on alerts and watch the dataverse modeling walk

429
00:26:49,160 --> 00:26:55,080
through next draft your migration plan today inventory model pipeline cutover and execute before

430
00:26:55,080 --> 00:26:57,080
the next outage makes the decision for you.

431
00:26:57,080 --> 00:26:59,080
Entropy wins by default. Choose Structure.