about turning data into insightful knowledge – for business and personal curiosity

Main menu

Tag Archives: PowerPivot

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!

I think this topic is introduced best using an example. Let’s assume an offline store and we are responsible for the data analysis. The boss wants us to count the people visiting the shop considering their age group and also how many of those visits lead to a purchase.

PowerPivot itself cannot be automated with VBA. But with a few tricks we can control how the data drawn from PowerPivot is accessed within Excel. This article assumes that you know how to import data with PowerPivot and how to integrate it into an Excel file as a pivot table. Also you should have activated the developer tools in Excel. There you find all the VBA relevant stuff like the macro recorder and the dialogue controls you want to add.

Let’s get it started with an example

We start with a PowerPivot table (named “data”) keeping the values for two variables A and B for every day in 2012 imported from a MySQL-DB in this case. So in the PowerPivot window this would look something like that:

So let’s assume some manager is asking for a dashboard displaying those two values in a chart but only one at a time and s/he is not inclined to deal with pivot table field lists but wants to switch between A and B using buttons.

Setting up PowerPivot – that means all the way from downloading it and successfully fetching data – keeps a number of pitfalls in readiness. To spare you the sweat I will give you some guidance on this topic. I assume that you are using Excel 2010 on Windows 7 and that you want to connect to MySQL 5.

Managers like Excel and that’s why you will have to use this application when dealing with or being responsible for data and figures in your company. And actually I think Excel is not that bad at all – as long as you know what to use it for and when to resort to other tools. What I like about Excel the most is for one the pivoting tool which is very convenient and efficient to use when you have the data structured appropriately. Another thing is that Excel provides a very convenient and powerful bridge between the data and its representation in dashboards and reports ready for the C[here comes a capitalized letter]O. This bridge is mainly built of different ways to reference and process individual cells and groups of cells and aggregate and arrange their values in a way appealing to the eye – or worse turn it into a PowerPoint slide.