Pentaho CDE Samples

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

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.

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.

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.

Steel Wheels Demo Dashboard

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

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.

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.

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

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.

[Edit]

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.

Advertisement

Additional Databases

Sample Databases

The Pentaho Demo comes with SampleData (SteelWheels based) HSQLDB that will suffice for modeling and learning all the Pentaho tools. But you may want to model metadata* or mondrian from scratch, require more rows in your tables, or want to replicate lots of samples that refer to the Microsoft Server. Well, at least that what crossed my mind when I started using these tools.

So I looked up the links to recreate two additional databases in MySQL for you to use. 🙂

I. DATABASE

Two Databases

The classic databases for learnimg SQL and MDX are AdventureWorks (OLTP) [Updated link on 2022] and FoodMart (DW). AdventureWorks is a relational database and FoodMart is a Data Wharehouse database.

Download them and Unzip them. You need to add this line on the top of FoodMart file:

CREATE DATABASE IF NOT EXISTS foodmart; USE foodmart;

Upload them to your MySql database and grant access to pentaho_user.

mysql -u root-user -p root-password < AWBackup.sql
 mysql -u root-user -p root-password < foodmart_mysql.sql
 echo "grant all on adventureworks.* to pentaho_user@localhost identified by 'password';`" | mysql -u root-user -p root-password
 echo "grant all on foodmart.* to pentaho_user@localhost identified by 'password';" | mysql -u root-user -p root-password

II. CONFIG

Add datasources to jndi list

1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/simple-jndi
2. Open jdbc.properties
3. For new datasources, add (You can define it also in the web app if you like)

   FoodMart/type=javax.sql.DataSource
   FoodMart/driver=com.mysql.jdbc.Driver
   FoodMart/url=jdbc:mysql://localhost:3306/foodmart
   FoodMart/user=pentaho_user
   FoodMart/password=password
   AdventureWorks/type=javax.sql.DataSource
   AdventureWorks/driver=com.mysql.jdbc.Driver
   AdventureWorks/url=jdbc:mysql://localhost:3306/adventureworks
   AdventureWorks/user=pentaho_user
   AdventureWorks/password=password

Add datasources in the web app

Download the FoodMart mondrian (snowflake schema) here and unzip them. We will use the FoodMart file in next section.

Add datasources in the web app

1. Click at the left top menu: File → Manage Data Sources
2. Create a JDBC Data Source for each Database:
   a. Click on the New Data Source button
   b. Name the Datasource and type: FoodMart, Database. Click Ok.
   c. Create (plus sign icon) a new Connection: FoodMart.
   Name: FoodMart; Select Type: MySql; Host: localhost; Database: foodmart;
   user name: pentaho_user; password: password.
   d. Click Test and it should report a succesfull connection, click Ok.
   e. Repeat for AdventureWorks just change name and Database: adventureworks.
   f. After you create both data sources, click Cancel to return to the Data Source Dialog.
3. Create the Analisys Data Source for the FoodMart model:
   a. Click in the Gear Icon.
   b. Select New Connection.
   c. Click on Import Analysys.
       select the downloaded FoodMart.Mondrian.xml file.
   d. Click Import.

That’s it.

To use FoodMart.mondrian.xml you can use the Desktop Tools Schema-Workbench and Aggregation-Designer (here is an excelent tutorial by Diethard Steiner), but for the Pentaho-Server you will need to install jPivot4J from the marketplace as jpivot or Saiku cannot read it.

---

* There are two tutorials for using the meta-data editor to configure user or group business models and to provide restriction access to databases. The first one is by Diethard Steiner on 2009 or one written by Herwin Rayen in 2014. The PRD can be programmed to use this access. But I have to tell that no client has ever asked/payed for it. 🙂

Pentaho 7.1 – Demo Install

The newest release Pentaho 7.1.0.0.12 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 Sourceforge.net:

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