In a previous post we have installed the Pentaho server and used CDF code to create a dashboard.

In this post we will develop another dashboard for Bugzilla using CDE - the Dashboard Editor..

 

Here is what we are going to build:

 

The data for the dashboard is coming from Bugzilla on Tikal ALM suite testing environment using SQR.

 

 

Installing CDE to bi-server is simple (see previous post for pentaho quick installation notes):

$ wget cdf-de.googlecode.com/files/CDE-bundle-1.0-RC3.tar.bz2
$ tar xjvf CDE-bundle-1.0-RC3.tar.bz2
$ rm -rf biserver-ce/pentaho-solutions/system/pentaho-cdf biserver-ce/pentaho-solutions/cdf 
$ cp -rf CDE-bundle-1.0-RC3/* biserver-ce/pentaho-solutions/

 

 

CDE makes Dashboard development as easy as 1-2-3

  1. The Layout (view)

  2. The Components (controller)

  3. The Data sources (model)

 

 

Step 1: The Layout

 

The layout of a dashboard in CDE is done with a hierarchy of rows and columns (each one is a html <div/> tag on the CDF layer)

The layout editor toolbar allows you to save and load templates, add resources (css/js), add rows and columns, add images, add html, reorder and delete.

 

 

As you can see in the final result we will need a header, a body and a footer; and in the body we'll need a filter panel and another panel to hold the line chart, the bar chart and the pie chart.

 

Step 2: The Components

The list of available components is impressive, charts are based on the CCC project which is based on Protovis which is based on SVG, you can find the regular line, bar, pie and you can render any diagram based on Protovis, Rephael and OFC.

 

Additional components (under Others section) include simple HTML elements such as texts, tables, and buttons as well as the BI platform services such as PRD reports, PDI generated content and xaction services.

 

 

For our exercise we use:

  • two parameters: one to select issue-type and the second to select project
  • two selectors: one for each parameter
  • three charts: line chart, bar chart and pie chart
  • one text description

 

 

Lets look at the line chart component definition as an example:

 

 

It uses data from line1datasource, it is dependent on the two parameters which means that the parameter values will be passed in the query to the datasource, it listens on the two parameters which means that whenever a parameter changes value the chart component will be refreshed and finally it has an htmlObject code of line1 which determines which div in the layout it is going to occupy.

 

 

The select component is mapped to the parameter which means that the selects will change the value of the parameter and will trigger all listeners, the selector options are generated by a datasource which will be demonstrated shortly.

 

 

The text component uses simple JS to generate the text dynamically.

 

function(){ return "Showing " + filter1param + " " + filter2param } 

 

Step 3: The Datasource

CDE data access is based on CDA, there are way to many cool features to mention all here but alternatives include: SQL, MDX, MQL, XPATH, PDI, xaction, JS, ...

 

 

 

We have 5 queries (two for the parameter selectros and three for the charts):

 

 

The data-source definition looks like this:

 

 

Thats all, the CDF generated code is attached.

 

Conclusion

Using the right tool for the job makes life easier and merrier.

 

Big thanks to the Pentaho community and to Pedro Alves for leading CDE/CCC/CDF/CDA development and support.

 

 

11

Comments

Hey UDI, thanks for the great tutorial! Everythink works fine except for including parameters into a query...   I tried to include the 'par_sources' parameter in a MDX query like this one: select NON EMPTY {[Measures].[price], [Measures].[count], [Measures].[count_switch]} ON COLUMNS,   NON EMPTY {Except([Booking Window.Arrival category].AllMembers, {[Booking Window.Arrival category].DefaultMember})} ON ROWS from [tomamo_price] where [Source].[par_sources]   Is there a special syntax or how can we include parameters into a MDX or SQL query?   Best regards, Nops

Yes, you will need to write [Source].[${par_source}] to reference the parameter.   My query to for the bar-chart is select NON EMPTY {[Measures].[Issues]} ON COLUMNS, NON EMPTY DESCENDANTS([Product].[${filter2param}],1,LEAVES) ON ROWS from [Issues]   Glad you enjoyed it.

Thx, works perfectly!   Maybe this synthax should already be included in the CDE - SQL-Wizard, where you can add parameters into a query by selecting it on the left panel. Now it just adds the parameter name (in your case, i.e., 'filter2param') instead of '${filter2param}'.   All the best, Nops    

hi, i'm using Pentaho 3.8.0, i try to use CDE: i creat a Datasource with OLAP Chart wizard but when i clic in Preview the chart don't appear..the dashbord is empty.Help please!!

hi,       i have biserver-ce-3.7.0-stable and CDE 1.0 R3.my mysql data not show in table. but my dashboard show only layout but any data not show.

 Hi ,udid   1) I use CCC Bar in horizontal orientation , values of y axis are long (about 20 -25 character) : names of cities and regions, for this reason half of values are out of the chart and half of them in the chart , is it possilbe to show all of them out of the chart? What should i do to show the values at the left of the chart?       Sorry for my english,if i couldnt explain the problem.   

No worries, your english is ok..   Edit the CCC component and click on Advanced Properties and increase the yAxisSize to fit your longest member name, it also nice to right-justify the test using Extention Point yAxisLabel_textAlign:right (Extention Points are also on the Advanced Properties of the component).   take a look at the code:   var render_bar1chart = { type: "cccBarChart", name: "render_bar1chart", chartDefinition: { width: 400, height: 200, dataAccessId: "bar1ds", path: "/Fuse/Dash5.cda", crosstabMode: false, seriesInRows: false, animate: true, clickable: false, timeSeries: false, timeSeriesFormat: "%Y-%m-%d", stacked: false, maxBarSize: 100, panelSizeRatio: 0.8, banelSizeRatio: 0.9, orientation: "horizontal", colors: [], showValues: false, valuesAnchor: "right", titlePosition: "top", titleSize: 25, legend: true, legendPosition: "bottom", legendAlign: "center", showXScale: true, xAxisPosition: "bottom", xAxisSize: 30, showYScale: true, yAxisPosition: "left", yAxisSize: 100, xAxisFullGrid: false, yAxisFullGrid: false, axisOffset: 0, originIsZero: true, extensionPoints: [["yAxisLabel_textAlign","right"]] }, parameters: [["filter2param","filter2param"]], executeAtStart: true, htmlObject: "bar1", listeners: ['filter1param','filter2param'] };  

 Thank you ,udid! Now it works ! Where do you know that there is such parameter as yAxisLabel_textAlign ?  Where can i see the list of all parameters ? Any documentation ?  I have another and last question : When I choose  Series in rows=true values of chart are in different colors and i can set list of colors, but when I set Series in rows=false there is only one color even I add more than one color in Advanced Properties. Is it possible to draw chart with different colors when  Series in rows=false ? And set color for each value, for example : if percent is between 60% and 100% then color is red  , if percent is between 30% and 60% then color is yellow, otherwise green     PS: I registegered in pentaho forum , but i get such message when i try to ask question: goon, you do not have permission to access this page. This could be due to one of several reasons: Your user account may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system? If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.  

hi, the formal docs are available from www.webdetails.pt for a small fee and I hear they have very good trainings around the globe or you can search the web and find good bits of information, such as http://www.vinzi.nl/media/CTools-intro-v1.pdf.   The colors are per series, so if you select "Series in rows=false" and you have only two columns (X & Y) on the query, all the data points would belong to the same series so they will have the same color, if you want another sreries you should add another column.   If you are looking for conditional formatting of bars, using a different series for each group is not the best way to go, because the graph will have strange spacings, instead IMHO you should be able to script (via JS) the chart look-and-feel (via Protovis API), but it is a bit more complex, see http://pedroalves-bi.blogspot.com/2011/02/protovis-component-in-cde.html   Happy Dashboarding \o/