OLAP tools review: Pentaho Analyzer Report

In this article we'll look at Pentaho Analyzer Report tool. This is modern OLAP tool and it's available only in commercial editions of Pentaho BI.

For evaluation I'll use demo data warehouse supplied with Pentaho BI Server called SteelWheels.

Result table setup

As usually let's start from initial screen.

Big white area in the middle is a place for building a table. The list from the right is columns from data warehouse: Dimensions and Measures. To create a table you may simply drag fields from the right pane to the middle area. Analyzer Report also allows to use layout pane for building a table. You may open layout pane by clicking it's icon, which marked on the image above.

I drag Product Line to Rows, Markets Territory to Columns and Quantity to Numbers. The result you may see on the picture below.

Note that this table don't have total values. And I like it because total values only add more mess to a table. And here you may concentrate on comparing values of similar levels. But if you need total values - you may add them via Report Options. Let me show you Report Options window, to open it click on "More" in the top right corner and then select "Set Report Options".

One useful type of analysis is to compare how Measure changes over same months in different years. But if you try to put Months to rows and Years to Columns Pentaho Analyzer Report will tell you that "Months has to be on the same axis as Years." Same issue present in Jasper Ad Hoc and JPivot. This issue extends to all Dimensions where you need to put higher level on rows and lower level on columns. Currently this is a restriction of mondrian tool on which all these tools are based. JPivot  allows to achieve similar to desired result by using MDX queries, but it's not very convenient. And Pentaho Analyzer Report have a resolution for Time dimension which I'll show later, but I don't sure if it's possible to use this solution with other types of dimensions.

Data filtering

One of the most frequent request in data analysis is to look at recent data. That may be expenses by months for recent year, or sales by days in recent month. Navigating trough Time dimension every time you need a recent period is tedious. And I'm happy to demonstrate one feature which Pentaho suggest for that purpose. To demonstrate it I'm using a data warehouse with recent data of time which I spend for self education.

Say I want to see how many time I spent in each month of recent 1.5 year for self-education. To achieve that, first thing I do is adding Month to Filters. You may either open Filter pane and drag Month there or right-click on Month level, as shows on the picture below, and choose Filter option.

Filter Dialog will be shown. Check "previous" and enter 18 in corresponding Month field. And press OK. Now all data will be filtered by this criteria.

After that I've added Years and Months to Rows, selected Measure to use, and get following result:

This feature is actually extremely useful by the following fact. All these relative terms from Date Filter Dialog(current, previous, next) are really relative. In other words if you create a report with "current month" filter, save it, and then open it next month - you will see data for new month.

Changing detail level

Seems like Pentaho Analyzer Report don't support Drill Member and Drill Position types of navigation, instead it suggest clear and simple Drill Replace and Drill Through.

For Drill Replace just right-click on a Dimension's member, say "Classic Cars", and select "Keep Only Classic Cars and Show Vendor" like on the picture below.

As a result you'll see details for "Classic Cars".

What I like about Drill Through in Pentaho Analyzer Report is that it shows all dimensions in the result table. Drill Through links can be turned on in Report Option dialog. I won't spend time on that, let's go further.

Calculations

Pentaho Analyzer Report provides several types of calculations. First is a set of available functions: percent, rank, running sum and percent of running sum. Second type is an ability to enter MDX calculated member formula. Third is Trends. And forth is an ability to change subtotal aggregation.

They are available from menu when right-clicking on Measure header in a table. The menu is shown on the picture below.

So on one hand Pentaho provides many calculations out of the box, and makes simple MDX formulas easy to write, but on the other hand, if you need something more complex - fill free to use MDX expressions :-).  I'm going to analyze deeper available facilities, but now we'll dive deeper into one really useful type of calculations called Trends.

Do you remember the type of analysis when you need to put months by rows and years by columns described and the start of this article. It's possible to achieve this result with Trends set of functions. They are available from menu shown above: User Defined Number > Trend Number.

To prepare for using this function I put Year = 2005 to filters. Put Months on Rows and select Quantity as a Measure. As a result we see quantities for each month of 2005. Then right-click on Quantity header and select menu option User Defined Number > Trend Number. Dialog as below will be shown.

After doing all this magic and pressing ok, new column is added to the table which contain values for a year 2004. You may check the result on the picture below.

Visualization

For demonstrating visualization features lets use following table. Years are on rows, Product Lines on columns and Quantity is a Measure. Now click on chart icon for switching to chart mode.

We switched to chart mode. Column chart show comparative quantities of different product lines in different years. For time is better to use line chart, so let's change Chart type to Line.

On screenshot below you see the results of changing chart type to Line. And now, another great feature is an ability to drill down on a graphs and charts. Let's click on Vintage Cars of 2004

Results of drilldown are shown below. You see a line chart for quantities of Vintage Cars over quarters of 2004.

Conclusions
I have to admit that Pentaho Analyzer Report made a good impression on me. Analyzer Report looks like it was carefully designed with a great attention to user requirements. Many features which I planned to put in this review as desirable I found in Pentaho Analyzer Report in the process of preparing the review and demonstrated here.

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.