Pentaho CDE Examples

This is a collection of Dashboard examples already shared by bloggers that are recreated and tested on Pentaho Server CE 8.1 community edition.</p>

Most of these tutorials, presented in alphabetical order, are still online but some required minor changes to work in the current suite.

You can upload the code to see it in action or use it as reference. At the bottom you’ll find the link and instructions to upload them to your system.

Annual Sales

An interesting Dashboard is “Annual Sales” from Tercer Planeta (post) that shows the table component with columns like the databar or sparkline. It’s also interesting that it uses functions to change extension points, protovis colors or CSS in CCC charts.

The database is AdventureWorks (see previous post to upload it in SQL statements for MySql).

recreación Sales 2003-2005

Bullet Graph Dashboard

The original post of 2012 provided a step by step guide to build this Dashboard that uses a MDX query (Mondrian model from a star schema) and a tabular component to show the monthly data, it also gets that information in Kettle/PDI (ETL) and adds metrics data (like a scorecard/KPI) and shows them on a Bullet Chart. Its a simplified example (processes, bullet graph metrics and aggregations on mondrian) of a real Dashboard that I did for a client. There are also samples in pre-excecution and format functions to modify values in parameters or CSS to change the Chart style.

The chart data is refreshed by updating the queries when the year or month selectors change.

recreación Bullet Graph

For this to work on your system check last section.

Data Dashboard Steels-Wheels

This is a Dashboard that ATOL Conseils & Développements shared a while back. I couldn’t find the tutorial link but they have more complex and beautifull Dashboards in their site or articles like this one that talks about data access in a mondrian cube.

In this example you can see the use of pie and line charts and also the table component. At that time the arrows had to be drawn in javascript (use an if to show a gif file), now its a type of column. There is also code for the tooltip data.

recreación Atol

Sales Overview YTD Dashboard

This one was one of the first Dashboards that I saw. In 2011 Slawomir Chodnicki showed in his blog the complete procedure to build a Dashboard from a draft up to detail javascript that modified a graph (at that time no bullet graph component existed) to show what he wanted. I updated the components but the post is a jewel.

recreación YTD

Steel Wheels Demo Dashboard

This was a Dashboard included in CE. It shows bar graphs updated by a selector.

Demo Steel-Wheels.jpg

Welcome Dashboard in CTools

I guess this is a previus version of the current Dashboard example. The current one even lets you select views so this one could be used as a startup point to lear this tools.

Demo Welcome Dashboard

New Dashboard (RequireJS)

CDE used by default the 24 column blueprint design framework, now its the 12 column, but it also supports Bootstrap, it even has a button to generate a skeleton layout in the Layout view. When you craete a new CDE Dashboard it enables the RequireJS javascript library, you can change to node.js in the settings toolbar. That is important as some events are referended diferently in the former is this.dashboards and in the later is Dashboards with some events and methods available in each one.

This sample is documented at theat Hitachi-Vantara site and is built using the bootstrap framework in layout and RequireJS code. It is built differently that the old ones and further in the tutorial it says it can be embeded in pages outside the server.

Demo Sample with RequireJS

More samples

There are three more examples, two are components to use in more complex Dashboards, one has bars and the other dots.

And the thirth is a report from Will Gorman. This one needs tables from google analytics to work, but its a beautifull sample built with Pentaho Report Designer (PRD).

Will Gorman report.jpg

The Dashboard’s Code

To get this files into your system you have to:

  1. Get this zip file.
    You can confirm that it contains a manifest in xml and a folder with the different CTools files.
  2. Then login into yor Pentaho-server , select the home button and select Browse Files → click on Public folder.
  3. In the right menu the upload option will appear. Click on it. Click on the Browse button and navigate to the downloaded file and click Open and Ok.

After a few momments you can navigate in the Additional Examples folder. To see all the files you have to click on the top menu view and click on Show Hidden Files.


Most of them work with the SampleData Datasource so they should work on your system as it is.


If you want to see Geo Localization examples then check Kleyson de Sousa Rios Github page and download and upload the zip file with his examples for the NewMapComponent (NMC-samples).

* Mondrian schema location (Bullet Graph Dashboard)

I could’t find a way to reference the mondrian schemas on the system JCR repository taht is needed in the Mondrian Input Step, so I edited the Datasource Kettle Component → dsBulletGraph by clicking in (…)


And used ${Internal.Transformation.Filename.Directory} and move two levels up “/../..” to refer to your tomcat/pentaho solutions.


If you develop your PDI transformation, the Catalog location field must contain the complete path-filename to your mondrian XML specification, then afer you upload it in your Pentaho Server, make the edit to your schema path.

So, for this example to work, you have to download a copy of steelwheels.mondrian.xml that’s in your Pentaho Demo system repository to the /tomcat/pentaho-solutions directory, or download this copy in a Zip file.


Pentaho Server 8.1 with MariaDB (MySql)

[Edited on june 13th to add the solution note to Sql parameters]

Migrate your Database

The official way to change the database is at pentaho-wiki and pentaho-doc. These are notes based on my 2011 post that modifies the DEMO to use MariaDB (or MySql) instead of the HSQLDB that comes with it.

Important: I verified this on a Ubuntu 18.04 and MariaDB 10.1, keeping the  jcr (Jackrabbit repository) in thefilesystem and have a problem with JINDI SQL Queries that I have to substitute with JDBC SQL Queries (see the final note).




[Optional] Update the driver to Connector/J (, choose older version and platform independent. Then unzip its content and move mysql-connector-java-5.1.46.jar to ~/Pentaho8/pentaho-server/tomcat/lib. The OPTION error on logs will go away. You should move the older driver to another location.

If you have the desktop tools, copy the driver also to ~/Pentaho8/report-designer/lib, ~/Pentaho8/data-integration/lib, ~/Pentaho8/shema-workbench/lib.

Create content in Database

Start the MariaDB engine and run the suplied scripts in ~/Pentaho8/pentaho-server/data/mysql5, to create required databases:

cd ~/Pentaho8/pentaho-server/data/mysql5
 mysql -u root-user -p root-password < create_quartz_mysql.sql
 mysql -u root-user -p root-password < create_repository_mysql.sql

Download the Sample Database (here), unzip and upload it:

mysql -u root-user -p root-password < SampleData_2018_mysql.sql


Quartz Settings

  1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/quartz/
  2. Open
  3. Find #_replace_jobstore_properties and change it to
    org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

Hibernte Settings

  1. Goto to ~/Pentaho8/server/pentaho-server/pentaho-solutions/system/hibernate
  2. Open hibernate-settings.xml.
  3. Find the  tag config-file and change it to
  4. Check settings to MySQL Configuration according to your system (If you use defaults there is nothing to change) in mysql5.hibernate.cfg.xml.

System connections

  1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/META-INF/
  2. Open context.xml
  3. Change classname, url and SQL query 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.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
    		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.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
    		maxWait="10000" username="pentaho_user" password="password"
    		driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
    		validationQuery="select 1"/>

Spring config

  1. Go to ~/Pentaho8/pentaho-server/Pentaho-solutions/system/
  2. Open
  3. Change driver, url and dialect to

Add datasources to jndi list

  1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/simple-jndi
  2. Open
  3. Change the driver definitions to mysql

Stop HSQLDB from starting

  1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/WEB-INF/
  2. Open web.xml
  3. Find the word HSQLDB. There are to points to edit. You will delete “–>” on the first line and “<–” on the last, to make it into a comment.
    In version 8.1 go to lines 25 and 167. They should look like this:




III. Change Web App Datasources

  1. Start your server and log in your browser (localhost:8080/pentaho) as Admin
  2. In the PUC (Pentaho User Console) page click at the left top menu: FileManage Data Sources
  3. You need to select SampleDataJDBC data-source and click on the gear icon to open the config window.
  4. Change the database type to MySql. Then change the Database Name to sampledata (the database name is in lowercase), port to 3306, type again the password (password) and test the connection. Click OK if its succesfull.

Here, you can define a connection to your databases or you can return later.

That’s it, you should have a working Server with MariaDB. To test it by using the samples: Browse: Samples → Public → Steel Wheels.


IV. Modify your SQL Queries that uses Parameters

But there is a problem if you want to use a query wit SQL with parameters. Maybe there is a missconfiguration in CDA or the repository, or a problem with the MySql driver for a MariaDB engine, as the parameters in the JNDI configuration report an error and do not work.

To get a result I use only SQL JDBC, by supplying these config parameters on each query

driver:   com.mysql.jdbc.Driver
 url=      jdbc:mysql://localhost:3306/sampledata?generateSimpleParameterMetadata=true
 user:     pentaho_admin
 password: password



If you have problems check the log file is ~/Pentaho8/pentaho-server/tomcat/logs/catalina.out its very verbose so look for ERROR lines

Pentaho 8.x

[Edited on may 15th 2018 for 8.1]

In sourceforge, Pentaho has been renamed “Hitachi Vantara | Pentaho”, the binaries for all projects in this version has been uploaded for the community. Pedro Alves has summarized the main features for 8.0 and 8.1.

The links to the Web server and the Desktop tools are:

Pentaho 8.1:
Pentaho 8.0:

  1. Server
    • Tomcat server to squedule jobs, grant access to run reports and jobs to users and design dashboards.
      BI Server 8.1
      BI Server 8.0 [1.2GB]
  2. Client Tools
    • Pentaho Data Integration – Best ETL you’ll find.
      PDI 8.1 [1 GB]
      PDI 8.0 [979.8 MB]
    • Pentaho Report Designer. Reporter for different databases.
      PRD 8.1 [868.7 MB]
      PRD 8.0 [666.0 MB]
    • Pentaho Metadata Editor – Grant access and model (physical and business) on how to query your data.
      PME 8.1 [861 MB]
      PME 8.0 [836.5 MB]
    • Pentaho Aggregation Designer – Specify mondrian cube aggregations.
      PAD 8.1 [26.3 MB]
      PAD 8.0 [25.6 MB]
    • Pentaho Schema Workbench – Edit your mondrian cube.
      PSW 8.1 [31.3 MB]
      PSW 8.0
      [84.4 MB]
    • Big Data Plugin
      BDP 8.1 [629.5 MB]
      BDP 8.0 [619.8 MB]

Sound like its time for discovery.

Hitachi Vantara

Today September 19, 2017, Hitachi Vantara was announced, it is a new business entity that will unify the operations of Hitachi Data Systems, Hitachi Insight Group, and Pentaho.

So we’ll be saying both Pentaho and Vantara for a while to refer to this BI suite.  🙂

This is the press release:

The main new Site:

And new twitter account to follow:

The Community Edition at Sourceforge: with the Hitachi Logo and title “Hitachi Vantara | Pentaho”. The wiki in

Data integration is still “PDI” or “Kettle” in the community area:

It seems (#HitachiNEXT) they’ve got a nice business and consulting strategy. Lets see what they do with the original Open Source philosophy.

Pentaho 7.1 – Demo Install

The newest release Pentaho in its community edition is available for download at sourceforge (link) since the 22th of may of 2017.

Pedro Alvez announced its availability the same day with an excellent post in his blog (link). He mentions new visualizations, scalability with big data engines and repository improvements -someday they’ll deliver something you can work confortable with, maybe-, and a new web theme. We’ll see how they are supported in the CE edition. A new mobile access for the EE. The PDI team is exited about its ‘metadata injection’ improvements.

You’ll find it at

  • Web application: Business Intelligence Server [1.1 GB] with 58 closed reports.
  • The best ETL aplication: PDI [904 MB] with 142 closed reports.
  • Report Designer [606 MB] with 19 closed reports in their JIRA system.
  • Pentaho Metadata [787 MB] with 5 closed reports.
  • And Big Data Shims folder.

Steps for backup, install, upgrade to a MySQL local DB are the same that you are already familiar with and has been previously posted in this blog (link) and well documented at the Pentaho Wiki.

Knowage Suite 6.x CE

I found Spago BI version 5.1, that is about two years ago. It was a Complete Open Source BI Sotution, the only one there is, they said. They were proud of it!. And I was waiting for 5.3 to start a series of posts as they announced amazing changes.

On may 4th 2017, @SpagoBI anounced they were starting a #spagobirevolution. The same company (Engineering Group) did an extreme makeover to XU experince and released a Comunity Edition (CE) and an Enterprise Edition (EE) with a Knowage Brand.

What I have seen is that the CE is an interesting product, somewhat crippled as important functionality like scheduling and MDX calculated fields and more are excluded, but lets hope its cockpit designer, metadata, models and widgets, deliver a promising alternative for OS BI data exploration.


Check their overview

Visit their site:

Install notes

[Updated on June and December 2017, and May 2018]
Knowage has twitted about ta Guide [Updated to a Manual] with instructions for windows, linux and DB migration scripts from 5.x [link].

Prerequisites: You’ll need Java JRE and JAVA_HOME defined.

  1. Download the Knowage Installer [ow2 download page] [2.9GB from one server in ow2] [2.3GB] [1.078GB] [798mb]
  2. Open a Terminal, create a folder, unzip its content:
    mkdir Knowage6
    cd Knowage6
    cp ../dowloads/
    unzip Knowage
    chmod 777
  3. Run the installer, it will:
    a. Open a welcome dialog. click Next.
    b. Ask if you accept the license agreement. Do it so and click Next.
    c. Ask for your preference on a charting library. Accept the default and click Next.
    d. Ask for six modules to be installed. If all are selected, Click Next.
    e. Select a destination folder. I selected HOME$/knowage6 it will create a Knowage-Server-CE folder.
    f. It will ask for your MySql credentials: jdbc:mysql://localhost:3306/, user and password.
       Version 6.1.1 contains MariaDB files, I used my existing version without problems.
       It said that two schemas: kwowage-ce and foodmart-demo will be created

    The installer will extract the server, the .war files and update the database.
    In the final dialog unselect all and click finish to end the installer.

  4. I modified the startup and shutdown scripts to set the correct path to java:
    cd $HOME/Knowage6/Knowage-Server-CE/bin
    [add your JRE path to the first line like:]
    export JRE_HOME="/usr/lib/jvm/java-8-openjdk-amd64"

    save your script and repeat for

  5. You might want to increase the session time out from the default 30 minutes.
    Then you should edit ./HOME$/Knowage6/Knowage-Server-CE/conf/web.xml

  6. If you used your MySql/MariaDB instance, you can delete three files:
    rm $HOME/Knowage6/Knowage-Server-CE/webapps/mariadb-10*
  7. Start your tomcat server:
    cd $HOME/Knowage6/Knowage-Server-CE/bin/
  8. Start yor browser and set it to:

You will find examples on the left menu, enjoy.

If you have problems with the server you can check your ./logs folder.