Modules:Analytics Introduction

Contents 1 What are OLAP Cubes 2 How Openbravo integrates them 2.1 Mondrian 2.2 Saiku 3 Stack of technologies 3.1 An example of the XML file generated for Saiku 3.2 An example of the MDX query generated for that file 3.3 An example of one of the SQL queries generated for that MDX query

What are OLAP Cubes

An OLAP cube is a multidimensional array of data. OLAP is an achronym for Online Analytical Processing, it is a category of software, part of the broader category of Business Intelligence, that enables analysts, managers and executives to gain insight into data through consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user.

OLAP tools enable users to analyze multidimensional data interactively from multiple perspectives. OLAP consists of three basic analytical operations: consolidation (roll-up), drill-down, and slicing and dicing.

More information about OLAP Cubes can be found here and about OLAP here.

How Openbravo integrates them

In order to provide Analytical Analysis, Openbravo integrates two different external technologies.

One is Mondrian by Pentaho, which acts as the underlying OLAP engine. The other is Saiku by Meteorite Consulting, which acts as the user interface for developing the Reports.

Mondrian

Mondrian is an OLAP engine written in Java. It executes queries written in the MDX language, reading data from a relational database (RDBMS), and presents the results in a multidimensional format via a Java API.

It has been fully integrated with Openbravo, so there is no need for an intial configuration to start working with it. Although it is possible to configure several parameters to improve performance in environments with large amounts of data. More documentation about the configuration can be found in the configuration section.

More information about Mondrian can be found in their Documentation.

Openbravo supports Mondrian version 3.5.

Saiku

Saiku is a modular analysis suite offering lightweight OLAP which remains easily embeddable, extendable and configurable. It offers a user friendly, web based analytics solution that lets users, quickly and easily analyse corporate data and create and share reports. The solution connects to a range of OLAP Servers.

Saiku is embedded into some Openbravo Windows, like OB Analytics, providing a user friendly interface to create Analytical Reports.

More information about Saiku can be found in their Documentation.

Openbravo supports Saiku version 3.5.

Stack of technologies

With this two new technologies incorporated in Openbravo, it is important to understand how they communicate with the application and between them.

A full example of the stack of technologies starts with a report created in Openbravo, like the Multi-Dimensional Financial Reports, and ends with the queries fired against the database.

In this example the report creates an XML file that Saiku can understand. Saiku then generates the MDX query that then sends to Mondrian. Mondrian reads this MDX query and, if it has the data already cached, returns the result. If that is not the case, then launches a series of SQL queries against the database to retrieve the data.

An example of the XML file generated for Saiku

<?xml version="1.0" encoding="UTF-8"?>
<Query name="6793C26C4B504EF9822E16C2C63BAE0FDAF5CA2984604E1A8673D1C659B4057D" type="QM" connection="Openbravo" 
cube="DPL - P&amp;L - F&amp;B International Group US_A_US Dollar" catalog="Openbravo" schema="Openbravo">
    <QueryModel>
        <Axes>
            <Axis location="ROWS" nonEmpty="true">
                <Dimensions>
                    <Dimension name="account" hierarchizeMode="PRE" hierarchyConsistent="true">
                        <Inclusions>
                            <Selection dimension="account" type="member" node="[account].[724F823C8DD44203A69BE218D6D8AF61]" operator="MEMBER" />
                            <Selection dimension="account" type="member" node="[account].[724F823C8DD44203A69BE218D6D8AF61]" operator="CHILDREN" />
                        </Inclusions>
                        <Exclusions />
                    </Dimension>
                </Dimensions>
            </Axis>
            <Axis location="COLUMNS" nonEmpty="true">
                <Dimensions>
                    <Dimension name="accountingDate">
                        <Inclusions>
                            <Selection dimension="accountingDate" type="member" node="[accountingDate].[2013]" operator="MEMBER" />
                        </Inclusions>
                        <Exclusions />
                    </Dimension>
                    <Dimension name="organization" hierarchizeMode="PRE" hierarchyConsistent="true">
                        <Inclusions>
                            <Selection dimension="organization" type="level" node="[organization].[organizationLevel]" operator="MEMBERS" />
                        </Inclusions>
                        <Exclusions />
                    </Dimension>
                </Dimensions>
            </Axis>
            <Axis location="FILTER" nonEmpty="false">
                <Dimensions>
                    <Dimension name="Measures">
                        <Inclusions>
                            <Selection dimension="Measures" type="member" node="[Measures].[balance]" operator="MEMBER" />
                        </Inclusions>
                        <Exclusions />
                    </Dimension>
                </Dimensions>
            </Axis>
        </Axes>
    </QueryModel>
    <MDX />
    <Properties>
        <Property name="saiku.ui.render.mode" value="table" />
        <Property name="org.saiku.query.explain" value="true" />
        <Property name="saiku.olap.query.nonempty.columns" value="true" />
        <Property name="saiku.olap.query.nonempty.rows" value="true" />
        <Property name="org.saiku.connection.scenario" value="false" />
        <Property name="saiku.ui.formatter" value="flat" />
        <Property name="saiku.olap.query.automatic_execution" value="true" />
        <Property name="saiku.olap.query.drillthrough" value="true" />
        <Property name="saiku.olap.query.filter" value="true" />
        <Property name="saiku.olap.query.limit" value="true" />
        <Property name="saiku.olap.query.nonempty" value="true" />
    </Properties>
</Query>>

An example of the MDX query generated for that file

SELECT
NON EMPTY CrossJoin({[accountingDate].[2013]}, [organization].[organizationLevel].Members) ON COLUMNS,
NON EMPTY {Hierarchize({{[account].[724F823C8DD44203A69BE218D6D8AF61], [account].[724F823C8DD44203A69BE218D6D8AF61].Children}})} ON ROWS
FROM [DPL - P&L - F&B International GROUP US_A_US Dollar]
WHERE {[Measures].[balance]}

An example of one of the SQL queries generated for that MDX query

SELECT
    "alias1_90"."c_elementvalue_id" AS "c0",
    "alias1_90"."em_obanaly_treeordinal" AS "c1"
FROM
    "c_elementvalue" AS "alias1_90"
WHERE
    (alias1_90.ad_org_id IN ('E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01',
'BAE22373FEBE4CCCA24517E23F0C8A48','19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43') 
AND 1=1 AND EXISTS(SELECT c_element_id FROM c_acctschema_element elem0 
WHERE alias1_90.c_element_id=elem0.c_element_id AND elem0.c_acctschema_id = '732913485BB040FFA4643FF06D1AA095'))
AND
    "alias1_90"."em_obanaly_treeparentid" = '724F823C8DD44203A69BE218D6D8AF61'
GROUP BY
    "alias1_90"."c_elementvalue_id",
    "alias1_90"."em_obanaly_treeordinal"
ORDER BY
    "alias1_90"."em_obanaly_treeordinal" ASC NULLS LAST]
1739582 [mondrian.rolap.RolapResultShepherd$executor_1] DEBUG mondrian.sql  - 12: SqlMemberSource.getMemberChildren: executing sql [
SELECT
    "alias1_90"."c_elementvalue_id" AS "c0",
    "alias1_90"."em_obanaly_treeordinal" AS "c1"
FROM
    "c_elementvalue" AS "alias1_90"
WHERE
    (alias1_90.ad_org_id IN ('E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01',
'BAE22373FEBE4CCCA24517E23F0C8A48','19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43') 
AND 1=1 AND EXISTS(SELECT c_element_id FROM c_acctschema_element elem0 
WHERE alias1_90.c_element_id=elem0.c_element_id AND elem0.c_acctschema_id = '732913485BB040FFA4643FF06D1AA095'))
AND
    "alias1_90"."em_obanaly_treeparentid" = '724F823C8DD44203A69BE218D6D8AF61'
GROUP BY
    "alias1_90"."c_elementvalue_id",
    "alias1_90"."em_obanaly_treeordinal"
ORDER BY
    "alias1_90"."em_obanaly_treeordinal" ASC NULLS LAST

Category: Analytics