Right Outer Join

2 September 2011

Simple Reporting on MongoDB

Filed under: iReport, JasperReports — Tags: , , , — mdahlman @ 16:22

Big Data (MongoDB) with Reporting and Analytics

Big Data databases are being adopted with rapidly increasing frequency. Opinions on NoSQL and Hadoop are being generated nearly as quickly as the data going into these databases. This article doesn’t contribute a viewpoint on any of that.

I’m assuming that you already have data in MongoDB. You probably had a great reason for using MongoDB, and reporting on that data was probably not a top consideration. But now that the data is there, what options exist for reporting and analysis?

Option 1: Dump it to a traditional RDBMS

This technique is not exciting or sexy, but it’s ideal in many cases. Create ETL jobs to get the data out of MongoDB and into PostgreSQL, MySQL, Vertica, Teradata, Netezza, or whatever good old relational database you prefer. From there you have a plethora of tools at your disposal for slicing and dicing the data or for designing reports. Having acknowledged the utility of this technique for many requirements… I will proceed to ignore it.

Option 2: Connect your BI solution directly to MongoDB

You’ve got a lot of data in MongoDB. That’s why it’s called “Mongo” after all; that’s why the whole field is referred to as “Big Data”. So there are plenty of downsides to pulling all of the data back out and putting it somewhere else.

If requirements are simple enough, you can code it up yourself. Write some PHP scripts to create reports. The MongoDB PHP driver makes it easy enough to connect and get data.

If your requirements are a bit more extensive, then the build-it-yourself method has important drawbacks. A simple tabular report can be created quickly. But what happens when your users like it and then say, “Great, now can I get the same thing in PDF?” Or soon they’ll ask for charting. Or you’ll find you need to implement security on who can see which reports. There are plenty of benefits to using standard reporting and analysis tools for solving problems like these. The biggest barrier to this is that most Big Data databases (including MongoDB) are not accessible via standard methods like JDBC. That means traditional Business Intelligence (BI) tools can’t get directly to Big Data data sources.

Jaspersoft has addressed this problem for a number of Big Data databases including MongoDB. It offers connectors to a variety of datasources. Read the marketing fluff. See the connectors.

OK, that’s enough background. The rest of the article below this point is a hands-on guide to creating reports in iReport against a MongoDB data source.

Prerequisites

First Report

The installation of the connector is simple. It’s covered in the documentation included with the connector. After installing the plugin and restarting iReport, you’re ready to define your connection. The connection needs to know the host, port, and database that you’ll be connecting to. This is expressed in a JDBC-like url shown below.

Simple JDBC-like connection URL

Now you’re ready to create your first report. Choose File -> New to launch the report wizard. Choose the Coffee template (well… choose any template besides the blank ones). Then click “Launch Report Wizard”. When you reach the step of the wizard that requires a Query in the MongoDbQuery language you have reached your first real test. MongoDB doesn’t have a query language. What should you enter here?

For your first report, use the simplest possible query:

{ ‘collectionName’ : ‘myCollection’ }

Simple MongoDB Query

Click “Next”. iReport then runs your query. It connects to MongoDB, reads each document from the specified collection, finds each field and its datatype, and presents these fields to you. Documents in MongoDB do not necessarily have the same fields. The list of fields presented is the union of all fields found for each document.

The available fields are automatically discovered. Choose the ones you want.

If you’re dealing with a very large collection, then you perhaps don’t want to wait for iReport to read through every record. You can tell it to read only the first 100 documents like this:

{ ‘collectionName’ : ‘myCollection’,
‘rowsToProcess’ : 100 }

Note: the field ‘rowsToProcess’ is read only by iReport’s Fields Provider. That means when you run the report it will still process all documents from the collection.

Finish the report wizard and then preview the report. You have just created a report against a MongoDB database without having to first dump the data into MySQL. Woo hoo!

Your first report showing data from MongoDB should be working now!

Useful Reports

Creating the first report was easy. But let’s face it: a report that includes every record from a collection is not really interesting. It’s bound to be a humongous report. Let’s add filters to get genuinely useful reports.

Continue to my article Cool Reporting on MongoDB for some advanced techniques.

You can download the report from this article (and all of the reports from the second article) here.

About these ads

6 Comments »

  1. My { ‘collectionName’ : ‘myCollection’ } validates against the test database, but I don’t see the any fields returned in ireport.
    I’ve validated the collection exist in MongoDB. eg. db.getCollectionNames()
    Any ideas?

    Comment by jjc — 22 April 2012 @ 10:46

    • Hmm… that’s strange.
      First question: what versions of MongoDB and iReport and MongoDB connector?
      First test: if you manually add a field ($F{myfield} of type String) then run the report, does it work?
      If so, the problem is in the Fields Provider. If not, the problem is probably with the connection. If you have not already done so, please post your question to the JasperReports Server forum on jasperforge.org (http://jasperforge.org/plugins/espforum/browse.php?group_id=112&forumid=102). That will be a better place to discuss the issue in detail.

      Comment by mdahlman — 23 April 2012 @ 11:59

  2. hi, like your blog. You’re way further ahead than I am!
    mongopi.wordpress.com

    I’m struggling to see my MongoDB through the MongoDB connector in iReport (details on my blog) Can you help?!
    ATB,
    STuart

    Comment by sdh1974 — 13 November 2012 @ 04:11

  3. I’m experimenting with Jaspersoft Studio and MongoDB. Your blog is really helpful in finding the right way.
    But from a reporting user point of view, there is still much to do, to get reporting on mongodb there, where it is with every other relational db.
    The most important one is “defining” or “finding out” the data schema in the database. I think that “trying to find out the data schema” in a schemaless db (thats what the connector does now) is the wrong way. I think that the Jaspersoft Mongodb connector should expect a JSON Schema (http://json-schema.org) to be provided to the connector and that the connector then configures the right count of datasources, enabling in that way not only flat structures but also complex nested ones…
    What do you think?
    Ognian

    Comment by Ognian — 9 June 2013 @ 03:56

    • In the long term something like this is possible. It could provide some interesting benefits. But currently MongoDB (and other schemaless databases) do not provide a JSON schema (or other schema) to document the structure. Until they provide something like this tools like Jaspersoft Studio need to determine the schema in a more ad hoc fashion like this.
      It’s not directly linked to flat vs complex. It’s already possible to handle complex nested structures with the current connector.

      Comment by mdahlman — 24 June 2013 @ 09:13


RSS feed for comments on this post. TrackBack URI

Go on... leave a reply

Fill in your details below or click an icon to log in:

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

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 39 other followers

%d bloggers like this: