BarChart Example in Pentaho CDE using mongoDB(NoSQL database)

This articles is for beginners in MongoDB+Pentaho Kettle+Pentaho CDE.

Aim of this post is : 
Creating a Bar Chart using MongoDB in Pentaho CDE (A static bar chart, not parametrized) 

• There is no direct way to connect to mongoDB in pentaho CDE.

• Using “Kettle Queries” option in pentaho CDE we can get required result sets for visualization.

Let's assume you have a collection called “PageSuccessions” in “Demo” database in mongoDB server.

You can refer below articles from pentaho WIKI to Write, Read, creating Reports in PRD using mongoDB.

http://wiki.pentaho.com/display/BAD/MongoDB

Let's assume you have below documents in “PageSuccessions” collection.

MongoDB Preparation for this example is from :
http://wiki.pentaho.com/display/BAD/Write+Data+To+MongoDB

In mongo shell let's have a look at what is there in “PageSuccessions” by giving below command.

> db.PageSuccessions.find().pretty();
{
    "_id" : ObjectId("5404609c5ae882923576f854"),
    "key" : "--firstpage--~^~/about",
    "url" : "--firstpage--",
    "nextUrl" : "/about",
    "Count" : NumberLong(504)
}
{
    "_id" : ObjectId("5404609c5ae882923576f855"),
    "key" : "--firstpage--~^~/about/awards",
    "url" : "--firstpage--",
    "nextUrl" : "/about/awards",
    "Count" : NumberLong(80)
}
{
    "_id" : ObjectId("5404609c5ae882923576f856"),
    "key" : "--firstpage--~^~/about/customers",
    "url" : "--firstpage--",
    "nextUrl" : "/about/customers",
    "Count" : NumberLong(667)
}


Creating Bar Chart – Top 10 url pages

Getting top 10 documents from Kettle.

As shown in figure-1 in you need to write kettle transformation to fetch the top 10 urls.

• As of 5.0.1 Pentaho kettle release, we can not directly query on mongoDB.
• We'll take JSON step to get the fields. 
• MongoDB input component query area supports only JSON query expressions which you can find at http://wiki.pentaho.com/display/EAI/MongoDB+Input
• From the ETL design at final step you should get the result set as shown below.

Download the ktr file and try to open each step.

URL            Count  
/demo            114747  
--firstpage--            108143  
/download            21583  
/feeds/press            15378  
/products            14686  
/product/product3            14380  
/partners/resell            14316  
/download/download3.zip    13939  
/product/product4            13334  
/buy            10293

Creating Dashboard in Pentaho CDE( A simple bar chart using above query result set)

1) Prepare your layout in bootstrap mode of Dashboard. (Find it in settings).
2) Take a BarChaart component from the Components section.
3) In Data sources section take “KETTLE Queries”.(Upload the ktr file to your dashboard folder using Pentaho User Console).
4) In “Kettle Tran formation file” property locate the uploated ktr file.(for eg: DashboardWithMongoDB.ktr).
5) Give the step name which is giving the top 10 rows(for example : Select values).
6) Check the output of this query usign CDA editor.
7) Go back to the Bar chart and set height, width, data source (the name of data source created in above steps.. for ex: query1 is the name of the kettle data source).

8) Save the dashboard & Preview it. Sample output is shown in figure-2.

IMP NOTE : As of 5.0.1 pentaho release JSON query expressions will give you the result sets  from monogoDB. (Which you can find at mongoDB Input step from BigData Node).



Transformation Sample Design Image:

Bar Chart Sample output Image :

Download Transformation : Click-1
Download Sample Bar Char : Click-2

References : 

1) http://wiki.pentaho.com/display/BAD/MongoDB
2)  http://wiki.pentaho.com/display/EAI/MongoDB+Input#MongoDBInput-queryexamples

Next post : Parametrized dashboard using MongoDB.


Sadakar Pochampalli
:-)