How do I use MySQL as data source in Microsoft SQL Server Analysis Services?

Question

I have installed the latest .net connector, 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

How can this be done?

Answer 1

Get the MySQL ( 6.3.5 ) .net connector ( from MY SQL ) Edit the connection string ( in the designer paste in the connection string ) so that it has the new "SQL SERVER MODE=True" option.

Apparently it affects performance - but what can you do

My sample connection string...(passwords & userids omitted )

server=svr215;User Id=;password=;Persist Security Info=True;database=dw;sql server mode=true

Answer 2

For any of you facing this issue more recently, there are two approaches that you may want to consider as well

1. Use the DevArt dotConnect connector at http://www.devart.com/dotconnect/mysql/. Using the .NET data source, you can connect your SSAS cube directly to MySQL.
2. Some have worked with doing OpenQuery connections to MySQL by connecting to SQL Server for the DSV and then using a linked server connection to MySQL. Some of those details are noted within http://dennyglee.com/2012/09/26/sql-server-analysis-services-to-hive/.