Crorella's Weblog

Logging is a useful tool that let us know what’s is going on “under the hood” of Pentaho, it’s also handy when you need to optimize the speed of the OLAP navigator (like jPivot or the new Saiku) because you can examine the SQL query that’s running against the RDBMS and that can give you lights on what are the tables involved and indexes that needs to be created in order to improve the performance.

In this post, I will explain what are the default log files on Pentaho, the levels of severity of a message/error and how to enable and disable those files.

log4j

Pentaho uses log4j logging service to display log messages, a good thing about this is that most of the options for the different components (Mondrian SQL, Mondrian MDX, general messages, etc.)  are managed at one central configuration file: log4j.xml. On Pentaho this file is located at /bi-server/tomcat/webapps/pentaho/WEB-INF/classes/log4j.xml

Common Types of Log Files

By default, log4j.xml file have the following pre-configured log files:

• Mondrian general information: You can enable this file by uncommenting the section preceded by the following comment: <!– Special Log File specifically for Mondrian  –>.

  <appender name="MONDRIAN">
       <param name="File" value="mondrian.log"/>
       <param name="Append" value="false"/>
       <param name="MaxFileSize" value="500KB"/>
       <param name="MaxBackupIndex" value="1"/>
  
       <layout>
         <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
       </layout>
     </appender>
  
     <category name="mondrian">
        <priority value="DEBUG"/>
        <appender-ref ref="MONDRIAN"/>
     </category>
  

By default, the name of this file is mondrian.log and is stored on bi-server/tomcat/bin. You can change the name of the file and the folder by changing the value of the File parameter (you can give the full path if you wish to).

If you enable this, you will be able to see detailed information of how Mondrian load the schemas, if uses aggregated tables, MDX and SQL queries performed along with their execution times. All this information is available only if you set the priority to DEBUG, lower priority levels won’t display this information.

This file is very verbose and it’s perfect if you want to see exactly what is happening with mondrian, but, if you just need the MDX or SQL queries there is a better option.

• Mondrian SQL Queries: You can enable this file by uncommenting the section preceded by the following comment: <!– Special Log File specifically for Mondrian SQL Statements –>. By default, the name of this file is mondrian_sql.log, you can change this name just like I explained previously.

  <appender name="SQLLOG">
       <param name="File" value="mondrian_sql.log"/>
       <param name="Append" value="false"/>
       <param name="MaxFileSize" value="500KB"/>
       <param name="MaxBackupIndex" value="1"/>
       <layout>
         <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
       </layout>
     </appender>
  
     <category name="mondrian.sql">
        <priority value="DEBUG"/>
        <appender-ref ref="SQLLOG"/>
     </category>

  If you enable this file, you will see the SQL queries and their execution times, it’s essentially the same information contained on mondrian.log but with the convenience that it’s a less verbose file and it’s easier to read.

• Mondrian MDX Queries: You can enable this file by uncommenting the section preceded by the following comment: <!– Special Log File specifically for Mondrian MDX Statements –> .
  By default, the name of this file is mondrian_mdx.log, you can change this name also.
  Just like mondrian_sql.log this file contains the MDX queries.

• Security Logging: It’s also possible to log the logging attempts to the PUC and the roles every user is granted, if you want to enable it, you’ll need to add the following category elements before the root element on your file:

  <!-- all Spring Security classes will be set to DEBUG -->
  <category name="org.springframework.security">
    <priority value="DEBUG" />
  </category>
  
  <!-- all Pentaho security-related classes will be set to DEBUG -->
  <category name="org.pentaho.platform.engine.security">
    <priority value="DEBUG" />
  </category>
  <category name="org.pentaho.platform.plugin.services.security">
    <priority value="DEBUG" />
  </category>

  Additionally, if you want to differentiate the failed logging attempts because of a bad password of the failed logging attempts because of a bad username you’ll need to add this code to your pentaho-solutions/system/applicationContext-spring-security-.xml file where <back-end> is one of memory, jdbc, ldap, or hibernate; depending of the type of back-end you’ve configured on web.xml

  <bean id="daoAuthenticationProvider">
    <!-- other properties/constructor-args not shown -->
    <property name="hideUserNotFoundExceptions" value="false" />
  </bean>

  After this, you will be able to see messages on pentaho.log which is the default log file for Pentaho system.

  Thanks goes to @magicaltrout and Billmania for the help.