Relational applications often model generic hierarchies of variable depth (tree-like structures) by maintaining a parent id that points to the immediate parent of the record. This approach is called the “Adjacency List” model. This article covers how to effectively analyze such hierarchies using a bridge table and how to create a bridge table using Kettle a.k.a. . . . → Read More: Analyzing Hierarchical Data Using Bridge Tables
When looking at time series that exhibit strong deviations, it is sometimes hard to get the general picture of the development. A practical approach is to smooth out the values by calculating a sliding average. To illustrate, I’d like to look at the example cube from the article about date dimensions, and select the sales per . . . → Read More: Using Sliding Averages in MDX
Many organizations have the requirement to monitor the best performing assets. These might be top selling salespeople, products, stores or even days. It often necessary to also aggregate the remaining assets into a single entry representing all other assets, effectively creating an entry to represent the performance of all “others”. This post shows how this is . . . → Read More: Creating Top-N Reports using MDX
Date dimensions are among the most important dimensions of many Mondrian cubes. The usefulness of a cube often depends on the way the date dimension has been modeled. This post shows how to create a basic date dimension and how it can be augmented with properties to suit specific analysis needs. If at some point you . . . → Read More: A Simple Date Dimension for Mondrian Cubes
When working with Mondrian cubes I often find myself wanting to look at a time series chart. Say I want to look at some KPIs’ development in the last 60 days. So I write an MDX query that dynamically determines the last 60 days and selects them together with the KPIs to produce the chart. Most . . . → Read More: Using Named Sets in Mondrian
The previous post explains how to create a star schema for a Mondrian cube. The dimensions in the example data were very simple. Each dimension had only a single field whose value ended up being a member in the cube. In this post I would like to build a cube that has dimensions with multiple levels. . . . → Read More: A Basic Mondrian Cube: Using Multi-Level Dimensions
The previous post explained how to create a simple Mondrian cube. It is meant to provide the most basic information on how Mondrian cubes are created and stored. The previous solution uses only one DB table to store the entire cube. It uses only degenerate dimensions, to use Mondrian parlance.
This article builds on the basics already . . . → Read More: A Basic Mondrian Cube: Introducing the Star Schema
This post is a hands on tutorial on how to create an analysis cube for the Mondrian OLAP engine. It is an introductory post for an audience with no OLAP experience at all. I will assume some experience with relational databases and the Kettle ETL tool. If you’d like to follow the examples you will need access to a database, a copy of Pentaho Kettle and a Mondrian installation. I will be using MySQL as RDBMS and JasperServer 3.7. CE for the Mondrian installation. Other possibilities include Pentaho BI-Server and a bare bones Mondrian/JPivot and PAT installation.
The example data
For the example data I would like to use a small and simple dataset, so it is easy to share and easy to understand. Therefore I decided to use an Excel extract from the public issue tracker for the Kettle tool. It has one thousand lines of issues, bugs and feature requests that I would like to put into an OLAP cube for analysis. Each row contains the issue type, a summary, the assignee, a priority, a status and a resolution. I took the liberty to replace the real names of the assignees with figures from Sesame Street in the input data. And no, I will not reveal who is who . . . → Read More: Creating a basic Mondrian OLAP Cube