Yesterday I was willing to invest a couple of hours to install Excel 2013 Home Premium and give PowerPivot a try using the largest dataset I have available locally. A data set from Eurostat keeping passenger and flight volume data going between pretty much all European airports and a partner country on a monthly basis. Extracted from SDMX and loaded into a Postgres database – the table keeps more than 8 million records already provided in long format. Okay – well, it’s not super huge but large enough to get a feeling about performance and usability of a tool. So I was looking forward to PowerPivot-driven data mining and in for a very bad surprise!
Excel 2013 and PowerPivot
To make a long story short – Excel 2013 does not offer PowerPivot for any license lower than Professional Plus … that was a shock! I was expecting a more smooth integration into Excel 2013 and not my toy being taken away. I think this restriction is as short-sighted as it is stupid and unnecessary but – that’s just Microsoft.
Data source integration in Excel 2013
But whatever – so I had a look at what Excel 2013 Home Premium offers by itself. The good news is that importing data from a database via an ODBC adapter is possible without further obstacles and you can even alter the SQL query used for that request. The data is then loaded into a sheet and linked to a source. Now the bad (old) news is they still didn’t do anything about the row number restriction of 1’000’000 and change. Actually that was one of the most important features provided by PowerPivot.
So Excel is trudging through the table occupying temporarily more than 3.4 GB of my 4 GB RAM – never seen something like that before – just to realize half an hour later that the table in question occupies more rows than what fits into a sheet. Being a very helpful person I immediately took action and emailed a nifty SQL hack (“select count(*) from the_table;” – an insightful article is already in the workings!) to firstname.lastname@example.org.
Okay, but Egg-Sell at least offered me to import the data set partially. Was playing around a bit with a pivot table and chart on it – worked smooth – no complaints. But I guess this will also work fine with LibreOffice …
Is there hope?
I think there is hope! Microsoft has escpecially in the last few months already corrected (at least) two very stupid product decisions – the home screen issue in Windows 8 and the restriction coming with restricting a purchased Office license to a PC with its components. Given that users are complaining all over the internet about lack of PowerPivot there is a chance that Microsoft recovers consciousness.