Performance and Stability: Design Best Practices

Quick reference

• 00:05 Let's talk about performance and stability of Power Pivot models.
• 00:08 How do you make sure that your models, when you click on something,
• 00:11 are actually gonna filter quickly, and they're going to be nice and stable so
• 00:14 they don't fall over and crash when you're working with them?
• 00:17 Well, the number one rule is minimize your data footprint.
• 00:21 Only pull in the columns you need.
• 00:23 You've got Power Query, so
• 00:24 you can actually drop the columns you don't need in your solution.
• 00:27 If you don't need it, don't bring it in.
• 00:30 Filter out the records you don't need as well.
• 00:32 If you're doing an analysis of this year versus last year,
• 00:35 why are you bringing in 20 years of data into your data set?
• 00:38 Hit the filter arrows in your Power Query, remove those from the data set, and
• 00:42 only bring in the smallest piece of records you
• 00:45 absolutely need in order to drive your model, why?
• 00:49 It's all about reducing the file size and memory need.
• 00:52 Remember, Power Pivot is an in-memory database engine.
• 00:56 The less that's in memory,
• 00:57 the more you're gonna increase the speed cuz there's less to sort through.
• 01:01 And the more you're gonna improve the stability because there's less stuff that
• 01:04 can potentially make the model fall over.
• 01:06 It basically is easier for your machine, or Power BI,
• 01:09 if you end up using that, to process the results.
• 01:12 Number one rule of thumb, keep it on the short and narrow.
• 01:15 Make sure your tables are narrow with only the columns you need,
• 01:18 make sure the tables are short with only the records you need.
• 01:21 And the more that you're jealous with your data footprint there,
• 01:24 the more you're gonna find your performance and stability are nice, and
• 01:27 improved, and work very well.
• 01:29 I want to show you a quick lesson on how Excel Pro is developed.
• 01:34 What they'll do is, they'll connect to a data source.
• 01:37 And then they'll go in and they'll collect as much data as possible,
• 01:40 they grab everything they can.
• 01:42 If they can bring in the entire sequel database in their Excel spreadsheet,
• 01:45 they'll do it.
• 01:46 Why?
• 01:47 Because we never know what our crazy boss might ask us for tomorrow.
• 01:52 Once we've got it, we explore it, then we build our solution, and
• 01:56 then we ship it out the door.
• 01:58 And this has got some problems because,
• 02:00 all of the data in the organization is now inside our spreadsheet.
• 02:03 This is one of the reasons why IT doesn't like Excel.
• 02:05 Because the data all gets stored locally inside there.
• 02:08 In addition, we got way too much data, so things get slow, and
• 02:13 it also makes the product more unstable, which is not so good.
• 02:18 Here’s how your SQL pros, your IT guys, how they develop.
• 02:22 They’ll connect to the data source, and the first thing they do,
• 02:25 they explore their data.
• 02:27 Once they've explored it, they grab as little as they possibly can.
• 02:31 Now, I've heard from a lot of Excel pros, they say, yeah, but
• 02:34 they've got the tools to do that.
• 02:35 They've got SQL Server Management Studio,
• 02:38 they can actually look at this stuff before they bring it over.
• 02:40 Well, guess what?
• 02:42 You have those tools too.
• 02:43 it's called Power Query.
• 02:44 It allows you to go in and preview the data, and see what's there, and
• 02:48 reshape it, and bring down as little as you possibly can.
• 02:50 So we have those tools now too.
• 02:52 We may not have in the past, but we have no excuses anymore.
• 02:56 Explore it in Power Query, grab as little as you possibly can, and then you'll
• 03:00 notice what your IT guys do is, they'll build their package of solution.
• 03:03 And then, before they release it to their users, they actually go back and
• 03:06 they optimize it.
• 03:07 So they say, I didn't actually use that column after all, or
• 03:10 maybe I don't need this measure, so they'll take it out to make this
• 03:13 package even more shrunken down and more efficient.
• 03:16 Once they've done that, they'll release it to the audience, or
• 03:19 their users, in order to work with.
• 03:22 But to be fair, oftentimes in our lives we don't get the option to go back and
• 03:26 optimize something.
• 03:27 And that's fine.
• 03:28 As long as you follow the rule for exploring your data first, and grabbing as
• 03:32 little as you possibly can, you have less need to go back and optimize it.
• 03:35 It's when you grab everything that that becomes really, really important.
• 03:39 Unfortunately, there's not really any good tools to work
• 03:41 backwards through the Power Pivot solution to say, I didn't need this after all.
• 03:47 Working the other way, where we only bring in the stuff we need and
• 03:50 then go back to the well to grab more if we need more.
• 03:53 That's a better way of actually developing.
• 03:55 I can tell you unequivocally, I've developed as an Excel Pro for a long time.
• 03:59 I have done exactly what I've showed on the previous slide.
• 04:03 This method here, hands down better.
• 04:06 When I develop today, this is the method that I follow.
• 04:09 My models are faster, more responsive, more stable, and get me what I need.
• 04:15 And they take way less space on disk to share as well,
• 04:17 so this is definitely a good thing that you want to focus on.
• 04:22 Some other tips for efficient models, again rehashing this one.
• 04:26 Keep your tables short and
• 04:27 narrow, drop those extra columns, filter out those extra rows.
• 04:31 Don't duplicate columnar data.
• 04:33 Use Power Query to reshape your data, and avoid calculated columns.
• 04:37 I didn't even really show you calculated columns inside these models,
• 04:40 because I want you to avoid them.
• 04:42 They take memory to store.
• 04:44 And if you're using a calculated column, which is basically saying this column and
• 04:47 this column over here together in a formula inside Power Pivot.
• 04:51 The challenge is is that, the original column exists, the other column you joined
• 04:55 to exists, and then you make a new column that actually adds more memory.
• 04:58 So you basically duplicate the amount of storage.
• 05:01 So if you're only using these columns to make a key column to link tables,
• 05:04 use Power Query to do it.
• 05:05 Then you just have the data in there once, and that's actually much better.
• 05:08 So, try to avoid calculated columns, if you ever see one of
• 05:11 those come up in the real world, skip it, they're not your friend.
• 05:15 But measures, measures are your friends.
• 05:17 Measures leverage CPU, not memory to calculate.
• 05:21 You perform calculations instead of storing in data in columns,
• 05:25 and you're gonna be way ahead of the game here.
• 05:28 Because again, CPU speeds are measured in gigahertz,
• 05:31 their much faster than the megahertz that memory is actually reading from.
• 05:35 So anytime you can use a measure to actually calculate something
• 05:38 rather than storing it in a table, you're gonna be in a better shape.
• 05:42 The last thing I want to throw out there is,
• 05:45 to break the old text versus values rule.
• 05:49 In old style Excel, what we'd say is, if you're never gonna add,
• 05:52 multiply, subtract, or divide the value, and it's not really a value.
• 05:56 It's actually text.
• 05:58 And things like product IDs were a perfect example of this.
• 06:01 Well, in reality isn't Power Pivot,
• 06:04 numbers compress magnitudes better than text.
• 06:06 Like, we're talking Huge amounts more.
• 06:10 I've seen a table of 334 values in text that'll take 28 kilobytes to store,
• 06:16 334 unique values.
• 06:18 61,000 unique values in the numeric column takes 10 kilobytes, so it takes half.
• 06:26 This is a huge, huge difference.
• 06:27 The bigger the models get, the more this becomes important.
• 06:30 So rule of thumb for
• 06:31 Power Pivot, if it looks like a number, convert it to a number.
• 06:35 How do you do it?
• 06:36 Use Power Query to do it.
• 06:37 So, the tools work very well together.
• 06:40 Follow those rules there, you should have nice efficient models.
• 06:43 But definitely the probably the biggest one is keep it on the short and arrow.
• 06:46 Less data, the more you're gonna be in good shape.

Lesson notes are only available for subscribers.