TOPIC
|
Mondrian Cubes Debugging :
1) How
to display SQL Queries
2) How
to display MDX Queries
Editing mondrian.properties file and log4j.properties file to view
SQL Queries and MDX Queries which are generated by Mondrian Engine.
|
Author
|
Sadakar Pochampalli
|
Date
|
27th April 2012
|
Source
|
|
·
How to look at the queries that Mondrian generates
while the user is navigating the OLAP cube. ?
·
It's really useful to look at Mondrian log files because they give us a lot of useful information
about how our system is behaving.
·
We can
o look at sql statements and MDX
queries,
o Have some profiling information
on queries that are executed,
o Get other useful debugging
informations.
The following Steps illustrate
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 file system.
Step 1:
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
Example:
Mondrian.properties file location.
D:\Installation Softwares\Pentaho\biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions\system\mondrian
Step 2: Update log4j configuration
At this point we're going to modify the log4j
configuration file adding the required appenders categories to have our logging information
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.
Example:
Location of log4j.xml file
D:\Installation
Softwares\Pentaho\biserver-ce-4.8.0-stable\biserver-ce\tomcat\webapps\pentaho\WEB-INF\classe
Add the following code.
NOTE: The code is already available within the file.. Just we need to
uncomment.
<appender
name="MDXLOG"
class="org.apache.log4j.RollingFileAppender">
<param
name="File" value="../logs/mondrian_mdx.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>
<category name="mondrian.mdx">
<priority
value="DEBUG"/>
<appender-ref
ref="MDXLOG"/>
</category>
<!--
========================================================= -->
<!-- Special Log File
specifically for Mondrian SQL Statements -->
<!-- =========================================================
-->
<appender
name="SQLLOG"
class="org.apache.log4j.RollingFileAppender">
<param
name="File" value="../logs/mondrian_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>
<category
name="mondrian.sql">
<priority
value="DEBUG"/>
<appender-ref
ref="SQLLOG"/>
</category>
|
Step 3: Enable the new log settings
To have the new log settings enabled restart the Pentaho bi-server instance.
Log files location
D:\Installation Softwares\Pentaho\biserver-ce-4.8.0-stable\biserver-ce\tomcat\logs
After restarting the
server and when you run the CUBE-OLAP...
You can find the
following two files in the above location.
Files are:
i)
Mondrian_mdx.log
ii)
Mondrian_sql.log
Now, Enjoy analysing SQL
queries that generated while performing run actions in various tools like
Pentaho Analyser, Saiku Analysis.
Thank you for reading the blog J J J
