Overview

This document shows how an Open Source Database like MySQL can be used with Microsoft’s popular Excel plugin PoweverPivot.  Many clients enjoy the familiarity of Excel and ease of use that PowerPivot brings but the clients do not want to married with any one particular vendor in their Business Intelligence process.  Consumers should have the choice and be aware that they can mix and match their solutions if they so choose.  

Technologies Used

Please go to the corresponding web pages to download if you don’t have them.

• Windows 7 x64
• Microsoft Excel 2010 
• Excel PowerPivot Plugin (http://www.powerpivot.com/)
• MySQL Workbench 5.2 (http://dev.mysql.com/downloads/workbench/)
• MySQL 5.5.13 (http://www.mysql.com/downloads/mysql/)
• MySQL 5.1 ODBC Driver (http://dev.mysql.com/downloads/connector/odbc/)

Define your MySQL connection on Windows

If using Windows 7, go to the search bar at the start menu and type in “Data Sources” and then click on the Data Sources (ODBC) icon that appears.  A window will pop up.  Verify your installation of MySQL ODBC Driver has installed by click on the Drivers Tab.  Look for the MySQL ODBC 5.1 Driver listed on the left:

Click on the System DSN tab and then click Add.  The MySQL Connector window will open up.  Fill in the appropriate information.  If you’ve installed it locally, type local host for your Server or the IP Address , or the host of your remote server.  Be sure to test your connection and give your connection a meaningful name and click OK.  

You should see your connection as below (Other connection names erased for confidentiality):

Click OK .

Viewing your MySQL information with MySQL Workbench

The reason I include showing how to use MySQL Workbench is to show those new to SQL or MySQL how to get started easily by visualizing the database.  MySQL includes a very useful tool for seeing the objects in the database.  It also does a variety of other useful features in one place and is somewhat similar to SQL Server Management Studio and the biggest advantage is that it’s free.  

When you first go into the Workbench, you’ll have to define a new connection.  Click new Connection at the bottom left of the screen:

This screen will pop-up: 

Fill out the information like you did for the ODBC driver.  I chose my default schema to be my “scratch” database.

Click on the close button.  Now double click on your new connection you’ve just created:

You’ll see how it organizes all your tables and views very nicely by schema.  You can very easily see the table structure.  This is what I recommend using to build your SQL or explore the database.  The part that malfunctions when using MySQL with PowerPivot is when you try to import whole tables.  However, it’s easy to write simple SQL using Workbench to put into Power Pivot.   Workbench can write a lot of it for you. Not only that, you’ll learn more about your data by seeing it visually.

Connecting MySQL to PowerPivot

Open Excel and then click on PowerPivot tab and then on the PowerPivot Window icon.  You’ll want to then click on the “From Other sources” icon:

Scroll down and then go to the Others(OLEDB/ODBC).  Click on it and then click “Next” and the Table Import Wizard will come up.

Let’s call our connection “Sales.”  Then click on the build button.  What’s a little tricky here is that the window goes to the “Connection” tab first which defaults you to a Microsoft SQL Server driver first.  Click on Provider and then select  Microsoft OLE DB Provider for ODBC” as shown below:

Click “Next” at the bottom.  Find the Local MySQL we defined earlier or your remote MySQL.  Select your default catalog (schema) to use and then test your connection.  Once tested press OK.  

You’ll see an OLE DB connection string created for you.  Now you’ll want to press next:

The first option below will not work because MySQL does not allow brackets in the SQL written to it.  This is the SQL that PowerPivot writes by default.  You can use this option to see what’s available in the database to create a simple Select * form table format.  You can at least the list of tables and be able to use that to get a simple Select * (this is all that this wizard writes for SQL anyway).

Click Back, and go back to the second option “write a query that will specify the data to import.  Write a simple query to get the table you’re looking for.  We will grab the sales table.  You can click the validate button to make sure your SQL is good to go:

Click finish and you’re done!  You’ll be able to import the data right into PowerPivot without any issues. There are a lot of different types of databases that can be imported into PowerPivot because most databases have ODBC connectivity.  The part that makes it tricky is that:

Not all databases allow brackets in their SQL, but this is easily overcome by using your own SQL.  You can still preview the tables to make it easier to know what tables to grab.

That you have to use the Microsoft OLE DB for ODBC.  I wonder if Microsoft was trying to make it harder on you by using the SQL Server driver by default and putting you on the connection tab by default.  . . .

You now can use nearly any database you want and enjoy the ease of use of PowerPivot.  Enjoy!

Using a non-Microsoft OLAP Engine?

You can in fact use an OLAP data source other than Microsoft Analysis Services, but it’s a little harder to implement like Pentaho’s Mondrian OLAP engine.  One easy way to do it is to use  JasperSoft’s ODBC connect.  Unfortunately, it’s not Open Source.  I’m sure there’s a way to to do this since it looks like it works based on XMLA.  I believe that’s how SAP’s OLAP engine works with PowerPivot.