PDA

View Full Version : Mondrian / MySQL



freshasp
11-22-2005, 08:55 AM
I installed the Pentaho demo setup - everything works fine. I created a MySQL database that mirrors the Hypersonic db. Started switching across samples to use the MySQL database.

BIRT, Jasper, direct queries - all working fine (eventually! :).

BUT... I can't seem to get anything working via OLAP (Mondrian). I am editing MDX_Datasource.action.xml (in the datasources sample set).

If I switch
"jdbc:hsqldb:hsql://localhost:9001/sampledata"to be
"jdbc:mysql://localhost:3306/aerotest"and update the username and password, I hoped that it would cut across to querying the MySQL database. However, I get the following error in the log:

java.sql.SQLException
MESSAGE: Access denied for user ''@'%' to database 'aerotest'

Needless to say I have specified a username, not "@'%' but it doesn't seem to be being carried through.

So, my question is, has anybody got this to work? Any help or pointers would be most welcome. :)

PS: It's nigh on impossible to post code on this board... it all gets stripped out regardless of the tags around it. I appreciate the need for security but this makes it impossible to explain problems.

Post edited by: freshasp, at: 11/22/2005 13:02

dmoran
11-22-2005, 02:05 PM
pauloram -

Sorry about the trouble, it seems we have an issue with the way we build the connect string that gets passed to Mondrian. If you add the following to the connection URL, the userid and password will get passed through to mySQL.

?user=the_user&password=the_password

the changes should look like:



<component-definition>
<connection>
<![CDATA[jdbc:mysql://localhost/sampledata?user=pentaho_user&password=password]]>
</connection>
<location>mondrian</location>
<user-id>sa</user-id>
<password></password>
<query><![CDATA[with member ... ]]></query>
</component-definition>

Sorry about the problems posting code and XML, we are looking for a a better forum component. In the mean time, you should be able to attach any code or XML.

Doug

Post edited by: dmoran, at: 11/22/2005 18:08

Post edited by: dmoran, at: 11/22/2005 18:08

Post edited by: dmoran, at: 11/22/2005 18:10

Post edited by: dmoran, at: 11/22/2005 18:11

Post edited by: dmoran, at: 11/22/2005 18:14

freshasp
11-23-2005, 03:02 AM
Many thanks Doug - this worked perfectly - Mondrian is now sourcing its base data from MySQL.

When does Mondrian rebuild its cube? If I change a value in the MySQL database and refresh the sample report it does not reflect the new values in the database (these have been committed).

At the moment I am having to stop and start Pentaho to ensure the latest figures are used. This seems like overkill. Is there another way to get Mondrian to refresh the cube's base data?

freshasp
11-23-2005, 03:19 AM
My mistake - Mondrian also seems to have a defined refresh time period. Where is this defined? Can it be changed?

Thanks,
Paul

jhyde
11-23-2005, 09:50 AM
Mondrian maintains a cache of members and aggregated cell values in memory. There's currently no way to programmatically flush that cache, but we hope to add it soon. Meantime, restarting the server is your best option.

Julian

jhyde
11-25-2005, 10:32 AM
Another option (thanks for Marc for suggesting!) is to periodically (say every 30 minutes) change the URL which you use as a Mondrian connect string. Mondrian will open a new connection, and with it a new set of cached values. The old connection and cached data will time out of the cache when no one is using it.