Deutsch   |   English
   
 digg.com  del.icio.us 

Real time OLAP with Mondrian

Mondrian is an open-source OLAP server which offers a XMLA interface to send MDX queries. Multi-dimensional cubes are defined logically in Mondrian and are mapped physically to a relational database. Cubes are calculated on demand in EXASolution and are cached in Mondrian for further queries. Pre-aggregation of data is no longer an issue and fast real time OLAP is possible in combination with EXASolution. The standardized XMLA interface offers easy integration into existing reporting and analysis tools.

In the following article we we will define a cube using the Mondrian Schema Workbench and publish it on the Mondrian Server using the XMLA interface. You can download a preconfigured Mondrian package including Tomcat here.

MDX-Schnittstelle über Mondrian

Definition of a cube through Mondrian Schema Workbench

A multi dimensional database consisting of the logical model (cubes, hierarchies and measures) and the mapping to the relational database is defined in a Mondrian schema, a description in XML.

The Mondrian Schema Workbench is a graphical development tool to create and validate such a schema. The tool also checks whether the mapping to the relational database is correct. Additionally it has a simple interface to send MDX queries against the created schema.

Download the newest version of the workbench and the EXASolution JDBC driver. You should have also installed a JRE 6. Unpack the Workbench and copy jdbc14.jar into the folder WORKBENCH_HOME/drivers.

You can start the Workbench with the script workbench.bat respectively workbench.sh. Create the connection to EXASolution under Options->Connection... . Choose "generic database" as connection type, as access "Native JDBC" as custom connection "jdbc:exa:<connection string>", as custom driver class "com.exasol.jdbc.EXADriver" and enter your username and password. Press the test button to check if the connection to EXASolution succeeds.

Open the file TPCH1.xml in the workbench. Our example is based on the relational TPC-H schema from the TPC-H Benchmark , which is part of our demo system. The schema editor is split in two areas:

On the left-hand side is a tree that shows the structure of the schema while the right part shows the attributes of selected elements. The toolbar on the top has various buttons to create new elements like cubes, dimensions etc.

Our predefined schema consists of two cubes: Q1 and Q2. A cube consists of a fact table and several dimensions and measures. The cube Q2 is based on the fact table ORDERS and has a dimension "customer" with the hierarchies "region" and "nation". The dimension table for this hierarchy is a nested join over the tables CUSTOMER,NATION and REGION (a standard Mondrian-construct, see screenshot). The dimension table is linked to the fact table ORDERS via the foreign key C_CUSTKEY. A typical OLAP time dimension consisting of year, quarter and month is defined over a temporary view of the column O_ORDERDATE from the table ORDERS:

  1. WITH ORDERDATE (FULLDATE,M,Q,Y) AS (
  2.         SELECT
  3.             O_ORDERDATE,
  4.             TO_CHAR(O_ORDERDATE, 'MM') ,
  5.             TO_CHAR(O_ORDERDATE, 'Q'),
  6.             TO_CHAR(O_ORDERDATE, 'YYYY')
  7.         FROM
  8.             TPC.ORDERS
  9.     )
  10. SELECT DISTINCT * FROM ORDERDATE;

Based on this view we will define the hierarchies year, quarter and month. Q2 also contains the measure 'Sum TotalPrice' which is defined with the aggregator sum using the column O_TOTALPRICE of the fact table ORDERS.

The workbench automatically validates the used columns and tables against the physical database schema. This secures that the mapping is working in the productive implementation. MDX queries can be sent directly to our defined schema with a small tool found under File->New->MDX-Query We will send a MDX query that calculates 'Sum TotalPrice' for the years 1992, 1993, 1994 of the regions AFRICA, ASIA and EUROPE:

  1. SELECT {[Orderdate].[1992],
  2. [Orderdate].[1993],
  3. [Orderdate].[1994]} ON COLUMNS,
  4. {[Customer].[AFRICA                   ],
  5. [Customer].[ASIA                     ],
  6. [Customer].[EUROPE                   ]}
  7. ON ROWS
  8. FROM [Q2]
  9. WHERE [Measures].[SUM TotalPrice]

Additional information about the definition of mondrian schemas can be found on the Mondrian Documentation website.

Publication of the cube and setup of the XMLA interface

If you haven't yet downloaded the preconfigured Tomcat/Mondrian package, please download it now and unpack it. The package already contains the preconfigured schema file TPCH1.xml in the folder TOMCAT_HOME/webapps/mondrian/WEB-INF/queries.

To configure the XMLA interface we will edit the file TOMCAT_HOME/webapps/mondrian/WEB-INF/DataSources.xml.

We define the name of the DataSource as 'Provider=Mondrian;DataSource=TPCH' and the URL of the XMLA interface as 'http://localhost:8080/mondrian/xmla'. The JDBC connection to EXASolution is defined in the element 'DataSourceInfo'. Please edit username and password as required. Finally we define the Catalog TPCH with the URI to the schema definition file TPCH1.xml. The complete DataSources.xml looks like this:

  1. <?xml version="1.0"?>
  2.  
  3. <DataSources>
  4.  
  5.     <DataSource>
  6.    
  7.         <DataSourceName>Provider=Mondrian;DataSource=TPCH;</DataSourceName>
  8.  
  9.         <DataSourceDescription>TPCH</DataSourceDescription>
  10.  
  11.         <URL>http://localhost:8080/mondrian/xmla</URL>
  12.        
  13.         <DataSourceInfo>Provider=mondrian;
  14.             Jdbc=jdbc:exa:212.204.74.3..8:8563;
  15.             JdbcUser=test;JdbcPassword=password;
  16.             JdbcDrivers=com.exasol.jdbc.EXADriver;
  17.             Catalog=/WEB-INF/queries/TPCH1.xml 
  18.         </DataSourceInfo>
  19.  
  20.         <ProviderName>Mondrian</ProviderName>
  21.  
  22.         <ProviderType>MDP</ProviderType>
  23.  
  24.         <AuthenticationMode>Unauthenticated</AuthenticationMode>
  25.  
  26.         <Catalogs>
  27.             <Catalog name="TPCH">
  28.                 <Definition>/WEB-INF/queries/TPCH1.xml</Definition>
  29.             </Catalog>
  30.         </Catalogs>
  31.  
  32.     </DataSource>
  33.  
  34. </DataSources>

Testing the XMLA interface with JPivot

We will now test the configured XMLA interface with the OLAP web application JPivot. JPivot is part of the standard Mondrian package. Before the test you can modify the file TOMCAT_HOME/webapps/mondrian/WEB-INF/queries/xmla.jsp, for example to change the sample mdx query, the XMLA URL or the used catalog. The complete xmla.jsp looks as follows:

  1. <%@ page session="true" pageEncoding="UTF-8" contentType="text/html; charset=ISO-8859-1" %>
  2. <%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
  3. <%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
  4.  
  5.  
  6. <!--
  7.    * Change uri attribute to your deployment of this webapp.
  8.    * The dataSource attribute is necessary for Mondrian's XMLA.
  9. -->
  10. <jp:xmlaQuery id="query01"
  11.    uri="http://localhost:8080/mondrian/xmla"
  12.    dataSource="Provider=Mondrian;DataSource=TPCH;"
  13.     catalog="TPCH">
  14. select {[Orderdate].[1992],[Orderdate].[1993],[Orderdate].[1994]} ON COLUMNS,
  15.                 {[Customer].[AFRICA                   ],
  16.                 [Customer].[ASIA                     ],
  17.                 [Customer].[EUROPE                   ]}
  18.                 ON ROWS from [Q2] where [Measures].[Sum TotalPrice]
  19. </jp:xmlaQuery>
  20.  
  21. <c:set var="title01" scope="session">Accessing Mondrian powered by EXASolution by XMLA</c:set>

Set the environment variable JAVA_HOME to the according path, for example SET JAVA_HOME=C:\jre16 on Windows systems respectivly EXPORT JAVA_HOME=/user/bin/jre on Linux/Unix systems. Start the Tomcat application server with the script TOMCAT_HOME/bin/startup.sh respectivly startup.bat and point your webbrowser to localhost/mondrian/testpage.jsp, to test the XMLA interface with JPivot. The result of our sample MDX query is showed as a table.

Thereby we secured that the XMLA interface works correctly. As a next step you can connect your reporting- or analysis tool with the XMLA interface. When you connect your tool at the XMLA interface at 'http://localhost:8080/mondrian/xmla' you can use your OLAP tool with EXASolution as backend and profit from the performance gain and additional flexibility. Your cubes are calculated on the fly.

Are you stuck?

To Support

Learn from the experts:

Training

References
  • AHOOLY
  • COOP

  • “With EXASolution, we are best prepared for future data growth. The fact that we can now process geodata – that is to say, also evaluate the dimension of the spatial reference – means that we can offer our customers brand new analysis options and even more comprehensive market overviews. We are very satisfied with the new solution. We can react flexibly to future demands but still reduce our total cost of ownership as the selected BI stack fully supports our vision of a ‘lean BI’.”

    Guido Niermann, Head of IT, Dataforce GmbH
    Dataforce

  • "By integrating EXASOL we can offer our customers a whole new experience in explorative data analysis… Besides the technical aspects we were also extremely pleased with the pre-sales advice and support during the integration." 

    Martin Heink
    CEO and owner, econda 
    Econda

  •  

     

     

     
     
    IMS

  • "With EXASOL’s innovative database we are able to perform complex analyses more precisely and with a larger amount of data. Thus we have a significant technological lead over our competitors." 

    Tobias Kiessling
    CTO, intelliAd 
    Intelliad

  • "The continuously high performance and the possibility to run real-time-analyses were the decisive factors for us when chosing EXASolution." 

    Tobias Kroha, CEO, seto GmbH (m-pathy-project)

     
    m-pathy
  • Media Control
  • Olympus

  • "We chose EXASolution because of its high performance; it offers optimal flexibility and handles our increasing amount of data very well." 

    Dr Michael Röbbecke
    (former) CEO, RatePAY 
    RatePAY

  • "With EXASolution we can considerably optimise our business processes."

    Gerhard Zapf
    Project Leader, Semikron 
    Semikron

  • "Reliable and quick support, excellent customer care and proven expertise…"

    David Hodge
    IT Director, Sony Music Entertainment Germany 
    Sony Music
  • SOQUERO
  • SponsorPay
  • Stayfriends

  • "EXASOL’s database is technology made in Germany on which we can count on in the long term. As it is self-scaling and physically expandible, the database grows with our company, and we are able to react to new requirements flexibly and quickly also in the future." 

    Heinrich Zetlmayer
    CEO, Turtle Entertainment 
    Turtle Entertainment
  • United Internet Dialog

  • "With EXASolution, we have acquired a database solution that ideally meets the high standards we demand of complex analyses on increasing volumes of data for our customers." 

    Christian Sauer
    CEO, Webtrekk 
    Webtrekk

  • "We chose the solution from EXASOL after an extensive benchmark test. The high performance of the system, the price/performance relationship and the service had us completely convinced." 

    Dr Ulrich Fricke
    Head of Business Intelligence, XING 
    Xing

  • "Efficiency, speed, high performance and flexibility were the determing criteria when chosing our database… The new database offers us scalability with reduced total cost of ownership. Thus also in the future we are able to perform optimal analyses for our customers…" 

    Sebastian Hoop
    Head of Operations, xplosion interactive     
    Xplosion
  • Zalando

EXASOL is developing a data management system capable of rapid data analysis and evaluation. EXASolution is an on in-memory technology based relational database, designed for time-critical, complex analytics, planning and reporting. Specially developed for enterprise data warehouse applications and business intelligence solutions. Successfully in use in a broad field of applications, e.g. web analytics, data mining, big data and cloud computing.