Step by Step Beginners Guide to CDF (Community Dashboard Framework)

Dashboards used to be the hottest buzzword among BI enthusiasts such as myself, a Dashboard is like a mini application which gives managers a bird's-eye view of data for the purpose of decision making.

Wikipedia adds that:

“Like a car's dashboard (or control panel), a software dashboard provides decision makers with the input necessary to "drive" the business. Thus, a graphical user interface may be designed to display summaries, graphics (e.g., bar charts, pie charts, bullet graphs, "sparklines," etc.), and gauges (with colors similar to traffic lights) in a portal-like framework to highlight important information.”

In this post we will learn how easy it is to create a Dashboard such as this one:

The Dashboard will allow the user to search for a companies. Companies are arranged in a 3-level hierarchy, and the Dashboard will allow users to navigate down the hierarchy, so if the user clicks a company in one level, the levels below it will show it's subsidiaries and the levels above it will show its parent companies. When ever a company is selected (in any of the levels) the graphs on the bottom display additional data for the company.

To accomplish the task, we are going to use Pentaho BI Server and CDF, which is an open source dashboard framework developed by Pedro Alves from WebDetails.

So first thing first, we have to set up our infrastructure, the good news is that CDF is now an integral part of Pentaho CE, so we just need to download and install Pentaho 3.6:

$ wget http: //sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/3.6.0-stable/biserver-ce-3.6.0-stable.tar.gz/download
$ tar xzvf biserver-ce-3.6.0-stable.tar.gz
$ cd biserver-ce
$ ./start-pentaho.sh

Great, you can see that it is working by navigating to port 8080 on the localhost. You can log-in using the default user and password (joe/password), there are many samples, including CDF samples and also CDF documentation is available through the system.

I am going to publish the system on the web so I have to edit tomcat/conf/server.xml and to change the Connector port from 8080 to just 80, and also to edit tomcat/webapps/pentaho/WEB-INF/web.xml and modify the base-url parameter to the url I am going to use.

Next step is to create a datasource to the database, this can be done using the administrator colsole, but for me it is easier to just editing tomcat/webapps/pentaho/META-INF/context.xml and add this:

<Resource name="jdbc/datasource" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="biserver" password="nottelling"
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/dw"/>

At this point don't forget to restart the server.

OK, next step is to create a solution and a dashboard, the easiest way is copy-paste from the examples.

$ cd pentaho-solutions
$ cp -r cdf-samples/20-samples/charts mySolution
$ cd mySolution

The solution is defined by the index.xml file and we can edit the index.properties file to give it a name and a description. The Dashboard is defined by the .xcdf file which we should also rename and edit to our own names.

Now you can go back to the web-browser, in order to see the new solution and new Dashboard you have to “refresh the repository cache” (there's a menu option for that and also a refresh icon) and Voilà – a new Dashboard was born.

The content of the dashboard is stored in a template.html file, there are several parts to it which I would explain herein.

First there is the html template itself which defines the structure of the Dashboard. In my case I needed a search box, 3 multi-select-boxes, two bar-charts and one gauge which I am going to layout as follows:

<table align="center" style="border: 0px solid>
<tr>
<td valign="top">Search:<div id="search_object"></div></td>
</tr>
<tr>
<td valign="top">A Level:</td>
<td valign="top">B Level:</td>
<td valign="top">C Level:</div></td>
</tr>
<tr>
<td valign="top"><div id="a_object"></div></td>
<td valign="top"><div id="b_object"></div></td>
<td valign="top"><div id="c_object"></div></td>
</tr>
<tr>
<td valign="top"><div id="channel_object"></div></td>
<td valign="top"><div id="dialChart_object"></div></td>
<td valign="top"><div id="product_object"></div></td>
</tr>
</table>

The next step is to create the different components using CDF, the search box is declared using javascript:

var query;
var searchComponent =
{
name: "searchComponent",
type: "textInputComponent",
htmlObject: "search_object",
executeAtStart: true,
parameter: "query",
tooltip: "enter partial company name and press Enter",
postChange: function(){ searchFunction(query); }
}

The htmlObject property is very important, it tells CDF where in the html template the component is going to reside.

The postChange trigger function is going to be called when the users enters a new search query, and would be described in more details later.

Each of the 3 multi-select-boxes is declared in a similar way:

var a;
var aComponent =
{
name: "aComponent",
type: "selectMultiComponent",
parameters:[],
parameter:"a",
size: "10",
valueAsId: false,
htmlObject: "a_object",
executeAtStart: true,
queryDefinition: aQueryDefinition,
listeners: ["event"],
postChange: function(){aFunction(a);}
}; 

The queryDefinition object, as you must have already gueesed, is responsible for querying the data that is going to populate the component, it will be described in more details below.

Bar-charts are created using this code:

var channelComponent =
{
name: "channelComponent",
type: "jFreeChartComponent",
listeners:["selectedRegionMeasure"],
chartDefinition: channelDefinition,
htmlObject: "channel_object",
executeAtStart: true,
listeners: ["event"]
}

The listeners property lists event names to which this component listens, when ever such event is fired the component will refresh it's data.

Finally the code for a gauge component:

var overallComponent =
{
name: "overallComponent",
type: "dialComponent",
listeners:["departmentMeasure"],
chartDefinition: dialChartDefinition,
htmlObject: "dialChart_object",
executeAtStart: true,
listeners: ["event"]
}

Now that all the components are ready we can go ahead and initiate the Dashboard with this call:

Dashboards.init([searchComponent,aComponent,bComponent,cComponent,overallComponent,channelComponent,productComponent]);

CDF includes many components such as Buttons, Combo-boxes, Menus, Tables, Maps, FlashCharts, Pivot views, Traffic lights and more... check out the online documentation for the full list, descriptions and samples.

The next thing we want to define is the behavior of the Dashboard, in my case the state of the Dashboard is going to be managed using 4 variables and is going to use just one event to refresh all the views when the state changes:

var queryStatus;
var aStatus;
var bStatus;
var cStatus;

Now we can go back and look at the postChange function of the search component, which stores the query and resets any previous state. The call to fireChanges is will dispatch the event through all our views causing it to change.

function searchFunction(val) {
queryStatus = val;
aStatus = "";
bStatus = "";
cStatus = "";
Dashboards.fireChange("event",queryStatus);
}

And for each of the multi-select components, the code is just as simple:

function aFunction(val) {
queryStatus = "";
aStatus = val;
bStatus = "";
cStatus = "";
Dashboards.fireChange("event",queryStatus);
}

Now we are approaching the last part of the development, which is to define the data queries in either SQL or MDX. The query is constructed based on the state of the dashboard.

The queryDefinition object for the multi-selects is:

var aQueryDefinition = {
queryType: 'sql',
jndi: "datasource",
query: function(){
var q= "SELECT distinct A_ID, A_NAME " +
"FROM company " +
"WHERE 1=1 " +
(queryStatus==""?"":("AND A_NAME like '%"+queryStatus+"%' ")) +
(aStatus==""?"":("AND A_ID="+aStatus+" ")) +
(bStatus==""?"":("AND B_ID="+bStatus+" ")) +
(cStatus==""?"":("AND C_ID="+cStatus+" ")) +
"LIMIT 100";
return q;
}
}

As you can see, we are using an SQL query to the datasource to pull a list of all the A level companies that match to the criteria that is defined by the status of the Dashboard, so if the status is a query we will pick only companies whose names contains the search query and if the status is a company we will pick only companies on that branch of the hierarchy.

Similarly, the queryDefinition of the bar-charts is:

var channelDefinition = {
width: 300,
height: 250,
chartType: "BarChart",
datasetType: "CategoryDataset",
is3d: "false",
isStacked: "true",
includeLegend: "false",
foregroundAlpha: 0.7,
queryType: 'sql',
jndi: "datasource",
title: "Channel Revenues:",
query: function(){
var query = "SELECT name, sum(Revenue) " +
"FROM company LEFT JOIN revenue ON company.id=COMPANY_SEQ LEFT JOIN channel ON CHANNEL_SEQ=channel.id " +
"WHERE " +
(aStatus==""&&bStatus==""&&cStatus==""?"1=0 ":"1=1 ") +
(aStatus==""?"":("AND A_ID="+aStatus+" ")) +
(bStatus==""?"":("AND B_ID="+bStatus+" ")) +
(cStatus==""?"":("AND C_ID="+cStatus+" ")) +
"GROUP BY id ORDER BY sum(Revenue) DESC LIMIT 10";
//query = "SELECT 'London',100";
return query;
}
}

This query will return the total revenues by channel of all the companies that are on the branch of the selected company, if no company is selected the query will return no results.

The gauge query will select the average value over all the companies in the branch.

var dialChartDefinition = {
width: 300,
height: 200,
chartType: "DialChart",
queryType: 'sql',
is3d: 'true',
jndi: "datasource",
title: "Overall",
intervals: [40,60,100],
includeLegend: true,
query: function(){
var query = " SELECT avg(ovrall) FROM overall LEFT JOIN company ON COMPANY_SEQ=id "+
" WHERE " +
(aStatus==""&&bStatus==""&&cStatus==""?"1=0":"1=1") +
(aStatus==""?"":("AND A_ID="+aStatus+" ")) +
(bStatus==""?"":("AND B_ID="+bStatus+" ")) +
(cStatus==""?"":("AND C_ID="+cStatus+" "));
//query= "SELECT 75";
return query;
}
}

That would be all. We have defined the Dashboard's view by suppling the html template and the components to populate it, the Dashboard's controller was defined using the components call back functions and the update event and the Dashboard's data model by defining the dynamic queries to the datasource. The Dashboard is ready to be used. Enjoy!