PowerPivot and mySQL - is it really impossible?

[unclesomebody]

I've trawled the depths of Google (beyond page 20 of search results!) to try and find a solution to my current problem. Given that I haven't found a solution I'm tempted to believe that it's impossible but being an eternal optimist I've now decided to post something in here.

Outline of what I want to do:
Connect PowerPivot to a mySQL database. Import some or all of the tables AND maintain the relationships between the tables.

The Problem: The AND bit is where it's all falling apart. I have managed to get my ODBC drivers up and running (multiple versions in fact). I can connect to mysql without a problem and I can manually write some code to pull down some information. That all works perfectly fine and it's great. But it's somewhat redundant if I can't maintain the relationships between the tables! There are approximately 30 tables, some with 50+ columns, and several with millions of rows (max is 4 million rows). As a starting point I want around 15 tables (including the ones with millions of rows). I could just select them individually using a sql command and then build the relationships in powerpivot (via diagram view) but I don't think this is efficient and it means that any relational changes made in the database won't propagate down to excel.

Currently, if I try to use the table import wizard I just get an error. In all my googling I've not found anyone who's managed to use the table import wizard correctly with a mysql database and I think this is because of the difference in syntax between SQL and mySQL. Please correct me if I'm wrong (as if often the case)

My goal:
To have powerpivot connected to a mysql database so that I can use it to build a dashboard and perform analysis on near real time data. I want to be able to open excel, refresh the tables in powerpivot, and then have my dashboard update. This is as a starting point (I'd clearly like to make it a bit more advanced once I get the basics up and running).

My question:
Is this possible? Can powerpivot connect to a mysql database and pull down the data with the relationships preserved?

 

[xChillout]

i am also interested in an answer to this question. thanks for posting unclesomebody

 

[unclesomebody]

Given that no one has replied, I'm going to just go on record and say I think it's impossible. This is a real shame as I think PP is wonderful but combine this problem with the lack of incremental refresh and it begins hard to understand how anyone could use it with anything approaching a big data set.

 

[RickSchultz]

Hi Uncle. I just stumbled across PowerPivot today, and will be using it with a couple of large MySQL databases, so I'm gonna have to hope that you're wrong.

As I understand it, PowerPivot needs you to create an SQL query to pull the data from the database (like I said, I'm just starting, so I could be way off here!). In my experience with MySQL, the query needs to define the relationships in the "FROM" clause with "JOINS". Are you finding that the joins don't work as expected? I'm sorry, I don't know what the table import wizard is - I go straight for the queries!

BTW, one thing I know will work - you can use a macro to pull data from your database into Excel. I've made several "data pumps" on MySQL which pull data into Excel. Once the data is in excel, you could link to that dataset using powerpivot. Not as elegant, but it's easily refreshable.

 

[theBardd]

I haven't done it personally, but I would have thought you could pull from MySQL into PowerPivot, as long as you have an ODBC driver on your machine. Again, I would hope that the database structure would be exposed to you, you wouldn't need to write an SQL query with the joins and such, PowerPivot can handle that.

 

[theBardd]

Just found a blog that confirms what I said, How to Use MySQL and Microsoft PowerPivot Together | Carolinian Business Intelligence, it can be done. Download the ODBC driver, and you see the database structure so that means you can use the PowerPivot Table Import Wizard.

 

[RickSchultz]

Hi Uncle,

OK, I've spent some time with Powerpivot, and I think I have a better understanding of your issue. I'm sorry for posting before I really knew what you were referring to!

The blog that theBardd referenced notes, at the end, that ODBC links built through the Table Wizard create SQL code with brackets, and MySQL does not support brackets - that's why you're not getting data through the Wizard. However, I read somewhere that an earlier ODBC connector, 3.26.15 (I think...) DID work with the Wizard. That might be something to try if you definitely want to use the Wizard.

You mentioned that you can write SQL code to pull data from tables, and you can link them in the diagram view. I've done that too, and to refresh the data, I just use the Refresh button in the Powerpivot window. This gets new data and doesn't break any existing relationships. So, that part of your requirement IS possible.

Where I'm a bit confused is that you want to capture "new" relationships in the table automatically. Do you mean that you are adding new secondary keys in the database as you build it out, and you want PowerPivot to automatically capture these and connect them? That's a potentially dangerous thing, and not something I would recommend if it were possible. Crystal Reports has similar functionality, and it always led to problems.

In short, I can see how you can create tables, link them, and refresh the data without needing to recreate the links. Making new links to new columns should ALWAYS be done manually to avoid data corruption.

So, I think what you want to do CAN be done, but I may have misunderstood your objective. Can you clarify for me, please? Maybe point out how my suggested solution would not work in your case?

Thanks, and best of luck!