How to Use MySQL and Microsoft PowerPivot Together 8


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.   


About Benjamin Goewey

Founder, President, and Principal Consultant of Datamensional, LLC.

LinkedIn Twitter Google+

Ben has been passionate about providing actionable, relevant, and timely information for decision makers to help them make decisions more quickly and effectively and helping transform organizations as a result. He has been a Business Analytics and data guru for nearly 11 years and enjoys seeing the positive impact an effective solution can have on an organization.

Ben currently leads a team to bring the best practices of Business Analytics as well as hands on implementation to companies big and small.

Specialties: Performance Management, Analytics Strategy, System Architecture, Sales Management, Sales Analytics, Marketing Analytics, Business Intelligence, Reporting, Data Integration, IT Management, Data Warehousing, Management Decision Support, Dashboards, KPIs, Automation.


Leave a Reply

8 thoughts on “How to Use MySQL and Microsoft PowerPivot Together

  • Roberto Cobo

    I’m in the importing step :

    Just tried firt choyce and error appears regarding a syntax I haven’t typed. The reason are brackets, as you said.
    Second try with hand written ” select * from claim ” and never returns.

    table1 is very simple:
    1 B2007-11-051 broken window 150 1 2007-05-11 1
    2 C2007-04-131 Stained carpet 1000 0 2007-04-13 2
    3 C2007-22-011 Stolen camera 650 1 2007-01-22 2
    4 B2007-04-011 House burned d 350000 1 2007-01-04 3

    windows 7 enterprise SP1
    excel 2010 and PowerPivot for excel 2010 just downloaded from Microsoft.
    mysql-connector-odbc-5.1.9-win32.

  • Lucas

    mysql-connector-odbc-5.1.9-win32, don’t use this one, remove it and install the 5.1.8.
    Go to odbc and setup your connection.
    use the MySQL Workbench, connect to the database,
    Go to the table and select edit table data( right click on the table ),
    Check the SQL Script at the output window. Copy it,
    example:
    “3 13:51:04 SELECT * FROM a_database.sites LIMIT 0, 1000 29 row(s) returned

    I use “SELECT * FROM a_database.sites ” without the brakets in the Table import wizard.

    Hope it helps.
    Lucky Luke

  • Robert Reardon

    When importing various queries I get the following:

    An unknown error occurred converting to the data type table ‘d7a26a93-898e-4a4b-a519-a316883ee072′ column ‘name’.

    The operation has been cancelled.

    Any Idea what to do? All queries have been previously executed in mySQL.

    Thanks.

  • Miguel Escobar

    @Robert
    that’s the error that you get when one of the fields in the table is not supported in the powerpivot environment. Try creating a query with only the fields that you need and leave unnecessary fields out of the importing process.

    Let me know if that works.

    • timo

      I’m having the same issue. Creating a query without the erroring field works. The filed type in question for me is always ‘text’. Varchar type works fine.