Using MySQL as data source in Microsoft SQL Server Analysis Services

Question

Hi, I have installed the latest .net connector (http://www.mysql.com/downloads/connector/net/), I can add MySQL databases as Data Sources, I can even browse through the data from Business Intelligence Studio.

The problem is that I CANNOT create a datasource view, or if I do create one without tables, trying to add them after the fact gives me the same error.

Specifically it looks like the data source view wizard tries to submit queries against the MySQL database using square brackets/braces, and the query bombs.

I get an error message like:

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 '[my_db].[cheatType]' at line 2

So, in summary, has anyone been able to create a data source view using MySQL tables and, if so, can they please show me how this can be done.

Thanks for any help!

Answer 1

Tried using named queries instead of tables?

Create a new named query in the DSV, as SELECT * FROM MySQLTable, then build in the DSV on top of that.

Answer 2

Using named queries still generates the [ ] SQL queries and fails likewise. I tried that out :)

Answer 3

I would recommend using SQL Server. You will save yourself a lot of time and headache trying to do it this way. If your tables don't need transforming, just use SSIS to move the data from MySQL to SQL Server.

This is what we did at my previous employer - OLTP was MySQL and we built the warehouse in SQL Server, then used SSAS off of that warehouse for the cube.