Creating Pentaho Reports from MongoDB


So you’ve made the move and started using MongoDB to store unstructured data.  Now your users want to create reports on the MongoDB databases and collections.  One approach is to use a Kettle transformation that retrieves data from MongoDB for reports.  This approach is documented on the Pentaho Wiki.  However, I want to use the MongoDB database directly without dealing with Spoon and Kettle transformations.  Fortunately Pentaho Reporting also supports scripting with Groovy built in.  This tutorial will show you how to create a report against MongoDB data using the Javadrivers and Groovy scripting.

Pre-Conditions
You should already have mongodb installed and accessible.  I’m running on the same machine with the default settings, so vary the code as needed for your configuration.
You also need to put the mongo-java-driver-2.7.2.jar file in the libraries for Report Designer and the BA Server
$PENTAHO_HOME/design-tools/Pentaho Report Designer.app/lib/
$PENTAHO_HOME/server/biserver-ee/tomcat/webapps/pentaho/WEB-INF/lib
Restart the app and BA Server if they are running to pick up the new .jar files.
Setting Up
The first thing you need is some data.  I’ve created an input file of sales by region and year to use as an example.  Download and import the data using the mongoimport command:
> mongoimport -d pentaho -c sales data.json
Verify that the data has been successfully imported by opening the mongo shell and using the following commands:
> use pentaho
> db.sales.find();
You should see a list of documents that were added.
Creating the Report
  1. Using Pentaho Report Designer, create a new report.
  2. Add a data source and choose Advanced -> Scriptable
  3. Select groovy as the language and click the (+) for a new query
  4. Enter the following code as the script (check Server Address on database connection creation)

import com.mongodb.*

import org.pentaho.reporting.engine.classic.core.util.TypedTableModel;
def mongo = new Mongo("127.0.0.1", 27017)
def db = mongo.getDB("pentaho")
def sales = db.getCollection("sales")
def columnNames = new String[6];
columnNames[0] = "Region";
columnNames[1] = "Year";
columnNames[2] = "Q1";
columnNames[3] = "Q2";
columnNames[4] = "Q3";
columnNames[5] = "Q4";
Class[] columnTypes = new Class[6];
columnTypes[0] = String.class;
columnTypes[1] = Integer.class;
columnTypes[2] = Integer.class;
columnTypes[3] = Integer.class;
columnTypes[4] = Integer.class;
columnTypes[5] = Integer.class;
TypedTableModel model = new TypedTableModel(columnNames, columnTypes);
model.addRow([ new String("East"), new Integer(10), new Integer(10), new Integer(14), new Integer(21) ] as Object[]);
def docs = sales.find()
while (docs.hasNext()) {
  def doc = docs.next()
  model.addRow([ doc.get("region"), doc.get("year"), doc.get("q1"), doc.get("q2"), doc.get("q3"), doc.get("q4") ] as Object[]);
}
docs.close()
model;
This will read the data from MongoDB and return the table model needed by the reporting engine.
From here it’s just standard report generation and publishing, which is described in the Pentaho documentation.

via Creating Pentaho Reports from MongoDB.

Thanks to BillBack  @billbackbi

Advertisements

9 thoughts on “Creating Pentaho Reports from MongoDB

  1. Hello David,
    Good Information. if i want to use mongoDB in Spoon, then what kind pre-requisite should i need to do in Spoon?
    I have installed MongoDB 2.2.2 in Ubuntu and the mongo-java-driver-2.7.2.jar is present in the /opt/pentaho/design-tools/data-integration/plugins/pentaho-big-data-plugin/lib location by default.

    While trying to run the transformation in Spoon, the below error was thrown
    “2013/02/14 14:34:10 – MongoDb Output.0 – ERROR (version 4.4.0-GA, build 17542 from 2012-11-01 20.06.29 by buildguy) : A problem occurred while trying to connect to localhost on port 27017″

    Any suggestion?

    Thanks,
    Karthik.K

  2. Thanks for your reply!!!!
    I have tried both the methods which are provided above, but it seems not working.
    I have install the Pentaho 4.4 GA and MongoDB 2.2.2 in Ubuntu Server. Also i can able to connect the MongoDB using client software i.e. MongoVUE successfully.
    I feel this issue is caused by incorrect placement of jar.

    Any ideas please

    Thanks,
    Karthik.K

      • Yes my MongoDB is up and running. while trying to access my mongoDB admin console, following two behaviors are observed

        1. using localhost: 27017 link,
        You are trying to access MongoDB on the native driver port. For http diagnostic access, add 1000 to the port number
        2. So I used localhost: 28017 link, now i can able to access the mongoDB admin console

        I am bit confused which port number should i used in Spoon.

        Thanks,
        Karthik.K

  3. Hi,

    I have the mongodb server in a remote server. How can I use grovvy to get connected to the remote server? I can not see any option in the Pentaho Report Designer.

    PS: For the moment I can tunneling the port from remote to localhost. But this is a workaround, not a proper solution.

    Thank for your time

    Best regards

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s