OLAP tools review: JPivot

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

JPivot is a free opensource OLAP client. It's slightly outdated, but still in some aspects outperforms modern tools. JPivot is bundled into both Pentaho BI Server and JasperReports Server. Also JPivot is available in Mondrian as a default UI.

At first glance

JPivot may look a little complex for people not familiar with OLAP. The goal of this article is to show the simplicity and the power of JPivot. Modern tools don't offer anything significantly better then JPivot.

We will work with sample data warehouse supplied with Pentaho BI Server called SteelWheels.

When you first open JPivot you'll see a picture like that:

We see a toolbar with a set of buttons and a table under it. The table shows us requested data, and buttons helps to navigate through this data. All columns you see under Measures caption are aggregates of data warehouse Measures columns. Other columns are Dimensions. As I've told in the previous article OLAP tools tends to start analysis from grand total values.

At first glance it's difficult to tell what these numbers are about. It's very helpful to understand what individual line from a data warehouse means, which part of business process it represent. This information can be obtained from data warehouse developer, but so as we can't talk to developer now, we will drill down to details and will try to figure out ourselves. If you want to look at most fine-grained line from data warehouse find the last image in this article called Drill through example.

Result table setup

From all buttons on toolbar, the most important is the first one from the left, called "Open OLAP Navigator". OLAP Navigator allows you to setup result table: to choose what to display on rows, what on columns, which measures to use, and how to filter the data.

Let's put Time on Columns, Leave Markets on Rows, and move everything else to Filters, after you press OK button you should see this result:

Note that we still see same total number 105,331. It's a total Quantity of ordered product items. Total haven't changed because we haven't filter data yet. You can filter data by clicking on Dimension of Measure name in OLAP Navigator. A list of values for the dimension will be opened from which you may select values you need to filter by. Also try to open Measures to see different Measures which available there.

Changing detail level

The most important feature of OLAP system is ability to drill down (open details). JPivot supports 4 modes: Drill Member, Drill Position, Drill Replace and Drill Through. By default Drill Position mode is active. And in this mode if you click on plus sign before "All Markets" member JPivot opens a list of markets, displaying values for All Markets as a first row. Next picture illustrate that:

Total number for all markets still 105,331. And if you sum values for each market (APAC, EMEA, Japan and NA) you'll get this total value. So drill down shows you details from which total value is comprised.

Here is description of all drill down modes:

• Drill Member. Show Total and Details. Replace all occurrence of Total Member with combination of Total and Details.
• Drill Position. Show Total and Details. Replace single occurrence (selected by user) of Total Member with combination of Total and Details.
• Drill Replace. Show Details only. Replace all occurrence of Total Member with Details.
• Drill Through. Shows individual lines from data warehouse table which comprise the selected value.

Actually Drill Member and Drill Position are very similar. If you want to understand the difference between them, try to add several dimensions on rows and look what happens when you drill down first dimension and then second.

I like Drill Replace mode best and would made it a default mode in JPivot. That's because in this mode you may create a nice table which will not intermix totals and details all together. By looking at a table below you may easily compare how Quantity Measure changes for each market over years. And in addition to table I added a chart which visualize values form table. Chart isn't difficult to add, and I'll not describe it in this article.

And now the time come to understand what business process this data describes. Usually this is a first thing you are interest in, but to make explanations easier we come to this feature only now.

Let's turn on Drill Through mode and click on a small value at the intersection of Japan and 2005. We are expecting to see a list of individual lines from data warehouse table, and selected the smallest value to not overload the system. Here is the result:

Drill through example

By looking at first line we can conclude that Some customer form Japan, Tokyo, Minato-ku called "Tokyo Collectables, Ltd" ordered a product of line Classic Cars called "1970 Triumph Spitfire"    in 2005, QTR1, Jan in number of 48 items and this order was Shipped.

Now we understand data much better, we may conclude that all Quantities which we have seen in tables are Ordered items which not necessary was shipped to a customer. To see the actually shipped Quantities we need to filter data by Order Status - Shipped.

Calculations

Calculations in JPivot can be archived by manually writing MDX formula. Average user will never learn MDX, so we will not even consider this here. But ability to manually enter MDX expressions is very desired feature for advanced users, unless all MDX power will be completely replaced by user friendly UI.


Hopefully I've showed you how powerful JPivot is, nevertheless it's interface is complicated.