Sunday, April 26, 2009

Pentaho: "Guided Ad hoc" , "Structured Ad hoc" , Parameterized Reporting...which one already!

(This is how it would be done in Pentaho 3.0. With the introduction of 3.5 and the new Pentaho Report Designer, there are even easier ways to achieve this. )

This blog entry is an introduction to a video which highlights the fundamentals of creating a Pentaho "Guided Ad hoc", "Structured Ad hoc" or parameterized report using the Pentaho Report Designer and Pentaho Design Studio. Here you will find a Pentaho Wiki entry which has a bit more detail and also demonstrates a slightly older example.

One of the biggest barriers in adopting a Business Intelligence tool is ease of use. It is possible that if the BI tools are too difficult to use, then most business users will not use them and may resort back to the way they used to do things, MS Access and Excel. ;-) No really...if the tools are not being used to the best of their abilities, then the organization is not leveraging their investment in BI. Therefore they are not going to realize a return on investment for that solution.


"Guided Ad hoc", "Structured Ad hoc", advanced parameterized reporting...whatever you want to name it, it is a simple way for non-technical business users to create numerous combinations of reports, without the need to involve the IT department. It not only provides the ability to limit the desired result set, but can have added functionality to re-structure sort groups, change measures and even add user defined drill down paths. Oh, and if you are using the Pentaho BI Suite, then you even have the ability to add components for on demand distribution, alerting, collaboration, scheduling and more. All this integrated under one platform, designed on open standards, reducing IT dependency and providing complete flexibility and end user empowerment.

I like the term "Guided Ad hoc". I used the term when demonstrating similar functionality with my old company's software. I believe the terminology accurately describes the meaning of the tool quite well. "Guided", because it "guides" the business user to the answer they are looking for. "Ad hoc", because the tool is usually used "for a purpose". It makes report generation easy without sacrificing functionality and again, without IT involvement.

"Guided Ad hoc" is really nothing more than a fancy term for parameterized reporting. The concept is to provide an easy to use, familiar user interface for users to interact with. If the business users are familiar with the web and have used eBay or either a banking or social networking web site; or better yet have shopped on-line, then they are already familiar with the concept of "Guided Ad hoc". They have seen the common form controls like text boxes and radio buttons, drop down lists, sliders, folder trees and pick lists. Using a combination of these form controls while providing them a simple navigation interface, allows them to simply pick what they want and how they want it. Almost 80% of reporting needs can be satisfied in this manner while at the same time requiring little to no training to use and understand.

The below video briefly demonstrates some simple fundamentals when creating a "Guided Ad hoc" report with the Pentaho Report Designer and Pentaho Design Studio. It also quickly demonstrates a completed version that I have built using the new Community Dashboard Framework which is now integrated with Pentaho 3.0

Just remember this, whether you call it "Guided Ad hoc", "Structured Ad hoc" or parameterized reporting, the result is always the same; it is easy to use and can be delivered using the Pentaho BI Suite.

Monday, April 20, 2009

The Continuation of the Unmatched Power and Flexibility of Pentaho

Continuing my blog post in regards to the Unmatched Power and Flexibility of the Pentaho BI Suite, I wanted to share another powerful component that I have been working on. I know you might be thinking this is a shameful attempt of Pentaho publicity, but sincerely it isn't. I am truly passionate about my job and I love to share what is possible so others can benefit from it. The Pentaho BI Suite allows a level of flexibility and customization that surpasses many BI frameworks simply because of its Action Sequence architecture. The Pentaho BI Platform architecture allows easy integration with many 3rd party components and frameworks. It allows you to step outside of the box while still utilizing open standards and common skill sets, something that may be hard to achieve with proprietary BI software.

Please note that this is one possible way of how something can be represented.

There are a few ways you can visualize change of data over time, but most commonly used for this is the Line Graph. A Line Graph can be described as a visual representation of connecting data points or sets of data points that have been collected over a period of time. The most useful benefits to a Line Graph are recognizing trends over time as well as observing the rate of change (slope) more clearly. Whether you are plotting sales figures or unique page views, what a Line Graph can tell you depends on the individual reading it. However, when wanting to view this data over long periods of time there are some creative ways that it can be represented.

I have chosen to represent large amounts of data over time by integrating the LGPL 2.1 Chronoscope Timepedia widget into the Pentaho BI Platform. I created a reusable, customizable component that can be easily added to Pentaho Dashboards by non-technical users. This dynamic widget can visually represent metrics over large periods of time in an interactive, browser only, Time Series Line Graph.

The idea was introduced to me by a colleague, Zachary Zeus from Sydney Australia. Originally it was structured as a JSP page which would execute a Pentaho Action Sequence and generate a JSON data string to feed the Timepedia Chronoscope Widget. This was a great idea, but I needed to enhance the capability to make it easy to use, include and modify.

Utilizing the flexible nature of the Pentaho architecture, I was able to reconstruct the example to be completely self contained in the Pentaho Solution Repository. This eliminated the need to setup a separate web application path, JSPs and any external web references needed by the component. It can be easily executed from the Pentaho User Console and added as a component to the Pentaho Dashboard Designer with full parametrization. With the added parametrization capability in the Pentaho Dashboard Designer, all filters and metric variables can be exposed so a non-technical user can customize the component to meet their need.

Because this component is utilizing the Pentaho architecture, my data can be retrieved from almost any data source. I could use a SQL query, RDBMS stored procedure, Pentaho Metadata Query, Mondrian Analysis MDX statement, XQuery, or even a call to Pentaho Data Integration (ETL) with no changes to my underlying application.

Here is a short list of the Value and Benefits to this component -
  • LGPL 2.1
  • No software licensing fees (had to get that in there)
  • Can be added to a Pentaho Dashboard
  • Can be executed from the Pentaho Solution Browser
  • Completely Pentaho Solution Repository based
  • Reusable
  • Easily customizable
  • Provides Parametrization for changing metrics, date range filters, vertical and horizontal ranges

For information about the TimePedia Chronoscope Widget itself see the links below:

Please note that I am working with Pentaho Product Management to get all the samples I create included in to the Pentaho BI Platform. If you are interested in becoming a Pentaho Subscription customer, or just want to know about what is possible with Pentaho, please feel free to contact me.

Sunday, April 5, 2009

Pentaho: Unmatched Power and Flexibility

With each passing day I am continually amazed at the power and flexibility of the Pentaho BI Suite and its architecture. With the Pentaho Dashboard Designer, found in the new Pentaho Enterprise Edition 3.0, I was able to add the capability for even the non-technical business user to utilize "dynamic" Google mapping mash-ups. "Dynamic" meaning that the map displayed and its points, are based off of what the user selects from the input screen. These dynamic Google Map objects can be created as parameterized templates for the business users to add, layout and interact with - in their own Pentaho Dashboards.

Google Maps has a powerful Geocoding service that Pentaho can seamlessly pass data to without any coding. I have always stated that one of the differentiators in our stack is the ability to easily integrate results from Pentaho Data Integration in to Pentaho Reporting, Analysis and Dashboards. This holds true even when using Google Maps. With Pentaho Data Integration and its HTTP transformation entry, I am able to pass user input such as address, city and state to the Google Maps Geocoder web service and return the appropriate coordinates to be mapped. This PDI transformation can be called from one of Pentaho's work-flow objects and pass back the results to another component without any coding. Now I am sure there maybe a number of ways that this can be achieved with other methods, but the differentiator here is that there wasn't any hard-coded complex coding involved. Due to the fact that our BI Platform is nicely integrated with Pentaho Data Integration, the approach is reusable for and callable from other applications and objects with little to no modifications per application. Using open standards is a key when creating any type of application. Another differentiator in the Pentaho architecture. I believe the founders of the Pentaho BI Platform and those of Data Integration (formerly Kettle) have much to be proud of when their decision to merge the two together came forth.

From just "one" parameterized Pentaho Action Sequence (a core Pentaho work-flow object) I am able to get the user's input, get the required data to be mapped (from almost any data source), send a call to the Geocoder service via PDI, and display the appropriate Google Maps center area along with the associated plotted data points. With Pentaho's service based architecture, I am able to easily change my data source and/or query on the fly without having to change any other layers in my application, which is a must when quickly deploying BI applications to the masses.

See the brief demonstration YouTube video here on the Pentaho YouTube channel that I created. YouTube's normal resolution can be a bit hard to see, so make sure to view the video in HD mode and select full screen to see the example fully. Just another example of many showing unmatched power and flexibility.

To learn more about Pentaho and see an "Introduction to the Pentaho Enterprise Edition" come see our new 5 minute introductory video on our home page at