OLAP tools review: Jaspersoft Ad Hoc Report

This is fourth article from "OLAP tools review" series. To read the first one open this link.

Here I'll review Jasper Ad Hoc Report. This is modern OLAP tool and it's available only in commercial editions of Jasper BI.

I'll use "foodmart data for crosstab" data warehouse supplied with Jasper Server. Here is how the first screen looks like:

You see all dimensions and measures, and it's intuitively clear that you may drag them either to Columns or to Rows fields or to Filters pane. This is a great step forward in user interaction comparing to JPivot's UI where unless you know a place they hidden - you'll search for dimensions and measures for an hour.

Note that Jaspersoft Ad Hoc Report allow to switch between Sample Data and Full Data modes. And also between Table, Chart and Crosstab modes. The links for these modes are above Columns field. Table mode somewhat corresponds to Pentaho Interactive Report from Enterprise Edition. Crosstab and Chart modes corresponds to Pentaho Report Analyzer.

Result table setup

I'm putting Customer Country to Columns, Product Family and Unit Sales to Rows. The result is on the picture below.

We see totals for each row and column. Unlike in JPivot, Totals are below and from the right of detailed values. These looks more familiar and also Totals are shown with bold font which simplifies comprehension of the table's values.

One nice feature introduced in both Jasper Ad Hoc and Pentaho Analyzer is the ability to exclude selected column or row from table or on the contrary remove all rows or columns except for selected one(Keep Only). To use this feature just right-click on column or row header. It's illustrated on the picture below.

One feature I would desire to see in OLAP tools is the ability to create so called name sets. Suppose I have a Product dimension, and see that one product's sales are much better then all the rest, and I want to compare sales for this product versus all the rest. I imagine this like ability to select several members from a dimension and save that either as Dimension's member or as another Dimension. Later user will be able to use them in a similar way as regular dimensions. Of course this is not easy, security questions may arrise. But I suppose that situation when user need to introduce new classification for dimension's members is common in analysis.

Changing detail level

Suppose we want to see detailed data for Food's family. I'm moving Unit Sales on Columns, adding Product Category on Rows and clicking on plus sign before Food. As a result you see detailed data for Food's family.

Also if you click on plus sign before Product Family header - it will expend all Product Families. This corresponds to Drill Member and Drill Position of JPivot. Drill Replace can be replaced by combining Drill Down and "Keep Only" features. However I would prefer to have a special mode of Drill Replace, to do it in one click.


Drill Through is also available when you click on Measures values in cells, but it shows a single column with values of the Measure and if you needs more - go and add dimensions yourself. I don't think it's a good idea, it's better to show all dimensions as columns, paginate result, and remove totals at the end, I have already seen totals in main table.

Calculations

I've found two types of available calculations in Jasper Ad Hoc. First is the ability change aggregation function for Measure. And second is a capabilities to create a custom fields.

Aggregation function can be changed by right-clicking on Measures name header, like on the picture below.

For creating custom field you have to right-click on one of existing Measures based on which calculation will be performed. On the picture below you may see which types of formulas are available. As I understand in Basic functions it's currently not possible to enter another field name, so you only may add, subtract, multiply or divide by a constant number. Advanced functions are also helpful.

Visualization

Jasper Ad Hoc can visualize data. You are able to select one Dimension across which Measures values will be grouped and displayed on the chart. You may switch between several chart types: Pie, Bar, Line and other. It's possible to compare several different Measures grouped across same Dimension. But from what I can find Jasper Ad Hoc is only able to compare a list of values on a chart, and currently can't compare a table of values. Suppose you want to see a column chart to compare expenses by months and by categories. Usually it can be drawn by using special color of column for each category and placing columns for each month close together. Or as a stacked column chart, or as multi-line chat. I hope to see this feature in future releases of Jasper Ad Hoc.

In this review I tried to show you the most significant features of Jaspersoft Ad Hoc Report. In next review I'm going to review Pentaho Analyzer Report tool.