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.



Fabio said...

ASTHONISHING!!! IMPOSSIBLE ULTIMATELLY IMPRESSIVE!!! oh-ow! How did you do that??? Do you sell those things? Can you even hint on how to build that? I just can't overstated how I just loved that video. That is what I want to do! Please, how do I do those ones??

Michael Tarallo said...

Ah! Yes. You must be speaking of the html UI I developed to execute the Pentaho parameterized report.

That UI was developed using some components from the Community Dashboard Framework. You can read more about it here:

The CDF had been integrated into the Pentaho BI Platform starting with version 3.0.

It is a component AJAX library, complete with samples and code mark-ups.

Everything from execution of Pentaho Action Sequneces, date pickers, dynamic/chained lists, dial controls and more.

An .html template in a solutions folder of the Pentaho Solution Repository is where you create the UI to interact with the Pentaho Action Sequences. It is standard DHTML, HTML, CSS, JavaScript etc.

Hope this helps.

In the default Pentaho Installation under the steel-wheels solution / Reports. There is an older version of Guided Ad hoc that uses the YUI tab component and a Pentaho html style template for the prompt facility.

Keep in mind that in the Enterprise Edition (non-community version) there is the integrated Dashboard Designer that has some of these capabilities. More and more features are added with each release. I am glad you liked it.

Fabio said...

Michael, it did help a lot, indeed! I've attended t a course on Pentaho Dashboard Creation with CDF. I can now figure out what've you done - it seems so terrible simple I itch to try and reproduce your UI!! ;-) Thank you very much! You pushed Pentaho to a whole new level for me!

stevie_bi said...

Thanks Michael,

Really a great example of reporting. It shows that Pentaho can match (by developers) the power of the commercial tools.

Thanks again,


John said...

can i get the source for the ad-hoc report ?

John said...

how can get the source for the ad-hoc report ?

Michael Tarallo said...
This comment has been removed by the author.
Michael Tarallo said...

You can get a sample example that has the basics here:

Rama said...

It is amazing to see the demonstration. I appreciate your efforts to put these things together and publish for the public. I am wondering what all the different Pentaho products that I need to install on my laptop to try out this demo? I am looking for a complete open source and community versions. Would appreciate if you can point me to the Pentaho products that I need to install.


Rama said...

Amazing examples and wonderful effort.

Can you please tell us what all different Pentaho products I need to install to try this out?

Bill W. said...

Mike, I have scoured forums, wiki, pre-sales info and many blogs looking for a dynamic MDX example. Do you have anything that shows something like (in message format)

select $(PARMS1) on COLUMNS,
from [My Monthly Sales]

Note that $(PARMS) may be one member or multiple members. Also, I am not sure what to put for the fields since there may be one or more columns that are selected.

surya said...

Hi Michael ,

Here is a scenario , Where my PUC has 2 clients(users)(non-admin roles) .

A user having admin role can only create a new adhoc data source , but both of my users are non-admin roles ..!!!

So is there any way to create a adhoc data sources by non-admin users ..?

if so can we achieve security for the created data sources...?
i.e, A can see its own datasource (created by itself)
B can see its own datasource

Can you please help


Michael Tarallo said...

Hello Surya - I no longer work for Pentaho. I work for QlikTech now as Senior Product Marketing Manager for QlikView and QlikView Expressor. I left Pentaho back in 2011. In regards to your question - I believe you are referring to the Pentaho User Console and its out of the box Ad hoc tools. You should post your question on the Pentaho community.

Stay well

Follow me: @mtarallo

surya said...

Thanks Michael ,keep going, will follow u & Pentaho community.