“Don Corleone, we have some information for you.”
For awhile now I’ve been meaning to share a few tips from who I call “the Italians” – Alberto Ferrari and Marco Russo. I reviewed their book awhile back, if you recall. Over the past several months they have discovered a few things that I think you will appreciate.
Tip 1 – <CTRL>-Scroll Wheel to zoom the measure window
OK, we all know that the formula measure editor window uses a small font:
PowerPivot Measure Editing Font is Small
Alberto discovered that if you hold down the <CTRL> key on your keyboard, and scroll the mouse wheel, you can increase the font size!
Bigger Font in the PowerPivot Measure Editor
Neat huh? Funny thing is, this does not work on all of my computers. I use different mice, different versions of Windows, etc. – I scramble all of the variables. So it’s hard to know why it doesn’t work everywhere. (In fact, the two screenshots above were taken on different machines).
While we’re on the topic, you may also want to consider writing your measures *outside* of this dialog, so take a look at this post on using Notepad++ written by Colin Banfield.
Tip 2– Sort data before it’s imported to improve perf
Another Alberto discovery: Believe it or not, when PowerPivot imports data, the sort order of the incoming data actually has an impact.
The original post is here. Bottom line: you can shave 25% or more off your workbook size if the data coming in is sorted ahead of time, and Alberto’s results suggested that it doesn’t matter much which column you sorted by.
NOTE: We are NOT talking about sorting data AFTER it’s been imported. We mean sorting it during the import process. Sorting after import has ZERO effect.
Tip 2a – Smaller workbooks are faster workbooks
He also noted that pivottables built on those smaller workbooks are faster than on the workbooks created from unsorted data.
That 100% matches my expectations, because PowerPivot’s compression is not just an on-disk compression – that compression is also maintained when the data is loaded into memory, AND is used to speed up queries!
So I have been telling people for a long time: smaller workbooks are generally faster workbooks. You can use workbook size as a rough indicator of how fast it will be.
Tip 2b – The column you choose DOES matter, if…
At Pivotstream we recently had a SQL table that just refused to import. PowerPivot basically just locked up every time we tried to import it.
The table was sorted by a column with millions of distinct values (the dollars column). When we sorted it by basically ANY other column, it imported just fine.
And this was only a 1.5M row table. It seems that certain data sets can just chew up massive amounts of RAM during import if they are not sorted properly.
This discovery was what triggered me to go back and share all of the tips above, so it was a fortunate find.