Creating Dashboards with CDE

• Tweet
• Share
• Email

• Tweet
• Share
• Email

This post shows the steps to get a customized CDE dashboard up and running on Pentaho BI Server. Starting from a mock-up sketch of a dashboard, this post shows the implementation steps all the way to the finished result, explaining CDE concepts as they appear along the way. CDE uses web technologies i.e. HTML/CSS/JS and SVG. The tutorial dashboard is tested on Firefox 3 and above and on Google Chrome 12. It may require some tweaks or hacks to get it to work on other browsers, especially older ones. So please make sure you have a modern browser when following the tutorial.

To whet your appetite, here’s what the final dashboard looks like. The select box at the top allows to choose the year to report, and all dashboard elements update accordingly.

Download the demo dashboard files. You can import them into a new folder on your server repository. It might not display properly when opened directly after the import. If that happens, open, preview and save the dashboard in your CDE. The dashboard will display properly then.

The sample dashboard is designed on Pentaho Community Server 3.8, but it should work down to and including 3.6.

You’ll need CDE a.k.a. Community Dashboard Editor, too. CDE is part of an entire set of open source plugins to the Pentaho BI Server, contributed and maintained by Pentaho Partner webdetails. There are several community plugins maintained by webdetails, some of which depend on or extend each other, and the best way to get the latest version of them is using the CTools installer. New features and bug fixes are put into the CTools on a regular basis, so it is a good idea to rerun the installer to update them regularly. Specifically, please make sure to update the CTools before following the tutorial. Older versions of CTools may not behave as described in this tutorial.

If you cannot use the CTools installer, or would like to start with a fresh server anyway, go with a freshly built Community BI Server with CTools and Saiku included: Sign up and login to the analytical labs continuous integration server at ci.analytical-labs.com and follow the instructions as outlined in Tom’s post. In fact, that’s what I did myself to work on this blog post.

From now on I’ll assume that you have a properly set up server with recent CTools installed. Let’s start!

When working on a dashboard I find it very helpful to create a sketch of it up front and have it around during implementation. It need not be digital, and may just be a pen and paper drawing, but I’d recommend always having some mock-up reference before attempting the implementation. For the tutorial dashboard the reference looks like this.

The idea is to have 5 overall sections

1. A title bar with a year selector
2. A sales performance chart in the upper left area, the vertical marks show previous year’s performance
3. A table of numbers showing the detailed sales numbers compared to previous year’s in the upper right area
4. A top customers chart in the lower left area
5. A top product lines chart in the lower right area

Let’s make it happen!

Before diving into the implementation, I’d like to briefly discuss some basic CDE concepts. A CDE dashboard is defined in terms of three distinct categories: Layout, Components, and Data Sources.

A CDE Layout defines static HTML blocks that the dynamic parts of the dashboard can hook into. Think of it as providing the underlying grid for the more interesting dashboard elements. CSS and general JavaScript are also maintained in the layout section.

CDE Components are entities that render and display data. Charts, selector boxes and data tables are components. If it’s a dashboard entity that displays data or requires data in order to work properly, it’s a component. In the example dashboard the year selector box, the charts, and the data table are components. Components are placed on the dashboard by first preparing a named space for them in the layout section, and then linking the component into that space.

CDE Data Sources provide data to components. They are usually parametrized SQL or MDX queries and they do not have direct visual representation on a dashboard. It’s just the data rows.

The following illustration shows the three concepts and how they relate.

The entire process of creating and tweaking the dashboard is shown in the screencast below. The sections below the video explain the steps taken to build the dashboard. I recommend viewing the video in a new browser window, while reading through the explanations. Enjoy!

The layout provides the general structure of a dashboard. I like to think of it as the grid for the other elements to hook into. In CDE you create a layout by defining (possibly nested) rows and columns, while also placing custom HTML markup where necessary. CDE uses blueprint css to simplify this task: think of the dashboard to be 24 units wide in total, and assign column widths accordingly. If you want to fill the whole page, you may go for a single column of size 24, two columns of size 12 or three columns of the sizes 6, 10, and 8 for example.

It may help to have a look at the blueprint general and grid test pages to get the idea. Also, when you’re trying to get the layout right, feel free to assign color to the rows and columns so their extent clearly stands out in the preview.

The selector box at the top allows choosing the year of interest. To place it on the dashboard, the layout, the component and a data source must work together. Here’s the steps to successfully place a component:

1. create a new data source that provides the required data and name it properly i.e. “get_all_years”
2. create a new layout column in the appropriate spot, and name it properly i.e. “year_select”
3. create a new component of the desired type and assign the data source (“get_all_years”) and the HTML object (“year_select”)

The SQL query for the year selector is as follows:

SELECT DISTINCT YEAR_ID as YEAR FROM ORDERFACT ORDER BY YEAR_ID

Let’s skip the entire upper section of the dashboard for the moment and continue with the top customers section to the lower left. It should display the top 5 customers for the selected year. The query to start with may look something like this:

SELECT 
	'Revenue' AS SERIES,
	CUSTOMERNAME,
	SUM(ORDERFACT.TOTALPRICE) AS REVENUE
FROM 
	ORDERFACT
	INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMERNUMBER = ORDERFACT.CUSTOMERNUMBER
WHERE
	YEAR_ID = 2004
GROUP BY 
	SERIES, CUSTOMERNAME
ORDER BY
	REVENUE DESC LIMIT 5

The result set is something like this:

The dashboard puts that into a bar chart component which is configured to show horizontal bars.

The chart looks quite reasonable. But the chart should update whenever a new year is chosen in the selector box. By default when a new year is chosen, nothing happens. To fix that, dashboard parameters are required.

Dashboard parameters are essentially variables living on the dashboard. Select components can alter their values using their “Parameter” property. All components can listen to changes of parameter values using the “Listeners” property. The demo dashboard should have a parameter holding the currently selected year. The parameter should have a default value of 2004, the selector box should be able to alter that value, and all other components should listen to changes of that value, and update themselves accordingly.

When a component, such as the “Top 5 Customers” chart, detects a change in the year parameter, it should rerun its underlying query passing the newly selected year into it. In order for that to work the query itself must be parametrized, and the chart component must map the dashboard parameter to a query parameter before running the query. The “Parameters” property is used for that. Query parameters are declared on the data source, and can be referenced within the query using ${parameter_name} syntax.

Dashboard parameters and query parameters often share names (i.e. both are called year), but since they are distinct entities, the executing component always has to map dashboard parameters to query parameters explicitly.

After the chart reacts to year selections properly, its appearance is tweaked a bit.

I’d like the longest bar to be at the top of the chart, which is at the bottom by default. That’s solved by sorting the data in SQL. The adjusted query reverses the order of the result rows.

SELECT * FROM 
(
	SELECT 
		'Revenue' AS SERIES,
		CUSTOMERNAME,
		SUM(ORDERFACT.TOTALPRICE) AS REVENUE
	FROM 
		ORDERFACT
		INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMERNUMBER = ORDERFACT.CUSTOMERNUMBER
	WHERE 
		YEAR_ID = ${year}
	GROUP BY 
		SERIES, CUSTOMERNAME
	ORDER BY 
		REVENUE DESC
	LIMIT 5
)
ORDER BY REVENUE ASC

I’d like to remove the vertical axis on the chart, so the bars and the labels are connected more clearly visually. One way to do it is to use extension points. Extension points allow direct JavaScript access to protovis properties of CCC charts. And yes, CCC a.k.a. Community Charting Components is another member of the CTools family.

To remove the vertical axis, I set the yAxisRule_lineWidth extension point to 0.

The xAxisLabel_text extension point allows manipulating the text of each label using a function. The original value of each label is conveniently passed into the function as an argument. I’d like to prepend the $ sign to each of the labels.

xAxisLabel_text = function(d){return '$ '+ sprintf('%d',d/1000)+'k'}

The incoming value is the number that goes on the label, say 1.000.000, and the function returns a short and neatly formatted “$ 1,000k”. The sprintf() function is a nice formatting helper available in CDE. It implements a formatting scheme compatible with other sprintf() implementations. Look up the docs on the C++ or Perl implementation.

The CCC chart accepts “Value format” and “Tooltip format” functions, which I’d like to use to get nicely formatted tooltip texts too.

Value Format = function f(value){return '$'+sprintf("%d", value)}
Tooltip Format = function f(series, name, value){return name + " " + series + ": "+ value}

Finally, I’d like to adjust the font sizes on the x and y axis a bit. I’m using the following extension points.

xAxisLabel_font = "13px Arial"
yAxisLabel_font = "13px Arial"

Protovis allows a CSS font shorthand declaration for the font property. Go creative

Now that the chart has been tweaked a bit, let’s compare the before and after:

The top product lines chart is created exactly as the top customers chart. The only difference being the different placement and the different data source to go with it.

The financial performance chart at the top of the dashboard shows two measures per metric, the current measure, and last year’s measure as a line mark. CDE does not support this kind of chart directly, and to a first approximation the chart should display two bars per metric. Last year’s performance and current performance. In an additional customization step, the set of bars for last year is going to be converted to lines.

This is a sample result set for 2004; the target series carries last year’s measure in this case:

The SQL to get this result set looks more complex than it should be, and in real life situations it is probably best to get this kind of data from a MDX query, but in this tutorial I’d like to stick to plain old SQL.

The two-bar version of the chart looks like this:

CCC charts are implemented using the protovis library, and if you are JS savvy enough, and know how to use the JS development tools of your browser, you may actually tap into the chart definition and change it to your liking. The following PostExection function (an event fired after the chart has finished drawing) changes the rendering of the chart to remove last year’s bars, resize the current value bars, and draw the appropriate line marks on top of them. Using the PostExecution like this may introduce a slight flicker on the screen when the dashboard is built, depending on the browser. But having a customized chart, may well be worth it.

function f(){
    
    // define some handy shortcut variables
    var bar = this.chart.barChartPanel.pvBar;
    var chart = this.chart;
    var resultset = this.query.lastResults().resultset;
    
    // drop the prev year bars (give them size 0)
    // and resize the others
    bar.data(
            function(d){
                return [0,resultset[d*2][2]];
            }
        )
        .height(27)
        .bottom(2);
    
    // implement last year markers as lines
    bar.add(pv.Line)
        .data(function(d){
            var x = chart.xScale(resultset[d*2+1][2])-chart.xScale(0)-2;
            return [{x:x,y:0},{x:x,y:31}];}
        )
        .left(function f(d){return d.x;})
        .bottom(function f(d){return d.y;})
        .lineWidth(3)
        .strokeStyle("#b08c58")
        .root.render();   
}

The appearance of the chart changed accordingly:

The data table component is useful for displaying data in a HTML table. It is set up exactly like a chart, except that it also accepts header titles and sprintf() compatible format strings for each column. It usually also requires some additional CSS styling to fit into the general design of the dashboard. It also gets a separate SQL query to generate the data.

If you’ve been following along, you should have an operational dashboard by now, congrats!

If you believe that BI dashboards should communicate intuitively, clearly and without unnecessary distractions, you’ll find that CDE’s customization mechanisms give you the total control you need for well designed implementations. Check out the designs data visualization pros are proposing. See for example, Stephen Few’s books on the matter:

Edit: Jos van Dongen mentions another great source:

Edit: Alex Meadows adds a classic:

Good dashboard designs strive to minimize visual distraction. Unfortunately distraction is often generated by dashboarding and charting packages by default. CDE excels at giving you minute control over each dashboard element, thus allowing you to fully optimize the information-to-noise ratio of your dashboard.

This post introduces the basics of CDE, and shows some customization techniques. But there’s more! Be sure to investigate at least the following topics to get the most out of CDE:

• interactivity, i.e. drill downs on charts etc.
• overriding the “draw” function of the table component to gain total control over how the cells are rendered
• implementing custom components using your favorite JS library like Highcharts or Raphael

With CDE the sky is the limit. So if you’re embarking on a project in which flexibility is king, be sure to look into CDE!

Cheers

Slawo

SELECT * FROM 
(
SELECT 
	'Revenue' AS SERIES,
	PRODUCTLINE,
	SUM(ORDERFACT.TOTALPRICE) AS REVENUE

FROM ORDERFACT
	INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
WHERE
	YEAR_ID = ${year}
GROUP BY
	SERIES, PRODUCTLINE
ORDER BY
	REVENUE DESC LIMIT 5
)
ORDER BY REVENUE ASC

(
    SELECT
    	'Profit' as CATEGORY,
    	'Measure' as SERIES,
    	SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
	FROM 
    	ORDERFACT
	INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
	WHERE
    	YEAR_ID = ${year}
)
UNION ALL
(
	SELECT
    	'Profit' as CATEGORY,
    	'Target' as SERIES,
    	SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
	FROM 
    	ORDERFACT
	INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
	WHERE
    	YEAR_ID = ${year}-1
)
UNION ALL
(
	SELECT
    	'Cost' as CATEGORY,
    	'Measure' as SERIES,
    	SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
	FROM 
    	ORDERFACT
	INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
	WHERE
    	YEAR_ID = ${year}
)
UNION ALL
(
	SELECT
    	'Cost' as CATEGORY,
    	'Target' as SERIES,
    	SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
	FROM 
    	ORDERFACT
	INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
	WHERE
    	YEAR_ID = ${year}-1
)
UNION ALL
(
	SELECT
    	'Revenue' as CATEGORY,
    	'Measure' as SERIES,
    	SUM(ORDERFACT.TOTALPRICE) AS MEASURE    
	FROM 
    	ORDERFACT
	WHERE
    	YEAR_ID = ${year}
)
UNION ALL
(
	SELECT
	    'Revenue' as CATEGORY,
	    'Target' as SERIES,
	    SUM(ORDERFACT.TOTALPRICE) AS MEASURE    
	FROM 
	    ORDERFACT
	WHERE
	    YEAR_ID = ${year}-1
)

SELECT
    MEASURE,
    CASE WHEN MEASURE_TARGET > 0 THEN MEASURE_TARGET ELSE 0 END,
    CASE WHEN MEASURE_TARGET > 0 THEN (MEASURE/MEASURE_TARGET-1)*100 ELSE 0 END
FROM
(
SELECT 
    SUM(ORDERFACT.TOTALPRICE) AS MEASURE    
FROM 
    ORDERFACT
WHERE
    YEAR_ID = ${year}
) measure
INNER JOIN (
    SELECT 
        SUM(ORDERFACT.TOTALPRICE) AS MEASURE_TARGET 
    FROM
        ORDERFACT
    WHERE
        YEAR_ID = ${year}-1
) target ON TRUE 

UNION ALL

SELECT
    MEASURE,
    CASE WHEN MEASURE_TARGET > 0 THEN MEASURE_TARGET ELSE 0 END,
    CASE WHEN MEASURE_TARGET > 0 THEN (MEASURE/MEASURE_TARGET-1)*100 ELSE 0 END
FROM
(
SELECT 
    SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
FROM 
    ORDERFACT
INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
WHERE
    YEAR_ID = ${year}
) measure
INNER JOIN (
    SELECT 
        SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE_TARGET 
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = ${year}-1
) target ON TRUE 

UNION ALL

SELECT
    MEASURE,
    CASE WHEN MEASURE_TARGET > 0 THEN MEASURE_TARGET ELSE 0 END,
    CASE WHEN MEASURE_TARGET > 0 THEN (MEASURE/MEASURE_TARGET-1)*100 ELSE 0 END
FROM
(
SELECT 
    SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
FROM 
    ORDERFACT
INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
WHERE
    YEAR_ID = ${year}
) measure
INNER JOIN (
    SELECT 
        SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE_TARGET 
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = ${year}-1
) target ON TRUE