Customizing bullet graph in Ctools on Pentaho BI Server Community Edition - Part II - Conditional Formatting

In my previous post, we had seen how to customize some aspects of the bullet graph including the colours of the range values and the bar (measure value) and the target value (marker). In this post, we will carry on from there and add conditional formatting based on the data (red or blue).

One of the principles of Information Design is to use colour to attract attention to something that needs attention. All the other colours need to be blended so as to not attract attention. I would like to add the capability that the colour of the bar (measure value) changes based on which range the value falls in.

Conditional Formatting of a Bullet Graph

Lets start, from the place we left off. Since we have already customized our chart quite a bit, let us duplicate that and start from there.
To duplicate, navigate to the component you are interested in, highlight it and click on the icon to duplicate component as shown below.

I have modified the following values.

Parameter	Value	Description
Name	chtInnovation	Name of the component instance
Bullet Measures	69	The observed measure for this performance indicator
Title	Innovation/ Technology Investment	A title for all the graphs. In case, the graph is created for multiple categories, one bullet title appears for all the bullet graphs. Usually appears on top of the graph.
HtmlObject	Panel_3	Name of the placeholder for the graph. Clicking on backspace in the text input box, displays the list of available html containers.

These changes are shown below.

Clicking on preview, we can see the following dashboard.

To apply conditional formatting, we need to change in Advanced Properties. Make the following changes in Advanced Properties. Click on values for Extension points

Add the following entry as an additional argument

Argument	Value	Description
bulletMeasure_fillStyle	function(v,d){var accVar=50;if(d.ranges && d.ranges.length>2){accVar=d.ranges[d.ranges.length-2];} if(v>accVar){return "#43578A";}else{return "#CE2B0F";}}	Adds conditional formatting to the measure bar, that if there are ranges defined in the bullet graph and there are atleast 2 of them, then highlight the fill colour if the measure is less than second last range.

In the code above, since our measure value is 69, and the top but one range starts from 75, our graph should appear in a shade of red rather than the default colour, which is a shade of blue.

Upon adding, the new argument will look as following on the Extension points form.

Now saving the graph and previewing it, we can see the bullet graph colour change when value is less than second range (75), which means it is a cause for concern, as shown in the screen below.

In this post, we saw how to add conditional formatting to bullet graphs so that is there is something that needs to be brought to the attention of a busy executive, it can be highlighted appropriately based on relative value of the measure to the ranges.

Creating a dashboard with Pentaho Community Edition

As the information sources for business executives is increasing, taking decisions is becoming harder. It is for this reason, Executive dashboards are becoming more and more important. Organizations of all shapes and sizes are trying to see how to create Executive dashboards that provide actionable insights to their executives. In the government space, this is even more complex, as many departments and organizations have overlapping mandates that may also be contradictory at times. Executive dashboards provide a way to organize information within the enterprise to focus on actionable information. Open source BI platforms such as Pentaho, offer a compelling option to organizations, who would like to test their ideas before investing thousands in main stream BI platforms.

In this post, we will start the process to create a BI dashboard using Pentaho tools for a multi-perspective multi-organization dashboard. This exercise will go through several steps that involve  multiple technologies. To start off, I will start with creating a customized dashboard look and feel that will house the different KPIs. This I will do in the current post.

Now, in a separate blog post (http://syntharch.blogspot.com/2013/01/quality-of-life-kpis-measuring-human.html), I have started listing Social KPIs and Measures that could be used for measuring a range of social development outcomes. We will build the dashboard using these social measures within the series.

Starting Pentaho BI Server

In previous posts, we discussed installing Pentaho BI Server Community Edition, as well as Community Dashboard Tools on top of it. Let us navigate to the place our Pentaho BI Server is installed and start the Tomcat instance that ships with it.

On my machine, it as follows

> cd Work/Servers/Pentaho/biserver-ce

> ./start-pentaho.sh

Now, once the Tomcat instance has started, we can access the browser to launch the application. For me the URL is http://ub1204.arthgallo.com:8082/pentaho

Logging in the instance with the default username and password, we are shown the familar screen containing the home page. Upon logging in, navigate to the browse panel on the left, right click and select option to create a new folder.

In my case, I am going to call the new folder nydash.

Once created, I want to add a new dashboard page.

Now that the page is created, click on the Save option on the dashboard page. I am going to call it nygov scorecard and save it under the nydash folder.

Now that the dashboard is created, we need to create the bullet graphs that we need. The state government may want to track itself along multiple dimensions, say operational, policy and strategic.

On the Layout section, select the option to Apply a template. We need to click the icon on the Layout Structure to achieve this as shown below.

On the dialog box that pops up, choose the 3 column template.

Clicking on Ok, will give you the following screen.

Clicking on Preview gives us the following display.

We can close the preview. First thing we need to do is to fix some of the UI elements.

For this navigate to the Layout section, and expand the first row and column. Navigate to the background colour property and change it to #003366. Also change corners to Simple.

Next navigate to the nested html element. Clicking on the html element shows the html in an editable colour. Make the following changes

Text: New York State Performance Dashboard

Color: FFEFFF

Save and preview the changes.

Next navigate to Row 3. It will have 3 columns, each of which we will modify as follows.

Color: #EAEAEA

Corner: Simple

Next we update the first column of the last footer row to change the text as follows in the html element.

We also change the settings for the column as follows.

Span Size: 12

Text Align: Left

For the next row, we change it to

Span Size: 10

Height: 40

Text Align: Right

We also update the html to replace the default text with an image from NY gov site.

Lets preview what our template looks like

At this point, we are ready to add some components. We will do this in the next post. In my next post, we will add a bullet graph to the dashboard and customize the look and feel.

Adding Postgresql support to Pentaho BI Server Community Edition

At the start of a new project, I wanted to use postgresql database as my database repository for a BI dashboard project. I had selected Pentaho as the BI Server of choice along with its associated components. Previous posts have walked through the installation of the Pentaho BI Server Community Edition on a Ubuntu box as well as Installation of Dashboard tools on Pentaho BI Servers Community Edition.

I launched the Pentaho BI Server and accessed the Community Data Dashboard to see the default dashboard.

The first step is to add a data source, which I did my accessing File> New > Data Source on the top level menu for Pentaho BI Server.

This brings the following page, where I selected Database Table(s) as the option.

Selecting Database Table(s) brings the following options, where I gave the database name and the option that I wanted to use the data source for reporting and analysis.

At this point, it also lists the available connections on the Pentaho server environment. To add a new connection, you need to click on the + button to the right of list of data connections, that is a green circular button with a plus sign.

Clicking on the button brings the following dialog box to define a new connection.

As you can see, Postgresql is not a supported option. Thankfully, they have provided a link for Adding Databases, which is a help link that provides the information needed. It suggests that to add support for a new database, it has to be deployed and configured in multiple places, and provides the list of places where changes need to be made.

Here is a step by step description of what all I did.

Downloading the Postgresql JDBC drivers

First step was to download the Postgresql JDBC drivers. Postgresql JDBC drivers are available at http://jdbc.postgresql.org/download.html

The page provides links to the latest drivers as well as older drivers. To ensure I was getting the right version of the drivers, I opened a new terminal window, launched Postgresql and typed the command to get the version.

> psql

psql > show server_version;

So I downloaded the JDBC drivers for Postgresql 9.1 from the website and copied them to the folder where I could access them.

Before copying them over to the pentaho location, I had to shutdown the Pentaho server, which I did by issuing the stop command in the Pentaho installation folder.

> ./stop-pentaho.sh

I copied the driver jar file to the first location below.

//Work/Servers/Pentaho/biserver-ce/tomcat/lib

I also copied postgresql to the administration-console folder for pentaho.

Then, I restarted the Tomcat server and retraced my steps to define a new connection as shown earlier in this post by accessing File > New > Data Source on the menu and clicking on the option to add a new connection.

On the connections dialog box, we can now see the option to add Posrgresql as a valid data source.

Adding all the details in for the connection, we can add the connection to the environment.

Now we can configure our portal using postgresql data.

Install Community Dashboard Tools on Pentaho BI Server 4.5 Community Edition

Soon after installing and setting up Pentaho Community Edition on my box, I realized that it does not ship with a dashboard tool. That has to be installed from another project called CTools.

Downloading CTools

CTools is available at http://www.webdetails.pt/index.html#ctools. Once you access this URL, you will access the following page.

The page contains a link to download Ctools bundle. Accessing that link shows the following page on the Github site.

We can download the shell script and the README.txt file to our local folder. I must warn that saving the ctools-installer directly from the browser and right clicking, actually gave me the html of the current page, rather than the file. For downloading the file, I had to click the file, go into edit mode and copy-paste the contents to a local shell script file.

Anyways, I downloaded it to a folder called ctools under my Pentaho folder. The first thing is to make ctools executable. This is done by entering the following command on the command line in a terminal window.

> chmod a+x *.sh

The next thing we need to do is to run the installer with the correct parameters. This is done, as shown below.

> ./ctools-installer.sh -s pentahoServer/biserver-ce/pentaho-solutions -w pentahoServer/biserver-ce/tomcat/webapps/pentaho

On my machine, the above works out to the following.

> ./ctools-installer.sh -s ~/Work/Servers/Pentaho/biserver-ce/pentaho-solutions -w ~/Work/Servers/Pentaho/biserver-ce/tomcat/webapps/pentaho

Executing the above script, we can see that the script downloads the necessary files needed for the installation. The script prompts to replace several of the existing scripts that I accepted, as show below.

Once the prompts are through, the installation starts and finally it is done!

Now we need to start the embedded tomcat instance contained within Pentaho dashboard, and we can see that upon logging in as a user, we can access additional links for Community Dashboards (called CDB, CDV, CDE and CDC).

Accessing link for CDE (Community Dashboard Editor), the user can now define their own dashboards as shown below.

That's it. We have successfully installed Community Dashboards on Pentaho Community Edition. In the next post, we will see how to configure a dashboard.