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.

PowerPivot 11

The first step is to download the most recent version which currently is 11. Follow the suggested installation settings and start Excel. You should now see a new ribbon tab labelled ‘PowerPivot’.

MySQL ODBC connector

Next thing we need is an ODBC connector which establishes the connection from Excel to MySQL. The different falvours there are you find here. To choose the propper connector you need to know whether you are using Office 32bit or 64bit. You’ll find this information in Excel by clicking the ‘File’ tab and then ‘Help’ on the left hand side. Below ‘About Microsoft Excel’ your precise version is displayed. The connector needs the same architecture (bits) as Excel.

I remember having experienced problems with the currently freshest connector version 5.2. The symptoms were that everything seemed fine, also the test connections could be established succesfully but I got an error when I fired a simple totally fine query using PowerPivot (with R I did not experience this issue). So I suggest you check out the newest version and resort to an older one in case you experience such problems.

Register the data source in Windows

After you installed the connector you have to register it as a data source in Windows 7. The thing is that Windows 7 handles data sources differently depending on their architecture (bits). If you are using Windows 7 64bit as I do you can open the data source manager for 64bit data sources by entering “ODBC” in the search input field of your start menu or through tracking it down in your system control. There you can register a 64bit connector but not a 32bit one. The 32bit data source manager you are going to find here:

The rest is a cinch – you click “Add..” on the User-DSN sheet, choose the “MySQL ODBC 5.x driver”, fill in the credentials, choose a database (from the select menu) and a data source name and you’re done. It’s the same procedure for 32bit and 64bit.

Configuring the connection in PowerPivot

Back in Excel you go on the PowerPivot section of the ribbon and open the PowerPivot window  (the green icon on the left side). In the ‘Home’ section of that window you will see a small gray cylindrical symbol (the international symbol for “database”) which will suggest to you different data sources to choose from. Take the one where it says “ODBC”.

In the next dialog you click on “create …” (or whatever it is called in your language) – choose the adapter and then “Okay”. Back in the assistant you can check the connection (hopefully with successful result) and proceed.

Now you have the choice between importing the data from tables using an import assistant – I advise to choose the second option which is to write the query yourself. The fancy assistant just didn’t work propperly most of the time when I tried to use it. Problems occur for big tables and also for certain VARCHAR typed fields which seem to hold characters or character combinations which are indigestible for PowerPivot.

Next thing you see is a pretty user unfriendly editor where you can place your query (let’s not complain about having to use a non-mono spaced font – we can be happy they didn’t go with Comic Sans MS). The names or aliases in the SELECT are eventually used for the column names. Here you can do anything ODBC can translate to MySQL – that means almost anything. Grouping, joining and so on.

This is the point where we delve into the wonderful and mysterious world of Microsoft® SQL Server® 2012 PowerPivot for Microsoft® Excel® which I plan to write more about in future articles.