1

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

whytheq
  • 34,466
  • 65
  • 172
  • 267
Adnan.moe
  • 31
  • 4
  • 1
    Instead of `WHERE` clause try moving it to a subselect like so - `FROM ( SELECT {[Period].&[start_period]:[Period].&[end_period]} ON 0 FROM [sales_data_cube] )` – SouravA Nov 12 '15 at 15:57
  • Mondrian cannot recognize the subselect clause, it does not support MDX subqueries. – Adnan.moe Nov 24 '15 at 15:43

1 Answers1

2

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 = ...

more details about this issue (Mondrian Slow MDX Query)

Adnan.moe
  • 31
  • 4
  • While I have no experience of working on olap4j, i really appreciate you coming back here and adding a well researched answer. It really adds value to the site when people do that. – SouravA Nov 24 '15 at 16:24