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.

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.

32bit data source manager in Windows 7 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”.

PowerPivot dialog

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.

PowerPivot dialog

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.

7 thoughts on “How to set up PowerPivot and make it talk to MySQL

  1. Is there a limit to the size of the MySQL database for connection with excel (dataconnection) and PowerPivot?

    I’m working with a large database, and can’t seem to import data

    • Hi Damien, yes there are limits. I am uncertain about the precise values but to ballpark it: 1.3 GB for 32bit Excel due to RAM access restrictions and 4 GB for 64bit Excel due to FAT file size constraints. And then there are some fuzzy limits due to timeouts in the network domain if the data fetching takes too long.

  2. Thank you for your Article How to set up PowerPivot and make it talk to MySQL.  I was able to download and install PowerPivot and install the ODCB Driver and I can connect to the MySQL database.

    As you pointed out the wizard failed.

    OLE DB or ODBC error: [MySQL][ODBC 5.1 Driver][mysqld-5.6.11-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘[country].* FROM [country]’ at line 1; 37000.
    An error occurred while processing table ‘country’.
    The current operation was cancelled because another operation in the transaction failed.

    I do not know MYSQL but do understand Excel.

    I can import one table by using Select * from Country but how do I select all 9 tables?

    Thank you.

    Carl Walton

  3. I’m very curious what MySQL ODBC version you fell back to, i’ve tried it with 5.2 and get an error, tried it with 5.1 and don’t get an error, but the import keeps running infinity. Some other users are also facing this bug, would be cool if you found a sollution…

Leave a Reply

Your email address will not be published. Required fields are marked *