Pentaho Released Version 5.2 CE: This Week (october 6)!

Pentaho & Webdetails dit it again, as promised, on October, the new GA release, I can’t wait to see whats new (new theme & marketplace, improved PRD COSSTABS?) or check the release notes. Still no integrated Saiku on CE but you cant have it all 🙂

These are direct links:

BI Server 5.2

http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/5.2/biserver-ce-5.2.0.0-209.zip/download

Data Integration 5.2
http://sourceforge.net/projects/pentaho/files/Data%20Integration/5.2/pdi-ce-5.2.0.0-209.zip/download

Report Designer 5.2
http://sourceforge.net/projects/pentaho/files/Report%20Designer/5.2/prd-ce-5.2.0.0-209.zip/download

Pentaho Metadada 5.2
http://sourceforge.net/projects/pentaho/files/Pentaho%20Metadata/5.2/pme-ce-5.2.0.0-209.zip/download Mondrian

Mondrian Schema Workbench 3.8
http://sourceforge.net/projects/mondrian/files/schema%20workbench/3.8.0/psw-ce-3.8.0.0-209.zip/download

Mondrian Agregation Designer 5.2
http://sourceforge.net/projects/mondrian/files/aggregation%20designer/5.2/pad-ce-5.2.0.0-209.zip/download

The steps I took for the upgrade are on a new post.

Yeeeeiiii!

Advertisements

Bullet Graph Dashboard: PDI-MDX-SQL DataSource + BI Server + CDE

To Do

The idea to build this dashboard comes from joining Stephen Few’s spec [pdf], an excelent implementation from Slawomir Chodnicki and finding out that the C*Tools (Webdetails dashboard project) has published a Bullet Graph widget from the Protovis Library that is easier to use and customize.

Dashboard PDI Final

Final Dashboard

Components

We’ll need:

  1. CDE Editor. Install or update to the latest version [Instructions]
    Used to create and edit the parameter, datasource and mappings to the graph.
  2. Pentaho PDI/Kettle ETL tool [Download].
    Used for data retrieval by selected parameters, normalization and PKI process.
  3. The Pentaho BI Sever
    To excecute the dashboard via its menu and see the html result.

Building The Layout

Lets start a new CDE dashboard by a click on the CDE icon in the top toolbar: CDE icon

Header Rows

The CDE Editor will open with ‘Layout’ selected. This is the asociated toolbar:
cde layout toolbar

  • Click on the row icon (=)
    Select Row
    Set:

    • Name=header
    • BackgroundColor=99a5de
      (in the bottom text field, close clicking on the color ball)
    • In the ‘Corners’ attribute. Use the down-arrow to scroll to Round
  • Click on the row icon (=) three more times to have a total of 4 rows.
    Be carefull, the toolbar is dynamic and the icons will change.
  • On the first and second rows add one ‘Html’ element (<>)
  • On the third row, set Name=ParamRow
  • Type in the first html type a title:
    <h2>Sales Dashboard By Product Line</h2>
  • On the second Html type:
    <div class="topRow1">Sales YTD</div>
    <div class="topRow2">Actual amount (horizontal bar) and Previuos Year (blue mark)</div>
    <hr class="topRowHR"/>

It should look like this.

Dash 00

Save in your development folder with a meaningfull ttitle (used by the BI menu system)  and click Preview to see the headers. Close the dialog.

Parameter Row

  • The third row will need four columns.
  • The first and the third column will have also an Html element.
  • Put in the first one the text Year, and in next Month.
  • Select the second column and look for the name attribute set it to nYear (node for year select).
    Name the forth one nMonth.

Result Row

  • Add two columns
  • Name the first one nResultGraph
    and the other nResultTable
  • Set for each column span size=12 (there’s a total of 24 columns in the template)

Save the final layout, that should look like this.

Dash n

Important: If the message “Dashboard saved successfully” doesn’t appear, check your mofifications for errors. If you can’t fix it, exit and resume from the latest point saved.
To edit a dashboard, select it on the browse panel in the BI Server. Right click on it and then select Edit in the context menu.

Building Datasources and its Widgets

Three types of datasources:

  1. Two SQL queries are needed for Year and Month.
  2. The table will be  a simple MDX.
  3. The Graph will need additional columns for ranges, we’ll use an ETL transformation for this one.

SQL

Click on Data Sources ‘button’ in your top-right. The left side of the CDE will list all the connections the CDE can use to get data.

  • Click on SQL Queries, then on sql over sqlJndi
    The group SQL Queries will be added
  • Set:
    name=dsYear
    Jndi=SampleData
    query=
SELECT DISTINCT YEAR_ID FROM DIM_TIME ORDER BY YEAR_ID DESC

Click on Components ‘button’ in your top-right.

  • We need a parameter to hold the Year selection
    • Click on the Generic button on the left list
    • Click now on Simple parameter
    • Set Name=pYear
      Set de default to Property value=2005
    • Save
  • Drop Down
    • Click on Selects button on the left list
    • Click now on Select Component
    • Set Name=csYear
      Parameter=pYear (the one defined above)
      HtmlObject=nYear (attach point, that is the column we named that way in the layout)
      Datasource=dsYear
    • Save
  • Click Preview. Check the select box. Close the preview dialog.

For the Month SQL, the set up is a little diferent as we need to set up a listener for changes on the Year select box and then pass that parameter to the Component and then the SQL datasource.

  • Click on Data Sources
    • Click SQL Queries, then sql over sqlJndi.
    • Set:
      name=dsMonth
      Jndi=SampleData
      Click on Parameters, click on the Add Button.
      in the dialog: Name=pYear, value=2005, Type=String
      query=

      SELECT DISTINCT MONTH_NAME
       FROM DIM_TIME
       WHERE YEAR_ID=${pYear}  ORDER BY MONTH_ID
  • Click on Components
    • Click on the Generic button
    • Click on Simple parameter
    • Set Name=pMonth
      Set de default to Property value=May
    • Click on Selects button on the left list
    • Click now on Select Component
    • Set Name=csMonth
      Parameter=pMonth
      HtmlObject=nMonth
      Click on Listeners, on the select arrow choose pYear
      Click on Parameters, click on the Add button,
      in the dialog set Arg=pYear, Value=pYear (you can select with the “…” button)
      Datasource=dsMonth
    • Save

If you click Preview you should see something like this.

Preview 01

MDX

Let’s define the query against the ‘Steel Wheels Sales’ cube as our Datasource. There are some links at the bottom of the article about MDX and sumary functions.

  • Click on
    • Data Sources
    • MDX Queries
    • mdx over mondrianJndi.
  • Set:
    • name=dsTable
    • Mondrian squema=SteelWheels
      (jndi will be set to SampleData)
    • Query=
      with
      member [Measures].[Sales_Curr] as 'Sum(Ytd([Time].CurrentMember), [Measures].[Sales])'
      member [Measures].[Sales_Prev] as 'Sum(Ytd(ParallelPeriod([Time].CurrentMember,1)), [Measures].[Sales])'
      select
      {[Measures].[Sales_Prev], [Measures].[Sales_Curr]} ON COLUMNS,
      {[Product].Children} ON ROWS
      from [SteelWheelsSales]
      where [Time].[2005].[QTR2].[May]

And now the grid element to display our data.

  • Click on:
    • Components
    • Others
    • Table Component
  • Set:
    • Name=ctTable
    • HtmlObject=nResultTable
    • Datasource=dsTable
  • Save & Preview
  • Now click on Advanced Properties (below the Component button)
  • Click on:
    • Column Headers
      add button for: Sales, Previous, Actual
    • Column Formats
      add button for: %s, $%.2f , $%.2f
      Those are for: string (%s) and currency with two decimals ($%.2f)
  • Set:
    • Paginate=False
    • Show Filter=False
    • Sort Data=False
  • Save & Preview

For testing and format purposes the used query doesn’t change when the month or year are modified. Now we need to add listeners, the parameters in the component and the datasource to calculate the ‘quarter’ depending on the month.

Click on Components (you may be already there)

  • Click on:
    • (left) Generic button
    • Simple parameter
  • Set:
    • Name=pQtr
    • Property value=QTR2

Click on Data Sources

  • Click on:
    • (group triangle) MDX Queries
      select dsTable
    • Parameters
  • Add three lines
    • Name=pYear, Value=2005, Type=String
    • Name=pQtr, Value=QTR2, Type=String
    • Name=pMonth, Value=May, Type=String
  • Click on Query
    change last line from:

    where [Time].[2005].[QTR2].[May]

    To:

    where [Time].[${pYear}].[${pQtr}].[${pMonth}]
  • Save & Preview.
    You should see the same image than before. Close the dialog.

Click on Components

  • Click on:
    • (group triangle) Others
      select ctTable
    • Listeners
      Select in the drop down: pYear, pMonth. Click OK
    • Parameters
  • Add three lines:
    • Arg=pYear, Value=pYear
      (you can use the three dots (…) button to select  value)
    • Arg=pQtr, Value=pQr
    • Arg=pMonth, Value=pMonth
  • Click on:
    • Advanced Properties (at the top link on your right)
    • preExecution
  • Paste this javascript function to set Quarter based on month selected:
    • function Q (){
      var vQtr='';
      switch (pMonth) {
      case 'Jan':
      case 'Feb':
      case 'Mar':
      vQtr='QTR1'; break;
      case 'Apr':
      case 'May':
      case 'Jun':
      vQtr='QTR2'; break;
      case 'Jul':
      case 'Ago':
      case 'Sep':
      vQtr='QTR3'; break;
      case 'Oct':
      case 'Nov':
      case 'Dec':
      vQtr='QTR4'; break;
      }
      pQtr=vQtr;
      }
  • Save & Preview.
    Try changing the month to ‘Jan’. You should see something like this.Table Preview

Close the preview dialog.

ETL

In he last part we’ll use an ETL process and the Bullet Graph widget.

Start the PDI. Create a new transformation and save it in the demo/development folder with the name SalesDashboard.ktr.

This is the PDI transformation

Dashboard PDI

Dashboard PDI Transformation

  1. Prepare the default parameters for testing.
    On the menu click Edit ->Parameters
    Select Parameters Tab, Define pYear, pQtr, pMonth.
    Dash PDI 00
  2. Set the MDX
    Add Input->Mondrian Input
    Dashboard PDI 01
    Check the ‘Replace variable’  checkbox so the parameters are used.
    The query is:
with
member [Measures].[Sales_Curr] as 'Sum(Ytd([Time].CurrentMember), [Measures].[Sales])/1000'
member [Measures].[Sales_Prev] as 'Sum(Ytd(ParallelPeriod([Time].CurrentMember,1)), [Measures].[Sales])/1000'
select
{[Measures].[Sales_Prev], [Measures].[Sales_Curr]} ON COLUMNS,
{[Product].Children} ON ROWS
from [SteelWheelsSales]
where [Time].[${pYear}].[${pQtr}].[${pMonth}]
  • Rename columns
    Here we copy columns to new ones but set the new names. This is necesary if you want to remove the dynamic nature of column names in MDX results, for example when you use time in columns then you may get [Time].[2005].[QTR2].[May] or [Time].[2005].[QTR1].[Jan] then you better use ‘CurrentMonth’ to use in PDI steps.
    Add Scrpting->Modified Java Script
    Dashboard PDI 02
    The script is:

    var rowOut = getOutputRowMeta();
    newRow = createRowCopy(rowOut.size());
    var ProductLine = rowMeta.getString(newRow, 0);
    var Actual   = rowMeta.getNumber(newRow, 1);
    var Previous = rowMeta.getNumber(newRow, 2);
  • Get the maximum value for each column.
    Here max value for Actual is renamed to Max1 and Previous o Max2.
    Add Statistics->Group by
    Dashboard PDI 03
  • Calculate max values and two ranges.
    Add Scrpting->Modified Java Script
    This is an oversimplified ETL, with ranges set on maximum values for all rows. In any case you could use KPIs from tables and a lookup step.
    – maximum value from both columns
    – round it up
    – I set two marks one at 70% other at 90%, for grade of color in the graph bar.
    Dashboard PDI 04
    – The sample script use is:

    var Maximum=0.0;
    var Mark1=0.0;
    var Mark2=0.0;
    if ( (Max1!=null) || (Max2!=null) )
    if ( Max1 > Max2 )
    Maximum = Max1;
    else
    Maximum = Max2;
    if (Maximum!=0.0) {
    if (Maximum>=1.0) {
    Maximum = Maximum / 10.0;
    Maximum = ceil(Maximum) * 10.0;
    } else {
    Maximum = Maximum / 1.0;
    Maximum = ceil(Maximum) * 1.0;
    }
    Mark1=Maximum*0.7;
    Mark2=Maximum*0.9;
    }
  • Process measures
    Each row may need processing, if KPI is set for each measure then it should be calculated on this thread. Here we just add a subtitle string (k=thousand).
    Add Transform->Add constants
    Dashboard PDI 05
  • Remove excesive text from product line description
    The first colum has text like “[Product].[Ships]”, we can remove the fixed part with a regular expression to replace the unwanted and fixed text
    Add Transform->.Replace in String Step.
    Type in Search:  ^\[Product\]\.\[(.*)\]$
    Notice that no output field is specified so ProductLine will be used, that the dot and squared brackets are ‘escaped’ with backslash and that the replace string is (.*)=$1
    Dash PDI Regex
    For using this step: Reference1, Reference2
  • Join the data and its ranges.
    Add Joins->Join Rows (cartesian product)
    Dashboard PDI 06
    The important thing is to state the step from where is the data coming from, if you don’t do this you end up missing data or even complete rows: Add constants.
  • Sort as you like.
    Add Transform->Sort rows
    Dashboard PDI 07
  • Output
    Make available only the relevant columns:
    – Measure Title
    – Measure Subtitle
    – Value of Bar
    – Value of Mark
    – Min Range (darker shade in bar)
    – Max Range (lighter shade in bar)
    – Max value
    Add Transform->Select values
    Dashboard PDI 08

Selecting this last step and previewing the output you get.

Dashboard PDI Result

You can close the PDI.

Bullet Graph

Back to the CDE Editor.

Click on Data Sources

  • Click on:
    • MDXKettle Queries button
    • ketle over kettleTransFormFile
    • Select the ketle over kettleTransFormFile Component
  • Set
    • Name=dsBulletGraph
    • Kettle Transformation File=SalesDashboard.ktr
    • Click on Variables, a dialog will open
      add three lines for:
      – Arg=pYear
      – Arg=pQtr
      – Arg=pMonth
    • Click on Parameters, in the dialog add three lines:
      – Name=pYear, Value=2005, Type=String
      – Name=pQtr, Value=QTR2, Type=String
      – Name=pMonth, Value=May, Type=String
    • Click on Output Options, in the dialog add six lines:
      – Index=0, Index=1, Index=2, Index=3, Index=4
      Index=5, Index=6
    • Click on Columns, in the dialog add six lines:
      – Index=0, Name=Product Line
      – Index=1, Name=Subtitle
      – Index=2, Name=Actual
      – Index=3, Name=Previous
      – Index=4, Name=Mark1
      – Index=5, Name=Mark2
      – Index=6, Name=Maximum
    • Click on Query=OutputBullet
      (last activity)
    • Save CDE

Click on Components

  • Click on:
    • Charts
    • CCC Bullet Chart
      a group will be added
    • Select the new CCC Bullet Chart Component.
  • Set
    • Name=cgBulletGraph
    • Width=420
    • Height=220
    • Datasource=dsBulletGraph
    • Clear Title and Subtitle
    • Click Bullet Ranges
      click on the minus button (-) to delete each line
      Close the dialog
    • Click on Parameters, in the dialog add three lines:
      – Arg=pYear, value=pYear
      – Arg=pQtr, value=pQtr
      – Arg=pMonth, value=pMonth
    • Set HtmlObject=nResultGraph
    • Click on Listeners
      Select pYear, pMonth

Save and preview. We are almost finished

Dashboard PDI Almost

Fine Formatting

There are three formating options in the CDE

  1. Format options in CDE, either in the component advanced properties or html or column layout
  2. CSS class from the web page
  3. Some properties of the components

To work with the CSS or DOM elements we add a resource file:

  • Create a text file and type:
    That is mainly header fonts and colors, parameter style and table formatting.

    #header { margin-top:1em; }
    h2 { font-size:1.4em; padding-top:7px; padding-left:1em; height:1.6em; }
    .topRow1 { font-size:1.2em; font-weight:700; margin-left:1em; }
    .topRow2 { font-size:1em; margin-left:1em; }
    hr.topRowHR { height:2px; width:900px; color:red; }
    #ParamRow { font-size:0.9em; vertical-align:bottom; margin-bottom:2em; }
    #ParamRow select { font-size:0.9em; vertical-align:top; }
    #ParamRow input { font-size:0.9em; vertical-align:top; }
    #nResultTable { margin-top:2em; }
    table tr.even, table tr.odd{ background-color: white; }
    table thead th { border-bottom: 1px solid #CCC; background-color: #fff; }
    table tbody td.number, th.number{ text-align: right; }
    table tbody td.string, th.string{ text-align: left; }
    table tfooter td { background-color: white; }
    .ui-widget-header { background: white; color: white; border-bottom:0px none; border-top:1px grey solid; border-left:0px none; border-right:0px none;}

    Save it in the demo/development folder as Dashboard.css

  • On the CDE, click on Layout option
  • Add a css resource file, select from the options:
    css
    external
  • Set:
    name=cssDashboard
    Resource file=Dasboard.css

This will be the result

Dash Final 1

We can modify the Graph if we access its properties, in this case the ‘Extension Points’. Lets change the Marker shape and color.

Click on Components

  • Click on the triangle of the Group Charts
  • CCC Bullet Chart
  • click on Advanced Properties.
  • Click on Extension points a dialog will open.
    Add Three lines. Fill with:

    • Arg:bulletMarker_shape  Value:bar
    • Arg:bulletMarker_lineWidth  Value:3
    • Arg:bulletMarker_strokeStyle  Value:#2e559c

Note:
Check posible values for Extension Points at CCC (Bullet Chart) and Protovis sites.
In the CCC (Bullet Chart) page check the keyword to access: base, mark, measure, title, rule, range. In the rest of the document and examples look for the second part: font, shape. Join them with an undescore: bulletMarker_lineWidth.
Or if you want to change the color of the inner bar, you could try:
Arg: bulletMeasure_fillStyle, Color: green or #0F0

You can use other properties to change the appearence, for example the tooltip to show the value of a value can ve modified.

Click in the Advanced Properties of the Graph, in Value format:

function f(value){return ('$'+sprintf("%.1f", value)+'m')}

The final product should be the initial dashboard image.

.

Additional Links

  • Bullet Chart on webdetails: here.
  • Class, Field, Method and Extension Points on webdetails: here.
  • MDX function YTD youtube video by phiintegration.
  • MDX Time Series Functions Part I. Series by William Pearson.
  • Another fine example about building CDE dasboards OSBI.
    Its in French, but the code and diagrams are easy to understand.

Complete Pentaho Installation on Ubuntu, Part 13

Additional Software

There are lots of plugins that enrich the pentaho BI suite. There is a plugin page for the BI Server PUC and for the PDI). I still haven’t tried them all but here are some interesting ones anyway:

BI Open Flash Charts

You can now add the Open Flash graphs in your dashborads in addition to the Open Flash Chart and JFreeChart. This plugin was started from the Pentaho framework so building with it should be familiar. But the stunning graphs come with a v3 which is not OS.

Here are links to the FusionCharts Blog, the Open source version, and the Pentaho plugin.

You can download the 0.02 version and its samples.

To install

  1. Extract the zip file into the /Pentaho/biserver-ce/pentaho-solutions/system.
  2. Extract the samples zip file into the /Pentaho/biserver-ce/pentaho-solutions/bi-developers.
  3. Change the file system/pentaho.xml to include xfusion on the acl-files list:
    <acl-files>...,xfusion</acl-files>
  4. Open the Pentaho User Console (PUC) and refresh the solution repository.

Here you can see installation, demo and usage in a Youtube video.

Ruby Step for PDI

Slawomir Chodnicki released on march 2011 the Ruby plugin step for Kettle 4 here.

There are examples included in the file you should download in the github page. Click on downloads and the select RubyPlugin_1.1_Kettle_4.2.zip. You already know thay it should be unziped on the plugins folder.

In a forum post some caracteristics are mentioned.

Excel Writer Output PDI Plugin

It is no longer a plugin as it is included in the 4.2 Kettle release, but post about its usage are still labeled as that 🙂

It let’s you set more options on formating and range.

Release notes and usability post.

BI iPhone plugin

Edit: The infomation below is no longer acurate:

  • Since 3.8 the BI server includes the iPad code, as stated by richad3 on the BI forum. But for 4.0 improvements were made on a week of fun.
    It seems the Enterprise plugin works great with the iPad, check the video. On the CE Edition the PAT/jPivot should work too, I’ll let you know what I find.
  • An alternate option for mobile devices is made on this OS project: PentaGoMo
  • The new site redesign has made the original code (the one that needed fixing) unavailable redirecting everything here.
    That’s bad if you still want to play with your Blackberry, Android or iPhone devices. I’ll let you know what I find.

The original information will remain here until new links and information is found:


The original article about a plugin for the iPhone is as old as this device, it was made available by the now vicepresident of engineering in Pentaho.

The BI PUC detects the browser and present a special menu. When you select an action (for report, dashboard, etc) a special program makes the parameter selection easier and presents one by one. Then the report is shown, you can see this video.

Unfortunatelly some corrections have to be made to the code to work with the new Pentaho BI version. Here are the download and correction instructions from Will Gorman on 2008 and additional ones on 2011 by Herwin Rayen. And a tech-tip so you can modify it further in the BI version 3.0.

There is also an Android app in the marketplace, here is the forum post that mentions it and the link to the 3.5 version.

In the Pentaho Blog an anouncement was made on summer 2011 about a iPad prototype. If you can’t wait “expand the plug in to cover iPad, just modify the Java source code, recompiled its class, then updated the JAR accordingly. This method can be used to expand the plug in to cover Windows Mobile, BlackBerry, and Android” – Paul Pambudi.

If you dont have all these devices you can check the emulators in Firefox’: User Agent Switcher add-on or testiphone.com web-based iPhone browser emulator.

BI BIRT Report ‘Plugin’

You can use the BIRT report view engine in the BI Pentaho User Console (PUC) which is newer and different than the plugin that is used in pentaho.

You have to download an eclipse runtime and extract a directory to your pentaho tomcat/webapss/pentaho folder and also download the samples to your pentaho-sulutions folder. Check the description, instructions and samples here.

Or you can use the plugin for PDI/Kettle to just run and burst your BIRT specifications. Check this link.

Data Cleaning [future] Plugin for PDI

Data validation needs coding in the actual PDI, but a nice open source utility for doing validation and correction exist and it seems it will become part of the PDI soon.

Data Cleaner can analyze, profile, transform and clean data on its own. But Matt Casters is working on a plugin so Kettle can use it. Here is the link that briefly shows its functionality and mentions the plugin here.

GeoSpatial Analisys on Kettle

On July 2011 version 2.0 of GeoKettle was announced by Spatialytics.com here. It is a step add in for Kettle 4.0 that allows “spatial analysis functions such as buffer calculations, overlays, metric operators, etc” from and to different file formats. It even reads sensors. Sounds like fun.

Weka Plugin

You can use the Knowledge Flow Plugin, that lets you use a weka predictive model as a step in a PDI transformation. Install an usage here.

Complete Pentaho Installation on Ubuntu, Part 11: C*Tools Dashboards

[Note: Install instructions posted on 2011 are no longer maintained by the developers. Please use the option ‘The Marketplace’ on the Pentaho User Console. The Dashboard building guide and reference links still works].

Install the Dashboard Editor

Webdetails is the contributor of C*Tools to the Pentaho Suite and is made of:

  • CDF: Community Dashboard Framework
    This HTML/javascript framework is part of the BI Server distribution. It let’s you create pages with reports, charts and tables.
  • CDA: Community Dashboard Access
    These are several components that give access to diferent types of datasources.
    However there is a security warning about the posibility of sql injection, so if you are not on a secure environment think twice about instaling this.
  • CDE: Community Dashboard Editor
    This is the web interface that lets you build a HTML/CSS layout and connect it to page elements, datasources and events so you can build your interactive Dashboards.

Manuals and training are commercial offerings of Pedro Alves Company.

Upgrade CDF, Install CDA y CDE.

We’ll need to update a couple of folders, and create a few more to install the editor, restart the server and learn to use the components of the web editor, so:

1. What You Need

Go to http://ci.analytical-labs.com/jenkins/view/Webdetails/ in the tab ‘webdetails’ and download the most recent versions of:

2. Upgrading and Installing

  1. Stop server
  2. Make a backup of your Pentaho folder
    at least a copy of Pentaho/biserver-ce/pentaho-solutions/
  3. Delete the following folders (some of them will not exist on first install):
    /biserver-ce/pentaho-solutions/system/pentaho-cdf
    /biserver-ce/pentaho-solutions/system/pentaho-cdf-dd
    /biserver-ce/pentaho-solutions/system/cda
    /biserver-ce/pentaho-solutions/cdf
    /biserver-ce/pentaho-solutions/cde_sample
    /biserver-ce/pentaho-solutions/bi-developers/cda
  4. Unzip CDF
    pentaho-cdf-TRUNK-SNAPSHOT.zip into
    /biserver-ce/pentaho-solutions/system/
    Unzip CDF samples
    pentaho-cdf-solution-TRUNK-SNAPSHOT.zip into
    /biserver-ce/pentaho-solutions/
  5. Unzip CDA
    cda-TRUNK-SNAPSHOT.zip into
    /biserver-ce/pentaho-solutions/system/
    Unzip CDA samples
    cda-samples-TRUNK-SNAPSHOT.zip into
    /biserver-ce/pentaho-solutions/
    (examples will end up in bi-developers folder)
  6. Unzip CDE
    pentaho-cdf-dd-TRUNK-SNAPSHOT.zip into
    /biserver-ce/pentaho-solutions/system/
    Unzip CDA samples
    pentaho-cdf-dd-solution-TRUNK-SNAPSHOT.zip into
    /biserver-ce/pentaho-solutions/
  7. Start your server

3. Alternate Road

If you like things the easy way, you can download a small shell script and run it from a terminal. It will check for the latest release, do all the upgrades and even install Saiku.

These are notes from Pedro Alves page on github, his blog post or get the shell script here.

Using the CDE

To start the CDE you can click on the toolbar or use Edit->New

CDE New Dashboard

CDE New Dashboard

You have three areas:

  1. Layout:
    You click on the row (=) icon to create a row, and click on the parallel lines (||) to insert a column, and then you can click on the (<>) icon to insert HTML text. To do that you have the properties and events on the right column. Note: If you give elements a name you can use it as an ID on your CSS/javascript code.
  2. Components:
    You can define parameters as your variables.
    You can specify in components (selects, charts, html tables, etc.) the parameter to hold values, or associate them with datasources to get data at startup, or tell them to which elements they need to listen for changes, which component ID will they get displayed on or what code to use on some events.
  3. Datasource:
    You can define all kind of queries to your data, depending on your datasource.
CDE Areas

CDE Areas

Additional tips:

  • There is no global error checking and with so many setting options it is better to ‘preview’ your changes often.
  • To test the datasource (CDA) part of your dashboard you can select the .cda file on the left bottom panel and right click to open it. Your parameters will be used to run a test to your data.
  • Save often also. If you don’t like your changes not all optios have undo capabilities.
  • To edit a dashboard use: Edit (you need to have the sources)

    CDE Edit

    CDE Edit

  • The componets have a link for advanced settings
    CDE JS Functions

    CDE JS Functions

    You can use javascript on clicks, pre or postprocessing, just name the functions:
    function f(){ param2=param1-1; }

Reading Material

  • Detail documentation: CTools-intro-v1.pdf.
  • A step by step CDE Dashboard creation post.
  • An excelent article on Dashboard creation and modification of the CCC-protovis components: creating-dashboards-with-cde.
  • Demos:
    – Five webdetails dashboards at pedroalves-bi.blogspot.com that can inspire you.
    – Stratebi has a demo also at sample.stratebi.es, register for free access.
    – Ambient shows its tips and work on a CDE implementation of a Steven Few sample of a CIO Dashboard.
  • [In Spanish] Dashboard creation: http://churriwifi.wordpress.com.
    And another post on how to use xaction, CDE, MDX and charts working in a dashboard, with code and videos, from startebi in dataprix.
    I suggest you use google translation as they are good articles and you can skip to the code.

Acknowledgement

Thanks to Catarina Félix this post was written, her email with the documentation draft and installer script made me curious and started me in the right direction to overcome the initial fear of the neon-nightclub-CDE interface. 🙂

Complete Pentaho Installation on Ubuntu, Part 10

Install Pentaho Report Designer (PRD)

If you have installed de BI Server, PDI (ETL), PDS (sequence of actions), modeling (Query metadata and OLAP workbench), you have all you need to provide access to your data and automation of extractions, even mail distribution, using tables or excel formated files.

Now with the report application you can create specially formated output presentations with areas, colors, tables, graphs. Using subreports you can mash them up even from different datasources: jdbc, metadata, ETL.

I prefer to publish the reports to the BI server but you can use the PRD (Pentaho Report Designer) as a stand-alone application, just like the PDI.

Installation

  1. Download a stable version from the Pentaho project in sourceforge.
    Or you can download the 3.8 release candidate [67MB].
  2. Extract its content in the /Pentaho folder so you end up with a:
    /Pentaho/report-designer/
  3. Make shure the *.sh files are excecutable.
  4. Edit the
    Pentaho/report-designer/configuration-template/simple-jndi/default.properties
    change the database strings to MySql like in
    Pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
    to use the JNDI connection options.
  5. Start the app with:
    $ ./report-designer.sh

In the startup dialog you can check the samples, the wizard, or start from zero. Now you have a standard reporting tool, where you define a datasource and then drag and drop fields into header, grouping or detail bands, then publish your reports.

Report-Designer Sample

Report-Designer Sample

Tutorial

Michael Tarallo from Pentaho has made an excelent tutorial about using the Pentaho Report Designer in a series of short videos. I think this is the place to start.

And a step by step guide in the Pentaho Wiki for creating your first report.

Example from the Beginning

Before I found the tutorial, these were my guidance notes.

  1. Click the New Button.
  2. In right panel, select the Data tab
  3. Right click on Data Set
    choose one of the options: JDBC (/JNDI), metadata, ETL (PDI), OLAP, XML or Table.

    Report DataSource

  4. If you choose Metadata:
    1. Browse to:
      Pentaho/biserver-ce/pentaho-solutions/steel-wheels/metadata.xmi
    2. In the dialog, type:
      SteelWheels for Domain
      Click on the Query plus sign and set a name for the query name
    3. And build a query by clicking on the pencil button.
      Like this:

      Report-Designer Medatdata

      Report-Designer Medatdata

    4. If you click ok, you’ll see all your queries and parameter datasources. Use preview to check your connection and data.

      report-designer Query

      report-designer Query

  5. If you choose JDBC:
    1. Select SampleData (our MySql datasource)
    2. Click on the plus button and define a query by selecting tables and fields, the joining fields are asumed when name matches.
    3. The designer is easy to learn or you can build your query on the MySql Browser and type it in the query field.
  6. After you close the datasource dialog you can drag columns to your report area and change the display properties on the Structure Tab.

Now

  1. Check the samples.
  2. Check this great post that uses sparklines: Report Parameter & Sparkline.
    and while you’re at it, read about Edward Tufte’s data visualization  history and articles.

Good luck!

[Edit] Resources

The PRD can substitute the .xaction files for initial parameter information. Then it can get data from OLAP, databases or ETL jobs and mash them into a page or file, which make it a very interesting tool, but each function has its details, so here are some of the articles about them:

  • Creating Parameters with Pentaho Report Designer: prashantaju.com
    Shows how to build a query, ask for a parameter, then modify the query to use it.
  • Several parameter tips on diethardsteiner.com: using it with queries, metadata, olap, single and multiple values, formulas and more.
  • PRD parameter type definition in pentaho.wiki
    Defines each type, shows the difference between ‘date’ and ‘date (sql)’.
    An llustration of each type and how appears in the PUC at prashantaju.com
  • Showing multivalue list and SQL query on prashantaju.com
  • How to ask for a SQL query parameter ‘* = all’ option: diethardsteiner.com
  • Calculate previous date with functions: bizcubed.com
  • Using ‘date picker’ and format date bizcubed.com
  • Cascade parameters (results of one parameter depends on the previus one): prashantraju.com
  • Overview of PRD 3.6 on five interesting videos.
  • Wiki aricles.
    Including this introductory guide.
  • PRD documentation.
  • Subreports:
    passing parameters and working with subreports on pentaho.wiki.
    Subreports, multiple reports, passing parameters on bizcubed.com.
  • Limit output type (PDF, HTML) parameters on sherito.org
    Set the default with attribute on setting options. Check the tip on the next section.
  • Tutorial and tips on PRD classic (that’s 3.5 and lower) at bizcubed.com, useful for maintenance. The tutorial for current version is also on bizcubed.com
  • Explanation on parameters and GWT fundamentals at sherito.org
  • Article on building interactive reports.
  • Note on How to call stored procedure/kettle files in Pentaho designer.
  • Pentaho Reporting 3.5 for Java Developers, Book Sample: booksonline.
  • Change row background color at guru4us.
  • Control page break in style sub section also at guru4us.
  • Using chart objet to display a value aginst results on prashantahu.
  • Link a report in html. In the example a top ten report lets you click on a single item.
  • Crosstabs.
  • An article about BIRT vs Jasper vs PRD.

Eight Tips

  1. Remember to use unique names for your parameters.
    If you use the same name as one of your data columns even on subreports, it will confuse the PRD and it will be sustituted by null.
  2. To ask for a date:
    • Add a parameter:
      Right click on right panel, option data, at the bottom, like
      – name: DATEGIVEN
      – type: Date (SQL)
      – format: MM/dd/yyyy
      – timezone: use server timezone
      this is important or it will add or substract additional hours
      – Some default on date like 31 of january: 01/31/2011
      – Mandatory
      – Display type: Date Picker
    • Add another parameter that will be the one in our query:
      – name: DATESTR
      – type: String
      – formula: =MESSAGE(“{0,date,yyyyMMdd}”;[DATEGIVEN])
      – Hidden
      – display type : None

      Parameter Date

      Parameter Date

    • You can use ${DATESTR} in a query or
      DATESTR in PDI ‘getsystem info’ step as argument #.
  3. To change the order in which the parameters appers in the bottom-right panel, right click on them and select bring them forward=up or back=down on the menu.
  4. To add a subreport you use the icon in the left toolbar at the bottom.
    Drag the icon from the left toolbar and you will be prompted to use it as an area or as a band (this one uses the horizontal area). Each one can use its own datasource, graphs, etc.
  5. If you use subreports, you need to define first the datasources on the master reports.
    When you open the subreport you need to define the parameters in the data panel in a confusing importing-exporting two list dialog. Use te same names and order than in the first list.
  6. Using the attributes panel, you can remove the autos-submit button parameter, or set the default output type, as seen on the picture.
    PRD attributes

    PRD Attributes

    To remove the auto-submit button permanently for all reports:
    Check the section “How to turn off auto-submit button in Pentaho?” in this wiki that says you need to edit the file:
    \Pentaho\biserver-ce\pentaho-solutions\system\reporting\plugin.xml
    And change:

    <id>RUN</id>
    <command>content/reporting/reportviewer/report.html?solution={solution}&amp;path={path}&amp;name={name}&amp;locale={locale}</command>
    </operation>

    Add “&amp;autoSubmit=false” like this:

    <id>RUN</id>
    <command>content/reporting/reportviewer/report.html?solution={solution}&amp;path={path}&amp;name={name}&amp;locale={locale}&amp;autoSubmit=false&amp;layout=flow</command>
    </operation>

    You can also add “&amp;layout=flow” to make all parameters appears ‘inline’, not each one in its own row. Or change the default option in the output type, but you need to restart the server to see the changes.

  7. You can use the same report and give different levels of summarization. You only need a parameter that gives the options and define in the report the hide functions in the detail or header/footer bands. [Bizcubed original article]
    • Define parameter SHOW-TOTALS.
      String value on a drop down table with Yes and no options.
    • In the report structure, in Details level (under Details Body), find in the top Format menu ‘conditional Hide’ and type:
      =IF([SHOW-TOTALS]=”NO”;”True”;”False”)
    • If there are more levels than detail and summary, you’ll need to use a more complex OR function:
      =IF(OR([SHOW-TOTALS]=”T”;[SHOW-TOTALS]=”D”);”False”;”True”)

    You can check the style tab on the bottom right panel, in the ‘size & position group’, you’ll see the function in the visible attribute, thirdh column=function

  8. Another function created by the Format Menu us the row-banding or alternate color banding. Where you can choose from some colors in a dialog.
    But as is explained on this forum thread, after using the menu you can go to the data tab on the top right panel and search for the function ‘row banding’, click on it and in the bottom panel choose the color you really want.

Complete Pentaho Installation on Ubuntu, Part 8

Install the Schema Editor (OLAP).

The modeling tool for creating star schemas a.k.a. dimensional models is the Workbench Editor, courtesy the mondrian project developers.

Videos shown by Pentaho with the 4.0 version using Agile BI, suggests that you will be able to avoid using this editor for starting and simple OLAP models. On them you start with a datasource, explore interactivelly to create a pivot table in the BI web interface and finally save the query and models. This is very powerfull as usually you discover valuable relations exploring and adding data to your models.

In the community edition we can do:

  • In the ‘good-old’ way  <g>. First build a basic OLAP model, load it into the server, test it, explore the information and add more al over again.
    The Editor helps a lot and its usability is fine. Also the XML file is easy to read with a normal editor -you should do it to learn more on how mondrian will join the tables-. The tool documentation is clear enough to start building models, the hard part is learning the MDX syntax and how you query your models.
  • Using the new model perspective in PDI [development environment].
    Select the model button (1), click the select datasource button (2) and your connection and table (I guess one view o table with all our measures and select fields), then OK and click on the auto-populate button (4), you can trim the model and save it [.xmi], then press the go button (5) to start playing with your [snapshot] data.

    Madel view in PDI

    Madel view in PDI

    I could not make it work from a transformation-step, right clicking and selecting model or visualize, that would be awesome. In this pdf it seems to work on the enterprise edition.

Installation

Steps to install the workbench editor:

  1. Download the latest stable version from the sourceforge project page.
  2. Unpack it into the /Pentaho folder.
  3. Copy MySql driver (mysql-connector-java-3.1.14-bin.jar or mysql-connector-java-5.0.7.jar) from the PDI (/Pentaho/data-integration/libext/JDBC) or BI server (/Pentaho/biserver-ce/tomcat/lib) folder into:
    /Pentaho/schema-workbench/drivers

Steps to use it:

  1. Check if you have set up a BI Server publishing password (view post 5).
  2. Make shure the set-pentaho-env.sh and workbench.sh files are executables in the schema-workbench folder (use the nautilus file browser or sudo chmod +x *.sh command). You can delete the .bat files.
  3. Start the app from the terminal:
    /Pentaho/schema-workbench$ ./workbench.sh
  4. Define the connection in the menu: options->connections.
    You should be familiar with this dialog by now. Use a native (JDBC) and the pentaho_user to the datamart database in the localhost server.
  5. I suggest you open the example from:
    /Pentaho/biserver-ce/pentaho-solutions/steel-wheels/analysis/steelwheels.mondrian.xml

    Schema editor

  6. When you need to update your model select File->Publish
    Fill the publish password information, as well as user and password (joe/password). The next dialog lets you select the folder and confirm the datasource (SampleData or Datamart in our examples). The next dialog confirms if the update went ok or not.
  7. Remember to reload the model in the BI Server cache, use your web browser and in the pentaho console: Tools->Update->Empty the Mondrian Schema Cache.

Interesting modeling information:

  1. Mondrian documentation at pentaho.
  2. Mondrian technical guide: here.
  3. Using OLAP: Jpivot and Mondrian: here.
  4. Mondrian Schema and JPivot presentation: here.
    Skip slides 11 to 34 that are about installation.
  5. Online demo of jpivot: here
    You can browse a demographic cube and get ideas on usability in your models.
  6. Videoblog in http://openbi.ning.com.
  7. A simple date dimension for Mondrian cubes: here.
    Explains the mondrian schema to work with dates.
  8. This demo show how to use MDX YTD function in Mondrian’s JPivot: video.
  9. How to do a MDX percent of totals in mondrian.
  10. How to do trend lines in mondrian: here.
  11. Using a mondrian cube in an .xaction file: here.
  12. Modeling information from Ralph Kimball: articles and papers.
  13. Webminar from informatica.
  14. Tutorial on MDX at database Journal [William Pearson]
  15. Articles in spanish:  in [DMQY]TD, Closing period , parallelperiod.
    In indonesian: prevmember (google translates it, but the code is self explanatory).
  16. Lots of articles on MDX/OLAP in the web refer to propietary tools. Use the ideas and queries, just avoid  their special functions. Check this blog for example.

Additional notes and definitions [from mondrian docs]:

  • member is a point within a dimension determined by a particular set of attribute values.
    That can be a gender hierarchy ‘M’ and ‘F’. Or a city ‘San Francisco’, ‘California’ and ‘USA’ in the store hierarchy.
    [Although you don’t believe it this definition has generateted over 300 coments on a linked-in thread].
  • hierarchy is a set of members organized into a structure for convenient analysis. For example, the store hierarchy consists of the store name, city, state, and nation.
  • level is a collection of members which have the same distance from the root of the hierarchy.
  • dimension is a collection of hierarchies which discriminate on the same fact table attribute (for example, the day that a sale occurred).

Some MDX code to keep at hand:

  • Hot to use time on two axes (use cero insted of row or columns):
    SELECT VisualTotals
    ({[Date].[Calendar].[Calendar Quarter].&[2001]&[3]
    ,[Date].[Calendar].[Month].&[2001]&[7]}) ON 0
    FROM [Adventure Works]
  • How to add a member:
    WITH
    MEMBER [Measures].[Last Period Unit Sales] AS
    ([Measures].[Unit Sales]
    ,[Time].PrevMember)
    MEMBER [Measures].[Unit Sales Growth] AS
    [Measures].[Unit Sales] – [Measures].[Last Period Unit Sales]
    SELECT
    {[Measures].[Unit Sales],[Measures].[Last Period Unit Sales],
    [Measures].[Unit Sales Growth]}
    ON COLUMNS,
    {[Time]} ON ROWS
    FROM [Sales]
  • Format strings and time hierarchies:
    WITH
    MEMBER [Measures].[Sales Previous Period Growth] AS
    ‘(([Measures].[Sales]) – ([Measures].[Sales], [Time].CurrentMember.PrevMember))/
    ([Measures].[Sales], [Time].CurrentMember.PrevMember)’, FORMAT_STRING=’0.00%’
    SELECT
    {[Measures].[Sales],[Measures].[Sales Previous Period Growth]}
    ON COLUMNS,
    [Time].Members ON ROWS
    FROM [SteelWheelsSales]
  • How to change color of a cell:
    Using the MDX documentation, you can check how to Format Strings, Order of sets, or use Function List.
    You can use a MDX to set green background on cell less than $100,000, or a red background if they are greater than $100,000:

    WITH MEMBER [Measures].[Profit] AS
       '([Measures].[Store Sales] - [Measures].[Store Cost])',
      FORMAT_STRING = Iif([Measures].[Profit] < 100000, '|#|style=green', '|#|style=red')
    SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,
      {[Product].CurrentMember.Children} ON ROWS
    FROM [Sales]

    Or in the model like:

    <CalculatedMemberProperty name="FORMAT_STRING" expression="Iif(Value < 0, '|($#,##0.00)|style=red', |$#,##0.00|style=green')"/>