Thursday, April 30, 2009

MQL Query DataTable

In this post, I will continue with some of the great things that you can do with Pentaho and YUI.

Do you have the need to add new fields to a table without writing code? By leveraging the Pentaho metadata services, it is easy.

First of all, grab the files off google code.
-MQLAction.html - This file can really be placed anywhere under the webapps/pentaho directory, but I recommend right under the pentaho directory. The html file just loades the MQLAction.js.
-MQLAction.js - The MQLAction.js file will dynamically load all the needed components using YUI Loader. The loader will get yui and non-yui files. It is a fabulous tool as it also grabs the yui css files.
-TaskNode.js - Create by the YAHOO team, this subclasses the TextNode and allows the handy checkbox feature.
-MetadataTree.js - The tree is formed by calling the Pentaho servlet AdhocWebService. If you update the metadata.xmi file on the platform this tree dynamically updates. For the purposes of this demo, I have hard-coded the solution of steel-wheels and the model BV_ORDERS. That can be changed or in other solutions, I have a model drop-down list.
-MetadataTable.js - The table takes the selected nodes from the tree and calles the mqlAction.xaction to get the results in JSON format.
-DataAction.jsp - The is a utility servlet that wraps any Pentaho xaction with JSON, html, or text. In this case we use JSON for easy JavaScript usage.
-mqlAction.xaction - Generic xaction that will run whatever bit of MQL that is passed to it. It can handle selections, conditions, and orders. the solution, model and distinct are also passed as parameters.

Next, go get the yui platform and ensure it is in webapps/pentaho/yui

If you are able to use these file or have anothe approach for dynamic reporting, let me know.

At this time, you cannot save the datatable, but there is always tomorrow.

Tuesday, April 21, 2009

Reporting Architectures Take Two

When I wrote this whitepaper, I thought I was already a bit behind the times. Certainly after all of the years of building data warehouses and data marts, people would not go back to reporting off of replicated databases would they? As it turns out, yes they do. To my amazement, seven years later my clients still struggle with the same issues.

So how can this happen? As far as I can tell, the answer seems to organic growth. What started out to be one thing, grew into something much bigger. In a way, that is a good thing! If your systems do not grow and expand, you are probably going out of business.

Does that mean that we should continue to build reporting solutions directly off of our transaction systems? When that no longer works, build a replication system and use that? When that no longer works, build a data mart? When that no longer works, build a data warehouse? The way I see it, if you are still in business and you are moving up the scale, you are on the right path.

One obvious danger that I have recently witnessed is over engineering a reporting system. Can you be all things to all people? I doubt it. What ends up happening is that you do most things OK, some poorly and the people that depend on your solutions get frustrated and end up looking elsewhere for their data.

While I continue to advise my clients to architect a scalable solution, you must start small. When talking to a client the other day, I advised "Think about as much of the problem space as you can for requirements and architecture. When it comes time to implement something, do as little as you can and still be successful." This naturally translates into quicker cycle time and more cycles. The benefit of this approach is that you continue to demonstrate reliability in achieving your goals and objectives. And who would not come back to someone who continuously achieves what they said they would?

One key that I have found to be rather successful in starting small is the Logical Data Model. The Logical Data Model can take in all of those requirements and hang them together for the big picture. That is your data blueprint. Print out big copies and put them everywhere so that people know what you are building and what it will look like when it is done. Then go build something and create a Physical Data Model scoped to only what you need at the time. This says that you have an eye on the future, but are focused on the present delivery.

So go out there and deliver something! Anything!!

Wednesday, April 15, 2009

Adding Content to the ER/Studio Enterprise Portal with Pentaho Tools

At the Embarcadero Datarage last month, I provided a quick demonstration on how you can add content to the ER/Studio Enterprise Portal. Given that I helped create the v1.0 portal, it was easy to show how to add on.

Basically, it is just a matter of creating additional Pentaho content.
  • Use the Adhoc wizard
  • Use stand alone Report Wizard
  • Use Report Designer and Pentaho Design Studio for prompted reports
Check it out! You may have to register with Embarcadero Developers Network to view the content.

There are a ton of other great presentations from other Embarcadero Partners.


Friday, April 10, 2009

YUI Table as an xaction in Pentaho

This is the first of several posts demonstrating how to use YUI (Yahoo User Interface) with Pentaho. The two are a natural match. YUI handles the front-end while Pentaho provides the data. You could use SQL, MQL, Xquery, or PDI... which pretty much means everything. In this example, we will use the SQL Action.

At the end of this you get client side pagination, sorting, column drag and drop and column resizing.

Before we get started, the prerequisites...
1) Obviously, you need Pentaho BI Platform. I have tested it with 1.7, 2.0, 2.1 and 3.0.
2) You need the YUI javascript build files. Put the build files/directories under the webapps/pentaho folder as yui.
3) Download my xaction template and save under design studio's template directory. Save the ActionTable.js under webapps/pentaho/js/yui.

  1. At this point, all you need to do is create a new action sequence with this template.
  2. Modify, the SQL Relational step to get data from whatever table(s) you want and the xaction does the rest
Please let me know how it goes.


Sunday, April 5, 2009

Pentaho Partner Summit 2009

I spent the later part of last week in beautiful Menlo Park, CA at Pentaho's Partner Summit. In addition to all of the "life is good" talk, there was some real exciting presentations.

We heard from Brian Senseman about his experience deploying Pentaho in the Cloud.

We also heard from Larry Augustin about the health of the open source business model. Given the source the is not a surprising perspective. However it was backed up with several sources of facts. The message was that open source software like Pentaho is here to stay.

We saw the preview of Pentaho's Dashboard Designer where a business user can create a dashboard within 90 seconds. Now that is impressive.

The moral of the story is the Pentaho's Business Intelligence Platform has had an impressive past and is poised for an even more exciting future. I for one, am thrilled to be a part of it all.