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!
Tag Archives: PowerPivot
PowerPivot won’t get loaded
Sometimes it happens that PowerPivot crashes and is not loaded again automatically when starting Excel. Here is how you get PowerPivot back on track.
- Open the “File” section from the ribbon
- Choose “Options”
- Choose “Add-Ins”
- At the bottom on the right hand side you’ll find a select box labelled “Manage”
- Choose “Disabled Items”
- Click “Go …”
- Choose the item referring to PowerPivot
- Click “Enable”
- Restart Excel
That’s it. In my experience no handicaps are to be expected. The crashes seem pretty random to me. Like a sudden nervous breakdown – just that Excel doesn’t need time-off to recover.
Filtered measures with CALCULATE() in PowerPivot
(This article expects from the reader basic experience with PowerPivot. You should know how to access a PowerPivot table from Excel and how to turn it into a pivot table or a chart.)
KPIs for a grocery store
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.
Controlling PowerPivot-driven pivot tables and charts with VBA
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.
How to set up PowerPivot and make it talk to MySQL
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.
Advanced business reporting with PowerPivot for Excel 2010
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.