I'm using MONDRIAN server and OLAP4j API in a Java Web application, i have a performance issues when adding a where close to my queries. MDX query like :
SELECT
CrossJoin(
{[Product.ProductHierarchie].[AllProduct]}
, {[Measures].[Quantity]}
) ON COLUMNS,
[Client.ClientHierarchie].[AllClient].Children ON ROWS
FROM [sales_data_cube]
0.3 second to be done. But when adding a where clause, like
WHERE ([Period].&[start_period]:[Period].&[end_period]),
to get the sales between a start/end periods, the query take more than 250 seconds with a small fact table (8500 rows).
What i should do to have a better performance?
The application is running on a tomcat server with memory limit = 8GB, Data base server : MySQL 5.6.17
Finally, the problem was in the configuration of Mondrian. Mondrian use the logging package (log4j) where actually call 'Debug'-method every time they compared two objects when using a where condition.
The solution is to change the log4j-configuration and stopping the 'Debug'-mod. I have added this simple code to set up log4j, before the creation of OLAP Connection:
Logger.getRootLogger().setLevel(Level.OFF);
Logger.getRootLogger().removeAllAppenders();
Logger.getRootLogger().addAppender(new NullAppender());
Class.forName("mondrian.olap4j.MondrianOlap4jDriver");
Connection connolap = ...