Pedro Alves on Business Intelligence

CDA release - 12.01.26

2012-01-27T10:42:00.002Z

Aaaand, since there's been a lot of time without a release, CAD 12.01.26 is here.

Solved Redmine Bug #104: CDA Cache Manager -> UI didn't update after delete a query
Implemented Redmine Feature #105: CDA Cache Manager -> Delete all queries belonging to a cda file
Fix a bug where html output would duplicate output in some cases
Cache refactor; cache monitor: +removeAll, require admin permissions
cachemanager: user feedback for server requests
Support for cache plugin bean. Serialization changed
Added version info to cachemanager and SelfTest Page
Sorted out some images on SelfTest Page

Ctools installer with -b stable will get this version

CCC release - 12.01.25

2012-01-26T10:49:00.001Z

New CCC release 12.01.25 (standalone version, soon to be included in the next stable CDF release) and already available if you're using ctools installer with -b dev

Changelog:

Implemented Redmine Feature #107 - Control number of labels on the linear Axis for categorical charts (show "MinorTicks" option, including 2nd axis)
Implemented Redmine Feature #108 - Control number of ticks on the linear Axis for categorical charts ("DesiredTickCount", including 2nd axis with independent scale)
Implemented Redmine Feature #109 - Rounded maximum for linear axis in categorical charts ("DomainRoundMode" option, including 2nd axis with independent scale)
Solved Redmine Issue #78 - Fix the vertical order in which series are drawn, so that when applicable, they show from top to bottom.
Solved Redmine Issue #121 - Tooltips in barcharts do not appear if bars overflow.
Solved Redmine Issue #103 - Ordinal axis grids not being drawn
MultiValueTranslator: issue when no categories
Solved valueFormat receives numeric value, doesn't parse
Fixed typo of property name in LegendPanel
Add multi-series barline support
useCompositeAxis compatible with flat arrays
vml namespace conflict: revert sparkline, declaration in protovis-msie no longer lazy
align horizontal text in composite vertical axis towards the chart; revert convention breaks in multiline conditional expressions
workaround issue in 16th decimal position in IE9 64bit
Fixed regression with bulletcharts being translated in 10px down
Added new (and some of the missing) documentation to the testZZZ.html files
Fixed the drawing of bars and grid lines on the ordinal scale: they were not centered with the tick and label
In linear axis, made minorTicks "extend" (major)ticks, so that visibility (through .visible or .strokeStyle) of the later affects the former.
testZZZ.html files documentation mencioned '{x,y}AxisFullGrid_' instead of the correct value'{x,y}AxisGrid_'.
Fixed linear axis grid to show a line on the last tick (as opposed to the ordinal axis, that does not show the last line). When EndLine is active, it is drawn above the last grid line.
Fixed bug in the positioning of linear scale labels that revealed it self (don't know why) only on time series charts * Fixed bug in time series scale range calculation when with a second axis * Fixed bug in the drawing of minor ticks on time series scales (date arithmetic issues)
Fixed regression bug in ScatterCharts (DotChart, LineChart, StackedLineChart and StackedAreaChart) that caused null values to break line drawing.
Fixed the visibility of the first grid line of a time series axis - it did not show because, in this case, the first tick is not on the origin.
Fixed compatibility issue between jQuery.sparkline and protovis-msie when in IE8.
heatgrid: +scalingType:'discrete' (interval-based, no color interpolation)
tipsy w/ followMouse: don't fall out of window
Heatgrid: ignore null values in min/max calculations; nullShape not taking correct index into account;
solved dangling variable reference

Great stuff! :)

Firefox Telemetry - From adhoc R analysis to CDE Dashboards

2012-01-24T17:37:00.000Z

Time to put on my analyst hat. No matter what dashboard we're trying to build, it will all fail if the underlying analysis isn't good.

A dashboard is a great way to allow users to get information on a specific subject, considering we know what we're going to show. On this case I had absolutely no idea.

When we're sitting on a bunch of data, we need to go through a discovery phase where we'll actually decide what information can be valuable to the user.

Telemetry Analysis

Telemetry is a project from Mozilla that aims to make the products better - Firefox, Thunderbird and Fennec (codename for Firefox Mobile) by analyzing performance data sent by users while doing their real-world activity and the impact that developer changes had on that performance. The goal is simple - make better, happier, more productive.

As one can imagine, we have a bunch of data. All the submissions are primarily stored in HBase and later aggregated into ElasticSearch, allowing more versatile / real time analysis. We were then able to get a dynamic view over the data that summed up all the contributions from the users:

I previously blogged about the techniques that allow us to get data from/to elasticsearch, and once again it proved invaluable method. On this case, due to the huge amount of data, we had to use kettle's UDJC step, initially developed by Mozilla Metrics' chief engineer Daniel Einspanjer along with Jackson JSON processor to achieve high performance while processing the huge dataset of information and submitting some kettle improvements along the way.

New questions

This allowed developers to be able to view the impact of their changes and had the best effect a data tool can have while answering some questions - raise other questions.

Most of the following questions were related to time-based analysis, and be able to track, over time, the impact of the changes on a specific probe over a period of time. This would have the immediate effect of giving people data to decide if a specific release channel would be ready to pass to the next channel on the rapid release cycle and answer some of the new questions that the new process brings:

- Is Aurora ready to move to Beta?

- Are we getting the expected performance improvement in Nightly?

As a stretch goal, my personal objective was to implement any kind of system that allowed us to quickly identify regressions on the code without having to manually go through all the probes.

Back to basics - Kimball's DataWarehouse

This required a new approach on the data. Or rather, an old approach. In Business Intelligence, we live in exciting times where we have tons of available technologies that allow us to choose the best tool for the job (I recently did a blog post on the subject). But let's not forget 20 years of knowledge. This specific set of questions required building a standard, Kimball style data warehouse.

Telemetry Evolution

The goal is to have a way to track the improvements on the project's code by tracking, over time, the evolution of some key metrics we chose. Currently, the ones that are being tracked are:

Mean
Standard Dev.
Median
Percentiles (25,50,75)

Since telemetry data is stored in buckets on the client side, this values are not statistically accurate; they are not the mean and stddev of a particular probe, eg CYCLE_COLLECTOR, they are, instead, the mean of the bucketed values after submission. Same for all the others. However, if not an absolutely accurate representation of the end user's scenario, proved to be very effective in quantifying and measuring changes.

Concepts

We'll consider platform builds for a specific application, version and OS as having the same codebase. So our key is platformBuildID-appName-appVersion-OS, and we consider that to be our "primary key", and all submissions with similar keys are aggregated together and considered to be generated from the same code.

On a daily basis we'll query telemetry and we'll query for the builds made on the last 7 days (configurable value). In this there's the assumption that after 7 days is enough for sampling and changes to the main kpis after that period would be due to environmental changes and not due to code reasons. This number is currently being studied to find out the best value to use.

We're also discarding, for this datawarehouse, all submission with less than 500 counts, in order to have a good enough sample size.

R Analysis

Spent about two weeks building this datawarehouse. With no guarantees that the results would yield anything decent. So once I had a resultset I could work with, took the opportunity to use R to analyze the data. This has been, for ages, an item on my to-do list.

R is an insanely powerful statistical analysis tool with tons of packages that will guarantee that the bottleneck will be your own mathematical knowledge (or lack of), making it one of the analysts' favorite tool.

R does wonders when we have the data in a tabular format and want to do ad-hoc analysis, so I picked a resultset and started playing with the data. I used CYCLE_COLLECTOR probe evolution on windows platform and Nightly channel.

The first thing I did was trying to get a feeling of the shape of the data (this, obviously, after a couple of days trying to find my way around R). After a while, it was looking like this:

The initial analysis led to a relation between the submission counts and mean / std dev. The higher the count, the lower the mean and standard deviation. This is coherent with something the metrics team already knew - the initial submissions are not representative of the general population, so on this case size really matters.

Also tried for a while to find a statistical model to this data, mostly around fitting a normal distribution and thus trying to get more analysis from the parameters, like the CDF and other density functions. This proved to be a frustrating task, as no decent fit came from it.

Due to the all the distinct types of probes in the code, we decided only take in consideration means and standard deviation, and looking at the evolution on time. This is the view we decided to use:

In a single chart we could be able to tell the evolution of the CYCLE_COLLECTOR with point position represents the mean, size of the points represent standard deviation (not the accurate value, but according to the scale) and color coded representing the size of the sample.

From R to CDE Dashboard

The next step, after knowing the kind of analysis we need to give to developers, is to build a dashboard that allows users to get this data from the BI system automatically, up to date and with the ability to quickly parametrize it. And obviously skip the need for the consumers of the data to have knowledge on R.

All the Ctools were made having in mind the capability to be able to virtually build *anything*, and replicate a R analysis is a very good challenge. Here's the end result after.... 2 days

With all the live connections to the data users can freely play with the data and change the parameters to be able to quickly see the impacts of the code

Discovery

One of the biggest advantages of a datawarehouse is that it comes with an astonishing query language, MDX. In our case (and for anyone using pentaho as a BI server) we're using Mondrian as the Rolap engine that allows those queries to run.

MDX is very well suited for answering business questions, behaving particularly well on time-based analysis. So the next step was building a table that could compare the last 7 day average with the prior 28 days average. Big shifts would indicate either improvements or regressions. Here's the resulting table, ordered by default on regressions:

A regression of 1590% was immediately noticed. Clicking on that row allowed to inspect the actual histogram distribution:

I immediately checked with one of the firefox developers that mentioned that there was an error in that specific build that caused the counters of this probe to be totally skewed up. Success!

It's instantly rewarding to find out that the number of improvements absolutely outnumber the amount of regressions. One of my favorite ones that show all the improvements that developers have been putting in the code is IMAGE_DECODE_ON_DRAW_LATENCY

This is currently being used by the internal product developers to give them metrics over their code and the metrics team is working on allowing contributors outside the company to be able to take advantage of this tools.

Help Mozilla helping you

This is only possible to do with the help of users that are willing to submit their performance data back to mozilla. This is what we do with your data. There's absolutely nothing that can be traced back to you, as privacy is always the number one concern at mozilla. And here's how you can help:

Multiple parameters in CDF / CDE

2012-01-18T10:52:00.002Z

This tech tip shows how to configure multiple parameters to work in CDE / CDF.

We can use any query, but on this case we'll start with the parameter wizard, located in the datasources panel.

Create a new dashboard and go to the datasources panel. Under the "Wizards" select parameter wizard. Select a cube / dimension and drag it to the rows.

I selected the multiplebutton but any of the other multiple selection component would work too. The multiple button component supports both single selection (default) or multiple selection. We need to activate it in the component that was generated by the wizard

The generated code works, but we may want to define a set of defaults be preselected. In order to do that, we need to delete our simple parameter and add a custom parameter with the following code:

["[Markets].[EMEA]","[Markets].[NA]"]

This array has to match the id's we use in the parameter. That way, when we preview the dashboard we get those parameters selected by default as we wanted

Have fun!

-pedro

[RFC] CDC - Community Distributed Cache

2012-01-11T13:08:00.000Z

Time to discuss a new member for the CTools:

CDC - Community Distributed Cache

Objective

CDC is a new plugin to allow usage of a distributed cache engine implemented using Hazelcast in two distinct scenarios:

1. Mondrian Cache
2. CDA Cache

Features

Regarding the Mondrian Cache, the plugin should allow toggling between Hazelcast and the default Mondrian cache. This is achieved by changing the properties file for mondrian
As for the CDA cache, the plugin should also allow changing from Hazelcast to the existing cache provider and vice versa.
Plugin should provide an easy to use way to install an Hazelcast node in a standalone machine, enabling it to join the cache cluster.

Besides the backend functionality, the plugin must have an user interface (can be a cde dashboard, for instance) that allows:

1.Toggling between cache providers both for Mondrian and for CDA

2. Checking cache status (Running or not running / How many elements are cached)

3. Change cache strategy and parameters

4. Graphically view cluster composition (how many hazelcast nodes and where they are) and related information (percentage memory used, number of cached elements in each node - we need to check what is exposed by Hazelcast)

5. Cache cleaning operation.

6. View cached information - Ability to see cached key/values and ability to search by key (again, check what Hazelcast provides for this kind of operations).

A way to clean individual cache blocks in Mondrian is also needed. By this we mean that we should be able to easily clean the cache entries related to a given cube or query. This should also be supported by a gui that enables drilling down on the cubes metadata.
This funcionality can either be developed as part of the Community Distributed Cache or as a standalone plugin.

---------

This is what we have in plan - Any comments, features, requests?

Back to basics: Step by step Pentaho + Ctools installation

2011-12-15T00:41:00.000Z

Let's get back to the basics. This entry is a step by step tutorial on how to install http://www.pentaho.com/, the Ctools and Saiku.

Pentaho:

Download Pentaho Server from sourceforge. Choose zip or tar.gz according to preferences
Create a folder "pentahoServer" and decompress the downloaded package
inside it

Ctools and Saiku:

Download CTools installer
Run 'ctools-installer.sh. It will display the usage, but you'll probably want to do something like: ./ctools-installer.sh -s pentahoServer/biserver-ce/pentaho-solutions -w pentahoServer/biserver-ce/tomcat/webapps/pentaho
Choose the packages you want to install. Try them all for some kick ass software ;)

Start it:

Run 'start-pentaho.sh',under biserver-ce. Pentaho's up and running
Go to http://127.0.0.1:8080/pentaho (or whatever server you installed it in) and login with almighty joe/password
Have fun!

Note: Ctools-installer is a bash script that work on *nix/mac out of the box. This tutorial teaches you how to run it on windows through cygwin

Substance AND Style

2011-12-12T16:05:00.000Z

Just read the title of an interview from Quentin Gallivan titled BI visualization is just eye-candy.

No need to say I couldn't disagree more. After reading the actual interview I understand that the statement has to be read in the context of previous declarations by Christian Chabot, CEO and co-founder of Tableau Software when he said that visualization is key when presenting data to an end-user. But it's still a very very wrong headline.

While reading this two articles, there are some situations where I could almost directly quote Tableau or QlikTech's words. But I work only with Pentaho, so what's the catch? A few things, actually.

Self Service BI is a Myth

No matter what vendors try to tell you, this is a myth. For the simple reason that BI is a very vague word. You can definitely have "self service analysis" on a well defined datawarehouse, "self service reporting" on a predefined metadata model, even go a bit further ahead on a vertical market scenario but jumping to the generic terms is wrong. And this is pretty obvious to anyone that actually worked on implementing a real BI project.

You can't streamline good Visualization

Both Tableau and QlikView have great features in terms of usability. It's easy to work with, and you can do some interesting things with it. But on it's own it does nothing, and you can't just give users a bunch of dials, bullet charts, maps, 3d charts and call the result "good visualization".

It's all about the consumer

This is the million dollar question. Who's gonna use it? I divide BI "consumers" in two categories:

Analysts
End users (CEOs, CFOs, operational users, everyone else except the first bullet)

In my opinion and experience, most BI tools focus on the first bullet, and it's the second part that needs more love and attention. It's extremely hard to make sure users understand what they are seeing, and sometimes more options than one simple dropdown and one single table is too much. Because everything depends on the scenario and on the users that will consume the information. Those users never heard of Kimball, of dimensions, members, etc, and they don't have to. They need to know their business. Period. Analysts are pretty experienced users. In this cases visualization comes second to liberty to analyze the data.

It's all about the implementation

No tool does anything without a full blown implementation. This is what makes a BI project expensive. And in order to do a good implementation (substance and style) we need flexibility to do what the customer asks - and he'll ask for *a lot*. And this is why we're working with Pentaho over anything else. It's an amazing platform- great to see Quentin's not changing that strategy - on top of what we were able to develop the Ctools and, so far, we've been able to fulfill nearly all the requirements customers asked us. And in terms of final product and visualization, when you compare one implementation done with insert BI tool here, well.... see for yourself ;)

Open publication - Free publishing - More ctools

Introducing Add-Ins in CTools

2011-12-07T11:17:00.001Z

We just added Add-Ins support in CDF / CDE. This will have a great impact in both the development of the ctools but also in the usage

AddIns reference

AddIns are CDF's extension points. Can be used in any component to be able to fine-controlthat component and extend it in a very simple way
The use case for this concept is TableComponent's colType. Tables are a fundamental piece of dashboards / visualizations and there needs to be simple ways to extend the ways they are rendered

AddIn Implementation

In order to implement an AddIn, you need to create an object like the following:

  var sparkline = {
    name: "sparkline",
    label: "Sparkline",
    defaults: {
      type: 'line'
    },
    init: function(){

      // Register this for datatables sort
      var myself = this;
      $.fn.dataTableExt.oSort[this.name+'-asc'] = function(a,b){
        return myself.sort(a,b)
      };
      $.fn.dataTableExt.oSort[this.name+'-desc'] = function(a,b){
        return myself.sort(b,a)
      };
        
    },
    
    sort: function(a,b){
      return this.sumStrArray(a) - this.sumStrArray(b);
    },
    
    sumStrArray: function(arr){
      return arr.split(',').reduce(function(prev, curr, index, array){  
        console.log("Current " + curr +"; prev " +  prev); 
        return parseFloat(curr) + (typeof(prev)==='number'?prev:parseFloat(prev));
      });
    },
    implementation: function (tgt, st, opt) {
      var t = $(tgt);
      t.sparkline(st.value.split(/,/),opt);
      t.removeClass("sparkline");
    }
  };

Dashboards.registerAddIn("Table", "colType", new AddIn(sparkline));

name, label, defaults, init and implementation are the important bitshere. This specific code is an implementation of a sparkline using the jquery plugin.

Setting options

Options are passed on a per component basis, usually in the component's preExecution function.It can either be a static list of options that will be merged with the defaults or a functionwhere the options change according to the state

function f(){
 
    // Option 1 :Static list

    this.setAddInOptions("colType","sparkline",{barColor: "red"});
 
    // option 2: function
    this.setAddInOptions("colType","sparkline",function(state){
        // Let's turn the second sparkline into a bar
        if(state.colIdx == "2"){
            return { type:'bar'};
        }
    });
}

Setting defaults

It's also possible to ser a site-wide / dashboard wide defaults, and like the previousoption, can either be a static list or a function

Dashboards.setAddInDefaults("Table","colType","sparkline",{fillColor:"#aaa"});

Dashboards.setAddInDefaults("Table","colType","sparkline",function(state){ 
    return state.rowIdx%2?{fillColor:"#aaa"}:{fillColor:"#fff"};
});

Implementation arguments

implementation function has the following arguments:

    implementation: function (tgt, st, opt) {}

tgt: target - Target for the action. eg: On a cellType, it will be the td cell
state: state - Information about the specific addin call. On a cellType will be an object with: {rawData, tableData, colIdx, rowIdx, series, category, value, colFormat}
opt: options passed to this addIn

Calling AddIns from components

When developing a component, it's very easy to define a new AddIn type. Here's the examplethat TableComponent uses:

    var addIn = myself.getAddIn("colType",colType);
    addIn.call(td,state,myself.getAddInOptions("colType",addIn.getName()));

From this point on, there will be a new colType available to register.

Implemented AddIns:

sparkline
pvSparkline
dataBar
trendArrow
hyperlink

Here's some screenshots for the implemented addins. More details under cde_samples in the solution repository after installing ctools:

All credits to Brandon Jackson for sponsoring this huge develpment! /bow!

CDA v11.11.23 available

2011-11-29T00:16:00.001Z

Huge milestone. As promised, CDA v11.11.23 was released. You can download it from http://cda.webdetails.org/. The first of many, features an easy to use installer that will work on any operating system.

Works with any version of pentaho, form 3.6 to 4.1.

On a related note, the ctools-installer script was also updated. Version 1.9 now adds the support for a flag (-b) that allows selecting the branch you want to install, either dev or stable. Currently, choosing stable will only affect CDA, where this latest version will be installed, but as new versions of the other plugins are released you can switch from bleeding edge to a more stable / tested version.

Have fun, this is the first of the rapid release cycle of the CTools!

CTools - Rapid release cycle

2011-11-21T19:04:00.001Z

The Ctools are great. There's no doubt about it. But our release handling has been... hum... "lacking", to be very polite about it.

It's absolutely unacceptable to have the latest released version of CDE to be 1.0RC3 for over 6 months now, CDA on 1.0 and having everyone relying on CI builds and tools like the ctools-installer running trunk. We know that, and I apologize.

Webdetails is a services company, so all the work we do into productizing ctools mostly comes out of our own sweat and blood, thus ending in the situation where we are now. But we're way past the time where this is manageable and all the tools grew into such proportion, both in quality and user base, that we need to drastically change this.

We're now moving into a rapid release cycle. Our goal is to start with releases every 3 weeks and adjust that with time if necessary.

And we'll start one step at a time. Scheduled to this Wednesday is a stable release of CDA. As the release cycle moves on we'll add the other plugins.

We'll also change the way we handle version numbering. Since they're mostly useless anyway (and our marketing team said they didn't particularly care about them ;) ) we'll now move to a different style. Let's just say the next version will be CDA v11.11.23 . And I'll let you guess the pattern ;)

Cheers

-pedro

CTools moved to GitHub

2011-11-18T06:03:00.001Z

We're cleaning out the place. All the Ctools can now be found at github, webdetails organization.

Some moved from googlecode (CDF, CDA) while most of them moved from different owners, but if you got an error pulling from the source, don't worry, we didn't run away, just moved to a new home

CTools Training: Brasil

2011-11-07T11:14:00.003Z

There's no typo in the title. Brazil, in Portuguese, is spelled 'Brasil'. And December's training session will be in Brasília, Brasil, 13-15 de Dezembro (which means 13-15 December, but if you didn't figure that yet it won't probably matter, as this one will be in Portuguese).

You can check the training headlines here

This will be organized by our friends at it4biz, and for more informations check Caio Souza's blog entry

Até breve

CTools training: Australia

2011-10-20T01:45:00.002+01:00

Next in the Ctools world tour: Sydney, Australia, 16-18 November

Our friends at Bizcubed invited us for some amazing steaks, and we're obviously going. In exchange, we just need to give a Ctools training!

Register here and C you there!

Multidimensional support in CCC

2011-10-14T02:58:00.000+01:00

It's time to improve CCC!

Currently, CCC data engine uses a resultset composed of an array of series, one array of categories and an array of values. Internally, in the DataTranslator class we store one array where the first line has the series, the first column has the categories and the content is the values:

One of the biggest advantages of CCC is the way it automagically integrates with the other Ctools . A user defines his datasource, connects it to a chart, and voilá, he has a chart. In order to achieve that, we had to implement specific translators that can map the specific datasources to this inner structure. We need 2: a relational translator, for sql queries, and a crosstabl translator, for mdx-style format.

This is how the data from a relational datasource is mapped to the internal CCC structure, when we do a query like:

select series, category, sum(value) from foo group by series, category

This directly applies to SQL queries. They're all like this. The other distinct format of inputting data is through a CrosstabTranslator. This is mostly used with mondrian queries or other pivot-like structures. Example query:

select {[Series].children} on COLUMNS, {[Categories].children} on ROWS where [Measures].[Values]

This will be mapped to our internal structure almost directly:

This is what we currently support. It's enough for almost all the charts we support in CCC. Bar, line, pie, etc. For other, this isn't exactly applicable but we found a way to reuse this structure (eg: bullet charts)

But this is not enough for more complex visualization. Imagine you want to have a heatgrid but instead of having only different cell colors you also want different box sizes. Or imagine that instead of having only one category (eg: countries) we have 2, countries and years.

The following is our "RFI" to fix this: We'll implement a multiVal mode that will maintain the same tabular approach to the internal CCC structure but instead of supporting single values, on each cell an array of values will be supported

The regular cases will contain arrays of dimention one, and backward compatibility will be ensured by having the old values array to be a flatten representation of this new structure but holding only one value - the last index for the categories and series and the first for the values.

How does this work on a real scenario? Our sql query will be a bit different:

select s1, .., sN, c1.. cM, sum(v1) .. sum(vK) from X group by s1.. sN, c1.. cM

This is the mapping that will be done:

On the crosstab translator, the query will be different too, a very standard operation in analyzer:

select {C1} * {C2} * {M} on COLUMNS, {S1} * {S2} on ROWS where [Measures].[Values]

(we have to decide where we want the measures to appear. Either we stick them on the rows or allow them to be on the cols too, really haven't thought about that long enough)

Once this support is implemented in the backend, 50% of the problem is fixed: The data is there, available to use.

Now new charts can (and should) be implemented using the new multiVar mode when it makes sense. Having 1 series / category / value ends up being only a particular case of the generic support.

New methods will have to be implemented on the DataEngine too. We now have getSeries() and getCategories() (and all it's variants) , that were enough till now. We'll need to new methods here: getMultiSeries() and getMultiCategories(), both returning arrays, and specific methods for when passing a levelIndex, eg: getMultiSeriesForLevel(levelIdx)

Feedback is welcomed. This will impact the way scales and labels are generated, along with other things that I'm not yet able to foresee, but one thing at a time

This should really extend CCC's ability to generate new visualizations never losing one of the main goals - it has to be dead easy (and fast) to integrate with our data.

Pentaho Ctools versioning with GIT

2011-10-10T13:11:00.008+01:00

We have a few pentaho projects where we are involved all the way from data to UI. Mozilla is one of those cases. Controlling all aspects of the projects makes it easier to manage different version.

But we also have a lot of projects where we work in collaboration with our customers, us developing all the UX and dashboard layer and them converting the data access layer (the CDA part). On this cases, it gets a bit more complicated to manage the changes we do on the dummy data scenario with the real data implementation.

Cees van Kemenade, from Vinzi, is working on a tool to tightly integrate version management and some other amazing features that will put this issue to history, but in the meanwhile we'll try to solve this using a standard tool: GIT

The idea is to create a GIT repository for the project. While we work on the master branch, all client's commits will go to the "real" branch. From that point on, we'll keep working on the master and the client works on the real, merging all the subsequent versions from that point on. Any specific bugs the client fixes, we can cherry-pick back to the master branch.

While this is nothing groundbreaking, can really save days / weeks of development just by using technologies that we already use on other contexts.

Dashboardland - new Ctools sample

2011-09-20T22:48:00.009+01:00

This is the result of the recent Orlando training. It's one of the most advanced dashboards we built for training sessions, tons of customizations from tables, selectors, nested parameters, etc, in a very serious tone *not*

I particularly don't trust this survey. But I'm a Goofy fan!

Once again, all dashboards done using the Ctools and pentaho. Check the sample here

See you in a few days in Rome for the Pentaho Community Meeting

Kimball is getting old

2011-08-26T22:23:00.002+01:00

For a long time, BI almost appeared to stay immune to changes. Applications got better, breakthroughs here and there, but roughly same players, same strategy, same approach.

2 visionaries were on the foundation of what I call now "Classical BI" - Kimball and Inmon. They taught us what a datawarehouse is. And damn, they were right about it.

I've been working with BI for a few years now. A couple of years back, I'd probably have written that I was working with "opensource BI". It was, by itself, a differentiating factor. It still makes a difference to me: means I'll be able to hack around it, I won't be trapped in a code I can't access to, and will ultimately allow me to do my job better.

But I don't say that anymore. It's irrelevant. Fortunately the market is starting to realize that the fact that opensource isn't a benefit by itself. It will have a more affordable entry point but in the end the only thing that matters is if what's implemented allow users to make correct decisions based on the information taken from the BI system.

My passion is the visualization layer of BI. Usually known as dashboards, I think of them as much more than that, as the main interface between users and *their* data, and more than numbers they need to transmit an emotion, create a bond. It's not enough if the numbers are right, there has to be more than that. The user must feel comfortable using it, has to enjoy it. It's all about User eXperience.

The immediate conclusion is that users couldn't care less about where the data comes from. We don't need datawarehouses anymore. We need *whatever* allows us to provide that rich experience. And sometimes the classic datawarehouses are not the best approach.

Exciting times for IT. Lot of buzzwords. Lots of new technology. New ways of accessing information. All the hype about SQL vs noSQL and the bigdata movement. For me there's one clear winner: All of us. We now have more tools available to choose the right one for the job.

Don't just settle for getting the numbers right. Also try to get a smile from your user.

5 days, 5 dashboards, 5 websites - 5/5

2011-08-02T10:28:00.005+01:00

Day 5, mission accomplished!

Dashboard 5: Product Demo

This is an older proof of concept that we developed a while back. It's not feature complete - you'll notice only the last selector works and it doesn't render properly in older IEs - but it's by far one of the most advanced ones in terms of showcasing all the customization capabilities of the Ctools. And, by the way, we got the project :)

Try it here

Site 5: cst.webdetails.org

CST, Community Startup Tabs, is a great example how community projects should be created. St. Antonius Ziekenhuis requested us to develop the ability to define and customize the tabs that are visible to users that log in to Pentaho User Console (PUC) and in the end allowed to release that to the community. Bravo!

And that's it for now. In time, the list of samples will grow, but it's a great start in my opinion and should give everyone a better notion of what can be achieved using the Ctools.

Have fun! (quoting Thomas)

5 days, 5 dashboards, 5 websites - 4/5

2011-08-01T10:35:00.004+01:00

Day 4. You really didn't expect me to work on a weekend, right?

Dashboard 4: CDE Demo

This one is probably familiar if you installed CDE before, as it's included in the main distribution. A very simple example of what can be done in about one hour with the tool. This is not a standard Dashboard, with lots of KPI's, but more of a documentation-oriented approach, which can be very useful. Even though CDE can hardly be placed on the same league as a wiki or any of it's relatives, the truth is that since it's remotely accessible to edition it can definitely be used like that.

Try it here

Site 4: cde.webdetails.org

The crown jewel, CDE was initially started (like most of the others) for the Mozilla project. Starting as a proof of concept, quickly proved to be a boost of productivity. With almost 2 years since it's initial version, is now a mature project with tons of optimizations for both editing and rendering dashboards.

5 days, 5 dashboards, 5 websites - 3/5

2011-07-29T12:48:00.004+01:00

Day 3. Let's get geek!

Dashboard 3: Star Wars!

The first time I showed it to the team internally most of them said "There's no such Jedi!" in a very offended way! Bottom line, we did it cause we wanted to! It really shows that BI doesn't always have to be extremely serious and still pass data effectively and with a smile. Try it here

Site 3: cbf.webdetails.org

This was our first project, born over 4 years ago. And it's still a everyday lifesaver, a must have for any collaborative environment. Like it's siblings it also has a new home. This project is also in the base of the Guided CBF project by Analytical Labs

5 days, 5 dashboards, 5 websites - 2/5

2011-07-28T13:17:00.004+01:00

Day 2. Still looking good for the final goal

Dashboard 2: Sync Demo

This was made by students of the Ctools training course in Holland. Try it here

Site 2: cda.webdetails.org

An amazing and very mature project that hardly needs introduction - but definitely needed a home. CDA is The Way to extract data from Pentaho and can be used standalone. Tons of features.

5 days, 5 dashboards, 5 websites - 1/5

2011-07-27T18:48:00.006+01:00

Challenge: in 5 days, introduce 5 demo dashboards made with the CTools and introduce 5 websites for the projects.

Lets start!

Dashboard 1: Greatest Driver of all times

This was made by the students of the CTools course in São Paulo. Try it here

Site 1: ccc.webdetails.org

CCC deserves a proper home. It's a great project that's been in production for a while. And now it has a home! Not only you can check a showcase of the supported charts but also test the changes.

Have fun, more on the following days!

Accented (international) characters in pentaho user console

2011-07-22T20:50:00.007+01:00

The biggest disadvantage of a software made in USA is that they sometimes forget is that not everyone speaks English (and I'm the first one to admit that life would be simpler if we all did).

And most of the folks that *don't* sometimes need accented characters. And there's a few issues with PUC when trying, for instance, to create directories with accented characters (see picture)

There are 2 things you need to do to fix. The basic idea is: always work with utf-8 everywere: file encodings, database connections, browser encoding. It will save years of your life.

Modify your start-pentaho.bat/start-pentaho.sh to add the option -Dfile.encoding=utf-8
Modify webapps/pentaho/mantle/Mantle.jsp to add the following snippet:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

Restart your server. It will work.

ps1: this was a request of one of the Ctools student in Brazil. Come meet us in one of the others to get your deepest questions answered

ps2: Thanks for helping me out here, Nick B. ;)

Elasticsearch, Kettle and the CTools

2011-07-12T14:37:00.011+01:00

I'm not much into the sql vs nosql discussion. I have enough years of BI to know that the important thing is to choose the right tool for the job. And that requires a lot of tools!

Here's one more for our set: ElasticSearch. ElasticSearch is an Open Source (Apache 2), Distributed, RESTful, Search Engine built on top of Lucene

It may not be obvious, but there are tons of reasons why a search engine is a great choice as a BI data source - and far beyond the simple free-form text search.

Due to the intrinsic nature of nosql and it's schema-less approach, we can store virtually anything. Due to the clustering abilities of elasticsearch scalability is not even an issue. Using the query syntax we have a powerful way to get the data out. And it's blazing fast!

We initially used ElasticSearch for the twitter dashboard at Mozilla, described in a previous blog post. Everyone was very happy with the results, and we're betting quite a lot in elasticsearch at Mountain View.

So we made an effort to put elasticsearch closer to the Ctools and Pentaho. The first thing we did was to add an ElasticSearch Bulk Loader to Kettle.

In kettle 4.2 you'll be able to find this new step; Here's a sample of a transformation using it. As simple as it gets:

There are a few things I'd like to highlight:

It's simple - in 5 minutes you can get an elasticsearch engine with data on it
It's fast - 20krps on this sample docs (200k docs indexed in 9 seconds, for 60 Mbs of storage)
It's versatile - we can either index fields or full json documents

From this point on we can just query for documents in elasticsearch:

Now, what to do with this? What's really interesting is to be able to use this from with CDA. Doing that not only we'll be able to use ElasticSearch as a datasource to dashboards but also to reports. Using kettle to do the bridge between ES and our frontend tools guarantees a great degree of isolation and security. Here's a sample transformation:

Now we can tie this to CDA, and then use it with CDE for our dashboards. Here's the result:

Note: in order to run this from pentaho bi, both jsonpath.jar and json_simple.jar have to be added to the lib dir of the application server

With all this we can quickly build any dashboard that uses all this resources. As a very rough demonstration I built this one:

Have fun

Pentaho Data Integration 4 Cookbook available

2011-07-07T10:48:00.007+01:00

If you're like me, you're lazy. And if you're lazy, you're probably from the opinion that the best way to learn is by example.

Pentaho Data Integration 4 Cookbook is exactly that - a collection of over 70 (I counted 79, I think even María Carina Roldan and Adrián Sergio Pulvirenti, the authors of the book, stopped counting them at some time) real world examples of how to use kettle to do... well, almost everything. It even has a recipe for integrating kettle in a CDE dashboard \o/

This book is very "usage" oriented. You won't find details about using it's api's, or integrate kettle in a 3rd party java program. You'll find recipes for what 95% of the kettle users do: implement jobs and transformations.

This is the list of chapters, will give a good idea of the contents of the book:

Chapter 1: Working with Databases
Chapter 2: Reading and Writing Files
Chapter 3: Manipulating XML Structures
Chapter 4: File Management
Chapter 5: Looking for Data
Chapter 6: Understanding Data Flows
Chapter 7: Executing and Reusing Jobs and Transformations
Chapter 8: Integrating Kettle and the Pentaho Suite
Chapter 9: Getting the Most Out of Kettle

The best thing about this? It's the 5th pentaho - related book:

And this is great for the open source community! Keep them coming! What 's next? A ctools book? ;)

CCC charts in CDF

2011-06-24T11:15:00.002+01:00

Stealing an email Pedro Pinheiro sent to a customer, since this is useful information to anyone that wants to go hands down on this

I'm afraid we don't explicitly support CCC in CDF as of right now. It is possible, but you'll have to hack it together yourself.

Disclaimer: The following instructions will get you on your way to getting CCC in CDF, but this is a hack, and I cannot guarantee that they'll work. Don't complain if it deletes your data, kills your dog or haunts your dreams every night.

First you'll need the CDE bundle 1.0RC3 package from www.webdetails.pt
Inside, find system/pentaho-cdf-dd/resources/custom/CCC
Copy the contents to some location where you already have some javascript files you're importing into your dashboards.
In your dashboards, include all the js files (that's svg.js jquery.tipsy.js, protovis.js, tipsy.js, pvc-d1.0.js, protovis-implementation.js and ccc-implementation.js) as you normally would.
In the <script> tag for svg.js, make sure to add a data-path attribute that points towards the folder where you copied the whole thing. You can find an example of this for reference in component.xml, lines 39-41.
You can add a CCC component to your dashboard now. There is no actual component reference available, so you'll have to use component.xml as reference for what components are available and what parameters they take.

Regards,

CTools training in the US

2011-06-24T10:43:00.003+01:00

After Netherlands and Brazil, US!

San Francisco, 17th August - Register here

Orlando, 22nd August - Register here

C you there!

CTools installer - Making things fast

2011-06-21T12:13:00.004+01:00

Jos Van Dongen kept insisting that there should be an easier way to install the CTools. We work on a very fast pace, and the words "upgrade CDF|CDE|CDA|CCC" happen far to frequently.

And he's right. So I built a script that allows to install and upgrade all the plugins. Without further delay, you can get it here:

https://github.com/pmalves/ctools-installer

ctools-installer.sh is a shell script to install / update the different ctools into a pentaho installation. Currently supports CDF, CDA and CDE and pentaho 3.6 to 3.8

Requirements: Linux, macintosh or windows with cygwin, wget, unzip, diff (if you're administrating any server and don't have this, you don't deserve ctools anyway ;) )

Usage: ctools-installer.sh

Please backup your solution, we're not resposible by any harm this does to your server. Also note that if you have a changes in the plugin directories (eg: cdf templates) you'll need to back them up.

Thanks Analytical Labs for hosting the continuous integration

UPDATE:

Yogaraj Khanal

made a blog post with detailed instructions of how to use the installer in windows. Check it here, and thank you!

CDE + Ace Editor

2011-06-08T01:42:00.008+01:00

There's a lot of JavaScript and CSS involved in CDE dashboard creation. Who uses it will certainly be familiar with the text areas to edit embedded code.

However, sometimes it's useful for a set of dashboards to share a common logic or presentation layer. Maintaining a per-dashboard CSS stylesheet or JavaScript code is not a good approach.

What we usually do is create a common.js and common.css files and include them in the dashboard. While this works very nicely, it has one obvious drawback - we can't edit them through CDE, losing the ability to do remote maintenance / development.

Well, not any more!

We recently integrated Ace editor into CDE. Ace is a merge of 2 projects: Ace and Mozilla's Bespin (which was used as the CDA editor), lately known as Skywriter. You can take it for a quick test drive here.

It has a lot of interesting features:

Syntax highlighting
Auto indentation and outdent
Work with huge documents (100,000 lines and more are no problem)
Fully customizable key bindings including VI and Emacs modes
Themes (TextMate themes can be imported)
Search and replace with regular expressions
Highlight matching parentheses
Toggle between soft tabs and real tabs
Displays hidden characters
Highlight selected word

In the context of CDE, we now have the ability to create and/or edit CSS and JavaScript files in the solution repository, which is a major plus. As usual, easier to show than to describe:

Adding a resource:

Choosing a folder:

Creating a new file:

Editing the code:

Have fun!

CTools training in Brazil!

2011-06-01T01:40:00.005+01:00

Este post vai ser em português para anunciar mais uma ronda de treinamentos nas Ctools - CDE, CDF, CDA, CCC, CST, desta vez no Brasil, São Paulo, 20 a 22 de Julho. Organizado em conjunto com Caio Souza, inscrições limitadas. Para mais informações contactar info@it4biz.com.br

Até breve!

CDA - Export to HTML

2011-05-18T14:06:00.004+01:00

Our partners at Stonegate Senior Care sponsored a great addition to CDA.

I admit it - I now call it a great addition, but initially was very skeptical about it. I mean - when we have csv, json, xml and xsl output, why on earth would we need html output??

For the most stupid reason you can imagine: Excel requires it!

Turns out that the only way for you to link to a external datasource in excel is to point to a html table. This means that now you'll be able to have your spreadsheet files with updated data - with no other plugins :)

Check it out in action:

and

I'm sure this will be of great help to those that want to take advantage for their BI system but still be able to hack their own home-grown analysis.

See you soon!

CDF / C** Training Session - Do you have what it takes?

2011-04-21T21:19:00.003+01:00

Well, after this training session, you will! Learn how to work with all the components of the C** tools and make great looking dashboards.

So, see you 9th of May in Nieuwegein, Netherlands.

Mozilla Community Metrics - Technical challenges

2011-04-18T12:13:00.003+01:00

One of the goals for the Mozilla metrics team for ages has been to provide open data to the community. It never happened so far mainly due to lack of resources and different prioritization.

However, that will change! During last quarter we worked on a tool to give users better insight on their contributions to the Mozilla project. David Eaves did a great blog post describing the entire motivations behind the project

Technically speaking, this was very challenging and insanely rewarding. We had to build something that expressed the data we wanted to share in a very clear and useful way - and still look good on the process. And I think we hit both of them! :)

Here's a screencast, courtesy of Daniel Einspanjer:

All this was done using our regular stuff: CDF, CDE, CCC and CDA over Pentaho. The challenge now is allowing the community to use this tool! I'm obviously scared sh*t about it - afaik, no one every opened a pentaho server - literally - to the world. But hey - if someone has to be the first, I want our team to do it!

All the plugins I named previously were built having this longer term goal in mind: CDF / CDE optimize all dependencies (automatic minification / concatenation of scripts, aggressive client side caching) and CDA does a great task of caching the resultsets server side, regardless of datasource.

The Mozilla security team will now do an extensive audit on the system, making sure it's ready for exposure. Hopefully, my next blog post on this will not contain screenshots and screencasts, but the link for the dashboard itself :)

CDA datasources in PRD

2011-03-30T12:50:00.005+01:00

One word - FINALLY! I've been waiting for this for ages. Thomas Morgner blogged about this a while back, but it was all lies :)

Until now, and here's the proof:

This may look like a cyclic dependency... using a CDA datasource in PRD, while CDA itself uses PRD libs. Believe me, it's not, and it's incredibly handy to have a "dictionary of querys" on the server and just query them from your report. Caching comes for free, it's all executed in the server, it'll save hours of pain waiting for the hard queries to get executed. I know this is something that PRD 3.8 fixed, but having the cache on server is a huge bonus

In order to use this, just drop this jars in PRD-3.8 lib dir and it'll just work!

Have fun - I know we will!

Firefox 4, Twitter and NoSQL Elasticsearch

2011-03-22T17:13:00.007Z

Here's the main message of this blog post: Firefox 4 was released, get it now. It's an amazing piece of work, done by, and for the community.

As Mozilla Metrics team members we prepared a dashboard to monitor this release using several sources of information, and one of them is twitter, basically, "what are people saying about us?"

This obviously requires a different approach compared to normal BI; We need almost realtime data, a blazing-fast response and search capabilities. Perfect opportunity to a NoSQL approach.

We already had a very good experience using solr, an enterprise search platform that runs on top of Lucene. This time we decided to use ElasticSearch, also an Open Source (Apache 2), Distributed, RESTful, Search Engine built on top of Lucene. While the principle is very much the same as Solr, the fact that ES thinks in JSON for both query DSL and indexing documents add more BI-like features to it.

The result? Nothing less than astonishing! Using regular CDA to connect to elasticsearch, we were able to do our CDE dashboard development using the regular techniques. NoSQL datasource? SQL datasource? Excel? Don't care - it's all data.

Here's the screenshot as of... well, now (5pm, Europe/Lisbon time):

Important things to note:

At 5pm, firefox 4 noise is bigger than IE9 related noise for it's release date
User feedback is great!

Here's a quick screencast of it working, courtesy of Daniel Einspanjer, master of Metrics. Amazing stuff, thanks Daniel:

CCC Community Contributions: Heatmap

2011-03-17T10:02:00.003Z

The best part about community projects is when you get adoption to the point where you start getting very valid community contributions. And that's happening with CCC, the best / most powerful / most extensible charting engine available (and not only for pentaho). All in all, god's gift to visualization *cof*

Cees van Kemenade, from Vinzi, added heatmaps and fixed y axis with overlays. It's been merged to the main code and we're now working on giving those features from within CDE.

Surely the first from many contributions!

Sexy pentaho dashboards? Showbiz!

2011-03-07T12:15:00.008Z

For ages we've been working on allowing us (and everyone else, by using an opensource model) the ability to develop Pentaho dashboards faster and better.

That was the motivation behind CDF, CDE, CCC, CDA and all the other Cs. We are now able to present a service where our goal is to be the "developing hand" of dashboards to the pentaho user base.

We present: ShowBiz

In a nutshell, here's how it works:

You tell us what you think you want
We deliver what you didn't even dream was possible :)

Protovis component in CDE

2011-02-22T10:13:00.006Z

Due to popular demand in pentaho forums, here's some guidelines on how to use the protovis component in CDE.

Even though CCC defines the charts that we're interested in using 90% of the time, the other 10% are fundamental. Even if it's "just because we can".

The protovis component in CDE allows the dashboard developer to take full use of protovis language but goes a bit further than that; Does all the necessary link with the dashboard layout, does the necessary initialization so that it runs in IE and even connects with the data.

I'll show 2 samples: in one of them we'll supply an object to be used as the data, and in the second one we'll use the result of a query to build a chart.

Example 1: Bubble Charts

Create a new dashboard in CDE, and save it in some location (always start by doing this). Then in the layout panel, add one row and 2 columns with span 12, thus totaling 24, the full extent of the layout. Name the 2 columns as chart1 and chart2 respectively.

Add a custom component called "flare" and define the contents as what's in this flare.js file. This will be the data we'll use in the sample.

Now add a protovis component, call it anything you like (I called it protovisComp1). I used 400 for both width and height. In the htmlObject chose the first column.

Now all that's left is the definition of the function. It has the form:


function(vis,data){
// vis - the container where protovis objects will be drawn
// data - result of the query, if applicable
}

For this sample, I'll use the code in the bubble chart sample


function(vis,data){

/* Produce a flat hierarchy of the Flare classes. */
var classes = pv.nodes(pv.flatten(flare).leaf(Number).array());
classes.slice(1).forEach(function(d) {
d.nodeName = "flare." + d.nodeValue.keys.join(".");
var i = d.nodeName.lastIndexOf(".");
d.className = d.nodeName.substring(i + 1);
d.packageName = d.nodeName.substring(0, i);
d.nodeValue = d.nodeValue.value;
});

/* For pretty number formatting. */
var format = pv.Format.number();

vis.add(pv.Layout.Pack)
  .top(-50)
  .bottom(-50)
  .nodes(classes)
  .size(function(d) d.nodeValue)
  .spacing(0)
  .order(null)
.node.add(pv.Dot)
  .fillStyle(pv.Colors.category20().by(function(d) d.packageName))
  .strokeStyle(function() this.fillStyle().darker())
  .visible(function(d) d.parentNode)
  .title(function(d) d.nodeName + ": " + format(d.nodeValue))
.anchor("center").add(pv.Label)
  .text(function(d) d.className.substring(0, Math.sqrt(d.nodeValue) >> 4));

}

If you save and preview, you should see the bubble chart:

Example 2: Pie & Donut Charts

In the first example I defined the data that rendered the bubble chart in a custom parameter. Here, I'll show how to use a CDA query to feed the data.

Note: the pie chart I chose for example 2 is a rather uninteresting one. If you want a pie chart you'll better use CCC Pie Chart Component. I used this one of the sake of simplicity, but virtually any other protovis sample could be used here

Create a sql input over jndi query named "myQuery", choose SampleData for the source and use the query:


select c.TERRITORY, SUM(o.QUANTITYORDERED) as quantity
from ORDERFACT o
inner join CUSTOMER_W_TER c on (c.CUSTOMERNUMBER=o.CUSTOMERNUMBER)
group by c.TERRITORY

Now create a second protovis component, repeat the same instructions as for the first one but this time select "myQuery" as the datasource. The difference is that our protovis function is
called after the query runs, and the object "data" is the result of the query. Here's the function I used, slightly modified to take into account the format of the data cda returns.


function(vis,queryResult){

var data = queryResult.resultset;

/* Sizing and scales. */
var w = this.width,
   h = this.height,
   r = w / 2,
   a = pv.Scale.linear(0, pv.sum(data.map(function(d){return d[1]}))).range(0, 2 * Math.PI);


/* The wedge, with centered label. */
vis.add(pv.Wedge)
   .data(data)
   .bottom(w / 2)
   .left(w / 2)
   .innerRadius(r - 40)
   .outerRadius(r)
   .angle(function(d){return a(d[1])})
   .event("mouseover", function(){return this.innerRadius(0)})
   .event("mouseout", function(){return this.innerRadius(r - 40)})
 .anchor("center").add(pv.Label)
   .visible(function(d){return a(d[1]) > .15})
   .textAngle(0)
   .text(function(d){return d[0]});
}

If all went well, you should now have a protovis sample with both charts running, one of them with data from your query

Some important notes:

Don't create the vis object (the protovis root panel) - cdf takes care of that for you. The size and placement comes out of the options you provide
Don't call the render() method. Let the cdf lifecycle take care of that for you
Don't use 1.8 syntax. You'll see a lot of functions defined like function(d) d . While very practical, it will guarantee to break in internet explorer. Use the extended format of function(d){return d}.
CDF will make the necessary arrangements so that it will work on IE 8 or less, by converting it to flash. Flash 10 required and, as usual, make sure your javascript code is foul proof...

Hope that helps. By now, you should also be able to understand why we created CCC. It's a royal PITA to write pure protovis code for every basic chart we need :)

Revenues of open source contributions - Part II

2011-02-01T11:23:00.003Z

Almost 2 years ago I wrote a blog post entitled "Revenues of open source contributions".

The main reason that led me to permanently contribute back was the "just feels right" argument. What can I say? I have the Mozilla values buried deep within.

After this 2 years I can clearly identify one major benefit: recognition. Not only on the open source world, where every new project we release is followed with interest and we have a thrilling community testing, helping fixing bugs and commiting new features, but also in the enterprise world, where we're seen by customers as a very specialized and serious company. In the Pentaho Global Partner Summit our team got a recognition for all the hard work done recently, as we were awarded as the Best Community Contributor

Enough chat and back to work - we have a lot of releases coming soon, and that's what really matters. That's everyone for the support so far

Merry Christmas everyone

2010-12-22T22:01:00.001Z

webdetails team

Connected to Mozilla - 2010

2010-12-22T00:49:00.006Z

Last week I was in Mountain View, California, for the Mozilla All-Hands work week. We've been working with them on the metrics team for over 2 years now, and has been an amazing ride.

Working with such an amazing group of people from all over the world is truly inspiring, and we end up absorbing some of the Mozilla values to the way we run our own company. We prepared a presentation to show them, and in the truth spirit of openness, nothing better than to share it.

Open publication

Thank you Mozilla, for an unforgettable 2010.
Webdetails team

Saiku - the olap analytics tools everyone's waiting for

2010-12-06T20:41:00.003Z

You may not know what Saiku is. You may know it by the old name - PAT (or Pentaho Analysis Tool). And even if doesn't ring a bell, I can tell you it's going to be the 21st century replacement for JPivot as the de facto Open Source OLAP navigator client.

The Saiku team recently did a contest to get a new logo designed to it.

As self-nominated #1 dashboard experts of the entire solar system (including Pluto, that will always be a planet in my book!) we had to enter that contest. After 49345 entries and iterations, guess what? We won, with the logo you see in this post! (and by we, I don't mean me, I mean our UI expert, Nuno Moreira).

In the true open source spirit, Webdetails decided to donate the prize to an opensource project. After a lot of investigation and discussion we chose to donate it to - drums roll - the Saiku project!

So now, Tom, Paul and all of you that work on the project, now that you are earning the big bucks, just give us version 1.0! Keep an eye on Tom Barber's blog for updates. In the meanwhile, all we have is the teaser:

CDE 1.0-RC3 available

2010-12-02T02:29:00.005Z

Note: There was a mistake on the package. If you downloaded before December 03 and when you try to create a new dashboard and see an error like "missing ] after element list", please download and install again

What a quick turnaround. Turns out that RC2 didn't fix the main issue that prevented CDE to work on 3.7. With the help of some community members we were able to identify and fix it.

There were also some bugfixes included, and we tested all the samples in the distribution.

Admittedly there's a lack of documentation in CDE, so we included a sample that should clear the initial question of 'how to start'.

Download it from our website or directly from googlecode

Apart form this, the same release notes still hold as from the RC2:

This is much more than a new build, as lots of bugs and improvements were made in all the projects contained in the bundle (CDF, CDE, CCC, CDA, CST). Yes, you heard right, CST is also included in the bundle. One of the most important issues includes support for dashboard rendering in IE (including CCC charts)

Following the release notes:

CHANGELOG:

CDF:
- Upgraded to jquery 1.4.4
- Upgraded to jqueryui 1.8.6
- 3.7 compatibility fix for prptComponent
- Fixes to daterangeinput (no longer in chinese)
- Bug fix on blueprint render in IE

CCC:
- Several bug fixes in barcharts and stacked charts
- Implemented SVGWEB for IE rendering

CDE:
- Lots of internal changes in the way renderers work
- Added shortcut keys to CDE (press ? to see them)
- Fixed issue with order of execution
- Fixed dateComponent and Added support for lastWeek and lastMonth
- Added support for ${img:...} tags
- Updated jquery
- Added support for CCC in IE

INSTALLATION INSTRUCTIONS:

Copy the files in this bundle to the pentaho-solution directory. If you have any
older versions of this files (and you'll have at least CDF) make sure you move
them out of the way. A server restart is required.

SYSTEM REQUIREMENTS:

- Pentaho 3.6 or 3.7

CDE 1.0-RC2 ready for test

2010-11-28T18:59:00.002Z

More C-Stuff! Following a few reported issues of incompatibility with 3.7, it's time to release 3.7.

This is much more than a new build, as lots of bugs and improvements were made in all the projects contained in the bundle (CDF, CDE, CCC, CDA, CST). Yes, you heard right, CST is also included in the bundle. One of the most important issues includes support for dashboard rendering in IE (including CCC charts)

Following the release notes:

CHANGELOG:

CDF:
- Upgraded to jquery 1.4.4
- Upgraded to jqueryui 1.8.6
- 3.7 compatibility fix for prptComponent
- Fixes to daterangeinput (no longer in chinese)
- Bug fix on blueprint render in IE

CCC:
- Several bug fixes in barcharts and stacked charts
- Implemented SVGWEB for IE rendering

CDE:
- Lots of internal changes in the way renderers work
- Added shortcut keys to CDE (press ? to see them)
- Fixed issue with order of execution
- Fixed dateComponent and Added support for lastWeek and lastMonth
- Added support for ${img:...} tags
- Updated jquery
- Added support for CCC in IE

INSTALLATION INSTRUCTIONS:

Copy the files in this bundle to the pentaho-solution directory. If you have any
older versions of this files (and you'll have at least CDF) make sure you move
them out of the way. A server restart is required.

SYSTEM REQUIREMENTS:

- Pentaho 3.6 or 3.7

Download it from our website or directly from googlecode

Have fun, and don't forget to email us if you need help in your projects, we need to buy Christmas gifts for the kids and my Xbox just died on me! ;)

Pentaho dashboards on android and iphone

2010-11-17T11:24:00.006Z

Dashboards are cool. Mobile is cool. So, dashboards on mobile devices must be good, right? I usually blog a lot about very technical stuff like CDE, CDF, CCC, CDA and all the community projects webdetails as been working on for the past years. This blog post isn't technical, is just one picture showing the outcome of all the tech talk.

We've been working with Mozilla for a while, and we've incorporated a lot of their values into our company - value opensource, contribute to community, adopt standards.

And being faithful to standards has its advantages. We tried to access one of our latest and greatest dashboards (like all others ;) ) with our mobiles and the result was awesome. An iphone on the left side, an android on the right (using opera, unfortunately the built-in browser doesn't support svg yet).

Powered by Pentaho, generated with CDE using CDA, CCC, CDF and all that jazz, made by webdetails:

CST - Community Startup Tabs

2010-11-10T09:39:00.006Z

One of our customers, through a partner, asked us to develop a functionality extension to pentaho. We like that. In the true open source spirit, he allowed the work to be released to the Pentaho community. We love that. So here it is:

CST - Community Startup Tabs

This project allows pentaho developers to define a set of rules to define which tabs will open when a user logs in to pentaho. Example:

I want my financial users to open 3 tabs at start with pivot analysis, department managers to open 3 dashboards and everyone else to get the home screen

This is possible by editing a very simple configuration file that even supports regular expressions. Try it out.

Download

Source code

CDE 1.0-RC1 available for download!

2010-10-18T00:16:00.004+01:00

And here it is, the news that everyone was waiting for! The 1.0-RC1 build of CDE is available for download in webdetails website.

The bundle consists of:

Latest CDE version (inludes latest version of CCC)
Latest CDF version
Latest CDA version

Feel free to try it and give feedback. It's the result of over one year of development and collaborative work. And it's all opensource! Believe me when I say that you can make absolutely stunning dashboard in Pentaho with it. It has so much that I won't try to enumerate.

C you soon!

CCC - Community Chart Components

2010-10-07T22:03:00.004+01:00

So many amazing stuff happened at the community meeting that we're still picking up the pieces. Lots of great presentations (if you haven't seen it yet, be sure to check Jan Aertsen's live coverage of the event.

I'll put some of the content we presented as we have time for it (lots of requests for a binary version of CDE, I'll take care of it asap), but I'll start with CCC

Don't know what CCC is? Check the presentation:

Open publication - Free publishing - More webdetails

As usual, it's opensource, so you can check it at: http://github.com/pmalves/ccc

The best way to take advantage of it is to use it with CDE, but I'll leave it for another post.

Cheers

Pentaho Community Gathering - Talks

2010-09-15T21:19:00.002+01:00

Here's the presentation list for saturday - What a great schedule

10h00: Doug Moran - Community update
10h30: Pedro Pinheiro - CDA (Community Data Access)
11h00: Julian Hyde - Mondrian stuff
11h30: Matt Casters - Dynamic Kettle
12h00: Aaron Phillips - Plugins and extension points
12h30: Nelson Sousa - CDE (Community Dashboard Editor)
13h00: lunch time
14h00: Tom Barber and Paul Stoellberger - PAT
14h30: Jan Aertsen (with Matt) on the Kettle Franchise Framework (KFF) and much more
15h00: Nuno Moreira - Pentaho Dashboards. Breaking barriers
15h30: Jos Van Dongen - Data Mining
16h00: Dan (Codek) - Approaches to implementations and methodology
coffee break
16h30: André Simões - PDI job/transformation framework to be used as a BI project template
17h00: Pedro Alves - CCC (Community Charting Components)
17h30: Roland Bouman - Kettle cookbook

We'll also have the chance of having some fun on Sunday... but I'll leave that to later

Pentaho Community Gathering - Portugal. Sep 24

2010-09-09T17:07:00.005+01:00

One image is worth a thousand words:

So here's the video :)

And the image too...

I'll post an updated calendar, but roughly:

24 - Friday night: meeting in Cascais for some drinks
25 - Saturday: Presentations and drinks
26 - Sunday morning / afternoon: Outdoor stuff for people to socialize

Cya all there!

Hey Pentaho Community! How are you doing?

2010-08-12T23:28:00.004+01:00

Doug Moran, Pentaho community guy, started a very interesting thread that got me to think a bit and give an unusually long reply. So long that I decided to copy it as a new blog post and get my weekly task done!

Apparently people is always asking him

How's the community going?

followed by the inevitable

I just saw a post that says Jasper has 150,000 community members, how big is Pentaho's community?

Here's my take on this:

Comparing downloads is absolutely meaningless. Specially on BI, everyone should know that numbers are only comparable when they have things in common. You mentioned Jasper compared to Pentaho cause surely it's the biggest comparison people ask you to do. But can you really compare the 2 of them? Take a look at the forum numbers:

Platform threads:
Jasper: 4k;
Pentaho: 4k

Reporting threads:
Jasper: 18k (jasperreports) + 12k (ireports);
Pentaho: 8k

Analysis threads:
Jasper: 300;
Pentaho: 5k (forums) + 1k (mailing list)

ETL threads:
Jasper: 250;
Pentaho: 10k

Dashboard threads:
Jasper: - ;
Pentaho: 1k

Other threads:
Jasper: - ;
Pentaho: A lot

Anyone looking at this numbers can immediately tell that we have, on one side, a BI Tool and on the other a Reporting tool. Why compare different things?

Going back the the main question, I have a suggestion. Next time someone asks you "how's the community doing?", instead of answering, why don't you:

Invite them to go to Portugal in September to the community meeting. A pentaho meeting usually sponsored by the community itself and with people all over the world? Assuming, of course, they missed the one in Barcelona 2009 or in Mainz 2008.
Send them the links to some project made by the community, I currently can think of...
- CDF
- PAT
- Xmla4Js
- CDA
- CBF
- CDE
- GeoBI
Ask them to join the ##pentaho irc channel on freenode that usually has around 50 simultaneous users
Tell them to read a bit... Suggest authors like Jos Van Dongen, Roland Bouman or Maria Roldan
Visit other countries
Or if they have a computer, why not read some blogs?
- to get their eyes on pentaho,
- musing about what the community is doing
- from gurus, geeks, consultants or hackers to
- many others that will guarantee a good reading.
- So just let them think about it and draw their conclusions

I'd say the community is doing very well, thank you, and tell them we send our best regards.

Better reports on dashboards

2010-08-10T16:40:00.005+01:00

We now have an easier/better way to get reports in our dashboards without hacking with xactions. 2 components to help with this:

prptComponent
executePrptComponent

The first one isn't actually new, but a new option was added called "iframe". When set to true, behaves like the default:

When set to false, the report get's embedded smoothly into the dashboard inside the htmlObject, thanks to a special mode that Thomas Morgner implemented.

The other component is very similar to the executeXaction, but for reports. Now we don't need to create a new xaction just to launch the report. Just pointing to the .prpt will do the same.

Here's the button:

And the expected result:

ttfn!

Persistence options in CDF dashboards

2010-08-04T15:24:00.004+01:00

This is something insanely useful that's been in the todo list for ages. Now, thanks to our CDI methodology (CDI as in Customer Driven Improvement, not yet another project :) ) it's there already.

The principle is simple: You access a dashboard, define a set of filters that leads you to your favorite view. Next time you access the same one (or just do a refresh) you need to start all over again? Not anymore.

Now we can easily tell CDF to remember the options set by the user. Let's take the SelectComponent example:

regionSelector =
{
name: "regionSelector",
type: "select",
parameters:[],
valuesArray:[["1","Lisbon"],["2","Dusseldorf"]],
parameter:"region",
valueAsId: false,
htmlObject: "sampleObject",
executeAtStart: true,
postChange: function(){alert("You chose: " + region);}
};

This gives you the standard, boring yet most useful select component. Now, what changes are required for the user to see the last option he selected the next time he accesses the dashboard? Marked in bold:

regionSelector =
{
name: "regionSelector",
type: "select",
parameters:[],
valuesArray:[["1","Lisbon"],["2","Dusseldorf"]],
parameter:"Dashboards.storage.region",
valueAsId: false,
htmlObject: "sampleObject",
executeAtStart: true,
postChange: function(){Dashboards.saveStorage(); alert("You chose: " + Dashboards.storage.region);}
};

And now, if the user selects Dusseldorf, this is what he'll see the next time he accesses that dashboard:

No need to say that this can be used for a lot more than simple selectors - up to your imagination.

Can this get any easier?

A different dashboard

2010-06-18T23:37:00.003+01:00

There are some well known rules to follow when building dashboards:

They have to display accurate information

Fit in the environment

Fast

Bug free

We almost got a hit, but failed on the last item. Too many bugs on the windshield ;)

FirefoxCup - Let the world cup begin!

2010-06-10T22:36:00.009+01:00

The World Cup is here. Portugal may be going through a big financial crisis (just like in the last 10 years, we're actually used to it) but for the next month all of that will be forgotten: It's soccer time!

I may not influence how my team plays in field, but fortunately Mozilla started Firefox Cup, where I can, indeed, be a player in the field.

GO, PORTUGAL!!

What does this have to do with BI?

Well, actually, a lot! It's a well known fact - mainly because I keep repeating it proudly :) ) that we are part of the mozilla metrics team. And turns out that my alter-ego, pedro@mozilla.com, is in some sense the referee of this tournament!

This is a screenshot of the current score of the tournament. And where do those numbers come from? From the analysis of the logs generated by (much) more than one hundred million firefox users daily. This translates into the analysis of a few gigabytes of logs that are generated every hour. Through a somewhat complex and fine-tuned etl process, we are able to get all relevant data into a datawarehouse and, not more than 4 hours after midnight, be able to provide the accurate usage number for each of the existing 150k personas. Here's the current Portugal persona chart:

This numbers are then integrated into the Firefox Addons databases and are available for the world to see. In case you're noticing the discrepancy between the values in both of the charts, it's easily explainable - the number of users on the addons page is the 7 day rolling average, to prevent the "weekend effect', making the curve much smoother than just printing "yesterday's number"

And it's absolutely amazing the amount of cool analysis that we can do on this subject, and we hope to be helping the personas' team driving their strategy!

It's time! CDA 1.0 is available

2010-06-04T17:08:00.002+01:00

And it's done! We finally released version 1.0 of CDA - Community Data Access. Despite the "1.0" version, we've been using this in production environment on our clients for a while now, and I can assure it's very stable!

I've talked enough about CDA (and I feel so tired about this release that I can't really find anything to say).

Get it at webdetails site, and have fun. Every feedback is appreciated!

And for the ones that still don't know what exactly CDA is about, we've made this presentation for you:

ps: For the ones asking: is this really opensource? What's the catch? There are no free lunches... You're right, there aren't. We have an evil plan: Convince you that we are able to do quality work and that we are the perfect partners for your Pentaho implementation. Email us :)

Coming soon: CDA 1.0

2010-05-31T17:50:00.004+01:00

A CDA teaser, made by our team. Hope you enjoy it!

Pentaho reports by email - with steroids

2010-05-26T01:23:00.006+01:00

Most people don't know this but there's an hidden gem inside Pentaho 3.5 (and the soon to be released 3.6);

The ability to send a report by email ...

Nothing new so far?

... with all images and resources inline

So no more of being forced to send pdf attachments in boring text or html emails, or the need to have your pentaho server accessible in the www. Pretty cool, hein?

Did you just said you like the pdf attachments?

You can still send your pdf attachments with the html email!

And I'm not done yet! Want to impress your friends of the other company by receiving yesterday's sales report on your smartphone while having breakfast?

Just render a text version of that report

and it'll automagically generate a mime/alternate email message and your email client will chose the best format to display the information to you

You didn't know this was possible? It's all in there. More than one and a half year's ago we developed a bunch of patches to allow this. And why? Because one of our customers wanted this feature. <shamelessplug> And that's what we do, develop cool stuff for our customers</shamelessplug>

We added:

* A new repository implementation was built to allow the generation of a mime-message directly from the jfreereport engine (with Thomas Morgner's help, of course)

* A patch to the reporting plugin to add a new supported output-type 'email'

* Support to the text output in the reporting plugin

* A SimpleEmailComponent POJO that sends the emails

The existing action to send emails is very limiting, so we had to develop a new one. Pentaho guys allowed this to be commited along with the reporting plugin. We all agree that the BI suite need a full-featured email component so this POJO will eventually be moved out of the reporting plugin. But until that happens - hey, let's use it!

nuff said; Want to see this working? We stored on webdetails site an archive of all the necessary files. Unzip it to your pentaho solution folder of choice, see how it works and feel free to play with it!

Have fun!

Pentaho 3.2 Data Integration: Beginner's Guide (not after reading the book)

2010-05-10T20:54:00.004+01:00

This book was a total surprise. Though I've known Maria Roldan's work for a while as a very valuable community member - she wrote an excellent kettle tutorial in the wiki and by her participations in Portuguese and Spanish pentaho communities, but didn't know she was working on the book. Which is a very good sign for this very large and active community.

The contents of this book are also surprising. I wasn't expecting to find a chapter called "Working with databases" in #8 of the list. When working with kettle and the rest of the pentaho suite on a daily basis, building and populating large datawarehouses, you almost expect that to be first on the list. (By the way, the datawarehouse subject comes in chapter 12).

Odd? Well, not if you go through the book and think about it for a bit. Pentaho Data Integration (PDI) is not a tool to build datawarehouses and that can be used to other data integration tasks. PDI is an amazing tool to do any kind of data handling, involving datawarehouses or not, involving databases or not. Since it's great for any data handling, if course it's also great for the subset of data warehouse management.

And I think this is the main message Maria Roldan wants to pass. Forget perl hacks, forget homegrown shell scripts, please, don't write yet another stored procedure that will make you (or the poor guy after you) go nuts when it comes the time to support it. Just use kettle. You'll find a lot of screenshots with accurate explanations making this book very lightweight to read. You can even take it while on travel/vacations and you'll be able to understand it even without further experiments.

This book can bring anyone up to date with Kettle very fast. The first chapter is all about installation procedures. Chapters 2 and 10 describe all the concepts everyone needs to know about transformations and jobs. All the others are clearly the result of someone that spent a lot of time working with the tool. There are brief descriptions of a large number of steps used in real world examples and chapters that dedicate entirely to very important subjects: Data validation and error handling (chapter 7), transforming the rowset (chapter 6) and how to connect it all with the appropriate task flows (chapters 10 and 11)

But my favorite chapter is number 5 - The javascript chapter. When most of the experts in kettle will say avoid the javascript step at all costs, Maria dedicated an entire chapter to it. I love javascript and I love the javascript step (that's right Thomas, regardless of your complains). When used right, it can save your day - true, it can also ruin your transformation if you're not cautious. It will definitely slow your transformation down, and if you're an ETL God you'll look for other alternatives. But for almost other situations in the mortals world where we have other bottlenecks to worry about just use this step!

Summing it all up, if you need to take bits and bytes from point A to point B, just buy the book.

ps: I support the home team, and as such I'm biased towards Pentaho just like I'm biased towards Mozilla or my team at WebDetails but I'm not doing that in regards to this review. The proof that I enjoyed this book and all the hard work behind it is that as soon as I saw this twitter message I acted as fast as I could and now Maria Roldan is officially a member of the WebDetails team. Welcome Cari!

Pentaho Community WebEx - Slides and recording

2010-04-23T12:23:00.001+01:00

For whoever is interested, the slides for the presentation are available here and the recording can be seen here

Pentaho Community WebEx - CDA. April 21st

2010-04-20T17:42:00.004+01:00

Don't miss: Check details here

Pentaho, CDF, CDA, , Protovis

2010-04-15T12:42:00.003+01:00

One image is worth more than 1000 words. We're still experimenting but getting better and better at it. And we're soooo proud of it :)

We're giving more and more priority to layout, usability and interactivity (which doesn't imply flashy or animated stuff, on the contrary) and the feedback has been amazing so far.

ps: All the stunts I blog about are used in production before a live audience. Please don't try this at home

Pentaho Analysis Tools - When all the pieces come together

2010-03-19T18:06:00.012Z

The PAT project, by Tom Barber and Paul Stöllberger is coming along quite nicely. Expect it to be in a state that will replace jpivot very soon as the main olap viewer of the Pentaho BI Suite.

And the best thing is that they got to a point that they're not looking to it's own development but to the surrounding environment too. And it's great to see it connecting to the surrounding environment.

Here's what will happen everyone in a very near future.

I want to make a dashboard out of this query

No problem. We just need to follow this steps:

1. Access your Pentaho installation

2. Use PAT to access your datasources

Pat is already working as a plugin in the pentaho platform. A great step to make a prototype project into something that everyone's waiting for - a replacement for jpivot. Analyzer plugin is great but only available in EE, so the ecosystem is providing a (great) solution to this issue.

3. Build your query and export it as a CDA file

4. Open the CDF Dashboard Editor and load your CDA query

5. Design the rest of the dashboard

6. Enjoy

Sneak peak into the... present

2010-03-12T01:05:00.004Z

What happens when we join great people and great technology? Hot stuff!! Here's a screenshot of a new dashboard that WebDetails team developed with and for Mozilla Corporation for quickly monitoring ETL errors and alerts

Some months ago we could only dream of producing this kind of stuff. Pentaho as the engine, CDA for data access, CDF for the visualization engine, charts rendered by protovis and deployed by CBF, what do we get? Total Control!

The cool stuff about all this? All is open source. The not so cool stuff about all this? Sometimes putting all the pieces together is not easy, requires some of experience, expertise and imagination. (And guess what, we actually have all that, so if you want to have this kind of stuff too, why don't you just contact us? ;))

Interacting with the TableComponent

2010-03-05T12:57:00.005Z

It's been usual to see someone asking for interaction with the TableComponent in CDF. While it's true that we don't support that out of the box, it's incredibly easy to add it. Well, maybe not incredibly easy to everyone, but that's what blogs are for :)

To achieve that we'll use the amazingly useful postExecution function. It allows us to execute whatever we want after the component is rendered. Since CDF internally uses JQuery we have almost no restrictions to what we can do.

We'll use the bind and live functions of jquery. Let's define our click function:


var clickFunction = function(){
 alert("You clicked on: " + $(this).text())
}

And in our postExecution we'll add a call for it. I'll use the sample in the documentation


var topTenCustomers =
 {
  name: "topTenCustomers",
  type: "tableComponent",
  chartDefinition: MetaLayerHome2.topTenCustomerDefinition,
  htmlObject: "sampleObject",
  executeAtStart: true,
  postExecution: function(){
   var cols = $("#sampleObject td:nth-child(1)");
   cols.die("click");
   cols.live("click",clickFunction);
  }
 };

Some explanations. With the help of selectors we selected every first row (index on nth-child starts at 1). We can select whatever column(s) we want. Then I attached the clickFunction to the click event on those cells.

The reason I used live instead of bind is subtle - this way, when we change pages or filter within our component it will still work.

And that's it. In a real-world scenario we'd probably replace the alert() with something like the Dashboards.fireChange function like we do in charts, but you get the idea.

Of course we can play a bit around this idea. Here's my final alternative to the tablecomponent component reference example:



var MetaLayerHome2 = {
 topTenCustomerDefinition: {
  colHeaders: ["Customers","Sales"],
  colTypes: ['string','numeric'],
  colFormats: [null,'%.0f'],
  queryType: 'mdx',
  displayLength: 5,

  catalog: 'solution:steel-wheels/analysis/steelwheels.mondrian.xml',
  jndi: "SampleData",
  query: function(){

   var query = "select NON EMPTY {[Measures].[Sales]} ON COLUMNS,"+
    " NON EMPTY TopCount([Customers].[All Customers].Children, 10.0, [Measures].[Sales]) " +
    " ON ROWS from [SteelWheelsSales]"
   return query;
  }
 }
};

var mouseoverFunction = function(){
 var oldColor = $(this).css("color");
 $(this).data("oldColor",oldColor);
 $(this).css("color","red");
 $(this).css("cursor","pointer");
}

var mouseoutFunction = function(){
 $(this).css("color",$(this).data("oldColor"));
 $(this).css("cursor","default");
}

var clickFunction = function(){
 alert("You clicked on: " + $(this).text())
}

var topTenCustomers =
 {
  name: "topTenCustomers",
  type: "tableComponent",
  chartDefinition: MetaLayerHome2.topTenCustomerDefinition,
  htmlObject: "sampleObject",
  executeAtStart: true,
  postExecution: function(){
   var cols = $("#sampleObject td:nth-child(1)");
   cols.die("click");
   cols.die("mouseover");
   cols.die("mouseout");
   cols.live("click",clickFunction);
   cols.live("mouseover",mouseoverFunction);
   cols.live("mouseout",mouseoutFunction);
  }
 };
Dashboards.init([topTenCustomers]);

CDA - Community Data Access

2010-01-29T15:51:00.008Z

It's not CBF. It's not CDF. It's CDA!

Aren't you glad that I don't make a living out of inventing names for projects?

It's a well known fact that the data access part in CDF is very.... hacked (I would call it something different but I'd probably be banned from blogger.com).

There's no common layer, each component has the responsibility of getting the data it needs and worst of all it can permit sql injection (thus sql support is disabled by default).

This item has been on my todo list for a too long, and it's time for me to tackle it.

It wasn't obvious at all for me where to put this data access layer. I need something that I can use both on CDF and on our Dashboard Editor. Classloading hell regarding pentaho plugins are also a concern.

Since others have asked for the same, I decided to create a new independent plugin: CDA - Community Data Access. It will be accessed by simple url calls and support the following datasources:

SQL
MDX
Metadata
Kettle
etc.
Compositions of the above

The last item is very important for me. Several times I wanted to join a sql query with a mondrian query and use it for a single report. This is just a simple use case amongst many others.

It will support multiple outputs:

JSON
XML
CSV
XLS
HTML visual mode

Here's a snippet of a myDashboardData.cda:

<?xml version="1.0" encoding="utf8"?>
<CDADescriptor>
    <!-- DataSource definition. 
    
    Type controls what the datasource type is.
    The connection and query controls how the data is fetched. These values
    are specific to each access type

    -->
    <DataSources>
        <Connection id="1" type="jdbc">
            <Driver>org.hsqldb.jdbcDriver</Driver>
            <Url>jdbc:hsqldb:hsql://localhost:9001/sampledata</Url>
            <User>pentaho_user</User>
            <Pass>password</Pass>
        </Connection>
        <Connection id="2" type="jndi">
            <Jndi>sampledata</Jndi>
            <Catalog>./sampledata.mondrian.xml</Catalog>
            <Cube>SteelWheelsSales</Cube>
        </Connection>
    </DataSources>
    <!-- DataAccess object controls the query itself

    Access controls if the datasource can be seen from the outside or is to
    be used from a Compound datasource only

    -->
    <DataAccess id="1" connection="1" type="sql" access="public" cache="true" cacheDuration="3600">
        <Query>
            select o.YEAR_ID, o.STATUS, sum(o.TOTALPRICE) as price from orderfact o
            where o.STATUS = ${status} and o.ORDERDATE &gt; ${orderDate}
            group by o.YEAR_ID, o.STATUS
        </Query>
        <!-- All parameters are passed as string from the outside. Their
        type is defined here. If needed, a conversion pattern must be specified
        -->
        <Parameters>
            <Parameter name="status" type="String" default="Shipped"/>
            <Parameter name="orderDate" type="Date" pattern="yyyy-MM-dd" default="2003-03-01"/>
        </Parameters>
        <!-- Settings block here overrides defaults and general settings 
        
        Columns block allow specifying name and CalculatedColumn the creation of new
        columns based on formulas

        -->
        <Settings>
            <Columns>
                <Column idx="0">
                    <Name>Year</Name>
                </Column>
                <CalculatedColumn>
                    <Name>Test</Name>
                    <Formula>=[1]/100</Formula>
                </CalculatedColumn>
            </Columns>
            <Export type="xls" includeTotals="true">
                <Column idx="0" aggregator="None"/>
                <Column idx="1" aggregator="Average"/>
            </Export>
        </Settings>
        <!-- Output controls what is outputed and by what order. Defaults to everything -->
        <Output indexes="1,0,2"/>
    </DataAccess>
    <DataAccess id="2" connection="2" type="mdx" access="public">
        <Query>
            select {[Measures].[Sales]} ON COLUMNS,
            NON EMPTY  [Time].Children ON ROWS
            from [SteelWheelsSales]
            where ([Order Status].[${status}])
        </Query>
        <Parameters>
            <Parameter name="status" type="String" default="In Process"/>
        </Parameters>
    </DataAccess>
    <!-- CompoundDataAccess can be used to join queries. Some types are
    supported, and the user is responsible for assuring the different dataaccess
    are compatible:

    * Union: Appends different sources
    * Join: Joins 2 resultsets based on one or more keys. Results have to be 
    sorted by those keys.

    -->
    <CompoundDataAccess id="3">
        <Union>
            <Source id="1"/>
            <Source id="2"/>
        </Union>
    </CompoundDataAccess>
    <CompoundDataAccess>
        <Join>
            <Left keys="0"/>
            <Right keys="0"/>
        </Join>
        <Output indexes="0,2,3"/>
    </CompoundDataAccess>
</CDADescriptor>

This is about it. Suggestions are welcomed, and this is the correct time to integrate them since I'm still on the drawing board. (But hurry, cause I need to have this done very quickly :) )

Have fun

Merry Christmas and a Happy New Year!

2009-12-22T18:12:00.002Z

2009 is almost over. It's been a great year, both personally and professionally. Time to do the usual retrospectives and desires to the future.

In the spirit of Christmas, Webdetails is offering single-developer copies of our CDF tutorial to the first 3 persons that send and email to cdf _at_ webdetails.pt telling us how they (intend to) use CDF.

Have fun

Bringing Pentaho to Firefox

2009-12-14T16:41:00.009Z

Just got back from one week in Mountain View, more precisely in Mozilla headquarters. Without doubts one of the best group I've been in - and I'm very proud to be a part of it.

I always get back with a lot of ideas to apply to our projects and this time was not different; What was different is that I actually got hands down to implement it - a CDF jetpack:

The idea is to improve the user interaction with our dashboards. More than describe, I can show it with some pictures.

After I installed my jetpack, you can see in the status bar a new icon that will give the user access to some operations. If we click on any of the bookmarks we'll directly jump into the desired location. Alternatively we can start designing a new dashboard directly.

The jetpack automatically detects that we're in a CDF-DE dashboard. Then it shows more options - in this case we can edit it or even bookmark it.

After we give it a name, it will be stored and available in our personal bookmarks. Those bookmarks will be associated with the firefox user profile and persist between sessions.

Once in that page, we can also remove the bookmark from the list.

I don't know what others may think but I really like this beginning. Brings the user closer to the data, and there's a lot of stuff we could do here. If you have any idea just tell them - as wild as they may seem.

That's it for now. It's not the only idea we brought from Mountain View but that will be in another post. If you find this useful, we can include this jetpack in a future version of our designer.

Building Pentaho 3.5 with the latest and greatest Reporting Engine

2009-11-13T11:45:00.007Z

Pentaho 3.5 is out for a while, with the new Citrus reporting engine. Thomas and the rest of the Pentaho crew did a great job turning the mess that was the old Report Designer into a totally rewritten application with lots of new features.

But everyone knows that on the first version of a (re)new software it's inevitable to find problems. This is not an exception. Real world is very different from the labs. Here are some examples of this.

Gotta love open source; We have everything we need to be able to debug the problems. We were able to solve some and Thomas solved the nasty ones within no-time. Many thanks to the crazy German / Irish/ I never know.

Now the worst part - We have a shiny and working PRD but we need to make it work on Pentaho platform. One option is to use all the latest trunk that picks up latest PRD. Well... not really an option. Even I am not crazy enough to do that.

So I opted to keep using 3.5.0 GA but tell in the build process to get only the latest trunk of the reporting enging.

I use CBF for... well, everything. And CBF uses the main pentaho build process. We had to change an ivy file in order for this to work. I hate ivy. Everyone tells me it's a good thing, but from my experience it's only good for them.

Anyway, after some fight, here's the file that we need to replace. It's under bi-platform-assembly/ivy.xml. For cbf you can drop it in patches/target-build/bi-platform-assembly/ivy.xml. You'll also need the latest ivy version (2.1.x). Just drop the jar in the ant lib dir.

Also set this property in your build config file:

dependency.pentaho-reporting.revision=TRUNK-SNAPSHOT

Now you can have fun with the latest and the greatest.

CDF - Dashboard Editor. Version 0.2

2009-10-27T17:15:00.002Z

Somewhat to my surprise, this is actually working for a lot of people. Thanks for the great feedback everyone.

One of the most pointed issues was the fact that New -> Dashboard didn't work. It was not implemented at all because at the time we developed that (pentaho 3.0 was the current version) the menu hooks didn't work. They do now, so it makes sense to correct it.

Other very annoying bug was that we were unable to save to a folder that had whitespaces

Anyway. Both of it is fixed and 0.2 is just out the door!

Have fun!

(oh, and hire us for the implementations ;) )

Pentaho / Lucidera - an excuse for the big questions?

2009-10-13T10:40:00.002+01:00

It's amazing the amount of discussion generated by Pentaho's acquisition of ClearView.

The concerns it raised have nothing to do with this move; Everyone agrees it makes sense: Lucidera came to an halt, they had a valuable product and valuable resources, Pentaho took the opportunity and acquired something that fills a gap into it's own offering.

So, what's the problem here? ClearView is only available for paying customers.

This raised a lot of questions and some frustrations. A few hours ago Seth Grimes summed some of the concers in a blog post. Even questioned if Pentaho was shifting strategy, which deserved a prompt response from Pentaho's CTO James Dixon.

I don't agree with Seth in the strategic part. It's not by having closed-source components that Pentaho will stop being an Open Source company (I also like the term open core). They payed money to Lucidera; They make money from the subscribed customers; They want to give more value to those customers. What's the problem?

I do agree with Seth when he says that the message pentaho passes is confusing; I always have the feeling that Pentaho faces an identify problem and is a little bit ashamed of it's closed source components. When we access the main Pentaho page we can hardly tell it's a O-S company. Apart from the logo, everything is "buy, try, evaluate" etc. When we visit the sourceforge page we are clueless about the commercial approach. It's important to clarify that. I'd really like to see in pentaho main page some bold sentence with something like "We are a commercial company; We have an opensource product with some closed source add-ons that give productivity boost to our clients - the ones that pay for our salary".

For me, the million dollar question that has to be answered is:

If my company chooses to go for the opensource version today, will we end up with a stale product one year from now?

When users see something released only in a closed source form, they fear everything from now on will be released as closed source only. It's a genuine fear. We recently saw the Dashboard Designer, the chart editor and now ClearView being released for paying customers only. Pentaho's answer can't be "the community won't let that happen". While the community indeed plays a big role here, Pentaho will always lead the way. The worst that could happen to Pentaho is having the community loosing it's interest. So it's indeed a tough decision for them when they need to make a call if something goes open source or closed source.

But we must not forget something: Pentaho needs it's community. Most of it's business and visibility is made through the open source version and they absolutely can not change that. It's much worst from them as a company than it is for us, community users or paying customers.

Pentaho Reporting Explained

2009-10-02T10:29:00.003+01:00

Finally got my hands on The Book! While I was very happy with the overall quality of Pentaho Solutions I have to admit that I was more anxious to get this one;

Thomas Morgner and the JFreeReport team did a very big refactor in version 3.5 of JFreeReport - codename Citrus (so big that was enough to trigger the release of version 3.5 of the Pentaho platform). And Will, as part of the JFreeReport and Pentaho development team was able to sync the release of the book with the release of the software - can't be more up to date than this.

So, not only I have a new software to play with, I also have a new book that will guide me through all the changes and improvements.

The book is not aimed to be an intruction manual on how to use reporting in the Pentaho platform, on the contrary; Will gives extensive instructions on how to use the reporting engine regardless of the environment - a java swing application, a webapplication, whatever; Just call the APIs and you'll get a report in whatever format you want. Of course, the Pentaho platform comes as an added bonus.

It does a great job explaining the structure of the engine, how to work with the report designer, how to integrate with several datasources, the export options, how to call the api and many other subjects. Fortunately it's not one of those books that waste 300 pages with the printed api, so expect to read it from start to end, probably just skipping through some (not very extensive) java code that explains how to do task N that you'll just thank to god that it's there in case you need it. Unfortunately the book can't cover everything; I was hoping to have more details on olap reporting but found only some simpler examples.

Conclusion: If you're planning to do reporting in java/webapps/pentaho/whatever just buy the book and don't complain about missing documentation for the next 2 years

CDF - Dashboard Editor. Want a test drive?

2009-09-29T11:38:00.006+01:00

If you were in Barcelona for the Pentaho Community Meeting, you know what this is. If you were *not* in there, well... it's a dashboard editor... for CDF.

You can download the package from here . It includes the sample I built in Barcelona. No time for instructions at this time, but shouldn't be hard to find what to do with it.

This is *not* intended for production unless you really know what you're doing. We can't guarantee that it will work for you at this point - we do guarantee that it works for us and our clients, so if you need consulting services for your project contact us.

As I said, the code will be released as open source with some added value services on top of it - as soon as we have time to do it.

Have fun and provide feedback

(ps: the new dashboard button in the menu isn't implemented yet. Open a sample and save with a new name)

Interacting with users: The CDF CommentsComponent

2009-09-28T15:52:00.003+01:00

Now that CDF allows us to do most of the "core" stuff when we want to build a dashboard for pentaho, we can aim some steps higher; We're working heavily on allowing users to iterate with the data; In my view, the ideal BI system is not a static application but a place where several users are able to discuss the information to make additional question and draw conclusions.

We just built a new component for CDF that allows users to make comments on a dashboard. Without any additional configuration needed, it stores the comments in the hibernate database of pentaho (gotta love pentaho's customization abilities!) making them persistent.

This will be available in the next CDF version (or trunk, for the ones that use it)

Pentaho Reporting - The book (part 2)

2009-09-18T10:58:00.002+01:00

2 book in 2 weeks. Looking great.

Full review will come later when I get my hard copy that I can take to places where the computer is not. But I've read parts of it in the e-book version;

If Pentaho Solutions is the book I'd kill for 2 years ago, Pentaho Reporting 3.5 for Java Developers is the book I want and need now. Will Gorman's book comes in a perfect timing: Pentaho 3.5 is near GA (stable version, for the ones who still didn't understand Pentaho's naming sistem - believe me, takes time to get used to), and ships the newest version of the reporting engine. Thomas Morgner and his team did a great refactor and it's by far one of the biggest improvements done in years.

So, we have a new and much improved reporting engine that comes at the same time as a book that explains in detail how to use it. Who can ask for more?

Pentaho Solutions - The book

2009-09-14T11:04:00.002+01:00

Roland Bouman and Jos Van Dongen took 6 months out of their family life and created this. And cutting to the chase, every Pentaho learner should thank them for that.

Pentaho is a very complex application due to many factors. For starters, the BI field is, by definition, a very sensitive field; the fact that pentaho started by combining distinct successful open-source projects - mondrian, kettle, jfreereport and weka - doesn't help when we want to learn exactly what's what. There are so many distinct applications that gets confusing to know what each thing is for.

All this makes the learning curve a very steep one. Until now. Trough over 600 pages Pentaho Solutions does a great job explaining all the Pentaho stack, where each of the components fit it and when to use what. And it doesn't stop there; It's impossible to do anything in Business Intelligence without some very solid knowledge of Data Warehouse. While this book is definitely not about it (this one is) still gives the novice reader the key concepts needed to read the book and go through the practical examples that are used.

The chapters are very well organized. They are in the same order as the topic appear when we are implementing a Pentaho solution. In the end the 600 pages even feel short, so many concepts it introduces. Don't expect to become an expert in pentaho; You won't master any of the components with this book only; you won't be a OLAP guru, a reporting wizard or even a etl master. But you will definitely know where to search for.

In my opinion, the ones that will benefit the most with this books are those guys with emails ending with @pentaho.com. It's so difficult to start playing with pentaho that a lot of newcomers feel scared and abandon it, resulting in a loss of potential user. This book changes all that. I wish I had it when I started playing with pentaho over 2 years ago. And I'm glad I have it know.

- "I want charts!" -"Flash, image or canvas?" -"Say again?!"

2009-09-10T16:30:00.004+01:00

CDF's primary rendering engine for charts is JFreeChart, and there are a lot of reasons for that:

Pentaho supports it
We have a very good level of technical interaction with it, allowing us to do drill through, etc
By generating an image, we can use it in static reports, print it, etc
JFreeChart is really, really good. Does it's job. Period.

It has it's disadvantages, though:

It's hard to interact with it. Being an image, all it can do is render image maps, so sometimes we have an hard time finding a particular value
It's ugly.

I'm not very sensible to the second argument. I'm a fierce defender that a dashboard shouldn't have elements that distract you from what's really important - the numbers. But it's also true that users (and sales people ;) ) love eye candy. And the business users always like to do some show off, nothing wrong with that.

The first point concerns me, though. Should be easy to read information out of the charts, and sometimes that's not the case. Sometimes we need interaction with the data at a level that a PNG file can't give us.

On a recent team Mozilla metrics team meeting where I was showing a dashboard our team developed, I did a test to measure the reaction to find out exactly how important this issue is. I showed them the new dashboard using standard jfreechart components and near the end of the meeting I quickly swapped to the open flash charts version of them. The result was worst than I expected..

- Oh, WOW, this looks awsome!
- Hum... well, it's really the same thing, the information is identical
- I don't care! It's prettier. And look, there are popups with the numbers. I want this!

It's undeniable. It has a great impact. Since I work on both sides - developing the framework AND doing actual implementations, instead of fighting it, I can try to build the tools in order to allow the best of both worlds.

Working on a new charting engine for CDF has always been one of my objectives. After this meeting, it has become one of the short time objectives. This is a challenging task; Ideally, the framework developer part of me wants a layer that is independent of the rendering engine. That has a major problem - by doing so I'll probably have to settle with the least common denominator of all engine's functionalities, something that the other me - the one that has to implement each and every one of my client's requests - absolutely don't want to happen.

So I'll probably start by focusing on a good charting engine to pose as an alternative to jfreechart. Sure, there's support to open flash charts, but it's functionality is yet a bit limited. It just doesn't do what I want/need it to do.

I'm more inclined into one of this options (suggestions are welcome here):

Flot
FusionCharts
Something else...

I would love NOT to use a flash-based solution. I'd rather use a canvas-based solution. But I won't even think about any of this technical constraints if I need to loose anything in the functionality side...

Cya in Barcelona next week!

CBF - Going underground

2009-09-03T17:25:00.004+01:00

If you don't know what CBF is, don't worry. Hardly no one does. It's probably the most under-rated community project on pentaho.

CBF stands for Community Build Framework, and it's an alternate way to build your own pentaho distribution keeping the source untouched. Also simplifies upgrades, deploys and allows everything to sit in a well contained svn repository.

I still use on a daily basis what I started more than two years go in one of my first forum posts. And I can't imagine my life without it. Actually, I don't know how other manage their projects. It really amuses me to see how the thread with my tutorial for a datepicker in the secure-prompt filter is still heavily active after almost 2 years while CBF releases go almost unnoticed. C'mon, people still use .xactions?!

nicola:pentaho$ ls -1 -d project-* | wc -l
34

This listing on my project directory tells me that I have 34 different projects and variations that range from pentaho 1.7 to pentaho 3.5, stock or improved. Projects that I need to get up and running very quickly.

Here's a snippet of my day in terms of CBF commands:

9:00 - Carry on with the development of Mozilla metrics project. A quick svn up will allow me to pick the changes done by the team in the US: ant -f build.xml.cbf-3.0 -Dproject=metrics -Denv=pedro dist-clean all run
10:30 - An old client, still on 1.7 called saying they changed an algorithm used to generate a report: ant -f build.xml.cbf-1.7 -Dproject=etsa -Denv=pedro dist-clean all run.
10:38 - Report fixed and commited to svn. Update on client side, done.
10:39 - Back to the Mozilla implementation: : ant -f build.xml.cbf-3.0 -Dproject=metrics -Denv=pedro dist-clean all run
17:50 - Developments ready to be deployed. Lots of new code and patches, so I'll do a full remote deploy: ant -f build.xml.cbf-3.0 -Dproject=metrics -Denv=staging deploy-all . Magically, my changes can be seen on the other side of the ocean
18:00 - Commit done, time to look at the pentaho 3.5 project. Build a bare35 project and just: ant -f build.xml.cbf-3.5 -Dproject=bare35 -Denv=pedro dist-clean all run .
18:40 - Detected a minor CDF bug in pentaho 3.5. Fixing in with: ant -f build.xml.cbf-3.5 -Dproject=cdf -Denv=pedro dist-clean all run .
18:50 - Commit done to google code. Hudson will auto-magically pick it and will be used in the next pentaho build. Time to go home

I admit that many times I have the feeling that I'm on grounds that noone ever stepped on, but what alternatives do I have to quickly manage multiple projects with multiple configuration on multiple versions?

A pentaho dashboard in 8 steps

2009-08-20T16:58:00.011+01:00

1. Open the CDF Dashboard Editor

2. Choose a template

3. Give it a title

4. Add a selector

5. Add a chart

6. Edit some properties

7. Save it

8. View it

This may be a very simple sample, but it's amazing that took me 1 minute to do something that used to take a good couple of hours (even more, before CDF existed)

"How do I make dashboards in Pentaho?"

2009-07-21T13:12:00.006+01:00

How do I make dashboards in Pentaho? How do I start using CDF?

This is one of the questions I hear the most, either in the pentaho forums or in the ##pentaho irc channel in FreeNode.

To help answering that, the CDF team worked on a set of 11 samples in a step-by-step tutorial approach.

Here are some images:

You can get more details here or buy the samples directly in here.

Thanks in advance for the contribution.

Pentaho WebEx on CDF - July 15th

2009-07-13T17:44:00.004+01:00

Trying to bring development closer to the users, Pentaho has been organizing web meeting every two weeks to talk about something related to ongoing/future development.

After James Dixon, Will Gorman, Matt Casters and Thomas Morgner, it's my turn to give a webex on CDF (damn, what a honor!)

You can get more details Here

On the forge: CDF Training Material and Samples

2009-06-26T14:48:00.002+01:00

With CDF's inclusion in pentaho, the user base has increased a lot. And I'm absolutely sure that the vast majority of the users that download Pentaho have no idea of what they can do with it.

Looking back to 1 or 2 years ago the improvements have been huge; We can now develop Pentaho dashboards in 1/10th of the time, being able to focus on content, and not in technology.

Still, I have to admit that it's still a steep learning curve to a new users.

We are working on a set of samples/tutorials to make it easier for new users to understand how it works - from simple inputs to sparklines in dashboards.

Our goal is have it ready in the next month; Not sure how we'll distribute it - probably with webclasses or something like that - but we'll decide it when it's finished.

In the meanwhile, here's a screenshot of the work in progress, where tutorial itself is a CDF dashboard

Firefox breaks 100 Million users daily or The happiest bug report of my life

2009-06-11T10:49:00.005+01:00

It's amazing - Firefox broke the barrier of 100 million active daily users for the first time yesterday. 101,229,960 to be more precise. This is especially motivating with Firefox 3.5 just around the corner. Be sure not to miss it!

Just a few hours after GMT's midnight we got this little surprise in our email boxes (it still puzzles me a bit to think that the guys in the US get the report of the day before their day is actually over; It would be much simpler if the world was flat).

This immediately led to a bug report. This number is sooo big that we didn't properly account for it when we built the report:

But I must say it's the happiest bug ever!

ps: On a somewhat unrelated note, we have to take our hats to the extensible power of pentaho's jfreereport; We were able to implement sparklines and a email repository to the engine so that all images / resources are sent as attachments, not requiring connection to the server - not forgetting anti-aliasing support, to make it look a little nicer. Thanks a lot for all the help Thomas

A very busy month

2009-06-05T10:28:00.002+01:00

It stresses me just to think about it. Only one month till the end of the quarter and still a lot of objectives in the to-do list.

In this month, and following my previous post, our team will spend a good chunk of time extending CDF's functionalities:

Continue different charting widgets

Start building a centralized point for data access, that will allow to get data from several sources - mdx, sql and kettle (eventually metadata). One of the first goals is allow to merge the resultsets of 2 different datasources and be able to visualize / compare them in the same chart.

Start the foundations for a CDF Dashboard Generator / Designer / whatever.

All our experience so far led us to a lot of dashboarding implementation as a way to tie together the different pentaho components into the final solution, not only in OEM scenarios (where this is absolutely critical) but also for standard implementations.

And we need to continue improving in order to make it run better - and implement faster.

In summary, I need huge amounts of coffee

CDF Charting Enhancements

2009-06-01T10:47:00.005+01:00

The more I work with dashboards, the more I know there's a lot to learn; If you follow the excellent work of Edward Tufte you know there's much more to visualization than the technical aspects of it.

A balance needs to be achieved; It's as wrong to focus only on eye-candy as is to build a too technical dashboard for users that will feel scared about it.

At Webdetails we've been getting very interesting conclusions from the projects we have with our clients, namely the Mozilla Metrics project, where we're implementing a full scale Dashboard centric Pentaho implementation.

Side note: maybe it's just our experience, but our clients have been much interested in dashboard centric projects; All of them make extended use of CDF in it's standalone mode (that is, without the user console). The user console has been used more to specific usage rather than the common use. This is the main reason that makes us develop and extend CDF.

On the Mozilla metrics project we started with specific by-subject analysis and then evolved to a set of summary dashboards that allow dynamic drilling, slicing, focus and allow the users to get to any information they want visually, optionally getting the raw numbers with a single click.

Recently we've reached an interesting conclusion; as much as we can develop a full-featured all-in-one dashboard, some users may feel more comfortable with dashboards that show less information but more context; We've learned that, when designing dashboards, we should focus on users first and data next.

So this is the way CDF is evolving now:

Increase usability - Make users feel more comfortable

Increase development speed

We're currently giving our first steps in the first one, and till the end of this month we'll be working hard on this 2 subjects, hopefully trying to nail both of them at the same time - let's see if we can make it.

Our first stab into the first goal is extending charting functionality, allowing users to do perform certain operations while giving it some eye-candy. The picture above shows a typical bar-chart generated by the jFreeChart CDF component; A lot of
people in the pentaho community don't like this rendering engine a lot; while it's true that it doesn't look that good, the truth is that open flash charts is not a very matured library and we miss some core functionalities.

For the record, we're also working on a OFC CDF component; our objective is to get to a CDF component that is independent of the rendering engine - jfreechart, OFC, libsparklines, flot, whatever. But this requires a big leveraging effort in order to maintain the core functionality we expect from dashboard components.

Anyway, back to the subject; When we make a bar chart plot like the above one, there are clearly some type of operations that can be done regardless of the data displayed: get the details, looking at the data, etc. So we implemented a set of (extensible) operation that we can access while doing a mouse hover:

So far, we've implemented data details, zoom, export to excel and CSV and switch to bar/pie view, but we can make this list grow/change as desired.

This an example of exporting the data to excel regardless of the data source - MDX or SQL

Totals and Grand Totals in the bottom. hum.... why?

2009-05-09T20:59:00.005+01:00

Ever since I see reports, I see the totals in the bottom, and the grand totals as the very last line in them. But if you get to think about it, makes absolutely no sense at all.

Almost everyone that picks a report in the first time wants to see the big numbers first, and only later see the details. And this happens due to technical reasons.

Most reporting engines use running formulas to do the calculations and present the totals in the end. If you ever tried to display the totals in the header of the report, you'll know it's a nightmare; It's either by using complicated sql inner queries or if you do it on the reporting engine side you'll have a complicated logic to maintain.

On some conditions, though, there's a very easy way to do this. The answer is MDX (am I becoming repetitive? :) ) Just try this mdx query in the steelwheels schema:

select NON EMPTY {[Time].[All Years].[2003], [Time].[All Years].[2004], [Time].[All Years].[2005]} ON COLUMNS,
NON EMPTY Descendants([Product].[All Products], [Product].[Vendor], SELF_AND_BEFORE) ON ROWS
from [SteelWheelsSales]

This query, that uses the very useful Descendants function with the SELF_AND_BEFORE attribute, returns the following result:

If we just use this query in the report designer, we'll get a nice report with the totals in the front of the details.

Ah, and notice that with mdx we also get crosstabs for free

A Dashboard for every role

2009-04-14T12:50:00.008+01:00

Common scenario in a project:

We need to make a dashboard for the General Manager.
And then a dashboard for the EMEA Sales representative.
Don't forget the Portuguese Chef, he also needs information on his stocks of codfish.

This means a lot of work; What if we could define just one dashboard without worrying about row level security?

Fortunately for this blog entry, we can. Provided you're using CDF - and of course you are - the answer is to use MDX.

MDX is God's gift to business language; When God created Adam and Eve he just spoke [Humanity].[All Members].Children . That's how powerful MDX is. And Julian Hyde allowed to use it without being bound to microsoft.

In a lot of CDF components we can use MDX directly to get our result sets. But there's a somewhat "hidden" feature in there. If we have the session variable role defined, that role will be passed to mondrian.

Take this for example: Let's picture our Joe as a EMEA representative and our Suzy for US. I'll add the following code to the SteelWheels schema:



<role name="suzy">
<schemagrant access="none">
    <cubegrant cube="SteelWheelsSales" access="all">
        <hierarchygrant hierarchy="Markets" access="custom" 
          toplevel="[Markets].[Territory]" rolluppolicy="partial">
            <membergrant member="[Markets].[All Markets].[NA]" access="all">
        </membergrant>
    </hierarchygrant>
</cubegrant>
</schemagrant>
<role name="joe">
<schemagrant access="none">
    <cubegrant cube="SteelWheelsSales" access="all">
        <hierarchygrant hierarchy="Markets" access="custom" 
          toplevel="[Markets].[Territory]" rolluppolicy="partial">
            <membergrant member="[Markets].[All Markets].[EMEA]" access="all">
        </membergrant>
    </hierarchygrant>
</cubegrant>
</schemagrant>
</role>

I also changed the startup action sequence session-region-list.xaction to store in the username in the session variable role. As a result, here's the MetaLayer home dashboard seen by Joe:

And the same one seen by Suzy:

This is the most simplistic approach; Other things must be taken into account, but really eases the way it works as a whole. Added bonus, all our pivot tables will share the same definitions.
I definitely recommend MDX for every dashboard (or report... or anything, really)

Revenues of open source contributions

2009-04-08T11:38:00.002+01:00

A lot of people ask me:

Why do you waste so many time - and money - with community contributions?

I don't have a definite answer; It's not by some "revolutionary principle", in the end I want the same thing as everyone - get rich, retire, and move to some beach in Brazil and sell coconuts for the rest of my life.

There are a lot of logical reasons why I should *not* contribute:

It requires huge ammount of time to maintain a project / make contributions
I'm giving away knowledge - for free
I'm allowing others to make profit out of my work with 0 return
Could basically... sell it / commercialize it in some way

I don't believe in arguments like "if everyone does like you do, the work will be a better place" - most of the users out there are just "leechers" that when it comes to information sharing just pretend to be busy (and I bow to the exceptions that makes the engines run).

Still - I do it cause I feel it's the right thing to do. For a living, I benefit from the work others did - namely the work done by Pentaho guys. Commercial Open Source is still a relatively unknown area and there's a difficult balance between knowledge-sharing and kid-feeding ;)

But sometimes we get some very unexpected revenues; I had a meeting with a potential client that asked me if we could make a demonstration of our work; I was taken by surprise and was not ready to do so, but then I remembered asking if they had a downloaded version of Pentaho version 3. They did. "Great, now open the CDF samples under the bi-developers solution. We did that". Quoting mastercard... Priceless!

ps: I believe this kind of permanent debate inside me is exactly the same as happens daily in the Pentaho Towers between sales and marketing and o-s defenders; So I'm definitely not one of the voices that rises in complains when Pentaho HQ (or any other vendor, for what it matters) decides to relase some subscription module; what ever the decision is, it's a hell of a tough balance to make...

BSHExpressions - when we need control over reporting

2009-04-01T17:11:00.005+01:00

Following pentaho forums is a great way to find topics for a blog. This time I used Gunter's question.

I need to make a simple report that show the duration of an event and sum it per day. The type of duration in the database is time (MySQL).

I have tried summing the duration values, but apparently the ItemSumFunction cannot handle the summing. So i converted the time to an integer, made the sum using the ItemSumFunctio, but now i don't know how i can reformat it to show like 'hh:mm:ss'

I'm a very big fan of the latest approach; I think that using longs in the fact table just simplifies everything. Then we only need to solve the formatting aspect of it. Since it's a report we're talking about, we'll use a BSHExpression. (JFreeReport is awesome, there's always more than one solution available for each problem, this is just one of them).

We can't use a simple date formatter because we want dates also over 24h. So I created the following BSHExpression:

import java.text.SimpleDateFormat;
SimpleDateFormat formatter = new SimpleDateFormat("mm:ss");

String secondsToFormattedDate(n){
d = new Date(n * 1000);
return ( d.getTime()/3600000 + ":" + formatter.format(d));
}

String getValue(){
long l = dataRow.get("CategoryAmountExpression").longValue();
return secondsToFormattedDate(l);
}

There are a lot of options to do this code, obviously; we could easily avoid the date object, but it's a way to show how we can invoke other classes.

The final result of this is what we expected:

Interesting OLAP date crosstab question

2009-03-28T14:36:00.008Z

Tom barber came up with a very interesting question in pentaho forums:

I've got a sales per working day spreadsheet that pentaho generates every morning for my boss, days of the month on the columns and branches on the rows.

The problem is that with the query he was using originally, the columns expanded a lot, like this sample on the steel-wheels database:

select Descendants([Time], [Time].[Months], SELF_AND_BEFORE) ON COLUMNS,
[Markets].Children ON ROWS
from [SteelWheels Sales] where [Measures].[Sales]

His original approach was to use 12 subreports - one for each month, but I'd personally rather stab myself than to go through there. I tried to solve going through another route, by "crosstabing" months and days (on my steelwheels sample, years and months).

I added a new "utility dimension" in my schema, containing only the months:

By using this dimension, I can modify my query to the following:

select [Time2].Children ON COLUMNS,
([Markets].Children * [Time].Children) ON ROWS
from [SteelWheelsSales] where [Measures].[Sales]

And get a much more legible result, either to analyze on the pivot table or to put in a report

A start like any other

2009-03-28T02:59:00.004Z

About 2 years ago, when I started to work with pentaho I made a promise to myself:

If one day I get to know at least half this guy knows, I'll also start a blog to share my findings

Guess it's time to collect the debt...

Pedro Alves on Business Intelligence

CDA release - 12.01.26

CCC release - 12.01.25

Firefox Telemetry - From adhoc R analysis to CDE Dashboards

Multiple parameters in CDF / CDE

[RFC] CDC - Community Distributed Cache

Back to basics: Step by step Pentaho + Ctools installation

Substance AND Style

Introducing Add-Ins in CTools

CDA v11.11.23 available

CTools - Rapid release cycle

CTools moved to GitHub

CTools Training: Brasil

CTools training: Australia

Multidimensional support in CCC

Pentaho Ctools versioning with GIT

Dashboardland - new Ctools sample

Kimball is getting old

5 days, 5 dashboards, 5 websites - 5/5

5 days, 5 dashboards, 5 websites - 4/5

5 days, 5 dashboards, 5 websites - 3/5

5 days, 5 dashboards, 5 websites - 2/5

5 days, 5 dashboards, 5 websites - 1/5

Accented (international) characters in pentaho user console

Elasticsearch, Kettle and the CTools

Pentaho Data Integration 4 Cookbook available

CCC charts in CDF

CTools training in the US

CTools installer - Making things *fast*

CDE + Ace Editor

CTools training in Brazil!

CDA - Export to HTML

CDF / C** Training Session - Do you have what it takes?

Mozilla Community Metrics - Technical challenges

CDA datasources in PRD

Firefox 4, Twitter and NoSQL Elasticsearch

CCC Community Contributions: Heatmap

Sexy pentaho dashboards? Showbiz!

Protovis component in CDE

Revenues of open source contributions - Part II

Merry Christmas everyone

Connected to Mozilla - 2010

Saiku - the olap analytics tools everyone's waiting for

CDE 1.0-RC3 available

CDE 1.0-RC2 ready for test

Pentaho dashboards on android and iphone

CST - Community Startup Tabs

CST - Community Startup Tabs

CDE 1.0-RC1 available for download!

CCC - Community Chart Components

Pentaho Community Gathering - Talks

Pentaho Community Gathering - Portugal. Sep 24

Hey Pentaho Community! How are you doing?

Better reports on dashboards

Persistence options in CDF dashboards

A different dashboard

FirefoxCup - Let the world cup begin!

It's time! CDA 1.0 is available

Coming soon: CDA 1.0

Pentaho reports by email - with steroids

Pentaho 3.2 Data Integration: Beginner's Guide (not after reading the book)

Pentaho Community WebEx - Slides and recording

Pentaho Community WebEx - CDA. April 21st

Pentaho, CDF, CDA, , Protovis

Pentaho Analysis Tools - When all the pieces come together

Sneak peak into the... present

Interacting with the TableComponent

CDA - Community Data Access

Merry Christmas and a Happy New Year!

Bringing Pentaho to Firefox

Building Pentaho 3.5 with the latest and greatest Reporting Engine

CDF - Dashboard Editor. Version 0.2

Pentaho / Lucidera - an excuse for the big questions?

Pentaho Reporting Explained

CDF - Dashboard Editor. Want a test drive?

Interacting with users: The CDF CommentsComponent

Pentaho Reporting - The book (part 2)

Pentaho Solutions - The book

- "I want charts!" -"Flash, image or canvas?" -"Say again?!"

CBF - Going underground

CTools installer - Making things fast