Complete Pentaho Installation on Ubuntu, Part 12

The Pentaho Aggregation Designer (PAD) is a tool that comes from the Mondrian project. Its purpose is to generate SQL queries to create summarization tables, the instructions for data aggregation and the modification of the mondrian schema so the engine can use them.

The engine can check that precalculated data is available based on the names of fields used in keys and and report them speeding the display of results. The cost is that the cube construction will take considerably longer.

Install

  1. Download it from the mondrian sourceforge page (1.2.2-stable).
  2. Unzip it’s content to /pentaho/aggregation-designer
  3. Open a terminal, navigate to that folder and make the .sh files excecutables:
    $ chmod +x *.sh

To start using it

In that folder and type:

$ ./startaggregationdesigner.sh
  1. Click Configure and fill the parameters as we have done before: SampleData name, localhost, sampledata, pentaho_user, password. Click on test and ok.
  2. Click on Mondrian schema and locate steelwheels.mondrian.xml in /Pentaho/biserver-ce/pentaho-solutions/steel-wheels/analysis/, click Apply.
  3. Select Steelwheels model, and click connect.
    PAD will check for primary keys, nulls on foreing keys –which should not exist in a correctly done ETL process-. Click Ok.
  4. Now click advisor and then recommend in the dialog window.
  5. If you click on on the right bottom panel, the columns used will be displayed on the left bottom panel.
    PAD Screen

    PAD Screen

    You can select wich one to generate  as it shows a graph with cost to build (number of records) against benefit (time to get the result).

  6. Click export to open a dialog window:
    Click on Create Aggregates: export to Save it to a file like create.sql
    Click on Populate Aggregates: export to a file like populate.sql
    Click on Publish Mondrian Schema: export to a file like schema.xml
  7. Save your work to a file like PAD_workspace and exit
  8. Now you can check and modify the scripts. Maybe adding the TRUNCATE SQL instructions to the tables in the populate file.
    You can automate running them them with an .xaction sequence and a PDI job.

Reading Material

  • Julyan Hyde blog post about the 2.0 BI release –and PAD here
  • Mondrian documentation here
  • User Guide 1.0 PDF y 1.2 scribd
  • If you use Lucid DB: heres an article to use with a plugin LucidDbAggregateDesigner
  • In 2008, Chris Webb made an an accurate prediction about mondrian column databases (LucidDB) and PAD here
Advertisements

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. 🙂