A Dashboard Framework for the Pentaho BI Platform

August 21, 2007
Contributed by Ingo Klose, Pentaho Community Member
 

Community Dashboard Framework

Please note, we have published a new version of this Dashboard Framework.  The new version is called the Community Dashboard Framework and features many improvements.

Download files from Pentaho Dashboard Designer Incubation Home

You can download a zip file with this content and the source files from the /wiki/spaces/PMOPEN/pages/1245546175
Direct Link

1 Motivation

From our experience dashboards are a very important presentation of data. Business users like dashboards since they give them various information objects on one page, mixing diagrams, tables and maybe even textual information. Although the true value of a dashboards is mainly defined by its content and design, the technology plays a role, too. Technology defines the options that a designer have in terms of the representation of data and the navigation between information objects.
When we first investigated on how to build dashboards with Pentaho, we used the PCI's (Pentaho Preconfigured Install) sample dashboard implementation and the accompanying Dashboard Building guide. Then Pentaho introduced the Google Map integration and the AJAX API. We found that Pentaho had provided an alternative way to build dashboards. Unfortunately both approaches don't cater for an easy addition of new dashboards to a solution since they both require new JSP pages to be added to the web application.
Shortly after a client asked us to incorporate some dashboards to his Pentaho based BI web application. In order to ease the development and future additions of dashboards, we thought out this simple framework with the following features:


  • Minimize the changes in the web application to only three additional files,
  • creation of new dashboards by adding files to the solution repository only,
  • a separate dashboard navigation, and
  • the ability to apply security to dashboards via the Pentaho permissions system.

We hope this paper and the introduced framework will be helpful for some people, when they try to implement dashboards using the Pentao BI Platform.
At the time of writing this paper, Pentaho has started an incubation project for a Dashboard Designer, which looks very promising, judging by the screenshots. We still went ahead writing this paper since we hope to ease some dashboard building pains till the dashboard designer is available.

2 Concept 

This framework provides a standardized way to implement and publish dashboards to the Pentaho BI Platform. The Pentaho AJAX API is used to retrieve the BI content and the Pentaho Solution Repository stores the content definitions. Therefore all dashboard information objects, such as charts and reports, need to be defined in Pentaho Action Sequences. That is one difference to the standard sample dashboard which uses chart widgets and ChartHelper components instead of action sequences.
Three files are at the heart of this dashboard framework.

  • Dashboards.jsp - JSP file that provides the access point to the
  • template-dashboard.html - A template organizing the navigation and content area
  • dashboard-list.xsl - Style sheet to display the dashboard navigation bar

The Dashboards.jsp page is called by the user and provides the access to all Dashboards. The main layout of the page is defined in the template-dashboard.html template page and consists of four parts: Header, Dashboard Navigation Bar, Dashboard Content and Footer. Header and Footer are defined in the template file while the style of the Dashboard Navigation Bar is defined in the dashboard-list.xsl. The Dashboard Content area is the place where the actual dashboards are displayed.
As common within the Pentaho Solutions, this framework works on a set of assumptions concerning the organization and naming of the folder structure and files. A new Pentaho Solution called dashboards is the root of all dashboards. Each dashboard is defined in a separate folder that is a direct child of the dashboards folder and must contain two files, index.xml and template.html.
The index.xml is used to identify the dashboard's name and description just as in any other folder in the Pentaho solution repository. The template.html file determines the dashboard's design. The content layout as well as the static content are defined with regular HTML. <div>-tags function as placeholders for the information objects. When the user calls up a dashboard, one or multiple Pentaho action sequences will be executed on the BI Platform and the <div>-tags will display the result. A Javascript, embedded in or referenced from the template file, controls the execution of the action sequences. Two functions are needed for each information object, one to initiate the execution of a action sequence via a call to the Pentaho AJAX API and a second that receives the result from the BI server and places the content into the corresponding <div>-tag. 


3 Implementing Dashboards 

This section will provide a step-by-step guide on how to build a dashboard, Pentaho's sample dashboard will function as example. The implementation of the individual action sequences is not described in detail. Their XML-sources can be found in the Appendix.

3.1 Adding the dashboard to the solution 

It is assumed that there is a solution, called dashboards in your solution repository. If not create a folder with this name as a direct subfolder of your solution root folder. In order to add a new dashboard, create a new subfolder in the dashboards folder. For this example we add a folder called pentaho-sample-dashboard. Within this folder add the index.xml. It is the same kind of index.xml as in the other solution repository folders, please refer to the "Creating Pentaho Solutions" documentation for details.
Update the solution repository and the dashboard is added to the solution. If you call the dashboard page, the name of your dashboard should appear in the navigation bar, although it has no content, yet.

3.2 Defining the content of the dashboard

The first step is to create a template.html file to the new dashboard folder. The template contains a HTML fragment, <head> and <body>-tags are not necessary. If the dashboard contains static information, such as headers and logos, they need to be defined in the template. This is a sample template for the reimplementation of Pentaho's Sample-Dashboard. The first line contains a static header-1 "Select a region" and a second text "Select a Region By Clicking on the Pie Chart" in the middle of the code

<h1 style="font-family: Arial;" id="title">Select a region</h1>
<table style="width: 100%; text-align: left; margin-left: auto; margin-right: auto;" border="0" cellpadding="2" cellspacing="2">
	<tr>
		<td style="width: 50%; text-align: center; vertical-align: middle;">
			<span style="font-family: Arial;">Select a Region By Clicking on the Pie Chart</span>
			<br/>
			<br/>
			<div id="object_1"></div>
		</td>
		<td style="width: 50%; text-align: center; vertical-align: middle;">
			<div id="object_2"></div>
		</td>
	</tr>
	<tr>
		<td style="width: 50%; text-align: center; vertical-align: middle;">
			<div id="object_3"></div>
		</td>
		<td style="width: 50%; text-align: center; vertical-align: middle;">
			<div id="object_4"></div>
		</td>
	</tr>
</table>

For the dynamic content <div>-tags function as place holders. For this example we need 4 place holders, each which a different ID. (object_1 to object_4) The ID is necessary to find the placeholders and put the result of the action sequences as their content. The execution of the action sequences and the result's mapping to the <div>-tags is controlled by the following Java Script, which is part of the template.html. The script could be also in a separate file. Then the template needs to point to it from a <script>-tag.

<script language="javascript" src="dashboard_control.js" ></script>



The following Javascript controls the sample dashboard, the comments in the code will explain the use of each function :




<script language="javascript" type="text/javascript">
	// Variable for region and department to make them available to all functions
	var region;
	var department;

	// load() will be called when the dashboard is opened, here the first action sequence is called
	// by using pentahoAction() from the AJAX API. The last parameter "updateObject_1"
	// will be called after the execution is done
	function load(){
		pentahoAction( "dashboards", "pentaho_sample_dashboard", "RegionsPieChart.xaction",null,'updateObject_1');
	}

	// This function receives the result from load() and overwrites
	// the content of the target div with it
	function updateObject_1(content){
		document.getElementById( 'object_1' ).innerHTML = content;
	}

	// Setting the region variable
	// Changing the title and adding the region name to it
	// executing RegionVarianceChart.xaction using the region parameter
	function loadRegionVariance(region_param){
		region = region_param;
		document.getElementById( 'title' ).innerHTML = "Headcount Spending for " + region;
		pentahoAction( "dashboards", "pentaho_sample_dashboard", "RegionVarianceBarChart.xaction",
		new Array(new Array( "REGION", region)),'updateObject_2');
	}

	//Updating the object_2 with the results of loadRegionVariance()
	//in addition the other dependent <div>-tags are emptied
	function updateObject_2(content){
		document.getElementById( 'object_2' ).innerHTML = content;
		document.getElementById( 'object_3' ).innerHTML = "";
		document.getElementById( 'object_4' ).innerHTML = "";
	}

	//Setting the department variable and adding it to the title
	//Executing DepartmentDialChart.xaction using the department and region varibles
	function loadDepartmentDial(department_param){
		department = department_param;
		document.getElementById( 'title' ).innerHTML = "Headcount Spending for " + region +"/"+department.replace(/\+/g," ");
		pentahoAction( "dashboards", "pentaho_sample_dashboard", "DepartmentDialChart.xaction",
		new Array(new Array( "REGION", region),new Array( "DEPARTMENT", department)),'updateObject_3');
	}
	//Update the object_3 with the result from loadDepartmentDial()
	// afterwards call loadReport() to execute the next action sequence
	function updateObject_3(content){
		document.getElementById( 'object_3' ).innerHTML = content;
		//load the embedded report, too
		loadReport();
	}

	//Executing embedded_report.xaction with the department and region variables
	function loadReport(){
		pentahoAction( "samples", "dashboard/jsp", "embedded_report.xaction",
		new Array(new Array( "region", region),new Array( "department", department)),'updateObject_4');
	}

	//Update object_4 with the results of loadReport()
	function updateObject_4(content){
		document.getElementById( 'object_4' ).innerHTML = content;
	}
</script>

The load()-function serves as the entry point for the dashboard. If an action sequence needs to be execution on opening a dashboard, place the code here. To display an action sequence two functions need to be implemented. The first function calls the action sequence via the Pentaho AJAX API using the pentahoAction() function. The second one will be called after the execution has finished and will receive the content, therefore it needs to accept the content parameter.
Currently it is not possible to execute two action sequences simultaneously via the AJAX API. Therefore you need to execute them in sequence, as you can see in the function updateObject_3().



4 Appendix - New Chart Action Sequences 

The original Pentaho Sample Dashboard uses widgets to display charts. In this implementation we choose to use action sequences with chart components instead since we wanted to use the Pentaho AJAX API. In order to work properly, Pentaho 1.5.5 needs to be used due to some changes in the charting engine. For the tabular report the same action sequence as in the Sample Dashboard is used. This is the source code for the three chart action sequences:

RegionsPieChart.xaction 

<?xml version="1.0" encoding="UTF-8"?>
<action-sequence>
  <title>Chart Component Test (Bar Chart)</title>
  <version>1</version>
  <logging-level>DEBUG</logging-level>
  <documentation>
    <author>Ingo Klose</author>
    <result-type>chart</result-type>
    <description>The pie chart with the variance by region</description>
  </documentation>

  <inputs/>

  <outputs>
    <image-tag type="string"/>
  </outputs>

  <resources/>

  <actions>
    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Relational</action-type>
      <action-outputs>
        <query-result type="result-set" mapping="query_result"/>
      </action-outputs>
      <component-definition>
        <jndi><![CDATA[SampleData]]></jndi>
        <live><![CDATA[true]]></live>
        <query><![CDATA[select REGION, sum(ACTUAL) as ACTUAL
					from QUADRANT_ACTUALS
					group by REGION
					order by ACTUAL]]>
	</query>
      </component-definition>
    </action-definition>

    <action-definition>
      <component-name>ChartComponent</component-name>
      <action-type>Regions Pie Chart</action-type>
      <action-inputs>
        <chart-data type="result-set" mapping="query_result"/>
      </action-inputs>
      <action-outputs>
        <image-tag type="string"/>
      </action-outputs>
      <component-definition>
        <chart-attributes>
          <chart-type>PieChart</chart-type>
          <border-visible>false</border-visible>
          <include-legend>false</include-legend>
          <is-3D>false</is-3D>
          <url-template><![CDATA[javascript:loadRegionVariance('{region}')]]></url-template>
          <paramName>region</paramName>
          <title-font>
            <font-family>Arial</font-family>
            <size>12</size>
            <is-bold>false</is-bold>
            <is-italic>false</is-italic>
          </title-font>
          <use-base-url>false</use-base-url>
          <url-target>_self</url-target>
        </chart-attributes>
        <width><![CDATA[450]]></width>
        <height><![CDATA[300]]></height>
        <title><![CDATA[Headcount Spending by Region]]></title>
      </component-definition>
    </action-definition>

  </actions>
</action-sequence>

RegionVarianceBarChart.xaction

<?xml version="1.0" encoding="UTF-8"?>
<action-sequence>
  <title>Region Variance Bar Chart</title>
  <version>1</version>
  <logging-level>ERROR</logging-level>
  <documentation>
    <author>Ingo Klose</author>
    <result-type>chart</result-type>
    <description>Bar chart with the regions variance by department</description>
  </documentation>

  <inputs>
    <REGION type="string">
      <sources>
        <request>REGION</request>
      </sources>
    </REGION>
  </inputs>

  <outputs>
    <image-tag type="string"/>
  </outputs>

  <resources/>

  <actions>
    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Relational</action-type>
      <action-inputs>
        <REGION type="string"/>
      </action-inputs>
      <action-outputs>
        <query-result type="result-set" mapping="query_result"/>
      </action-outputs>
      <component-definition>
        <jndi><![CDATA[SampleData]]></jndi>
        <live><![CDATA[true]]></live>
        <query><![CDATA[select department, sum(variance)
			from QUADRANT_ACTUALS
			where region in( {PREPARE:REGION} )
			group by department]]></query>
      </component-definition>
    </action-definition>

    <action-definition>
      <component-name>ChartComponent</component-name>
      <action-type>Bar Chart</action-type>
      <action-inputs>
        <chart-data type="result-set" mapping="query_result"/>
      </action-inputs>
      <action-outputs>
        <image-tag type="string"/>
      </action-outputs>
      <component-definition>
        <chart-attributes>
          <chart-type>BarChart</chart-type>
          <orientation>horizontal</orientation>
          <color-palette>
            <color>#336699</color>
            <color>#99CCFF</color>
            <color>#999933</color>
            <color>#666699</color>
            <color>#CC9933</color>
            <color>#006666</color>
            <color>#3399FF</color>
            <color>#993300</color>
            <color>#CCCC99</color>
            <color>#666666</color>
            <color>#FFCC66</color>
            <color>#6699CC</color>
            <color>#663366</color>
          </color-palette>
          <chart-background type="color">#FFFFFF</chart-background>
          <plot-background type="color">#EEEEEE</plot-background>
          <url-template><![CDATA[javascript:loadDepartmentDial('{department}')]]></url-template>
          <series-name>department</series-name>
          <use-base-url>false</use-base-url>
          <url-target>_self</url-target>
          <title-font>
            <font-family>Arial</font-family>
            <size>12</size>
            <is-bold>false</is-bold>
            <is-italic>false</is-italic>
          </title-font>
        </chart-attributes>
        <width><![CDATA[450]]></width>
        <height><![CDATA[300]]></height>
        <title><![CDATA[Headcount Variance]]></title>
        <by-row>true</by-row>
      </component-definition>
    </action-definition>

  </actions>
</action-sequence>

DepartmentDialChart.xaction

<?xml version="1.0" encoding="UTF-8"?>
<action-sequence>
  <title>DepartmentVarianceDialChart</title>
  <version>1</version>
  <logging-level>DEBUG</logging-level>
  <documentation>
    <author>Ingo Klose</author>
    <result-type>chart</result-type>
    <description>Dial Chart with the headcount variance in percent for the selected region and department</description>
  </documentation>

  <inputs>
    <REGION type="string">
      <sources>
        <request>REGION</request>
      </sources>
    </REGION>
    <DEPARTMENT type="string">
      <sources>
        <request>DEPARTMENT</request>
      </sources>
    </DEPARTMENT>
  </inputs>

  <outputs>
    <image-tag type="string"/>
  </outputs>

  <resources/>

  <actions>
    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Relational</action-type>
      <action-inputs>
        <REGION type="string"/>
        <DEPARTMENT type="string"/>
      </action-inputs>
      <action-outputs>
        <query-result type="result-set" mapping="query_result"/>
      </action-outputs>
      <component-definition>
        <jndi><![CDATA[SampleData]]></jndi>
        <live><![CDATA[false]]></live>
        <query><![CDATA[select ROUND((sum(variance)/ CAST (sum(budget) AS DOUBLE))*100,2) as value, -15 as minimun, 15 as maximum
			from QUADRANT_ACTUALS
			where region in( {PREPARE:REGION} )
			  AND department in ({PREPARE:DEPARTMENT})]]>
	</query>
      </component-definition>
    </action-definition>

    <action-definition>
      <component-name>JavascriptRule</component-name>
      <action-type>JavaScript</action-type>
      <action-inputs>
        <query_result type="result-set"/>
      </action-inputs>
      <action-outputs>
        <variance type="string"/>
      </action-outputs>
      <component-definition>
        <script><![CDATA[variance = query_result.getValueAt(0,0);]]></script>
      </component-definition>
    </action-definition>

    <action-definition>
      <component-name>ChartComponent</component-name>
      <action-type>Dial Chart</action-type>
      <action-inputs>
        <chart-data type="result-set" mapping="query_result"/>
        <variance type="string"/>
      </action-inputs>
      <action-outputs>
        <image-tag type="string"/>
      </action-outputs>
      <component-definition>
        <chart-attributes>
          <chart-type>DialChart</chart-type>
          <border-visible>false</border-visible>
          <tick-interval>5</tick-interval>
          <value-color>#9999bb</value-color>
          <tick-color>#808080</tick-color>
          <!--  this is the color of the needle -->
          <needle-color>#808080</needle-color>
          <plot-background-color>#ffffff</plot-background-color>
          <!--  intervals define ranges on the dial that are colored differently from the dial background -->
          <interval>
            <label>under</label>
            <!-- this is the value that the range starts at -->
            <minimum>-15</minimum>
            <!-- this is the value that the range stops at -->
            <maximum>0</maximum>
            <!--  this is the color of the range -->
            <color>#bb4040</color>
            <!--  this is the color of the text for the range value and tick marks -->
            <text-color>#bb4040</text-color>
            <stroke-width>2</stroke-width>
          </interval>
          <interval>
            <label>under</label>
            <minimum>0</minimum>
            <maximum>15</maximum>
            <color>#40bb40</color>
            <text-color>#40bb40</text-color>
            <stroke-width>2</stroke-width>
          </interval>
        </chart-attributes>
        <width><![CDATA[150]]></width>
        <height><![CDATA[150]]></height>
        <title>Headcount Variance ({variance} %)</title>
        <by-row>true</by-row>
      </component-definition>
    </action-definition>

  </actions>
</action-sequence>