Archive

Posts Tagged ‘Crystal Reports’

Giving feedback to the OpenERP team

February 15, 2011 Leave a comment
Software value feedback loop

Image by jakuza via Flickr

I found the OpenERP Improvement Ideas website. It is a very interesting site and it is a way of collaborating on setting OpenERP roadmap. The way it works? It is pretty simple. After creating your registered user, you are assigned a point amount (15 I think, I tried it last Sunday but I forgot, and I don’t want to create a new user). And you are able to vote for the different development initiatives. Plus, you are able to comment on them and discuss them with the development team and other OpenERP users. It is a very cool idea for collaborating in the development process, giving feedback and enhancing the community. Plus, it is a good idea well implemented, since the voters system is quite simple and powerful at the same time.

Cheers!

Advertisements

ETL and OpenERP

February 9, 2011 4 comments
Figure 1: Simple schematic for a data warehous...

Image via Wikipedia

ETL stands for Extraction, Transformation and Loading. They are used to integrate different systems in different platforms, and they do it in batch mode, they don’t do it in real-time (well, there are some tools that do it, but most of the times we integrate the systems on a periodic basis).

I will not explain how an ETL system works, I will explain why you should use it with OpenERP. In a latter post I will cover the topic of updating OpenERP data with ETL.

Why use ETL? Many reasons. The first reason is it is a very productive tool and once you learn how to use it, your productivity as a developer increases twofold (at least). Developing integration jobs is quite intuitive and they allow you integrate diverse environments, such as data from different databases and operating systems in a single job.

The second reason is these tools already have integrated a production environment for monitoring and executing the integration jobs. These environments have grown more robust along the times and its logging facilities are quite impressive. OK, you can do develop the environment yourself, but it takes time and testing, it is not cheap.

The third reason is, you will always need to integrate systems with OpenERP. Always. As customers get larger, they tend to have more systems in place. That’s the law in this industry. And you can not change them, you have to live with them. And living with them means, integration your OpenERP information with their information. And their information with your OpenERP information.

The last reason is, ETL applications save you many hours when it comes to migrating data into your system. MANY HOURS.

Now, when it comes to OpenERP and ETL, you have some options. I just looked at two of them. Talend and Pentaho Kettle. These days we are starting a project that needs to extract information from an accounting system and integrate it with the MRP module in OpenERP. I think we will end up using Kettle, because it already has plug-ins for loading data into OpenERP. Have not seen that yet in Talend. I will cover those modules in a coming post.

Cheers!

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!