Locked lesson.

Upgrade

About this lesson

"Subscription Excel vs Non-Subscription Excel" and "32 bit vs 64 bit" are two huge points of concern for Power Pivot modelers. Which do you need, why is it so important, and how can you make a case to get them?

Exercise files

There are no related exercise files for this lesson.

Quick reference

The Case for Current

A discussion of the best configuration for Power Pivot modelers

When to use

When you want to set yourself up for success with the most stable and performant Power Pivot experience

Instructions

Subscription vs Non-Subscription

  • Quality went down from 2010 through 2016, with non-subscription Excel 2016 being the worst
  • Microsoft is pushing out new bug fixes and performance improvements on a regular basis on the Subscription version
  • Excel Subscription is by far the best version of the software to be on for Power Pivot as it has received a great deal of critical stability fixes that the other Excel versions will never see

32 bit vs 64 bit Office

  • Power Pivot is an in-memory database engine and needs RAM to run effectively
  • More RAM increases both speed and stability of Power Pivot
  • 32 bit Office limits how much RAM you have access to
  • 64 bit Office unlocks the RAM limits
  • Power Pivot modelers are the ONLY people who truly need 64 bit Office

Potential Issues that may arise with 64 bit Office

  • Custom VBA code that makes use of Windows API calls will need to be re-written to use 64 bit API calls.  If you don’t run any VBA based solutions, this will not be relevant to you, and if you do… you need to get on this.  (Your IT department should know if this is an issue in your organization)
  • 3rd party add-ins may only be available for 32 bit Excel.  If you do not need these add-ins to work effectively, then this is a moot point – uninstall them as they are just slowing Excel down.  You can locate these tools by going to File --> Options --> Add-ins --> Manage: COM Add-ins --> Go…  Test if you can run without them by unchecking the box and restarting Excel.  (If you DO need it, go back to the same place and turn it on again)
  • Many vendors HAVE re-written their tools to be 64 bit compliant.  If they haven’t, they need pushback from you to do it. Until they hear that users need 64 bit versions, they aren’t going to invest the time in a costly re-write
  • 3rd party software that pushes to Excel should not be affected by a move from 32 to 64 bit Office

Hints & tips

  • Even though you have 64 bit Windows, you most likely don’t have 64 bit Office (check Help --> Account --> About Excel)
Login to download
  • 00:05 So as you start building Power Pivot solutions,
  • 00:07 there's gonna be some things that become somewhat important to you,
  • 00:10 and I wanna sort of go through those right now.
  • 00:13 The first one is about subscription versus non-subscription versions of Office.
  • 00:17 And the big question is, who really needs an Office 365 subscription anyway?
  • 00:21 Can't you just go and buy the old stand-alone, install it once and
  • 00:24 keep it forever kind of Excel?
  • 00:26 Well, the reality is, we all need subscription but
  • 00:29 especially Power Pivot modellers.
  • 00:32 The benefits of moving to Excel 2016 on subscription are actually pretty high.
  • 00:36 And this is just my opinion, but as far as the actual stability of the product and
  • 00:41 the way the features go, I classify it to look like this.
  • 00:45 In the 64 bit version Power Pivot, which is what I would really push you to do, and
  • 00:49 we'll talk about that more in a little bit here.
  • 00:52 The stability was actually pretty good in Excel 2010.
  • 00:55 In Excel 2013 it went slightly down hill and
  • 00:58 then in the Excel 2016 non-subscription version, it was horrendous.
  • 01:01 This is the original release that happened around November 2015 or
  • 01:06 October 2015 timeframe.
  • 01:07 There was all kinds of bugs and it was brutal.
  • 01:10 But once you got on to subscription, they actually went through and
  • 01:14 patched the specific Power Pivot bugs in order to fix these things up.
  • 01:18 And the quality inside the Excel 2016 subscription builds is exceptional.
  • 01:23 It's getting better and better all the time as they keep on patching things and
  • 01:26 pushing them out, but
  • 01:26 those patches don't necessarily go back to the previous versions of the program.
  • 01:30 So the faster you can get yourself onto a subscription build of Office,
  • 01:34 the better off you'll be.
  • 01:37 The other thing is,
  • 01:37 you'll notice is there is a breakdown between stability of 32 and 64-bit.
  • 01:41 And we'll talk a bit about that as we go through.
  • 01:46 Now, before we get into the 32 versus 64 debate,
  • 01:49 I do wanna give you some simplified computer baselines here.
  • 01:52 We've got a few different components in your computer.
  • 01:55 We have CPU.
  • 01:56 This is typically what we use for processing and calculation, and
  • 01:59 it's usually the fastest part of your system.
  • 02:01 When you actually go to buy a new computer, you'll find that your processors
  • 02:05 have speeds measured in gigahertz, which are very, very fast.
  • 02:09 We also have another component that we use all the time which is called RAM.
  • 02:13 This is for storing data.
  • 02:14 It's the random access memory and this is much faster than actually reading from
  • 02:18 your disk's memory, but it's still slower than calculation speed.
  • 02:22 If you actually go and look at RAM,
  • 02:24 you can prove that out by seeing that the speed of RAM is typically measured in
  • 02:28 megahertz where gigahertz are about 1000 times faster.
  • 02:32 We also have disk.
  • 02:34 Disk is a term intended for long term storage and it's very slow to read and
  • 02:39 retrieve compared to RAM.
  • 02:40 Now flash disks or flash memory is obviously much faster.
  • 02:44 When you've got the old spinning drives, they're much, much slower.
  • 02:47 But the big key here is that when you actually look at speeds,
  • 02:50 disk is very slow, RAM is quick, and CPU is lightning fast.
  • 02:55 That's kinda the simplified way of looking at this.
  • 02:58 Why does this matter?
  • 02:59 It matters because of 64BIT Office.
  • 03:03 And it matters because of Power Pivot.
  • 03:05 Power Pivot is an in-memory database engine.
  • 03:08 Database gets retrieved, compressed, and it gets loaded and stored in RAM.
  • 03:13 Basically, everything you do in Power Pivot is working with data that's in RAM.
  • 03:19 And because of this, the limit of RAM that you have is actually pretty important.
  • 03:25 In 32 bit Excel you have a maximum of 2GB that's available to be addressed
  • 03:29 from the Excel system.
  • 03:31 Once you actually take off what's needed to run Excel and the other stuff going on,
  • 03:34 you pretty much have about 1.5GB of RAM that you can access.
  • 03:38 And this is true even if you have a machine that has 32GB of RAM in it.
  • 03:41 You'd still only get about one and a half to run your Power Pivot models.
  • 03:46 Once you get to 64bit Excel,
  • 03:48 you can use up to 8TB of RAM providing it's in your system, of course.
  • 03:53 But it gives you access to a lot more RAM.
  • 03:57 Why is this important?
  • 03:58 Because Power Pivot is an in-memory database engine, and
  • 04:01 it stores stuff in RAM.
  • 04:03 Now what happens when you exceed this limit?
  • 04:04 Well if you do, Excel will tell you that you don't have enough RAM, or it'll crash,
  • 04:09 or something like that, which is probably better than even reading from disk anyway.
  • 04:13 The key is, you don't really wanna run out of limit.
  • 04:16 Truth is, Power Pivot is the only reason you need 64 bit Office.
  • 04:20 You don't need 64 bit Office to read your email.
  • 04:22 Or if you do, well, I feel really bad for you actually.
  • 04:25 You don't need 64 bit Office to write Word documents.
  • 04:28 What you need 64 bit Office for is working with Power Pivot.
  • 04:32 Extra RAM increases the speed of your Power Pivot models,
  • 04:35 because it can push more stuff through.
  • 04:37 Extra RAM substantially increases the stability of your models, because it's got
  • 04:42 more RAM to work with in order to move things around and actually report on it.
  • 04:46 The thing to remember though, is that Windows bitness is not Office bitness.
  • 04:50 Just because you're on 64 bit Windows,
  • 04:52 there's no guarantee you have 64 bit Office at all.
  • 04:55 As a matter of fact, chances are pretty good you don't.
  • 04:58 Because Microsoft still recommends 32 bit Office as the standard default,
  • 05:02 which is why IT goes and installs that.
  • 05:05 To check what you're running, what you can do is go to File, Account, and
  • 05:08 About Excel.
  • 05:09 And at the end you're see that it will be tagged with either 64 or 32 bit.
  • 05:14 You really want 64 if you're working with PowerPivot.
  • 05:17 That's the number one thing I want to get through to you here.
  • 05:20 But what if you go to try and actually get it installed?
  • 05:24 You ask IT, hey I really want 64 bit Office, and they come back and
  • 05:27 they say no.
  • 05:30 Why?
  • 05:31 Well, because as of Excel 2016 Microsoft still has a recommended installation
  • 05:36 of 32 bit Office.
  • 05:37 This is a huge problem because it actually gives your IT department and it gives,
  • 05:42 more importantly, vendors, the ability to fall back and say hey, you know what?
  • 05:46 This isn't recommended.
  • 05:47 So we're just gonna stay with the recommended specifications.
  • 05:49 Sorry, too bad.
  • 05:50 Come back to us when that changes.
  • 05:52 Why is it a big issue here?
  • 05:54 Why is it that Microsoft still recommends it?
  • 05:56 Well that one I'm still trying to work out, to be honest.
  • 05:58 But the issues around this is that there's a lot of
  • 06:01 third party Excel add-ins that get installed to run different things.
  • 06:05 Maybe it's a connection into your big Oracle Server or something else.
  • 06:09 And they're written in VB6, which is a 32 bit compatible language.
  • 06:13 The problem is,
  • 06:14 these add-ins need to be re-written by the developer to be 64 bit compliant.
  • 06:19 Re-writing a 32 to 64 bit add-in takes time and effort and money.
  • 06:23 So if developer doesn't need to do it, if they can say hey,
  • 06:26 Microsoft still recommends 32 bit Office, so we don't work with that.
  • 06:30 Sorry about that, come back when that changes, you're in trouble.
  • 06:33 There's no much you can do here.
  • 06:35 Now, as long as you can live without these add-ins, let's say somebody is installed
  • 06:39 like a V LookUp helper and that's what is holding you back from 64 Bit Office.
  • 06:43 As long as you can live without them, what I would offer you to do is say,
  • 06:46 hey IT Let me beta the conversion.
  • 06:48 I'll live without this add-in.
  • 06:50 The reality is that vendors need pushback from users or they won't rewrite it.
  • 06:54 There's a big investment that needs to take place here, but
  • 06:56 until users push back and say this is really, really important or we're not
  • 06:59 gonna use your software anymore, they're not really motivated to rewrite it.
  • 07:03 There's a reference guide attached to this module with more detailed write up
  • 07:07 of the issues that you can hopefully share with IT in order to get buy-in
  • 07:10 to move you to 64 bit.
  • 07:12 It really is the best place to be for Power Pivot.

Lesson notes are only available for subscribers.

Gift this course