Pentaho Analyzer Report
Note:- This Is only for pentaho Enterprise Edition BA server.
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.
