Mondrian cubes debugging: how to display SQL queries

These days I've got the interesting need to look at the queries that Mondrian generates while the user is navigating the OLAP cube. This idea came to me when I decided to the check if the indexes applied to my tables gives me the best performances possible. To decide which indexes are eligible to be applied to my tables, my strategy is

• collect some queries and then 
• get the query plan of each query and check if the indexes are properly applied.

It's really useful to look at Mondrian log files because they gives us a lot of useful informations about how our system is behaving. We can

• look at sql statements and MDX queries,
• have some profiling informations on queries that are executed,
• get other useful debugging informations.

The following paragraphs illustrates how to enable Mondrian debugging logs, adding some properties to the Mondrian configuration file.
After that, we'll configure two new log4j appenders to have the desired log files properly written on our filesystem.

Enable Mondrian debug log
Mondrian has a big set of configuration settings that can be modified. In our case, to enable Mondrian debug informations follow the steps detailed below:

Open the mondrian.properties file located in <bi-server_home>/pentaho-solution/system/mondrian and add the following line.

mondrian.rolap.generate.formatted.sql=true

You can find the complete set of configuration settings here

Update log4j configuration
At this point we're going to modify the log4j configuration file adding the required appenders abd categories to have our logging informations displayed properly

Open the log4j.xml file located in <bi-server_home>/tomcat/webapps/pentaho/WEB-INF/classes

Based on what you want to log, add the one or each of the following lines to the file. They will create two new RollingFileAppenders. You're free to use
the kind of appender you prefer. In case you need further informations about log4j and its configuration parameters you can have a look at here
IMPORTANT: The location of the produced files is relative to the <bi-server_home>/tomcat/bin directory.  You can put the generated log files wherever you
want in the filesystem but always remember this important consideration.

   <!-- Add the following appender only if you're interested in logging SQL statements -->

   <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">

     <param name="File" value="sql.log"/>

     <param name="Append" value="false"/>

     <param name="MaxFileSize" value="500KB"/>

     <param name="MaxBackupIndex" value="1"/>

     <layout class="org.apache.log4j.PatternLayout">

       <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>

     </layout>

   </appender>

   <!-- Add the following appender only if you're interested in logging MDX statements -->

   <appender name="MONDRIAN" class="org.apache.log4j.RollingFileAppender">

     <param name="File" value="mondrian.log"/>

     <param name="Append" value="false"/>

     <param name="MaxFileSize" value="500KB"/>

     <param name="MaxBackupIndex" value="1"/>

     <layout class="org.apache.log4j.PatternLayout">

       <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>

     </layout>

   </appender>

Add the following new categories to the log4j.xml file according to your logging needs.

   <!-- and logs only to the SQLLOG -->

   <category name="mondrian.sql">

      <priority value="DEBUG"/>

      <appender-ref ref="SQLLOG"/>

   </category>

   <!-- and logs only to the MONDRIAN -->

   <category name="mondrian">

      <priority value="DEBUG"/>

      <appender-ref ref="MONDRIAN"/>

   </category>

Enable the new log settings
To have the new log settings enabled restart the Pentaho bi-server instance. Remember, as soon as you satisfied your debugging needs, to disable the tracing logs because they have a severe impact on system performances.