Powerpivot Hardware Considerations

[StrafeXL]

I have been developing a data model at work that is probably not hugely demanding, but I am just getting started on it. The workbook size is currently ~6MB but I already have dozens of measures that are dependant on others as well as dozens of pivots. Admittedly the biggest table is not yet denormalized (I just found out today that Data Explorer added native unpivoting, WOOHOO!) so I expect performance to improve but I also have more data sources to add.

I've been building this on an older quad core i7 with an SSD and 8GB of RAM (Win7, Office 2013 64bit, but I'm going to have to go back to 2010 for compatibility purposes ARGH!). I have not really had any performance issues other than when adding or modifying a measure it can take half a minute or more for everything to recalculate (or whatever it's doing). On a side note, what is the bottleneck at this point? The CPU is not particularly stressed. It does seem to be using all physical and logical cores but only spikes to ~30% usage. Memory usage is not more than a few GBs. The Disk IO does seem to max out at times but it isn't always maxed out, and isn't the cube in RAM anyway?

Getting to the main point, I'm getting a new machine. I have the choice between two different company offerings: a dual core i5 with 8GB of RAM and an SSD or a quad core i7 with 16GB and a conventional HDD (actualy it looks like it may be a hybrid, there is some sort of flash cache).

I'm a beleiver that an SSD is the biggest performance improvement you can add to a modern PC, however, the dual core i5 (no hyperthreading) is lacking. Based on the performance I've seen on my current machine, I'm not quite sure which way to go. I guess the best thing to do is to try both the machines out, and I have been offered that option, but there's a time component here in that I don't have any.

Any recommendations or general comments on how to remedy bottlenecks?

Thanks!

 

[Jacob Barnett]

Your bottleneck here is more likely to be model/measure design than hardware! As you say flattening out that fact table should improve things.

I am working on a laptop that has an older i7 CPU (M620 2xCores) and 8GB of RAM and it happily handles complex queries on massive models. Although there isn't a direct link between file size and RAM usage, your 6mb just can't contain enough data to be the only cause of your bottleneck if the model is well designed. As an illustration I have a 400MB model that contains 13m sales records over 7 years and I can get it to return a relatively complex YOY% sales comparison by store by financial week in under 3 seconds. It will give me the same by product class in under 8 seconds which is a Pivot with over 1m cells!

If you are talking about a new machine at work, you would be mad not to take one that was on offer but its not going to magically solve this issue. I'm guessing here but I don't see what the SSD will do for PowerPivot so I'd go for the i7 16GB all day long.

I'd be happy to contribute to a discussion on improving the performance of your model if you wanted to start a thread on it!

 

[StrafeXL]

Thanks for the response Jacob. I think you're correct, although an SSD would be more beneficial to the PC overall, the PowerPivot cube is all in memory. The i7 with 16GB and a conventional HDD is probably the way to go. I do want to clarify one thing however. The 'bottlenecks' I'm experiencing are only when during development and I add a new measure or modify an existing measure. As soon as I click OK in the Calculated Field dialog, Excel starts to crunch away with the 'reading data' message flickering down at the bottom of the window. In normal usage, like when clicking a slicer, I'm well within the 3 seconds of now. I'm just curious, if the CPU, memory and disk IO aren't maxed out, why am I still waiting when adding or modifying a measure?