Using MySql as source for cube in Analysis Services (SSAS)

Jac · Post by **Jac** » Tue 24 Jul 2007 07:08

I am trying to build a cube in Business Intelligence Studio, based on a MySql database.
It works ok, untill I try to do a process dimension (which is to actually read the data from the DB).

The error I get is the following:

Code: Select all

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: 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 '[kvhxservices_development_municipalities].[kommune_nr] AS [kvhxservices_developm' at line 3.

The query being issued is the following:

Code: Select all

SELECT 
		DISTINCT
	[kvhxservices_development_municipalities].[kommune_nr] AS [kvhxservices_development_municipalitieskommune_nr0_0]
		FROM [kvhxservices_development].[municipalities] AS [kvhxservices_development_municipalities]

The culprit is of course the square brackets around the table and field names.

The problem is that the SQL is auto generated by SSAS, so I cannot manually remove the brackets, as it is otherwise suggested in this forum.

Do you have any solution for this problem?

I am using CoreLab.MySql.dll Version 4.0.13.0

/Jacob

Alexey · Post by **Alexey** » Tue 24 Jul 2007 12:12

Could you send me your SSAS project? (Use e-mail address provided in the Readme file.)
Why can't you manually correct the query? In source file...

Jac · Post by **Jac** » Tue 24 Jul 2007 14:15

I have sent you the project, but as I have written in the mail, the SQL is not pregenerated but will be generated at runtime by SASS.

Alexey · Post by **Alexey** » Thu 26 Jul 2007 12:32

I've reproduced the problem.
Now we are investigating it.
Look forward to hearing from me again.

Alexey · Post by **Alexey** » Thu 26 Jul 2007 13:09

Make sure that CoreLab.MySql.xsl file exists in the "%ProgramFiles%\Microsoft SQL Server\MSSQL.4\OLAP\bin\Cartridges\" folder on the PC where Analysis Services reside. If you didn't install MySQLDirect .NET on that PC, perform using the minimal install mode.

Serious · Post by **Serious** » Thu 26 Jul 2007 13:37

You should also restart "SQL Server Analysis Services" service after installing MySQLDirect .NET.

Jac · Post by **Jac** » Thu 26 Jul 2007 13:52

I dont have the .xsl file nor can I find it anywhere, and I have installed MySQLDirect on the same machine (I only have the same).

My SQL server path is
D:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\Cartridges\

but %ProgramFiles% is c:\program files could this be confusing the installer?

Could you mail the file to me?

/Jacob

Alexey · Post by **Alexey** » Fri 27 Jul 2007 07:38

Try to search that file here:

Code: Select all

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\DataWarehouseDesigner\UIRdmsCartridge\

Jac · Post by **Jac** » Fri 27 Jul 2007 11:58

Nope it is not there either. (My VS is also installed on the d drive btw).

I have searched the entire c and d drives, but the file is not there. And I have tried to reinstall MySqlDirect, but it does not show up. Is it not included in the trial version?

/Jacob

Alexey · Post by **Alexey** » Fri 27 Jul 2007 12:06

I'd sent you that file. Please check the mail.

Jac · Post by **Jac** » Fri 27 Jul 2007 13:01

Thank you very much for the prompt replies. It is working now.

Alexey · Post by **Alexey** » Fri 27 Jul 2007 15:00

You are welcome.

sames2020 · Post by **sames2020** » Mon 13 Aug 2007 14:09

HI

I facing the same problem do you mind sending me the xsl file as well?

sames

Alexey · Post by **Alexey** » Mon 13 Aug 2007 14:42

CoreLab.MySql.xsl file has been sent to your address.

sames2020 · Post by **sames2020** » Mon 13 Aug 2007 15:28

Hi Aleksey

thanks for all the help. It is working now... Could you enlighten me on this why do we need a xsl file to get it working? tks

sames