Saiku Analytics installation-deployment on a Tomcat server

Saiku Analytics is an tremendous stand-alone tool for building and querying datamarts, build ad-hoc reports, graph, export reports to excel csv etc. Saiku is based on Mondrian OLAP engine, a ‘state of the art’ Java library for modelizing and requesting Multi dimensionnal cubes.

I wrote this tutorial for BI dudes that are not very familiar with J2EE development and Apache Tomcat, hope this permits you to accelerate utilization of Saiku.

Before starting with Saiku installation, this tutorial assumes you already have realized your datamart and modelized your Mondrian schema (please find at the end tutorials links for building Mondrian schemas).

Otherwise you can download a sample datamart and its related Mondrian schema there

• MySQL_sales.sql.zip : a MySQL schema and data that is a BI cube with 3 dimensions and a fact table.
• sales_mondrian_schema.xml : mondrian schema for sales cube.

Step 1 : downloads

Apache tomcat 7

Download apache-tomcat-8.0.5.zip on http://tomcat.apache.org/download-80.cgi

Java JRE 7

http://www.oracle.com/technetwork/java/javase/downloads/jre7-downloads-1880261.html, choose a version that corresponds to your system.

Saiku Analytics webapps

For Saiku Analytics you need to download two war files on http://meteorite.bi/saiku/download

Notice that it’s not compulsory to fill the contact form ; click on ‘Download Saiku’ below the form to display download links

Download Saiku Backend WAR 2.5 : saiku-webapp-2.5.war
Download Saiku UI 2.5 : saiku-ui-2.5.war

Step 2 : JRE and Tomcat installation

JRE

Uncompress JRE tarball (or zip) and set environment variables JAVA_HOME and PATH properly. There are a bunch of web pages for this subject (see http://stackoverflow.com/questions/20822101/setting-the-java-home-and-path-on-macos, http://stackoverflow.com/questions/2619584/how-to-set-java-home-on-windows-7, http://stackoverflow.com/questions/9643964/java-home-incorrectly-set-how-to-reset-it)

Now if you type in a console ‘java -version’, you should have an output like

java version “1.7.0_55″
Java(TM) SE Runtime Environment (build 1.7.0_55-b13)
Java HotSpot(TM) 64-Bit Server VM (build 24.55-b03, mixed mode

Tomcat

Unzip apache-tomcat-8.0.5.zip file.
Go to apache-tomcat-8.0.5/webapps directory and remove folders docs, examples, host-manager, manager and ROOT. Those folders are examples or adminstration webapps that are not used by Saiku nor covered by the tutorial.

You are now able to launch tomcat server (ok it hosts nothing), by launching apache-tomcat-8.0.5/bin/startup.sh or startup.bat (for unix users, a little ‘chmod u+x apache-tomcat-8.0.5/bin/*.sh’ would be wise), and you should have in apache-tomcat-8.0.5/log/catalina.out no errors, and a line like :

05-May-2014 17:00:24.284 INFO [main] org.apache.catalina.startup.Catalina.start Server startup in 230 ms

Step 3 :  Saiku webapps deployment

Stop tomcat server (if launched)

-rename/copy saiku-ui-2.5.war to ROOT.war
-rename/copy saiku-webapp-2.5.war to saiku.war

Copy those two renamed files to apache-tomcat-8.0.5/webapp. Now you have in this folder only the two files like this :

apache-tomcat-8.0.5/webapp/ROOT.war
apache-tomcat-8.0.5/webapp/saiku.war

Launch apache tomcat, look in catalina.out, you should see these two lines

05-May-2014 17:14:30.045 INFO [localhost-startStop-1] org.apache.catalina.startup.HostConfig.deployWAR Deploying web application archive /home/gesig/saiku/1_tutoInstallSaiku/apache-tomcat-8.0.5/webapps/ROOT.war
05-May-2014 17:14:31.844 INFO [localhost-startStop-1] org.apache.catalina.startup.HostConfig.deployWAR Deploying web application archive /home/gesig/saiku/1_tutoInstallSaiku/apache-tomcat-8.0.5/webapps/saiku.war

And now open this url : http://localhost:8080, and you will see the login form of Saiku.
default login is admin, and password is admin

Good job dude, You can jerk on your seat.

Step 4 : configuring Saiku for using your cube schema and your Database

Wicked, you’ve got your Saiku webapp but you have no choice in cubes dropdown on  top left. No we will push our specific BI stuff.

Stop Tomcat server

first of all you have to find and download your database’s specific driver for Java, commonly call a JDBC driver or JDBC connector.

In my example, we need a MySQL jdbc connector, so download on http://dev.mysql.com/downloads/connector/j/ the ‘Platform independent’ one (you can skip account creation or registration by clicking on ‘No thanks, just start my download’)

Uncompress the downloaded file mysql-connector-java-5.1.30.zip, and you should find in it a file mysql-connector-java-5.1.30-bin.jar. Copy that file in ./apache-tomcat-8.0.5/lib

If your use another database, find the corresponding jdbc driver, which is a jar file (ie a java library), and copy it into ./apache-tomcat-8.0.5/lib

-For a Postgres server, you should find postgresql-9.3-1101.jdbc41.jar on http://jdbc.postgresql.org/download.html
-For a MS SQL backend , I advise you to use JTDS driver ; on http://sourceforge.net/projects/jtds/files/ you will find jtds-1.3.1-dist.zip that contains jtds-1.3.1.jar.

Now we have to declare to Saiku/mondrian our cube :
copy a Mondrian schema file (sales_mondrian_schema.xml) into apache-tomcat-8.0.5/webapps/saiku/WEB-INF/classes/saiku-datasources/

in apache-tomcat-8.0.5/webapps/saiku/WEB-INF/classes/saiku-datasources/ create a file named sales.txt ( file extension has no importance, choose everything you want), and write into it lines below :

# declaration of Sauce Dallas sales cube for Sakiu
# ———————————————
type=OLAP
name=Sauce Dallas
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:mysql://localhost/1_tutorialSaiku;Catalog=res:saiku-datasources/sales_mondrian_schema.xml;JdbcDrivers=com.mysql.jdbc.Driver;
username=root
password=7777777

See explanation just after about datasource definition
Now start apache tomcat again, and you should have in saiku page an available cube in the dropdown list.
You can drag’n’drop measures, fields, and build crosstables and graphs as you need. All the stuff for analyze your activity and so overshoot our company crisis.

Explanation about datasource definition

the most tedious part in Saiku configuration is writing the datasource definitions for cubes. Let’s see in details sales.txt file :

type=OLAP specify that is an OLAP engine. I haven’t seen other value for this property
name : you specify a name for your datasource
driver: specify the Mondrian driver. I haven’t seen other value for this property
location : this property has several parts, separated by semicolons :

jdbc:mondrian:Jdbc=jdbc:mysql://localhost/1_tutorialSaiku :
specifies jdbc url of the database ; forget for now the part ‘jdbc:mondrian:Jdbc=jdbc:’ and just remember that mysql://localhost/1_tutorialSaiku is a JDBC convention for a mysql database. Here the host is localhost and database name is 1_tutorialSaiku

For a postgres database we can have jdbc:mondrian:Jdbc=jdbc:postgresql://localhost/1_tutorialSaiku

Catalog=res:saiku-datasources/sales_mondrian_schema.xml
indicates path of mondrian schema file. ‘res:’ (spring stuff) indicates that file is located in classpath, in our case, a path starting from classes directory of saiku webapp.

JdbcDrivers=com.mysql.jdbc.Driver
Indicate the java class file to use as driver for the database connection. You should see in mysql-connector-java-5.1.30-bin.jar that there is a classe file named Driver.class in folder com/mysql/jdbc
For Postgres write : JdbcDrivers=org.postgresql.Driver
For Oracle (thin driver) write : JdbcDrivers=oracle.jdbc.driver.OracleDriver
For MS SQL (jtds driver) write : net.sourceforge.jtds.jdbc.Driver

Tutorials for building Mondrian schemas 

• http://mondrian.pentaho.com/documentation/schema_workbench.pdf
• http://type-exit.org/adventures-with-open-source-bi/2010/07/a-basic-mondrian-cube-introducing-the-star-schema/

Coming soon

• customzing Saiku interface
• improve response time with aggregate tables
• executing MDX queries at saiku startup and populate Mondrian cache
• customize Mondrian Cache