Full Transcript for Excel Is Not a Database: Power Apps Dataverse Migration Explained

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.