Home > Configuration, Debian, Linux, OLAP, OpenERP v6.0, Ubuntu, Uncategorized > Business Intelligence, integrating OpenERP and Mondrian

Business Intelligence, integrating OpenERP and Mondrian

Crystal Reports

Image via Wikipedia

These days we are implementing a custom module we developed for a client, and the customer asked us for hundreds, if not  thousands, of reports. So, faced with the option of devoting hundreds of development hours to create reports, we decided to implement an OLAP and reporting tool for the customer. And integrating that tool with OpenERP. After making a quick assessment among the different business intelligence tools available in the open source “market”, we decided to go with JasperServer and iReport.

You can download both tools from the JasperForge website. Installing them was quite simple and straightforward, and in no time I was running reports against the OpenERP database. iReport is a report generation tool, and reminds me of Crystal Reports. JasperServer is a report and OLAP server. It can display the reports created with iReport and work with Mondrian cubes (more on this later).

I will not go into the details on how to work with both tools. I would like to talk about how I generated a cube from an existing OpenERP database. Well, this is the “DON’T TRY THIS AT HOME” section. Doing what I will explain in the next paragraphs is a very bad idea in a production setting, since OLAP calculations are resource hungry on the server, and this could run out of resources pretty soon if you are servicing your users and OLAP calculations.

Well, after making the disclaimer, I will proceed into explaining how to create an OLAP (actually ROLAP since in no moment I created a physical cube) from the OpenERP database. The first step is to create the Data Source in JasperServer. It is quite straightforward and JasperServer comes with a Test button which allows you check whether the connection settings you just created are right or wrong.

The next step is to create an OLAP schema. An OLAP schema in JasperServer (and the Mondrian world) is a XML file which pretty much describes the OLAP cube. In this file you describe the fact table, the dimesions, its elements and aggregations. For example, below you will find an example I created for the sales order table in OpenERP:

<?xml version=”1.0″?>
<Schema name=”SaleOrderSchema”>
<Cube name=”SaleOrder”>
<Table name=”sale_order”/>
<Dimension name=”Cliente” foreignKey=”partner_id”>
<Hierarchy hasAll=”true” allMemberName=”Todos los clientes” primaryKey=”id”>
<Table name=”res_partner”/>
<Level name=”Cliente” column=”name” uniqueMembers=”true”/>
</Hierarchy>
</Dimension>
<Dimension name=”Picking Policy”>
<Hierarchy hasAll=”true” allMemberName=”Todas las policies”>
<!– No table element here.
Fact table is assumed. –>
<Level name=”Picking Policy” column=”picking_policy” uniqueMembers=”true” />
</Hierarchy>
</Dimension>
<Dimension name=”State”>
<Hierarchy hasAll=”true” allMemberName=”Todos los estados”>
<!– No table element here.
Fact table is assumed. –>
<Level name=”State” column=”state” uniqueMembers=”true” />
</Hierarchy>
</Dimension>
<Dimension name=”Order Policy”>
<Hierarchy hasAll=”true” allMemberName=”Todos las ordenes”>
<!– No table element here.
Fact table is assumed. –>
<Level name=”Order Policy” column=”order_policy” uniqueMembers=”true” />
</Hierarchy>
</Dimension>

<Measure name=”Cantidad” column=”id” aggregator=”count” formatString=”Standard”/>
<Measure name=”Monto” column=”amount_total” aggregator=”sum” formatString=”Standard”/>
</Cube>
</Schema>
In this example, I have a fact table which is based on the sale_order table with three inline dimensions (order policy, picking policy and state) and the partner dimension. The cube has two measures, which is quantity and amount. The next step is to create the analysis connection, which connects the OLAP schema with the datasource. And the final step is to create the view. An analysis view is a query that pulls the information from the database, this is done with MDX, which is a query language similar to SQL for pulling OLAP information. An example of an MDX query is listed below:

select {[Measures].[Cantidad], [Measures].[Monto]} ON COLUMNS,
NON EMPTY {([Picking Policy].[Todas las policies],[State].[Todos los estados],[Order Policy].[Todos las ordenes])} ON ROWS
from [SaleOrder]

After creating the view (and entering the MDX) you will be ready to work with the OLAP cube. Just click on the view and you will see an screen like this,

Now you will be able to slice and dice your information,

Cheers!

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: