Knowage – OLAP Analysis

Onlyne Analytical Processing (OLAP) means analyze big amount of data, in real-time, with fast response time, to allow high interactivity. This is usually guaranteed by a pivot table: in Fig.1 you can see the Knowage OLAP Document that contains a pivot table as the main part.

In this AIKnow.info blog article we will see how to develop an OLAP document with Knowage, starting from the dataset described in the article Knowage – query databases in a visual way with QBE.


Fig.1 Knowage OLAP Document

OLAP employs Multidimensional Analysis. Whereas a relational database stores all data in the form of rows and columns, a multidimensional dataset, in short a cube, consists of dimensions, hierarchies and measures.

The first step to create an OLAP analytical document with Knowage is to write a new Schema with Mondrian. Mondrian is a ROLAP tool: it maps OLAP structures, such as cubes, dimensions and attributes directly on tables and columns of a relational data base via XML-based files. More information on this tool can be found at https://mondrian.pentaho.com/.

First it’s necessary to create a star schema, that consists on a table of fact that references two or more table of dimensions as depicted in Fig.2


Fig.2 Star schema

From the data in the table “0054_consumo_energia” (see article Knowage – query databases in a visual way with QBE Fig.3), it’s possible to generate a star schema where the fact is power consumption. The fact table (Fig.3) contains the power consumption data (column “dato”), the foreign keys “territorio” to Cities dimension table (Fig.4) and “anno” to Time dimension table (Fig.5).

Fig. 3 Fact Table

Fig.4 Cities dimension table


Fig.5 Time dimension table

With the star schema ready, it’s possible to create Mondrian schema. In few words a Mondrian schema is an XML file where dimensions and cube features are specified. In Fig.6 you can see in detail the XML file. The interpretation is quite simple, anyway more details about writing the schema can be found here https://mondrian.pentaho.com/documentation/schema.php


Fig.6 Mondrian schema

Once written, schema can be uploaded in Knowage using Catalogs-Mondrian schemas catalog Menu (Fig.7)


Fig.7 Mondrian schema upload

Now the Document can be created using Document Browser – New document – Generic Document Menu (Fig.8). Label and Name field are mandatory, Type must be set to On-line analytical processing, Engine to OLAP Engine, Data Source to the one in use (in my case Data Set name is MySQL). Then it’s necessary to specify the folder for the document using the tree in the right side of the page.


Fig.8 New Document Creation

Clicking on Template build icon opens the OLAP designer (Fig.8), where it’s necessary to choose the Type of Template (Mondrian), the Mondrian Schema (see Fig.6) and the Cube (see Fig.5).


Fig.9 OLAP Designer

Clicking Start on the right upper corner opens the Document (Fig.10), whose central part is the pivot table that can  be used to analyze data.

Axes panel can be used to place hierarchies (represented with filter card) on columns or rows axis, position them in a particular order, swap axes.

Clicking on the icon positioned on the top right side of the page you can open the side bar that allows to choose between different data representations and different drill types.

Fig.10 OLAP Document