Saiku UI Enhancements and new Excel export

Hi everybody. As promised we've some chestnuts that are ready to be eaten and here we are! We decided to start working on one of the major pieces of the Pentaho CE suite: the newest OLAP client Saiku. We have some enhancement in our pipeline to help the Trout, Paul and the other guys of the crew; this is the first of the list. We decided to start with two enhancements that makes all more beautiful to our customers eyes:

1) Improved columns header management
2) New and improved Excel export functionality

Improved columns header management

A thing that I totally don't like in Saiku was the way it managed the columns headers any time we had repeated values on it. You can see a sample of this old behavior in the figure below.

Now the things are totally different. With a little UI improvement column headers with repeated values are grouped together as displayed in the picture below to have better visualization of your data.

New Excel export

Here we have completely rewritten the export module using Apache POI instead of JExcelAPI. This gives us more power and more feature that could be useful in the future to enhance the functionality. The look and feel now is more elegant and exactly equal to what the user is seeing in the user interface. You can see a sample of the new output below

This is a first release beta release of these features so be careful if you decide to use it. Any feedback about how they work and what you would like to have next is fully appreciated.

Where you can find it

All the code is committed to the GitHub Serasoft repository. You can find it here. We're planning the installation of an Hudson server on our servers in the cloud so that you can pickup anything already compiled so be patient. At the moment you can only grab the sources and compile it. Remember that our code is always aligned with all the new functionalities of the standard Saiku distribution. So what else? Stay tuned for the next things that are going to be released...

Setup Kettle debugging in 2 minutes

Sometimes it could be very interesting to be able to debug something to understand better how it works from an internal standpoint. My experience says that this is the case with the majority the opensource projects: sooner or later this is will happen.

Today that happened with Kettle trying to better understand if my assumptions were correct.

So how can we easily debug Kettle? The answer is very easy is: use remote debugging. I'll explain everything in a minute. The development tool I'm using is IntelliJ IDEA but it is fairly simple to set up everything with Eclipse and Netbeans.

Setup Kettle to enable remote debugging

1) If you're on Windows, open Spoon.bat and add the following line

 set JAVA_OPTS=-Xdebug -Xrunjdwp:transport=dt_socket,address=8000,server=y,suspend=n  

The same on Linux will be to open Spoon.sh and add the following line

 export JAVA_OPTS="-Xdebug -Xrunjdwp:transport=dt_socket,address=8000,server=y,suspend=n"  

I've used the port 8000 as the socket port number at which the debugger attaches but feel free to use the one you prefer.

2) Go to the last line of Spoon .bat or Spoon.sh and update that last line as follow

Linux (Spoon.sh):

 $JAVA_BIN $JAVA_OPTS $OPT $STARTUP -lib $LIBPATH "${1+$@}"  

Windows (Spoon.bat):

 start javaw %JAVA_OPTS% %OPT% -jar launcher\launcher.jar -lib %LIBSPATH% %_cmdline%  

Configure IntelliJ IDE for remote debugging

1) Configure a project for Kettle source tree
2) Choose Build > Edit configurations from the menu
3) The Run/Debug Configurations dialog opens. Choose Add New Configuration (the + sign in the upper left corner) and select Remote from the Add New Configuration list

3) The Remote Configuration dialog opens

Configure Transport and Debugger mode choices as displayed in the image above. The set the Host address (localhost in this case or wherever you need) and the port (8000 in my case) and press Ok

And now let's dance...

So now start Kettle. As soon as Kettle started load you job or transformation and, before running it, launch the debugger from your IDE. As soon as the debugger started you're ready to launch your job/transformation and begin your investigation. The advantage of this approach is the ability to debug also remotely running kettle instances and that is useful sometimes in helping to investigate problems.

Have fun playing with your Kettle and see you the next article.

CDF internationalization early access release

Today I finally completed the first implementation of the CDF Internationalization support. It is my first big effort to help in CDF and I'm proud I had the opportunity to work on a so important missing feature.
This is a first release, a sort of early access release, and it is available after building the framework sources from the repository trunk. I hope anyone can share ideas or suggestions on this feature helping making it better.

How does it works
CDF internationalization uses a jQuery i18n plugin to handle internationalized messages. The plugin support has been wrapped in the CDF framework so that the user never had to bother about jQuery specifics but will use the support CDF gives for that. Everything rounds about resource message files as usual in the java platform.

To create resources files we need to create  a file for each locale our dashboard is going to support. These files are named <name>.properties, or <name>_<language>.properties or <name>_<language>_<country>.properties. For instance a resource bundle for UK will be MessagesBundle_en_GB.properties. In case the message file will not be present the framework support will display the default message string and you'll get a warning in the bi server message console (nothing to be worried about but it would be better to have the required missing resource file).
The <language> argument is a valid ISO Language Code. These codes are the lower-case, two-letter codes as defined by ISO-639. You can find a full list of these codes at a number of sites, such as: http://www.loc.gov/standards/iso639-2/englangn.html.
The <country> argument is a valid ISO Country Code. These codes are the upper-case, two-letter codes as defined by ISO-3166. You can find a full list of these codes at a number of sites, such as: http://www.iso.ch/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1.html.

How resource bundles are managed in CDF
We have two level of messages files in CDF

• Global message files: located in <biserver_home>/pentaho_solutions/system/pentaho_cdf/resources/languages and useful  when used in dashboard global templates.
• Dashboard specific message files: located in <dashboard_home> they can be distributed together with single dashboard in a sort of "package".  To enable Dashboard specific message files support we've to add a <messages> element to the xcdf file configuration whose value is the base name of the dashboard specific message file.

The CDF.i18n tag: internationalization support in HTML dashboard templates
To use the internationalization support in dashboards HTML templates we implemented a tag to use to get the message string from the resource bundle and use it where required in the HTML template file. This is useful for descriptions, annotations, generic labels and similar.

We can use the CDF.i18n tag using the following syntax:

CDF.i18n(<message_key>)


where <message_key> is the message key located in the message resource bundle files.

Internationalization support in CDF components
Because of the early preview stage of this first release, for the moment, only the jFreechart component supports internationalization. In this case the support applies to the chart's title attribute.
To support an internationalized title in a JFreechart component we added a new titleKey metadata attribute whose value is the message key for the chart title we can found in the resource bundle. A complete internationalization support for all the CDF components (where needed) will come shortly so be patient.

And here we go...
For anyone who want to have a first look on this, in the bi-developer solution you have in the repository trunk has a fully working example you can have a look and experiment with. Below two screenshots of the sample dashboard I made internationalized in the Italian language. I hope you enjoy playing with this first release and stay tuned here for the next updates from the field.

Easily build BI Server from behind an NTLM proxy

Pentaho projects bases on Apache IVY to manage project's dependencies. This is good because it gives you a consistent and uniform way to manage projects dependencies but gives you some troubles if you need to make a build of one of the many Pentaho projects from behind an NTLM firewall.

So here it is a brief and concise survival guide about how to do that quickly and efficiently.

Install a local proxy in you development computer

First of all download cntlm a local proxy that let you easily authenticate through your company or customer NTLM authentication proxy. You've versions for Windows and Linux. I learned about it two years ago when I was on Linux and I had to manage some Maven projects. Now I'm on Windows and the good for me is that it has a version also for this platform (I tried using also ntlmaps but I wasn't able to have it working properly with IVY)

Installation is really simple but, on WIndows, has a tweak we will talk about later. Download the .zip from sourceforge and unzip it wherever is fine for you. Execute the setup.bat and it will rapidly install the local proxy as a Windows Service.

Go to the installation directory (typically C:\Program Files\cntlm) and edit cntlm.ini. Replace the informations for username, password, domain and your proxies hosts and ports as detailed in the file excerpt given below

#
# Cntlm Authentication Proxy Configuration
#
# NOTE: all values are parsed literally, do NOT escape spaces,
# do not quote. Use 0600 perms if you use plaintext password.
#

Username        <your_username>
Domain          <your_domain>
Password        <your_password>   # Use hashes instead (-H)
#Workstation    netbios_hostname        # Should be auto-guessed

Proxy           <1st_proxy_host>:<1st_proxy_port>
Proxy           <2nd_proxy_host>:<2nd_proxy_port>

#
# This is the port number where Cntlm will listen
#
Listen                3128

Make the changes and save the file. Now, before starting the local proxy service, here is the tweak. In my case, the setup.bat script forget to set a service startup parameter that sets the cntlm configuration parameter file location. So to workaround that, open your registry and go to the following key HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\services\cntlm\Parameters. Look for the AppArgs attribute and modify it adding the parameter -i "C:\Program Files\Cntlm\cntlm.ini" as shown below

After that you're ready to start you service successfully otherwise you'll get an error.

Back to our Pentaho's project build

In my case I was making a build of the newest bi-server 3.6 get from the related branch of Pentaho repository (http://source.pentaho.org/svnroot/bi-platform-v2/branches/3.6). To enable IVY to use your locally configured authentication proxy server you've to set the following environment variable before starting compiling the project

ANT_OPTS=-Dhttp.proxyHost=localhost -Dhttp.proxyPort=3128

It is up to you to decide where to set that variable either as a Windows environment variable or from within a command line session (if you're starting the build manually).

And what about for the Linux users....

If you're on Linux you can install cntlm using the package manager of your favorite distro. I used it on Ubuntu and Fedora withou any problem. The configuration file is the same as before but is located in /etc/cntlm.conf. You've to change the same parameters detailed above for cntlm.ini (the file is exactly the same). Start the daemon, set the ANT_OPTS environment variable and have fun with your build.