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

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

Postby 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
Jac
 
Posts: 5
Joined: Tue 24 Jul 2007 06:55

Postby 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...
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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.
Jac
 
Posts: 5
Joined: Tue 24 Jul 2007 06:55

Postby 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
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Serious » Thu 26 Jul 2007 13:37

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

Postby 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
Jac
 
Posts: 5
Joined: Tue 24 Jul 2007 06:55

Postby 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\
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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
Jac
 
Posts: 5
Joined: Tue 24 Jul 2007 06:55

Postby Alexey » Fri 27 Jul 2007 12:06

I'd sent you that file. Please check the mail.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Jac » Fri 27 Jul 2007 13:01

Thank you very much for the prompt replies. It is working now. :D
Jac
 
Posts: 5
Joined: Tue 24 Jul 2007 06:55

Postby Alexey » Fri 27 Jul 2007 15:00

You are welcome.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

same problem

Postby sames2020 » Mon 13 Aug 2007 14:09

HI

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


sames
sames2020
 
Posts: 2
Joined: Mon 13 Aug 2007 14:05

Postby Alexey » Mon 13 Aug 2007 14:42

CoreLab.MySql.xsl file has been sent to your address.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

same problem

Postby 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
sames2020
 
Posts: 2
Joined: Mon 13 Aug 2007 14:05

Next

Return to dotConnect for MySQL