Real-Time Business Intelligence with CrateDB and JasperReports Server

2019-07-01, by Gianluca Natali

JasperReports Server is a powerful and feature-rich open source reporting and analysis server that can be used for business intelligence (BI). It enables the delivery of mission-critical information on a real-time (or scheduled) basis to the web, printer, or a variety of file formats.

CrateDB is a distributed SQL database that makes it simple to capture, store, and analyze massive amounts of machine data in real-time.

These two products go well together: JasperReports Server is a user-friendly self-serve platform that allows anyone to leverage the power of CrateDB.

In this post, I will show you how to get started on macOS, but these instructions should be trivially adaptable for Linux or Windows.

Install CrateDB

JasperReports Server makes advanced use of SQL window functions. Fortunately, CrateDB 4.0 just shipped with support for advanced window functions. So, we'll need to install 4.0 or higher to continue.

To get started, head on over to the CrateDB download page. At the time of writing, you need to select the Testing or Nightly distribution to get 4.0 or higher.

Download the tar.gz file.

Once downloaded, unpack the tarball and change into the resulting directory:

$ tar -xzf crate-*.tar.gz
$ cd crate-*

There is no need to build anything. However, we do need to make one small configuration change.

JasperSoft Server bundles a PostgreSQL server for its own use defaults to using port 5432. CrateDB offers PostgreSQL wire protocol compatibility that also defaults to using port 5432.

If we want to install both products on the same machine (for testing purposes), we must reconfigure one of these port numbers.

Let's reconfigure CrateDB.

Open the config/crate.yml file.

Find this line:

#psql.port: 5432

Change it to this:

psql.port: 5433

Save the file.

Now CrateDB will listen on port 5433 for PostgreSQL wire protocol connections.

With that done, start CrateDB:

$ bin/cratedb

Then, open the Admin UI by visiting http://localhost:4200/ in your browser.

You should see something like this:

Screenshot of the Admin UI

Load some test data

Next, we need some test data to work with.

Here's a Python script that will generate some for you and load it into CrateDB:

Let me show you how to run this script.

First, set up a new directory:

$ mkdir test-data
$ cd test-data

Then, download the script:

$ curl https://gitlab.com/snippets/1870650/raw -o load.py

This script has some dependencies, but to avoid installing Python packages in a way that affects the rest of your system, we'll set up a Python 3 virtual environment:

$ python3 -m venv venv

Then, install the dependencies, like so:

$ venv/bin/pip install Faker crate

Now, run the script:

$ venv/bin/python3 load.py

This will take a few seconds as it generates the test data and loads it into CrateDB.

Once it's done, navigate to the Tables Browser in the Admin UI by selecting the Tables icon from the left-hand navigation menu.

You should see a screen like this:

Screenshot of the Admin UI

Brilliant! That means everything worked. We have a sample_states and a sample_users table, both populated with data.

If you're curious, you can select QUERY TABLE to browse the records of either table.

Install JasperReports Server

Go to the Jaspersoft download page.

From there, select the TRY FREE FOR 30 DAYS button in the Enterprise column. (You will need to fill out a contact form to continue.)

On the next screen, first, select your operating system. Then select the DOWNLOAD X64 button in the JasperReports Server row.

Your download should begin.

Once the download is complete, open the archive and launch the installer. (On macOS, you will need to Control-click the installer to get an Open option on the security warning about the app developer being unidentified.)

You should see this:

Screenshot of the JasperReports Server installer

Follow the installer instructions and go with the defaults.

Finally, you should see a screen like this:

Screenshot of the JasperReports Server installer

Leave the top-most checkbox selected. When you select Finish, the installer will start JasperReports Server and open http://localhost:8080/jasperserver-pro/login.html in your browser.

Screenshot of JasperReports Server

Before you login, we need to make a quick configuration change.

Change into the install directory. For me, that was:

$ cd /Applications/jasperreports-server-7.2.0

Then, open the apache-tomcat/webapps/jasperserver-pro/WEB-INF/adhoc-ehcache.xml file.

Find these lines (near the top):

<ehcache name="adhocCache"  maxBytesLocalHeap="400M"
         maxBytesLocalDisk="2G">

And change them to this:

<ehcache name="adhocCache"  maxBytesLocalHeap="1"
         maxBytesLocalDisk="2G">

By setting the heap size to 1 byte, we effectively disable the ad hoc query cache. This is useful while we're testing because the ad hoc query cache sits between the reports we want to test and the data in CrateDB.

Save the file.

Then, restart the server:

$ ./ctlscript.sh restart

In case you need to, you can use this script to do other things:

$ ./ctlscript.sh 

usage: ./ctlscript.sh help
       ./ctlscript.sh (start|stop|restart|status)
       ./ctlscript.sh (start|stop|restart|status) postgresql
       ./ctlscript.sh (start|stop|restart|status) tomcat

help       - this screen
start      - start the service(s)
stop       - stop  the service(s)
restart    - restart or start the service(s)
status     - show the status of the service(s)

Configure JasperSoft Server

Before you continue, go back to the JasperSoft Server login page. Refresh the page, to make sure that the server restart didn't break anything.

Then, log in with:

  • Username: jasperadmin
  • Password: jasperadmin

You should be greeted with the home screen:

Screenshot of the home screen

Create a new data source

To connect to CrateDB, we must create a new data source.

From the home screen, select Create under Data Sources.

You should be presented with this screen:

Screenshot of the New Data Source screen

Change the Database from dbname to doc (the default CrateDB schema). As you do this, the URL field should automatically update itself.

We need to correct that URL, however. Select the 5432 port number, and change it to 5433 to match what we configured earlier.

For User Name, specify crate.

Then, select Test Connection.

If everything worked, you should see something like this:

Screenshot of the New Data Source screen

Select Save. A modal popup should appear.

Name the data source "CrateDB". Resource ID should auto-populate as CrateDB. And leave the Data Sources folder selected.

Screenshot of the New Data Source screen

Select Save again.

On the next screen (the Repository screen), you can use the left-hand folder tree navigation to find your newly created data source:

Screenshot of the Repository screen

Create a domain

Now CrateDB is set up as a data source, we have to create a suitable domain.

A domain is basically just a view on your data. Domains are used to prepare your backend data for end-users.

From the top navigation menu, select CreateDomain.

Screenshot of the Domain Designer screen

Select CrateDB. Then OK.

You should see this:

Screenshot of the Domain Designer screen

Select the doc schema and then select the right arrow to move it from the Available Schemas list to the Selected Schemas list. (Alternatively, you can drag and drop the schema.)

Screenshot of the Domain Designer screen

Now, select the doc schema from the left-hand tree menu.

Screenshot of the Domain Designer screen

Move both tables (sample_states and sample_users) from the Available Tables list to the Selected Tables list.

Next, select Joins from the top right-hand sub-navigation menu.

Screenshot of the Domain Designer screen

Expand the tables in the left-hand tree menu to reveal a list of column names. From here, drag and drop the state column from the sample_states table to the empty workspace in the middle of the screen.

Screenshot of the Domain Designer screen

Then, drag and drop the state column from the sample_users table to the box marked "Drag a field here" on the right-hand side of the workspace.

Screenshot of the Domain Designer screen

Great! We've just configured an inner join on both tables using both state columns as a key.

Now, select Data Presentation from the top right-hand sub-navigation menu.

Screenshot of the Domain Designer screen

From here, we have to select which columns to include. You can do this by dragging and dropping columns from the left-hand tree menu.

Here's how I set mine up:

Screenshot of the Domain Designer screen

Notice that I have configured a few columns to display an average Summary Calculation. Also, I selected the pencil icon next to the column names in the Label column and edited them to prepend "Avg.". (I switched the labels to title case too.)

When you're happy, select the floppy disk icon from the top sub-navigation menu, then Save Domain.

Call the domain something like "States, Users", and save it in the Domains folder.

Screenshot of the Domain Designer screen

Select Save.

Create an ad hoc view

Now we have our domain set up, we must create an ad hoc view, which is basically just a prepared query we want to run against the domain.

From the top navigation menu, select CreateAd Hoc View.

In the modal popup window that appears, you can use the text input to search for the domain you created. (Or you can just scroll down the list of demo domains.)

Screenshot of the New Ad Hoc View screen

Select Choose Data...

You should be presented with a screen like this:

Screenshot of the Data Chooser screen

Select the double arrow icon to move all of the columns from the Source list across to the Selected Fields list.

Select OK.

You should see something like this:

Screenshot of the New Ad Hoc View screen

Notice that the left-hand menu is separated into two:

  1. Fields
  2. Measures

This corresponds to the classification we chose when setting up the view.

For this ad hoc view, let's report the average age, dependants, and income per state.

To group by state, drag State from the Fields menu to the Rows input.

Screenshot of the New Ad Hoc View screen

Here, you can see that the query is generating one row per state. All that's needed now is to add some columns.

One by one, drag the following items from the Measures menu to the Columns input:

  • Avg. Age
  • Avg. Dependents
  • Avg. Income

When you're done, you should see something like this:

Screenshot of the New Ad Hoc View screen

Select the floppy disk icon from the top sub-navigation menu, then select Save Ad Hoc View.

Choose a suitable name for the ad hoc view, such as "Aggregate State Statistics". File it under Ad Hoc Components. Then, select Save.

Congratulations, you just created your first ad hoc view.

From here, you might want to explore creating a custom dashboard from many different ad hoc views. Alternatively, head on over to the main Jaspersoft documentation center and start poking around.

Wrap Up

JasperReports Server is a self-service reporting and analytics tool. And CrateDB is a distributed SQL database. Together, they provide a powerful end-to-end platform for real-time business intelligence.

In this post, I showed you how to:

  1. Get CrateDB up-and-running on your local machine
  2. Generate some test data
  3. Connect JasperReports Server to CrateDB
  4. Create your first ad hoc view

Questions? Looking for help? Get in touch!

Newsletter

Stay up to date

Sign up here to keep informed about CrateDB product news,
events, how-to articles, and community update.