Complete Pentaho Installation on Ubuntu, Part 4

Add Your Users

You should assign a user name and password to each person that will be signing-in into your Pentaho BI system. There are no policies to renew or control unsuccessfull attempts, so be very clear with your clients about password confidentiality. To create an account follow this are steps:

  1. Start the Pentaho Administration Console (PAC).
    Use file browser (last time we used a command terminal, But now we know that it works and don’t need to read the messages) and go to the folder:
    double click on:
    Click ‘Execute’ button.
  2. Open your internet browser on http://localhost:8099
    Login with admin/password.
    Ignore the connection refused message if your BI server is not running.
  3. Click on Administration tab to your left in the screen.
  4. Before clicling on the ‘Users’ icon, notice the eight default roles (the default view).
    For now the most important to us is ‘Authenticated’ one as is the one we’ll be assigning to our users.
  5. Now click on the ‘Users’ Icon (see image, #1).
    Click on the ‘plus’ (see image, #2) Icon to open the dialog so you can type the username, password and description of the activities of the user, this is for your control only (see image, #3).
    Click OK to close the dialog.
  6. Click on the ‘plus’ (see image, #4) role icon.
    Select Mand move to the left list, the ‘authenticated’ element and click OK to close the dialog.
  7. When you finish you can click on Update (see image, #5) to end this procedure.
Adding Users on Pentaho Administration Console

Adding Users on Pentaho Administration Console

And so on for each user. To stop the PAC, exit the browser and run:

Pentaho/administration-console/ .

Access Authentication

For most of us we should stop here as we have now the functionallity needed by most instalations. That is:

  • Allow access only to persons that you have granted permission.
    Remember that only a handfull of people should be on your pilot project as they’ll need a lot of your time and guidance.
    The selected processes/areas you’re starting with, are also a few.
  • You will use ‘folders’ to group department o process needs.
    That will ease navigation for your users.

You’ll see, most people will go to their folder, get the information they need and get out.  The mayority of people will insist on direct delivery of information. And if someone needs information from other area, there should be access to it. You shouldn’t give in to the temptation to set information silos. -BI is meant for users and information freedom-.

And there is another technical reason for not to set up authorization control in Pentaho. And that is that we have been using a file system to this point. If we want to control visibility of solutions/reports/data we must switch to a database repository that is SLOW, really slow. You must have really sensitive information to go this way as you will learn that you have to add parameters to each metadata layer o olap model, workflow, dashboard, etc.

So thats it, set up your users and check the activity as finished.  🙂

Authorization Control

But, if you must comply with authorization control you must use a database for the metadata repository.


There are three references to repositories types in Pentaho:

  • Solution repositories: Is a collection of files in folders. In our current installation you can refer to the ‘steel-wheels’ folder under Pentaho\biserver-ce\pentaho-solutions\ but you can tell the BI server to look for them in a database.
  • Metadata repository: This is the actual authorization layer that we are refering in this post. You can define access here for tables or columns. We’ll mention how to do that in a futre posts.
  • The PDI/ETL Repository: Kettle (the ETL tool) can use use local files for the transformation programs it uses but it can also use a database to store, share and control them more easily.

There will be an article of the metadata editor, then we’ll continue this configuration.


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.

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 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 [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:
    it will create a new saiku folder.
  2. [Edit – also not necesary]
    Create a saiku folder in the:
    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:

BI Server with new Saiku Analitycs buttons

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 Multidimensional Browser

Saiku Interactive Multidimensional Browser

jPivot Multidimensional Browser

jPivot Multidimensional Browser


  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
    by adding the file extension .adhoc to the <acl-files> property:

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 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

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

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.


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:


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


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:


Save it and exit.

For the Pentaho BI OLAP datasource.

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


edit datasources.xml

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

 <Catalog name="SteelWheels"> 
 <Catalog name="SampleData"> 

  <DataSourceDescription>NAME Cube</DataSourceDescription>
   <Catalog name="DataSourceName">


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.

Complete Pentaho Installation on Ubuntu, Part 2


This post was written with Pentaho version < 4.8 in mind. Most of it is still usefull specially the database setup. Some things change a little, like point nine, the datasource [re]definition that now must be done on Pentaho console itself or the examples section as they no longer exist.

Please check this series for version 5.x.

Modify Pentaho Demo Databases to MySQL Add your Database too

The Pentaho BI Server that you configured in last post uses HSQLDB engine. It creates and loads the database in memory each time you start the demo.

That’s fine for a demo, but we prefer a more permanent storage, so we’ll create six MySql schemas, their users, tables and corresponding ‘datasource’ definitions for our Pentaho system:

  • Two system databases: hibernate and quarz, the first one is used for configuration and the other one is for scheduling.
  • Another one for steelwheels data in the sampledata database, this is the one used in the Pentaho/HSQLDB demo.
  • Add a more complex data set: foodmart. This was the original database for mondrian examples (multidimensional project in Pentaho).
  • An empty databse for us to load and clean up data: loadig_area.
  • And the last one called datamart for our tables to report from.

Setting them up takes a few administration activities for which we’ll use the administrative MySql graphical tools. Then edit seven text files -more or less- to configure Pentaho to look for MySql insted of HSQLDB. We’ll be up and running in a couple of hours.

What you need Two things are for migration and one for your a special DB source of data:

  1. MySQL ‘database engine’ tested. MySQL root user with password. Tools: MySql Administrator and MySql Query Browser.
  2. SQL Scripts that create databases and load data content. We’ll use the scripts from Analysethis, and OSBI sites. [The Pentaho wiki says you shouldn’t link to them until their migration is compleated, so if it is broken there are another links in the comments]
  3. Optional: Your IP, user, password, schema name and software driver for your particular database. You’ll get this from your database administrator. It we’ll be easy for him/her if you remind them that the access is only read-only no excecution allowed.

If you did the install MySql step in last post, you have a working DB engine now, if not, check the recomended guide or search the web for your favorite one.

Download two files with the scripts for SteelWheels & MySql (130KB) and Foodmart (7.9MB). Extract the scripts to something like /Pentaho/sql_scripts.

For the drivers, download:

  • MySql 3.14 connector (mysql-connector-java-3.1.14-bin.jar) or 3.17 from here. The 5.1 driver sometimes has issues on some systems.
  • Optional: If you have an Oracle system you’ll need the ojdbc14.jar and orai18n.jar. Edit june 2011: You can get them from de PDI. Download the file -we are going to install it on part 6– and copy them from /Pentaho/data-integration/libext/JDBC .

Steps to Do

# Activity HSQLDB ->MySql New Datasource
1 Create your databases  X
2 Load scripts to sample databases X
3 Create system/database users and grant them permisions X
4 Modify system connections to MySql: hibernate and quartz X
5 Redirect Hibernate itself to MySql X
6 Set the spring-security to be on MySQL database. X
7 Modify the list with drivers to use for each jndi connection  X X
8 Place the drivers in the tomcat\common\lib folder X X
9 Edit the scripts to avoid HSQLDB to start X
10 Start the console administraton for datasource setup X X
11 Test your system X X

1. Create your Databases

If you haven’t use MySql tools, then here you’ll get your first acquaintance with them. They will be very usefull for administration/backups and data querying.

Start MySQL Query Browser (Ubuntu menu: Application->Programming) and login as root (use localhost or empty for ‘Server Hostname’ if it reports an error)

Login MySql Query Browser

Login MySql Query Browser

and execute two commands:

create database loading_area;
create database datamart;

Exit the MySql Query Browser.

2. Load the scripts to sample databases

Start MySQL Administrator (Ubuntu menu: Application->Programming) and login as root (use localhost or empty for ‘Server Hostname’ if it reports an error):

Login to MySql Administrator
Login dialog for MySql Administrator

Restore the SQL script that creates the hibernate database, hibuser user and DATASOURCE table:

  • Select ‘Restore Backup‘ from the left panel.
  • Click the ‘change path‘ button at the left bottom and navigate to the folder you left them (/Pentaho/sql_scripts), select ‘OK‘ Note: Pentaho has this same script on ‘/home/[your_user]/Pentaho/biserver-ce/data/mysql‘.
  • Select ‘1_create_repository_mysql.sql‘ and the ‘utf-8‘ options.
  • Optionally you can review and select which the operations will be excecuted from the ‘selection’ tab at the window top.
  • Click ‘Restore Backup’ button on the bottom right.

To create the quartz database use:

  • Use ‘Restore Backup‘, using the same folder.
  • Select ‘2_create_quartz_mysql.sql‘ and the ‘utf-8‘ option.
  • Click ‘Restore Backup’ button on the bottom right.

Do the same to create the ‘sampledata’ database, but use the ‘3_create_sample_datasource_mysql.sql‘ script.

The users of Pentaho system (not the database) will be loaded when you restore the next scritp: ‘4_load_sample_users_mysql.sql‘.

With the next two we’ll load the actual data of the samples, these are: ‘5_sampledata_mysql.sql‘ and ‘foodmart_mysql.sql‘.

2. Create system/database users and grant them permisions

Continue in MySql administrator, but change the left panel option, click on ‘user administration‘ and then click the left bottom ‘New User‘ button. Now fill the ‘MyQSL User’ field with pentaho_user. In the passwords fields type password. like this:

My SQL New UserMy SQL New User

Note: You can see in the image the @ (at symbol) indented under the left pentaho_admin user. This is where you can give different permisisons to each IP/Equipment.

Lets continue and create with the same steps the pentaho_user.

Now, click on the ‘Schema Privileges‘ in the top tab. In the ‘User Accounts’ list  in the left bottm sould be still selected the ‘pentaho_user’ user. Select the ‘datamart’ schema and move from the right list to the left the correct privileges. Click ‘Apply Changes‘. It will end up like this:

My Sql Privileges

My Sql Privileges for ‘pentaho_user’ on ‘datamart’ schema

Privileges for ‘pentaho_user‘ on datamart, foodmart, hibernate, quartz, sampledata, loading_area schemas will be SELECT, INSERT, UPDATE, DELETE (omit DELETE for hibernate and quartz).

Give ‘pentaho_admin‘ full privileges on those tables. You can close the administrator now.

Check that ‘hibuser‘ has full access to hibernate database (given them by the script). Note: this step was reported several times so check it twice.

3. Modify system connections to MySql: Hibernate and Quartz

Use the file file navigator ‘nautilus’ (on the desktop menu: Places->Personal folder) to the following folder:


Note that this are the tomcat (web server) files. Edit (right click->open with->gedit):


And change four lines with the classes, url strings and the SQL to verify the connection to:

<?xml version="1.0" encoding="UTF-8"?> <Context path="/pentaho" docbase="webapps/pentaho/">
    <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
        factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
        validationQuery="select 1" /> 

    <Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
        factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
        maxWait="10000" username="pentaho_user" password="password"
        driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
        validationQuery="select 1"/>

Save the file and exit gedit.

4. Redirect Hibernate to MySql

Navigate to:


This are the Pentaho config files. Open with gedit:


Change it to:


Save the file. In gedit open in the same folder:


Edit june 2011: This file should be ok, no need to edit. But you had to in previus versions, just to keep in the checklist.

Change three property tags in:

<!--  MySQL Configuration -->
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>
<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
<property name="connection.username">hibuser</property>
<property name="connection.password">password</property>
<property name="connection.pool_size">10</property>
<property name="show_sql">false</property>
<property name="hibernate.jdbc.use_streams_for_binary">true</property>
<!-- replaces DefinitionVersionManager -->

Save the file, exit gedit.

5. Set the spring-security to the MySQL database

We’ll change the spring security files (until 3.5 the technology was ‘acegi’, but on 3.6 they rename it).  Navigate up one folder level, and edit the file:


Change it to:


Save the file, close it and open on the same folder:


Edit, change the header and two lines:

<!--  This is only for MySQL. Please update this section for any other database you are using -->
<bean id="dataSource"
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/hibernate" />
<property name="username" value="hibuser" />
<property name="password" value="password" />

6. Modify the file that list the jndi (datasources) connections



Change the driver definitions to:


Important: For new datasources, add at the bottom


You can delete the five line shark statements as this workflow is no longer used.

7. Place the drivers in the tomcat\common\lib folder

Note: Some time ago I had trouble with the 5.0.7 driver for MySQL so I  moved it to another location and used de 3.17 version in my windows system, but now I’m working fine in my Ubuntu box with 5.0.7 so test your system and use the latest version you can use.

Move the downloaded drivers to the


This is important with Oracle if your ETL or reports use this driver.

8. Edit the scripts to avoid HSQLDB to start

Now we must stop HSQLDB from starting, edit:


This is a very important file, so it has a lot of parameters. Move to line 87 (or search for HSQLDB) and comment the hsqldb startup lines -or delete them- like this:

<context-param> <param-name>hsqldb-databases</param-name> <param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value> </context-param>

and in line 215 (or next find):

<listener> <listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class> </listener>

Edit june 2011: For 3.8 on windows and for 3.9 4.0 in linux and windows you need to edit another file to avoid the following error:

In the log -or console- it says “java.sql.SQLException Could not retrieve datasource via JNDI” and the server won’t start. In the browser you get a listener error.

Some people try to edit the systemListeners.mxl file and comment the quartzSystemListener from starting. That works, but you loose the scheduling capabilities of the BI server. Some others try to edit the file as it reports a MyDS problem. As I did for a day fumbling with the WEB-INF/web.xml and META-INF/Context.xml files.

The solution is to edit a new file at:


Just edit the data as in our previus example of context.xml.

The tip to solve it came from Luis Benavides from in a comment in this post.

9. Start the console administration for datasource setup

We need to update de Hibernate database with datasource connection information. We can insert the SQL string using the query browser but we have been using the graphical tools, so we now get the chance to use the Pentaho Administration Console (PAC), but need to edit it’s shell script , and add the JAVA_HOME variable in:


DIR_REL=`dirname $0`
cd -

export JAVA_HOME="/usr/lib/jvm/java-6-openjdk"

. "$DIR/"
setPentahoEnv "$DIR/../biserver-ce/jre"

After saving and in the comand prompt at:




Use you’re internet browser and go to http://localhost:8099, login with admin/password. You should see a connection error as it tries to connect with the Pentaho Server. But close it, for adding datasources its ok.

Click on Administration on the left, and select the ‘Database Connections’ tab. You can check the parameters for the SampleData datasource. We’ll be adding similar ones.

Pentaho Administration Console (PAC)

Pentaho Administration Console (PAC)

Click on the plus (1) icon, then fill the form with the values in the following table (2) -you know the user and password-, and test your connection (3).

Name Driver Class URL
FoodMart com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/foodmart
Datamart com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/datamart
LoadingArea com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/loading_area

The advanced settings are: maxactcon=20, idleconn=5, wait=1000, query=SELECT 1

Exit the browser and turn off the PAC with  ./ Note: We’ll use this console again to add new users. 10. Test your system To start Pentaho, in a command prompt at




You should see if an error prevents Tomcat to show it’s startup in the command window. If somethings goes wrong you’ll see lots of text, remember that is easier to check the logs (/Pentaho/biserver-ce/tomcat/bin/Pentaho.log) they will tell you if you have made typing errors, for example if it says something like ‘SampleData being out of context’, then something in the datasorce scripts went wrong. There are many posible causes and the log is the best way to track errors.

To close the BI server application use:


[Edit] There is another thing that breaks when we change the database. The examples on bi-developers->’ChartBeans Examples’. We need to edit the files on:


These files contains ‘actions’ to be carried out by our server, well review them on a future post. For now replace Sql text:


And In file chartbeans_scatter.xaction, change the sql:

WHERE DEPT=’Sales’ OR DEPT=’Finance’ for: WHERE DEPARTMENT IN (‘Sales’,’Finance’)

Also edit the ‘Flash Chart List’ SQL and make a search and replace for several INTEGER to UNSIGNED in:


Finally erase the file ‘chartbeans_simple_dial.xaction’ as it will not work on the CE (check this).

To make the simple dial example work (OpenFlash doesn’t work on the CE edition) we use the JFree drawing engine, make a change in:


<chartModel chartEngine="OpenFlashChart"
<chartModel chartEngine="JFreeChart"

One last note for windows users:

I had MySQL Workbench 5.2.34 CE working fine, but the following versions refuse to start reporting just: Err: MSVCR100.dll.

So, if you want to use this tool (5.2.37 is the latest one), you have to get two files that are no longer included (msvcp100.dll, msvcr100.dll). I copied them from the 2.34 version to their corresponding folder. Thats it.