Mondrian server OLAP (analytical processing in real time) with open source codes written in Java. Developed and maintained by the Corporation Pentaho.

To create the schema you will need to use a desktop application Pentaho schema workbench.

OLAP-куб — (On-Line Analytical Processing — interactive data analysis) multidimensional array data are usually sparse and long-term stored, used in OLAP. Can be implemented on the basis of a universal relational DBMS or specialized software. For details, see OLAP cube.

the creation of the schema

To generate the schema, you must run the action: File -> new -> schema. The attributes of the new scheme to fill name (name of scheme).

To save the schema, you need to run the action: File -> save.

configure a connection to a database

Sets the DB connection: Options -> connection. In the window that appears in section General to fill:

  • Connection name – Name for подключения;
  • Connection Type = Postgre SQL – Type storage данных;
  • Access = Native (JDBC) – Type подключения;
  • Settings:

cube Creation

  • Created schema, you must create a cube (PCM scheme and click Add cube» qmo)

The attributes of the new cube to fill:

  • name – the name of the cube.

  • Next, you need to add the fact table for the cube (RMB on the cube and click Add Table» qmo);

The attributes of the new table to fill in:

  • schema diagram БД;
  • name – fact table for the cube.

Create A Measure

Add measure to the cube (RMB on the cube and select the Measure» «Add) and fill:

  • name – the name of the action (Displayed in the web interface);
  • aggregator – a type of calculation that takes one of the values:

  • sum;
  • count;
  • min;
  • max;
  • avg;
  • distinct-count.
  • column – the name attribute of the fact table, which will be considered as a measure.

Create A Dimension

Measurement scheme

The measurement scheme is used in cases when within the same schema creates several cubes. For multiple cubes, you can use a single measurement scheme. Thus we only need to add a reference to this dimension in the cube.

1.To create a new dimension for the circuit (PCM scheme and click on the Dimension).

If a dimension is created for the schema, not the Cuba of the essential attributes only the name field is the name. Field type – the type of measure is populated by default value StandartDimension for time measurements. For time measurements is populated with the value of the TimeDimension.

2.The disclosure of the measurement appears in the hierarchy. Required attributes for hierarchy:

  • name – name of hierarchy
  • primarykey - column in the dimension table, the values of which coincide with any column of the tables (s) of facts (for the connection of cube and dimension)

3.Add table to measure (PCM hierarchy and click on Add Table). Among the mandatory attributes, the level has the following:

  • schema diagram БД;
  • name – the name of the table

4.After the table is added, we similarly need to add at least one level (level). Among the mandatory attributes, the level has the following:

  • name – the name уровня;
  • column (the column that will be displayed as a dimension in the cube).

5.To use the dimension diagrams in the cube, you need to click RMB on the desired cube and click add dimension usage

Required attributes:

  • name – the name of the dimension in the cube.
  • foreign key – a column in the fact table whose values must match the key specified in the dimension hierarchies.
  • source – select the dimension that will be used.

Optional attribute:

  • caption – the name for the measurement that will be carried for a specific cube.

Dimension for the cube

1.To use a dimension for a cube, you need to click RMB on the desired cube and click add dimension

If a dimension is created for cube, then fill in the following fields:

  • name – имя;
  • type – the type of measure is populated by default value StandartDimension for time measurements. For time measurements is populated with the value of the TimeDimension.
  • foreignKey – foreign key to link to the fact table of the cube.

2.The disclosure of the measurement appears in the hierarchy. Required attributes for hierarchy:

  • name – name of hierarchy
  • primarykey - column in the dimension table, the values of which coincide with any column of the tables (s) of facts (for the connection of cube and dimension)

3.Add table to measure (PCM hierarchy and click on Add Table). Among the mandatory attributes, the level has the following:

  • schema diagram БД;
  • name – the name of the table

4.After the table is added, we similarly need to add at least one level (level). Among the mandatory attributes, the level has the following:

  • name – the name уровня;
  • column (the column that will be displayed as a dimension in the cube).

Dimension JOIN

Used class diagram:

1.Create A Dimension 2.In an already established hierarchy RMB and create Join 3.Fill in the Join table references

4.In the Join you need to specify the primarykey and foreignkey

5.Create A Level:

6.Fill in the Hierarchy to associate with a fact table

7.In the cube you need to create a Dimension Usage

The cube schema:

Dimension hierarchy

Use Levels

1.Model:

2.To create a new dimension for the scheme or for the cube

If you intend to work with dates, it should be noted attribute type = TimeDimension 3.Next, fill in the Hierarchy 4.Add a table for the hierarchy 5.After creating the first level:

  • Fill In The Year:

  • Fill In The Month:

Using Closure Table

0.Model:

1.To create a new dimension for the scheme or for the cube

2.Next, fill in the Hierarchy

3.Add a table for the hierarchy

4.Further, in the disclosed hierarchy need to add a level

5.In the generated level, you need to add Closure

6.Fill in Closure, select the Table in the desired table

7.To complete a level, it is necessary to fill fields:

  • name – the name уровня;
  • table – select from the drop-down list for table измерения;
  • column – attribute primary key таблицы;
  • nameColumn attribute to display the value измерения;
  • the parentColumn attribute is for a key родителя;
  • type – the type данных;
  • uniqueMembers – mark the checkbox to mark that this level is the primary счету;
  • levelType – Regular;
  • hideMemberlf – Never
  • caption – the display name

8.Fill in the attributes Closure parentColumn and childColumn values from drop down lists:

9.For a cube to create a link to measurement:

10.To Fill In The Dimension Usage:

Create a virtual cube

To display multiple cubes in one schema function create the virtual cubes. This requires:

1.Create at least two of the cube dimensions and measures

2.In an open circuit, you need to create a virtual cube (PCM scheme and click Add virtual cube)

3.Fill in the window that appears, name (name) for a virtual cube

4.To add to the virtual cube is references to dimension normal cubes, for this:

  • RMB on the virtual cube and virtual cube choose Add dimension

  • In the resulting window, a virtual measurement to fill in the attributes: name and cubeName exactly as they are called in the source cube

5.Add in a virtual cube reference measures a normal cubes for this:

  • RMB on the virtual cube and virtual cube choose Add measure

  • In the resulting window, a virtual measurement to fill in the attributes: name and cubeName exactly as they are called in the source cube