OLAP tools review: Basis for comparison of OLAP tools.

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

There are two important sides of any OLAP systems: "Which questions can be addressed to OLAP?" and "Which answers can be received from OLAP?" In this article we'll review these topics in details.

Questions to OLAP System

All questions to OLAP Systems are about figures. As we know, OLAP systems are intended to answer business questions like: "What's a company revenue this year?" or "How does it compare to a previous year?". These questions can easily be answered by searching the appropriate information in a Data Warehouse. More advanced question will be: "Which revenue the company may expect next year?". Existing OLAP usually answer this question by calculating a formula provided by an expert.

Rather complex questions can be constructed with modern OLAP systems. For building these complex questions OLAP provides a form of conversation between a system and a user. Suppose you asked for a comparison bar chart for expenses on entertainments for each month of this year. Then next question may simply be "Why in April I spent so much?". System should remember the context of conversation and present to you a list of items under entertainment category for April of this year.

When complex question is built, OLAP usually provide an ability to save it, and then quickly run next time.

Answers of OLAP System

OLAP Systems gives answers in terms of figures. It can be a single number, but usually it's a row of numbers of a table of numbers. For our expenses example a table may contain months by rows and expenses categories by columns. See table below as example. A table helps you to compare numbers to each other. Each number will usually represent an aggregate value. The aggregates are not necessary should be sums, they may be average values, counts, ratios, and a lot more types.

	Categories
Months of 2011	Food	Entertainment	Car
January	$407	$224	$734
February	$451	$311	$354
March	$376	$74	$579

So we can classify all answers by dimensionality. First three types of answers are used in practice, and the forth I introduce as a possible direction of improvement of existing OLAP systems:

• Single number. Example: Total expenses in May.
• List of numbers. Example: Total expenses in each month of a year.
• Table of numbers. Example: Total expenses in each month of a year by categories.
• Several tables of numbers. Suppose we have a slider component for changing date and table will display data for that day. This may be considered as set of tables.

Answers visualization

A huge role in easy comprehension of these numbers plays visualization. Instead of reviewing a table of figures for several minutes a single glance at a chart or graph will tell you everything you need in a moment. As example system may show you a bar chart with expenses for each month of a year, and you'll easily identify the higher bar which denotes a month with the biggest expenses.

Let's think about visualization of different types of answer dimensionality:

• Single number. Visualization usually is not required, because nothing to compare this number with.
• List of numbers. Many ways to visualize (A bar chart, a pie chart, line chart)
• Table of numbers. Still many ways for great visualization (A stacked-bar chart, multi-line chart, many pie charts)
• Several tables of numbers. Difficult to visualize, an animation may help.

Initial context of  conversation

OLAP Systems can answer very complex questions, and it's difficult to classify them all. But OLAP usually supports conversations. That means user instead of writing a complex question is asking a series of short questions which leads to desired result. Along this process system should remember the context of conversation.

Many OLAP systems start conversation from grand total values and allow user to "drill down" to details. And it's reasonable that user wants to see the whole picture at first and then investigate the details. But it's also true that business users prefer to look at current situation, like expenses for last year. And seeing total expenses looks useless.

On the other hand, user which opens OLAP system for the first time, may be unfamiliar with Data Warehouse structure and it may be very helpful to see several most recent rows with  fine-grained data from DW, and then start data exploration in a bottom-up fashion. But I don't remember seeing OLAP system which adopts bottom-up style of navigation.

Navigation in figures ocean

Common operations which user can perform in OLAP system can be split into following classes: Result table or chart definition, data filtering, changing detail level, calculations.

Result table or chart setup: 

• Choose the measures. Example: Show total expenses.
• Choose Dimension or combination of Dimensions for columns. Example: Compare expenses by categories.
• Choose Dimension or combination of Dimensions for rows. Example: Compare expenses by categories(on columns) and by months(on rows).
• Sort data in a table.

Data filtering:

• Select dimension members for which to display data in the result table or chart.
• Filter data in result table by members of other dimensions(which don't used in result table definition)
• Filter data by Measures values, or by arithmetic formulas.

Changing detail level:

• Drill level down.  Example: If we see total expenses for a year "drill down" will open expenses my months of the year.
• Drill level up. It’s an opposite of drill down.
• Drill Through. It shows the most fine-grained rows from data warehouse which comprise selected value.

Calculations:

• Calculate column or row values with user defined formula. OLAP systems from this review support such functionality in less or more extent. But I think a lot of improvements can be made to existing OLAP systems in this direction.
• Calculate column or row values with a predefined function.
• Change aggregation function for Measures.

Let me describe one simple idea which OLAP tools may adopt in future to simplify advanced calculations.

Do you like using Excel? I do. Excel allows entering formulas with mouse clicks. Excel suggests function names to user. Excel highlights cells used in formula with different colors. All you need to learn to start using formulas in Excel is that formulas start with '=' sign.
Implementing functionality like that in OLAP system opens the door to completely new usage scenarios for OLAP systems. Suppose accountant use data warehouse to calculate taxes, and don't need to ask technical specialist to change some formula every time tax rate or tax policies changes.

In the next article I'll review JPivot tool.

OLAP tools review: Introduction

I'm impressed. And this impression finally pushed me to start these series of articles about Data Analysis Tools. I'm going to review and compare several OLAP Systems. Actually the compared set of tools is now limited to java web application which I had a chance to work with as a Buisness Intelligence consultant. They are: JPivot, Jaspersoft OLAP, Pentaho Analysis and may be I'll add something in future.

So what did impress me? Today it was a Pentaho Analyzer Report (Pentaho Analysis) which is a part of Pentaho BI Server Enterprise Edition. I really like how its usability was improved. It's not an advertisement article, so don't stop reading it after a praise to Pentaho, in the review I'll try to look deeper to functionality of each tool and as well into some technical details and I'll try to be unbiased. All in all most of these tools have very similar functionality. And also I want to find directions for these tools to evolve.


The purpose of OLAP systems.

Let's look at OLAP from different sides. At first let's look at OLAP as at Decision Support Systems (DSS). I'm not going to give a strict definition, but essentially what Decision Support Systems used for? To help people make decisions. How do they help? Decision Support System provides information required by the person for making a decision. In this sense Google Search is the best Decision Support System, it brings you the information required for making decisions. But Google Search can't help us calculate company's expenses for last month. Then let's refine our definition for OLAP. OLAP are business-oriented systems which work with figures. 

By classification of Daniel Power we deal with data-driven DSS. A data-driven DSS provide operational and strategic business intelligence using internal company data and sometimes external data. You may read more about it in Daniel's article, but we go further.

Also I want to compare DSS to Expert Systems. Decision Support Systems provides the information required by an expert for making a decision. In contrast to them, Expert Systems make decisions themselves and provide explanations to an expert. The point of intersection between them is an understanding how an expert explores the information in the process of making a decision. An understanding of expert's needs is a key to improve existing DSS systems.

To summarize, we talk here about OLAP systems which are kind of data-driven Decision Support Systems.

OLAP and Data Warehouse intuition

Let me suggest you an intuition behind OLAP Systems.

Suppose you earn a lot of money but you have found out that they all get spent somewhere. What will be the solution? I would suggest making notes of your expenses. If you open an Excel sheet and create a table, like one shown below, and will start filling it every day, you may become proud that you have build your first Data Warehouse.

Date of purchase	Category	Name of product or service	Cost
1 Jan 2010	food	cheese	$5.45
1 Jan 2010	food	milk	$3.30
2 Jan 2010	entertainment	visit to theater	$160.00
3 Jan 2010	car	gasoline	$50.00

Now after a month of doing these records you may calculate many useful aggregate values like: total expenses for this month, total expenses for each of categories, total expenses for gasoline. This is essentially what OLAP systems do for you. They calculate aggregated values filtered by different criteria.

That is to say you'll be able to ask OLAP about sums of these expenses or about lists of individual expense records. Like: "How much money was spent this month?", "How much money was spent last month for a car?", "Show me the bar chart with comparison of expenses in each month of this year." As well as much more complex questions.

Note that you always aggregate values from cost column. Such columns as cost are called Measure columns. There is no reason to sum values from other columns in our table. As example there is no sense to sum product names. Other columns are used to group or to filter costs: by month, by year, by product category. These grouping and filtering columns are called Dimensions (and sometimes they called Filters).

It's important for the user of OLAP system to have a clear understanding what is represented by individual line of a Data Warehouse table. In our case it's a single purchased item details. But when you look at these data from OLAP, you usually see sums of these items like total cost for some period of time. Usually the easiest task in OLAP is to see a grand total over all items, and a little more time consuming is to navigate to detailed subset of these items.


Dimension structure

OLAP system usually deals with large volumes of data. And the diversity of Dimension values can grow large. To help users navigate data, OLAP system allows structuring Dimension values into a tree form. The most popular example is a Date Dimension, which can have branches for each year, then each year will have a branch for quarters, each quarter branches for month, months will have leaves for days.


In the next article I'll try to build a basis for reviewing and comparison of OLAP tools.

Wrapping long text in Pentaho html reports in IE7

   To understand the issue just open pentaho demo report in IE7 and for comparation in FF of Chrome.
   The report is located in Steel Wheels/Reporting and is called "Order Status". It contain a long line of text which is not wrapped in IE7 and wrapped in other browsers.

   Internet Explorer 7:

   Firefox:

   There should be a dozen of ways to wrap this text, but I want to show how simple it can be resolved with CSS.

   Actually Pentaho report engine do a lot of things to wrap the long line: it sets <table> element with, <col> elements widths, it even wrap each line of data into span element. But IE7 just write it in single line.
   One promissing way to overcome this was to use table-layout:fixed css style. But this not helps because pentaho also have fixed height of rows. And data just trancates without moving to the next line.
   My solution is to apply block style to all spans in the table:
  table td span {
   display:block;
  }

   It simple and it works like a charm. If you need you can empower the rule with more strict constraints to keep other tables save.

JFreeChart in Firefox

   If you work with Pentaho via Firefox you probably upset with a quality of charts. Flash charts look just great. I'm talking here about JFree charts. This issue affects Firefox only! IE works a bit better in this case.

  JFreeChart
   JFreeChart have several advantages over Flash Charts. JFreeChart is a java library that produces images in various formats: gif, jpeg, png. As a result these charts can be incorporated into PDF, Excel and RTF reports. Second advantage comes from Apple - Adobe Flash war. Flash charts will not work on iPhone and iPad. So there are some reasons to use JFreeChart.

  Blurred Charts
   The case when charts in Pentaho are blurred is common enough. I designed a report with a chart in it with the latest Pentaho Report Designer 3.6.1, placed it on a dashboard, where it produced in HTML format, and added pdf export button. When I open my dashboard in Firefox the chart looks blurred. This is mainly concern to any text that is on the chart. Ok, you can understand these charts, but this is not a thing your customer will like. On the other hand charts exported to PDF looks very neat. Also when opened with Internet Explorer charts looks nice.

  How to fix it?
   The problem here is in the way Firefox scaling images. Chart images are usually in high quality, and their size can be twice larger then the place for a chart on html page. So Firefox forced to downscale these images. And here, for the blame of Firefox, it can't do that nice. Firefox just don't have a good image scaling algorithm. You can check a bug, which wasn't fixed at the time of this writing.
But the good news is that Firefox have a "bad" images scaling algorithm that can correctly downscale our chart! I'll explain it:
<img> HTML element have a property image-rendering. In Firefox this property can be set to any of 5 values: auto, inherit, optimizeSpeed, optimizeQuality, -moz-crisp-edges. Currently only two rendering algorithms are used. Bilinear resampling for values: auto and optimizeQuality. Nearest neighbor resampling for optimizeSpeed and -moz-crisp-edges values. And here is a trick: Nearest neighbor resampling works better to images with sharp edges.
So all you need is just add following style for all chart images:

<style type="text/css">

img {

    image-rendering: -moz-crisp-edges

}

</style>

This is how looks blurred image(you will see blurring in Firefox only):

And here is how looks sharp image(for me it's looks even better then in IE):

Sharp image can be rendered with problems if you changed zoom in a browser.

  Why pentaho can't fix it?
   Ok, I don't know. May be it consider this bug as not important. But it is certainly a disadvantage, which may scare some potential customers. JasperReports don't have this issue, however I don't sure which way they overcome it.

Implementing while loop in Kettle

   I ran into the problem of 'while' loops in Kettle when I was loading data from Yahoo Store API. Yahoo web service allows you to get up to 100 orders pre request. But it don't tell you how many orders it have at all. So I'm sending requests until I get a special response that there are no more orders there.
   There are several ways to implement loops in Kettle:

• To use "Execute for every input row" flag in job or transformation.
• To circle hops in a job.
• To use "Repeat" flag in Start step of the job.

   Every of these approaches have its strengths, weaknesses and area of applicability. Lets consider each of them.

 Using "Execute for every input row" flag in job or transformation.
   This is the most safe, correct and native way to implement loops in Kettle, but to use it you need to know in advance how many times you want to run the job inside a loop. If you can get or calculate the number of iterations in advance - just use this approach. For more information about it I can sent you to Slawomir Chodnicki blog article about it.

 Circling hops in a job.
   This approach is not safe. When using it keep in mind that loop depth can't be too big. If you broke this rule you risk to get StackOverflowError. That is because Kettle use recursive method calls when running this kind of jobs. So if you believe your loop will not exceed say 10,000 or 100,000 iterations, depending on StackSize settings in your JVM you can run the loop in this way.
   For more information about StackOverflowError in Kettle see this JIRA issue(http://jira.pentaho.com/browse/PDI-1463). And more info about implementing this loop you can find in another article of Slawomir Chodnicki.

  Using "Repeat" flag in Start step of the job.
   I recommend this approach if you can't use both above. By checking 'Repeat' flag in Start step of a job you can easy make job running forever. More important question is how to stop it! Ok, The only way to do that in 'Out of the box' Kettle(even 4.0) is to use Abort step. It stops the job and writes an error message in log that job is finished with errors. But that may not be the case! I want my job to stop normal, successful, without errors. Why can't I do that? Why should I flood the log with ERROR messages when actually no errors occured? For that reason I implemented a simple plugin called 'Stop Job' that stops repeating job without writing error messages in log.
   You can use this plugin in the same way you use Abort step. And you can add a message which will be written to log with log level BASIC.
  Be aware that when using Repeat flag, the job will repeat to run over and over even when one of it steps fails. To stop the job in this case use Abort job step as depicted at the image below.

Here is a Stop Job plugin that you are free to use and modify.

And update 11 April 2017:
Instead of using Stop Job plugin you may simply use JavaScript steps in jobs and transformations.
Use this code in job's JavaScript step to stop job execution without failing it's result:
previous_result.setNrErrors(0);
previous_result.setResult(true);
parent_job.stopAll();

And here is a code for transformation JavaScrpt step:
_step_.stopAll();
Feedback is welcome, because I'm just starting to validate these solutions.

Running Kettle jobs from BI Server

  Most power of Pentaho comes from integration. BI Server is an integration point of multitude tools, technologies and projects. This is one of the secrets of Pentaho success. Pentaho provides access to database systems, allows developer to choose between data manipulation languages(SQL, MDX, MQL), allows to perform data analysis using WEKA data mining tool, suggests different ways for incorporating business logic, helps to perform some actions like sending e-mail based on business decisions and presents the results to user in form of charts and reports.
   PDI by itself is also a great consolidating tool. I won't praise it as much as BI server just to save your time, but when consider diversity of data sources and technologies Kettle is even more sociable then BI Server. Just have a look at Kettle plugin list to get it. I believe it can run data to earth from any data source you want.
   And now imagine you tie these great tools together. You obtain fine-grained control over Kettle jobs. You can ask user for parameters before passing them to Kettle job. You can use Kettle job as a smart data source for your reports. Using java quartz lib you can schedule Kettle jobs to run with a second-precision (for comparison linux cron allows only minutes-precision).
   But many people abandon this power and run Kettle as a separate java process. I saw a lot of unanswered question on forums about running Kettle jobs in BI Server. Up to Kettle version 4.0 some bugs was in the way of using BI Server - PDI couple tied together in enterprise environment. An example is a call of System.gc() in every Kettle job.
   Below are some advices for those bold spirits who decide to integrate these tools.

  Setting up Kettle repository
   At first you need to show BI server where Kettle repository is. The easiest way to do it is to configure repository settings with Spoon tool. After that open User Home folder on you machine and find .kettle folder. You need to place .kettle to User Home folder of the machine where BI Server is running.
   Then open file pentaho-solutions\system\kettle\settings.xml. Use following settings to connect to DB-based kettle repository:
  1. <repository.type>rdbms</repository.type>
  2. <repository.name>kettle_repo</repository.name>
  3. <repository.userid>admin</repository.userid>
  4. <repository.password>admin</repository.password>
   admin/admin are default user name/password.

  That's it.

  Custom plugins.
   If you have custom kettle plugins you need to put it in one of following places: <PATH>/plugins, <User Home>/.kettle/plugins, <pentaho-solutions>/system/kettle/plugins.

  Running jobs
   And now to run Kettle job just add an action like that to your action sequence:

    <action-definition>
      <component-name>KettleComponent</component-name>
      <action-type>Pentaho Data Integration Job</action-type>
      <action-inputs>
        <kettle-logging-level type="string"/>
      </action-inputs>
      <action-resources/>
      <component-definition>
        <directory><![CDATA[job-dir]]></directory> 
        <job><![CDATA[job-name]]></job>
      </component-definition>
    </action-definition>

   For start use 'detail' value for kettle-logging-level. It writes enough logs but don't floods log files.

Week number function in MySQL, MDX and Java

  Introduction
   Vitually every data warehouse have a date dimension. Also almost every buisness user require a weekly reports (along with daily, monthly and other). At this point report developer often uses functions that return week's number in a year. Trouble may arrise if you decide to use together functions from different languages and technologies.

   In this article I'll compare week function in Java, JavaScript, MySQL and MDX(Mondrian). You'll see that without proper attention the same date could appear in different weeks when using different technologies together. For example 1-st Jan 2010 could be 0-th week in MySQL, 1-st week in Java, and 53-rd week somewhere else.

   If you interested in aspects of generating date dimension I would recommend to read Roland Bouman's article of this topic.

  Java: Calendar
   Java provides class java.util.Calendar for operating with dates. Calendar class have a constant WEEK_OF_MONTH for getting week's number. Also Calendar have two properties for configuring week's number calculations: minimalDaysInFirstWeek and firstDayOfWeek. Default values for these properties are locale-dependent.

   Details:

• firstDayOfWeek - first day of a week(Sunday, Monday,.. Saturday).  
• minimalDaysInFirstWeek - minimal number of days in first week that are also in this year.
• first week have number 1.

   Example:

java.util.Calendar calendar = java.util.Calendar.getInstance();
java.util.Date date = (new java.text.SimpleDateFormat("yyyy-MM-dd")).parse("2010-12-31");
calendar.setTime(date);
int week = calendar.get(java.util.Calendar.WEEK_OF_YEAR);
System.out.println(week);  // prints: 52

  MySQL: WEEK
   MySQL have a WEEK function. It also allows user to change calculation algorithm, but it's not as generic as in Java.

   In MySQL user can choose between two main approaches for determining the first week of the year:

1. It is the first week that starts in this year. Read as: Sunday or Monday in this year. (American approach)
2. It is the week that contain January 1-st, if more that 3 days of this week are in the new year. (European approach)

   This discussion may help to understand that.
   If a date falls in the last week of the previous year, MySQL may return 0 in certain configurations.

   Example:

select WEEK(STR_TO_DATE('Dec 31, 2009','%M %d,%Y')) ; - 52
select WEEK(STR_TO_DATE('Jan 1, 2010','%M %d,%Y')) ; - 0

select WEEK(STR_TO_DATE('Dec 31, 2009','%M %d,%Y'), 3) ; - 53
select WEEK(STR_TO_DATE('Jan 1, 2010','%M %d,%Y'), 3) ; - 53

  MDX: DatePart.
   MDX uses VisualBasic's function: DatePart. It allows setting first day of week to any day you like. And for first week of the year it provides following options:

• Use system setting
• Start with the week in which January 1 occurs (default)
• Start with the week that has at least four days in the new year
• Start with the first full week of the new year

 
  JavaScript
   JavaScript don't have a standard function for week number. So be careful when writing your own, or using functions provided with JavaScript libraries and when you want to use it together with Java's , MySQL's or MDX's functions.
 
  How to use them together?
   The straightest approach is to avoid using them together. You may pre-generate date dimension with help of Java for example. And when later you need to determine week's number by date you may use either Java or date dimension's table.
   However if you still need to use week number functions from a mix of technologies, be sure that you understand how each function works, and configure them properly.