Friday, June 27, 2014

I've posted my articles for many years now on Blogger. While Blogger is fairly low maintenance, getting the formatting right for my articles was always a bit of a hassle. I used to write my articles on Google Docs and then copy and paste them to Blogger. I was quite disappointed when Google decided to axe the direct publishing feature from within Google Docs to Blogger a few years ago and the Blogger editor always seemed a bit limited in functionality to me.

Writing technical articles should be easy and I really shouldn't worry too much about formatting, so I decided to embrace Markdown full swing.

Thursday, June 12, 2014

On some occasions you might have to set a variable value dynamically in a job so that you can pass it on to the Execute SQL Script job entry in example. In this blog post we will take a look at how to create an integer representation of the date of 30 days ago. And we want to achieve this without using an additional transformation!

The way to achieve this in a simple fashion on the job level is to use the Evaluate JavaScript job entry [Pentaho Wiki]. While this job entry is not really intended to do this, it currently offers the easiest way to accomplish just this. Just add this job entry to your Kettle job and paste the following JavaScript:

date = new java.util.Date();
date.setDate(date.getDate()-30); //Go back 30 full days
var date_tk_30_days_ago = new java.text.SimpleDateFormat("yyyyMMdd").format(date);
parent_job.setVariable("VAR_DATE_TK_30_DAYS_AGO", date_tk_30_days_ago);
true; // remember that this job entry has to return true or false

To test this let's add a Log job entry:

Add this to the log message to the job entry settings:

The date 30 days ago was: ${VAR_DATE_TK_30_DAYS_AGO}

And then run the job. You should see something similar to this:

Certainly you could just pass the value as parameter from the command line to the job, but on some occasions it is more convenient to create the value dynamically inside the job.

Friday, May 30, 2014

Pentaho Dashboards CDE: Create your custom table

You want to implement something in your dashboard that is not covered by the out-of-the-box dashboard components? Luckily, with Pentaho CDE the world is open: CDE makes use of the standard web technologies (CSS, JavaScript, HTML), so theoretically you can implement whatever is in the realm of these technologies. Obviously you will need some basic knowledge of these technologies (setup is not as easy any more as filling out some config dialogs), but the possibilities are endless.
In this post I’ll briefly talk you through how to source some data and then to create a custom table with it (which you can easily do with one of the CDE components as well, but that’s not the point here … imagine what else you could do):

In CDE, register a Datasource. In example create a sql over sqlJndi datasource, provide a Name i.e. qry_generic_select, choose SampleData for JNDI and specify following query:

SELECT customername, customernumber, phone FROM customers

In the component section, add a Query Component. This component is most commonly used for displaying simple results, like one number in a dashboard (i.e. max temperature). Here we will use this component to retrieve a bigger result set.

Click on Advanced Properties.

For the Datasource property specify the datasource you created in step 1 (i.e. qry_generic_select)

Provide a name for the Result Var. This is the variable, which will hold the output data of your datasource.

Do a preview and make use of your browser’s developer tools to see the generated HTML:

Ok, now that this is working, let’s add some very basic design. Click on Settings in the main CDE menu:

Choose bootstrap from the Dashboard Type pull down menu:
Click Save.

Back to the Post Execution function of the Query Component: Now we want to make this a bit more dynamic: For every data row must be enclosed by <td> and within each data row each data value must be enclosed by <td>. We also have to add the <tbody> element to make a proper table. And we will apply the Bootstrap Striped Table design:

You can find a text version of this JavaScript code a bit further down as well in case you want to copy it.

Do a preview now and you will see that we have a basic table now:

Note: In case you are creating this dashboard as part of a Sparkl plugin and you are having troubles seeing the bootstrap styles applied (and are sure that the problem is not within your code), try to preview the dashboard from within your Sparkl project endpoint listing (which seems to work better for some unknown reason):

One important thing missing is the header. Let’s source this info now. The Query Component provides following useful functions, which you can access within Post Execution function:

this.metadata
this.queryInfo
this.resultset

To get an idea of what is exactly available with in the metadata object, you can use in example this function:

Voilá, our custom boostrap table is finished. This is not to say that you have to create a table this way in CDE: This was just an exercise to demonstrate a bit of the huge amount of flexibility that CDE offers. Take this as a starting point for something even better.

Wednesday, March 26, 2014

Quite inspired by the recent interesting webinar by Harris Ward (@hazamonzo) of Ivy Information Systems I prepared this short write-down of the core principles of using the Bootstrap framework with Pentaho CDE (which in essence summarize the main points of the webinar). I take that you are familiar with CDE, so I will not go into details on how to install it or on how to create a dashboard. This article only focuses on using Bootstrap with CDE.

Why Bootstrap

For a long time Blueprint had been the default framework for CDE. While it worked nicely on the desktop, the content didn’t quite adjusted as nicely to tablets and mobile phones.

As of recently CDE supports the Bootstrap framework. The good news is that you can apply Bootstrap to the selector components like buttons, drop down menus etc as well, hence pathing the way to a uniform look of your dashboard!

How to use it with CDE

Configure your CDE Dashboard to use Bootstrap

The very first thing to do is define the settings for your dashboard. In CDE click on the Settings button in the main menu:

In the settings screen provide a Name and then set the Dashboard Type to Bootstrap:

Click Save.

Generate your standard layout

Next generate your Layout Structure as usual, just keep the following in mind:

With Bootstrap, the total span size of the page is 12 columns (as opposed to Blueprint, which has 24 columns). This means that if your page has only one column, the span size should be 12. If your page has 2 columns, the span size should be 6 for each of them and so on (calc: 12 / no of cde columns).

Within each column nest an HTML element: This one will hold the Bootstrap HTML snippet. In the most simple form it will look like this:
Make sure to provide a name for the HTML object in the Properties panel (otherwise, the styles do not show up properly in the preview - at least in my case this happened).

Get code from Bootstrap website

The Bootstrapwebsite offers a wealth of components. These ones can be found by click in the main menu on either CSS or Components. Use the side panel menu to get detailed info about each component:

One component which you will most likely use is called Penals [reference]. Again, there is a wide choice of panels available, let’s take a look at the Penal with header:

Copy the last main div section - this is the one we want to base our layout on.

Amend Bootstrap HTML

You can use for example JSFiddle to simply amend the Bootstrap HTML snippet and get a preview of it.

Add an id attribute to the content div. This way we can later on reference it when we want to assign a chart component.

Delete the default content text.

Then click the Run button to get a preview:

We are happy with the preview, so let’s copy the HTML code.

Add amended Bootstrap HTML to CDE Layout Structure

When we initially created the Layout Structure in CDE, we nested an HTML element inside each column - just copy bootstrap HTML there. Choose the HTML element in the Layout Structure panel, then click on the HTML ellipsis [...] button in the Properties panel:

Finally paste the Bootstrap HTML snippet:

Click OK.

Save the dashboard now and do a preview:

Our Bootstrap panel is nicely rendered within the CDE dashboard now. It’s time to add some content!

Create your data sources

Business as usual - nothing special to consider. Just create your datasources as you would do it for any other dashboard.

Create your components

Overall business as usual, just keep in mind that you do not want to reference the HTML object the old way (so based on the elements you defined in the Layout Structure), but instead make use of the div ids defined within the Bootstrap HTML snippets (that you saved within the HTML element in the Layout Structure).

So for this article I quickly created a CCCBarChart component. The important bit about it is that I specified chart1for the HTMLObject property:

Note that this ties back to the div id attribute we defined in our Bootstrap HTML snippet [stored in the Layout Structure HTML element]:

So let’s do another preview:

So that is it: It turns out using Bootstrap with Pentaho CDE is actually fairly easy. This example was certainly extremely simple and only meant to give you a brief introduction.

Last Example

Let’s add a button:

Adjust the Layout Structure of your dashboard to make room for a button.

Visit the Bootstrap website to find out all about their fancy buttons: reference.

Let’s copy the HTML snippet for the standard button. Jump over to JSFiddle and adjust it until you are happy with the preview. Remember to add a dedicated id attribute.

Copy the HTML snippet again and paste it into the HTML properties field of your HTML element in the Layout Structure panel.

Save and Preview your dashboard.
Again, a very easy example. I guess now you are certainly interested in creating some more challenging dashboards with Pentaho CDE and Bootstrap!

And finally, a big thanks to Harris for providing this excellent webinar!

Friday, February 28, 2014

End of last year I decided it was time to ditch Ubuntu for a pure Gnome experience. I didn't quite fancy installing Gnome 3.10 on top of Ubuntu due to some user having problems with this. So Fedora looked like a good candidate. And having worked with it for nearly two months now, I must admit that I quite like the new clean Gnome interface (and Fedora). Gnome gets criticized everywhere, a lot of people don't have anything good to say about, but honestly to me it seems like the Gnome development is going into the right direction. But this of course a matter of personal taste. Enough on this, let's talk about Pentaho Kettle:

Quite often, Pentaho Kettle Spoon - the GUI for designing transformations and jobs - starts up just fine on Linux OSes. Sometimes though, there might be some dependencies to install or special flags to set.

When starting Pentaho Kettle on Fedora I came across this nasty error message:spoon.sh: line 166: 10487 Aborted (core dumped) "$_PENTAHO_JAVA"

On other systems I also got this error message:

The error was 'BadDrawable (invalid Pixmap or Window parameter)'.

(Details: serial 13561 error_code 9 request_code 62 minor_code 0)

(Note to programmers: normally, X errors are reported asynchronously;

that is, you will receive the error a while after causing it.

To debug your program, run it with the --sync command line

option to change this behavior. You can then get a meaningful

backtrace from your debugger if you break on the gdk_x_error() function.)

This advice was originally posted here and here and in this blog post. Also here: Source1 and Source2.Update 2014-07-20:It turns out on Fedora 20 you do have to install xulrunner, but not via yum. Victor Sosa provided some instructions on this Jira case which I copy here for reference:1) download the xulrunner 1.9.2 from here: http://ftp.mozilla.org/pub/mozilla.org/xulrunner/nightly/2012/03/2012-03-02-03-32-11-mozilla-1.9.2/xulrunner-1.9.2.28pre.en-US.linux-x86_64.tar.bz2. [ ... and copy it to a directory of your choice. Extract it.]2) change this line in the spoon.sh The only change you need is OPT="$OPT -Dorg.eclipse.swt.browser.DefaultType=mozilla -Dorg.eclipse.swt.browser.XULRunnerPath=/opt/xulrunner-1.9.2"
Did you get any other error messages when starting Spoon and found a solution for it? Please comment below and I'll add it to this blog post so that we have a good resource for trouble shooting.

Matt Casters:

FYI, the package to install on Ubuntu is usually libwebkitgtk-1.0-0 (as documented). I'm sure it's the same on Fedora. I would avoid all that xulrunner stuff if possible.For those of us on Kubuntu there are bugs in theme oxygen-gtk so best switch to another theme like Ambiance of turn off a bunch of fancy-shmancy animations with oxygen-settings.21/05/2014:If you're having Spoon problems on Linux/OSX after an upgrade, try upgrading swt.jar from http://archive.eclipse.org/eclipse/downloads/drops4/R-4.3.2-201402211700/

I am sure you can’t wait to have a look at this Kettle transformation, which has just been created for you … well, let’s go ahead and fire up Spoon and open up this file:

Still excited? Well, you didn’t expect everything to be done for you … did you? That’s why your special insider knowledge is still required here! But more about this later on.

Creating the dashboard

As you are probably aware of by now, Sparkl makes use of two very popular tools: CDE and Kettle. So if you have ever used CDE to create dashboards before, editing the Sparkl dashboard should be quite familiar to you!

Ok, let’s edit the dashboard:

We will use a very simple approach here (the aim here is not to create a fancy looking dashboard, but just some very simple prototype):

Create a row called passwordTextRow.

With passwordTextRow still marked, click on the Add HTML icon.

Mark the HTML row and add this HTML snippet on the right hand side:<p>Specify your new password:</p>

Then add a new row called passwordInputRow. For this row, add two columns, one called passwordInputColumn and the other one passwordSubmitColumn. The layout should now look like this:

Add a button: Others > Button Component. Call it passwordSubmitButton. For Label specify Submit and for HtmlObjectpasswordSubmitColumn (just press CTRL+Space to retrieve the values of the available HtmlObjects):

Add an Input field: Select > TextInput Component. Name it passwordTextInput, assign the ParameterpasswordParameter and the HtmlObjectpasswordInputColumn to it:

Now switch to the Datasource Panel. Remove the SQL dummy query.

From the left hand side open MYPASSWORDCHANGE Endpoints and choose mypasswordchangerendpoint Endpoint. For this datasource specify the name myPasswordChangerDS in the Properties section:

Switch back to the Components Panel. In the Components area select the Button Component. Click on Advanced Properties.

For Action Parameters specify passwordParameter as [["passwordParameter"],["passwordParameter"]] and

For Action Datasource specify myPasswordChangerDS. At the time of this writing there were considerations about moving the datasource property to main properties area (instead of advanced properties), so this might have changed by the time you read this.

For Listeners specify passwordParameter

Save the dashboard.

Let’s see how our Sparkl plugin looks so far. Choose Tools > MyPasswordChanger:And you should see something like this:

Preparing the Kettle transformation

You can find a detailed documentation of the REST endpoints here (download the doc file). Harris also wrote an excellent blog post about it.

Extract the documentation zip file (which you just download) and then open the index file in your favourite web browser.

Click on UserRoleDaoResource from the REST Resources list, then choose /userroledao/updatePassword:

Let’s inspect the request body element a bit closer, so click on the user link:

So this tells us that we can either send an XML or JSON document containing the userName and password to this REST endpoint.

Watch out for the REST call result message. Here we see that the password was changed successfully. You don’t believe it? Log out of PUC and back in (with the new password).

Ok, now we know that this is working. Let’s make everything a bit more dynamic:

username: CPK server side parameter (currently logged in user)

password: supplied by CDE dashboard

URL: IP and port

authentication username

authentication password

We will not test the transformation any more in Spoon.

How to pass a standard parameter from a dashboard to a Kettle job or transformation

As you remember, we defined earlier on the passwordParameter parameter in CDE. To pass the value of this parameter to a Kettle job or transformation, simply define the parameter in your job/transformation as you would normally do:

Open mypasswordchangerendpoint.ktr in Spoon.

Right click on the canvas and choose Transformation settings.

Specify the a new parameter called passwordParameter. As you might have guessed, this parameter name has to be exactly the same as defined in the dashboard:

Change the transformation to look like this one:Disable the hob from the original Generate Rows step to Add XML. Add a new Generate Rows and a Get Variables step. For now the newGenerate Rows should only supply the url and user values: Right now we just want to test if the password parameter is passed on properly. Open up the Get Variables step and create a new fields called password which references the variable ${passwordParameter}:This setup enables us to use the passwordParameter originating from the dashboard in our Kettle transformation stream.

Just for testing purposes write the password field to the log (so that we see that the value is actually passed on). Change the Write to Log step config respectively.

Server-side Parameters

Kettle Properties

As you might well know, Kettle transformations/jobs running on the server have access to the kettle.properties file as well as long as everything is properly configured. If you have everything installed on one machine and are using the default settings, there is actually no additional setup on the biserver side required. The biserver will automatically access ~/.kettle/kettle.properties.

So for the REST authentication (server IP and port) I just defined a parameter in the kettle.properties file like this:

the plugin system dir (full path, this isn't used very often though and it might become deprecated)

cpk.webapp.dir

webapp dir (full path)

cpk.session.username

session username

cpk.session.roles

session roles (string with session authorities separated by commas)

Marco Vala explains:

Additionally, if you add a parameter named

cpk.session.SOMETHING

CPK will try to find a session variable named SOMETHING and "injects" its current value

(this is one way only, just for reading session variables)

There is also a handy parameter named

cpk.executeAtStart

If its default value is set to "true", CPK will execute that Kettle when plugin starts

Amending our transformation

We have to implement following changes:

Remove any fields from the new Generate rows step. It should be blank:

Open up the Get Variables set and configure it as shown in the screenshot … add the username variable referencing cpk.session.username:

Adjust the Add XML step:

Change the config of the REST Client step to not read any more the URL from the field but instead key it into the URL config field like this:${VAR_PENTAHO_BISERVER_URL}/api/userroledao/updatePassword

Also, in the Authentication reference the parameters we just set up: ${VAR_PENTAHO_BISERVER_USER} and ${VAR_PENTAHO_BISERVER_PW} respectively.

Save and then restart the server.

Then test again the dashboard and watch the Tomcat log.

Oh well, oh well, all is not perfect right now … you wonder why we actually have to authenticate - this file is already on the server - right? Rest assured, Pedro’s team is already working on this feature!

So with the solution created so far every user should be able to change their password. The core functionality is provided, we will not cover the rest in this tutorial.

Closing words

This is certainly an ugly looking app, the password should not be shown, there has to be a confirmation page etc … basically a long list of tasks still to do. The point here was not to create a fully functional, nice looking Pentaho Sparkl app, but to build a bare bones prototype: Demonstrating how the two main components - Kettle and CTools - can work together; enabling us to create a wide variety of apps for the Pentaho BI-Server ecosystem. Go and explore this exciting new world now and create some fancy Sparkl apps!