Complete Pentaho Installation on Ubuntu, Part 3: Saiku

Edit 2:

Pentaho 5.0 in 2014 changed some things and somehow Pentaho Reporting stopped working. Their developers had announced a code fusion with Pentaho Analytics but no date has been announced as they need support to develop it. All their effort is focused on the Saiku Analytics 3.x version. For installation you just need to use the toolbar option ‘Marketplace’ and click on the install button. No need to configure datasources.

Edit:
On May 2011, Saiku OLAP was made available. On June 2012, Saiku Reporting was anounced. Both projects are a promising alternative to Jpivot and WAQR, that are still needed for advanced funcionality not yet implemented on the new tools. But that thing is changing on each new release. On October 2012 version 2.4 was released.

Saiku Analytics

Saiku Analysis is a very interesting project for the Pentaho community and it may grow to take over ‘jpivot’, the old official multidimensional web browser. It was initially known as the Pentaho Analysis Tool (PAT).

Install SAIKU and Add OLAP Datasources

These notes are based on Tom Barber’s article. And updated on June 2011 and 2012 🙂

What you Need

Download two files from the saiku site:

1. The plugin: saiku-plugin-2.4.zip
   [Saiku Pentaho BI Server Plugin. 2.48MB. October 2012].
2. [Edit – From 2.4 and on, this is no longer necesary]
   The web-app file: saiku-webapp-2.4-war
   [Saiku Backend WAR 2.4. 24.2MB. October 2012].

If you need the old version you can get those files the links are saiku-plugin-2.3.zip. [6.5MB, June 2012], saiku-webapp-2.3-war. [24.7MB, June 2012].

Steps to Install

1. Open the (plugin) zip file and extract the content into:
   /biserver-ce/pentaho-solutions/system/
   it will create a new saiku folder.
   
2. [Edit – also not necesary]
   Create a saiku folder in the:
   /biserver-ce/tomcat/webapps/
   and extract the contet of the web-app file in it.
   (With a .war file you can put the file on the /tomcat/webapps directory and restart the server, use the procedure that you like most)
3. That’s it!

Start the Pentaho server and browse to your main page:

You’ll see a new Icon for ‘New Saiku Analytics’. Double click on it. On the Cube List select a FoodMart, SampleData or SteelWheels ‘cube model’, drag a measure to columns and another to rows, put a metric on one of them also. You can filter double clicking on them or use the filter area. Check their screencasts.

Saiku Interactive Multidimensional Browser

jPivot Multidimensional Browser

Notes:

1. Remember that you uploaded the FoodMart MySql data and set up the datasource on the previous post. Saiku reads this configuration and uses it itself.
2. User access control to SR reports can be achieved by opening and update
   /Pentaho/biserver-ce/pentaho-solutions/system/pentaho.xml
   by adding the file extension .adhoc to the <acl-files> property:
   <acl-files>xaction,url,prpt,prpti,xdash,xcdf,adhoc</acl-files>

---

Set up Saiku Reporting

On June 11, 2012 Pentaho Reporting was announced for the Pentaho BI Server in the community edition.The proyect page is on github.

Now you can drag and drop fields exposed by your metamodel directly on your browser.

To install it:

1. Download saiku-adhoc-plugin-1.0-GA.zip. This is the project page.
2. Unpackit into Pentaho/biserver-ce/pentaho-solutions/system.
   it will create a saiku-adhoc folder.

To use it I can recomend:

First, watch this video http://www.youtube.com/watch?v=7mLpxhZZT4I.

Then, to start:

1. Click on the Saiku Reporting toolbar button
   
2. Select a model on your left.
3. On the upper right, click on Row Limit and set it to 10 registers so it will not retrive a big recordset while you build your report and filters.
4. Drag and drop fields, set up filters and groupings.
5. You can change the theme, font or colors.
6. Change the Title by double clicking on it.
7. There should be an easy way to change the logo. But you can copy the image overwritting the file Pentaho/biserver-ce/pentaho-solutions/system/saiku-adhoc/web/js/images/src/logo_red.png that is an 136×23 image.

This is how it looks:

Saiku Reporting

There are a couple of things that are not obvious and I havent found yet, those are: 1) Totals and subtotals and 2) How to calculate additional columns with formulas, as soon as I find out I will update this.

Notes:

I needed to modify the metadata model (check post 9) on the time dimention table from integer selection fields to string type of fields or the filtering would not work. That means that I had to add fields for year, quarter, month (2012, 1, 12) and copied to (‘2012’, ‘1’, ’12’).

One nice thing is that if you set a date field in the metadata with aggregation to minimum and maximum, saiku anilytics will open two date calendars so you can specify a range for that field.

On the instructions, it mentions that the CDA should be updated. On part 11 the CDE is installed and CDA updated.

---

---

Setup an OLAP Datasource

Edit June 2011:
This steps are needed if you add a OLAP datasource to your BI server.
We’ll need this steps on a future post about mondrian modeling tool: Schema Workbench,
then we’ll have an XML model and we need to tel saiku and jpivot where to find it.

For the Saiku OLAP datasource

Navigate to:

/Pentaho/biserver-ce/tomcat/webapps/saiku/WEB-INF/classes/saiku-datasources/

Create a text file (.xml) like: DataSourceName.mondrian.xml with gedit or your favorite editor and add:

type=OLAP
name=DataSourceName
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:mysql://localhost:3306/YourDatabase;Catalog=[PATH]/DataSourceName.mondrian.xml;JdbcDrivers=com.mysql.jdbc.Driver;
username=pentaho_user
password=password

Note: You can put the fileo two places:
– Folder on Saiku under the /Pentaho/biserver-ce/tomcat/
[PATH]: ../webapps/saiku/datasources
– Folder on pentaho-solutions: /Pentaho/biserver-ce/pentaho-solutios/yourfolder
I prefer this one as it can be reached by jpivoy and saiku and is consistent with the config.
[PATH]: /yourfolder

This would be an example for the SteelWheels file (you dont need it as saiku considers it) so you can check the files path’s:

type=OLAP
name=SteelWheels
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:mysql://localhost:3306/sampledata;Catalog=/steel-wheels/analysis/steelwheels.mondrian.xml;JdbcDrivers=com.mysql.jdbc.Driver;
username=pentaho_user
password=password

Save it and exit.

For the Pentaho BI OLAP datasource.

To add the OLAP datasource to jpivot and BI server, navigate to:

/Pentaho/biserver-ce/pentaho-solutions/system/olap/

edit datasources.xml

add copy and paste the lines for the DATASOURCE tag before the closing </Datasources> tag:

 <Catalog name="SteelWheels"> 
 <DataSourceInfo>Provider=mondrian;DataSource=SampleData</DataSourceInfo> 
 <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition> 
 </Catalog> 
 <Catalog name="SampleData"> 
 <DataSourceInfo>Provider=mondrian;DataSource=SampleData</DataSourceInfo> 
 <Definition>solution:steel-wheels/analysis/SampleData.mondrian.xml</Definition> 
 </Catalog> 
 </Catalogs> 
 </DataSource>

<DataSource>
 <DataSourceName>Provider=Mondrian;DataSource=DataSourceName</DataSourceName>
  <DataSourceDescription>NAME Cube</DataSourceDescription>
  <URL>http://localhost:8086/pentaho/Xmla?userid=joe&amp;password=password</URL>
  <DataSourceInfo>Provider=mondrian;</DataSourceInfo>
  <ProviderName>PentahoXMLA</ProviderName>
  <ProviderType>MDP</ProviderType>
  <AuthenticationMode>Unauthenticated</AuthenticationMode>
  <Catalogs>
   <Catalog name="DataSourceName">
   <DataSourceInfo>Provider=mondrian;DataSource=DataSourceName</DataSourceInfo>
   <Definition>solution:steel-wheels/analysis/DataSourceName.mondrian.xml</Definition>
  </Catalog>
 </Catalogs>
</DataSource>

</DataSources>

If you paste the code, be carefull with the ampersand “&amp;” sometimes it gets replaced. Check for the complete string: =joe&amp;password=

If you start the server and click on ‘New Analysis View’ or ‘New Saiku Analytics’ main icons you’ll see your models.