Pentaho Schema Workbench(PSW)-OLAP CUBEs ------- Enabling log files for displaying SQL and MDX queries

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	http://ramathoughts.blogspot.in/2009/10/mondrian-cubes-debugging-how-to-display.html

·         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

Installation of Pentaho BI Server Community Edition 4.8.0 in Windows 7 x.64

Hi guys..!!!

This post teach you how to install the pentaho BA server in windows machine.

1. Download pentaho from sourceforge site
    http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/4.8.0-stable/biserver-ce-4.8.0-stable.zip/download

2. Extract the zip file in your fav location.

3. You will get two folders : administration-console & biserver-ce
     Adminstration-console purpose : You will give database connections here .
     User Console : (biserver-ce) : Core of the server UI .. You will analysis the date

4. Run the start-pentaho.bat file in biserver-ce
     location of file : your_location_of_drive\biserver-ce-4.8.0-stable\biserver-ce

NOTE: By default pentaho server runs on 8080 port number. If you wish to change the default port number to your desired one you can do that in server.xml file which is located in conf folder of tomcat 

Type this URL in any browser : http://localhost:8080/pentaho.. you will prompted to the login page of server. check for the logs if you find any errors.

5. Stop the server : stop-pentaho.bat in the same location of step 4

How to run the Adminstration console
Run the start-pac.bat  file in adminstraion-console folder
location of file :your_location_of_drive\biserver-ce-4.8.0-stable\administration-console
 Stop the server : click on stop-pac.bat in the same location of above step.


Where the problems comes into picture ??
You will only get the problem if you don't know how to install the java software and correct path settings for java
Install java software and set the path,classpath,java_home,jre_home

This post will teach you how to set all the things.
http://pentaho-bi-suite.blogspot.in/?view=classic 


Admin console  Problem:
Edit the console.properties file located in {CONSOLE_HOME}\resource\config}

console.properties

\# Pentaho Administration Console's Jetty Server Settings
console.start.port.number=8088
console.hostname=localhost
console.stop.port.number=8033

\# SSL Section for Pentaho Administration Console
console.ssl.enabled=false
console.ssl.port.number=8143
keyAlias=jetty
keyPassword=changeit
keyStore=resource/config/keystore
keyStorePassword=changeit
trustStore=resource/config/keystore
trustStorePassword=changeit
wantClientAuth=false
needClientAuth=false

\# Security Authentication Section for Pentaho Administration Console
console.security.enabled=true
console.security.roles.allowed=admin
console.security.roles.delimeter=,
console.security.realm.name=Pentaho
console.security.login.module.name=PropertiesFileLoginModule
console.security.auth.config.path=resource/config/login.conf

Notice that the console.ssl.enabled properties is set to false. Change that value to true. By default keytore and trustore path is resource/config and password for them is changeit. If you have something different you can edit the ssl section for the correct value. Remember that the default port for pentaho administration console https is 8143, so if you want to change it then you can do this right here in the properties file


http://wiki.pentaho.com/display/ServerDoc1x/Enabling+SSL+in+Pentaho+Administration+Console


NOTE:
There are many ways of installing pentaho bi server. This is a simple installation of bi server.
Navigate in  http://pentaho-bi-suite.blogspot.in  site.. I have posted other installation methods which worked successfully.

Sadakar.P
BI developer
("Learning never exhausts the mind")