Archive

Archive for January, 2011

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!

What to do when you get an error in OpenERP

January 30, 2011 2 comments
Cover of "American Splendor"

Cover of American Splendor

Well, this is a quick Sunday post. What to do when you get an error message when working with OpenERP. First thing to do is to read the message itself, it might give you information on what might be going wrong, such as no space left on the disk. Or insufficient permissions.

But sometimes you get a printout like this:

Operating System Locale : fr_CH.cp1252
Python Version : 2.5.2
OpenERP-Client Version : 5.0.11
Last revision No. & ID :Bazaar Package not Found !Traceback (most recent call last):
File “/usr/lib/

python2.5/site-packages/openerp-server/netsvc.py”, line 247, in dispatch
result = LocalService(service_name)(method, *params)
File “/usr/lib/python2.5/site-packages/openerp-server/netsvc.py”, line 76, in __call__
return getattr(self, method)(*params)
File “/usr/lib/python2.5/site-packages/openerp-server/service/web_services.py”, line 633, in execute
return self._execute(db, uid, wiz_id, datas, action, context)
File “/usr/lib/python2.5/site-packages/openerp-server/service/web_services.py”, line 613, in _execute
return wiz.execute(db, uid, self.wiz_datas[wiz_id], action, context)
File “/usr/lib/python2.5/site-packages/openerp-server/wizard/__init__.py”, line 178, in execute
res = self.execute_cr(cr, uid, data, state, context)
File “/usr/lib/python2.5/site-packages/openerp-server/wizard/__init__.py”, line 73, in execute_cr
action_res = action(self, cr, uid, data, context)
File “/usr/lib/python2.5/site-packages/openerp-server/addons/sale/wizard/make_invoice_advance.py”, line 65, in _createInvoices
val = obj_lines.product_id_change(cr, uid, [], data['form']['product_id'],uom = False, partner_id = sale.partner_id.id, fposition_id=sale.fiscal_position.id)
TypeError: product_id_change() got an unexpected keyword argument ‘fposition_id’

And you can’t make anything out of this. So… what do you do in this situation? Well, you can jump into Google and search for the error message

TypeError: product_id_change() got an unexpected keyword argument ‘fposition_id’

The first entry in the results is a bug notification in Launchpad. Checking the bug you can see there is a patch for the problem and that the bug is fixed. So download the patch and apply it with the patch utility.

Just to finish this post, you should watch this wonderful movie, American Splendor. I don’t get tired of watching it.

Cheers!

OpenERP Server log startup options

Tux, the Linux penguin

Image via Wikipedia

From time to time you might need to debug what might be going wrong with the OpenERP server. You can do that by checking the log file and the log messages created by  the application. How do you do that? First, checking the log file. OpenERP keeps a log in a file named openerp-server.log. In most Linux systems you can find the file in the /var/log directory. You can specify the location of the file with the –logfile parameter when you start the openerp-server program. For instance

# openerp-server –log-file=/home/gustavo/myopenerp-server.log

In this example the system is logging to the myopenerp-server.log. OpenERP can also log to the syslog file, you do that by specifying the –syslog parameter when you start the openerp-server program.

You can also set the logging level for the application. You do that by specifying the –log-level parameter when you start the openerp-server program. If you want to log the debug messages, you need to specify the –log-level to debug and enable the debug mode. You do that by specifying the –debug parameter when you start the openerp-server.

Hope this information helps,

OpenERP for Retail and Industrial Management book review

January 24, 2011 8 comments
A Picture of a eBook

Image via Wikipedia

I bought the book on Amazon. You might wonder why should I purchase the book when I can download the e-book which is free? Well, two reasons. The first reason is I do enjoy reading printed books. I don’t think reading books on tablets matches my experience reading a paper book. The other reason is convenience, I can take it anywhere and read it on subways, trains, whereever.

The book is written by Fabien Pinckaers, who started the OpenERP project. This is a very good reason for reading this book. You get to understand the system from the point of view of the person who developed the system. You get to know how the system is supposed to work, which saves you a lot of time.

The first chapter covers the Installation of OpenERP. Covers Windows and Linux. You can skip this chapter since you will find more updated information on the documentation section of the OpenERP website or the community forums.

The second chapter is a Guided Tour. You can skip the chapter but I suggest against it, since it not only covers the user interface and its usage, but also covers third-party add-ons with lots of functionality that are really handy. For instance, I got to know the product_expiry product which is really handy for products that have an expiration date.

The third chapter covers a real case. It is a very simple case, but helps you understand how the system works. Actually, it is the first thing you need to know when you are taking your first steps in OpenERP. The chapter guides you on the different steps of buying products from suppliers, stocking those products in the warehouse and selling them to your customers. It’s OpenERP 101.

 The fourth chapter covers sales management. From sales quotations, packaging, alerts, invoicing, deliveries and price management (including rebates). Does not cover the financial management of sales, CRM nor analytical accounts. They are covered in the e-book version of the book.

The fifth chapter covers Purchasing. It’s a short and concise book on how to create purchase orders, quotations, receipt notes and invoices from suppliers. It is a good read before reading the next chapter.

Stock Management. Covers the product creating, and the warehouse management, involving the operations for entering and delivering products to/from your warehouses. Also covers the stock valuation and traceability, which is becoming critical in the logistics operation.

Manufacturing is the next chapter. Starts with the Bill of Materials and guides you from there. From BoM you create production orders, which are covered in detail, to the different routings available for the different products. This introduces you to WorkCenters and the different operations involved in the manufacture of your product. This might be the most important chapter in the book and has many examples of its operation.

The last three chapters; Process, System administration and configuration, and Methodology. If you work with OpenERP you should read these chapters, but you should complement it with further reading, for instance the memento presentations available on Slideshare.

Well, to finish this post, I highly recommend this book. And after reading the book you should read the e-book, which has additional chapters on CRM and Analytic Accounts (for example). It is not a definite guide, but it is an excellent first step on OpenERP.

Cheers!

Cost Centers in OpenERP

Cost centers in OpenERP are analytic accounts. If you want to know how to manage them, read the chapter on Analytic Accounts in the OpenERP book,

http://doc.openerp.com/book/4/4_10_Accounts/index.html

It’s Sunday noon, that explains why this is such a short post

Have fun!

First steps in OpenERP 6.0 – Importing modules

Sooner or later you will need to import (or install) a new module to enhance OpenERP functionality. After all, OpenERP is an open-source system, and mature open-source systems tend to have hundreds (if not thousands) or third-party modules (just look at Drupal). At the end of the day, this rich module ecosystem is one of the reasons that differentiates open source software from commercial software (in my humble opinion).

Now, how do you add a new module to OpenERP? First you need to find it. A good place to search for them is the Open ERP module page. Select the module you need (in this case I will use the city module) and save the file in your computer. Then open the OpenERP application and select the Administracion > Modules menu item.

Next step is to click on the Import Module menu item. You will see the Import Dialog shown below.

In this dialog you will select the module file you just selected. The filename in this case is city.zip. After selecting the file click on the Import Module button which will begin the import process. After doing so, and in case the process was successful, you will see a dialog like the one listed below:

After importing the module, you have to enable it. You do that by clicking the Open Modules button. You will see the modules list with the modules pending for installation in the system.

Select the module you just imported by double-clicking on it. You will see the module information dialog

Click om Schedule for Installation and then on the Apply Scheduled Upgrades button. You will see a dialog button with the modules scheduled for upgrades, click on the Start Update button.

This will enable the system in your OpenERP system and create the objects, reports, views, forms included in the module. In my case I was importing a module that creates a city object. So to check if the object was created, I click on the Administration > Customization > Database Structure > Objects menu item. There I can see the city object was created in my system.

Server security in OpenERP

CD cover for Debian GNU/Linux version 4, code ...

Image via Wikipedia

Sooner or later you have to address security in your OpenERP installation. For many reasons, the sooner you do that the better it is. The later you address security in OpenERP, the more money you lose (in those cases you start working on security after you had the problem, which means you are losing money).

What is the cost of not taking care of security? When you have security problems, your company is stopped (employees can not print invoices, customers can not place their sales orders, sales reps can not make their calls, you can not procure your supplies, and the list goes on and on). So put a number to those days. That is the value of security.

How do you start? The first thing to keep in mind is your server.  No matter what the application you are running in your server, whether it is a website or OpenERP, you have to secure the server. Most of the people I met run their OpenERP systems in Debian and Ubuntu (two popular Linux distributions). Some people do so in their own servers, some people host their systems in a VPS (such as Linode). Either way, again, you have to secure your server.

I will concentrate on Debian and Ubuntu (otherwise this is going to be a very long post). I will not cover Windows and will not get into the OpenERP security system. I will leave that for later. This is not a Linux security course, this post is intended to give you pointers to places where you can start working on the security of your system. Also, this does not cover Postgresql security.

First thing you have to do is to secure your server against intrusion. If your server is connected to the internet, your server is a target. Plain and simple as that.  How do you get started? This is a link to a great introduction on Linux security:

http://www.tldp.org/HOWTO/Security-Quickstart-HOWTO/

If you are using a Debian or Ubuntu distribution, you should read the Securing Debian Manual. And if you are running Ubuntu, don’t forget to check the Ubuntu Security Documentation (you should complement it with the Debian security manual).

Those are some pointers to places where you can get information on how to get started on securing your Linux server. Securing it against intrusion is extremely important, and should be one of your first items in your agenda. Again, if your server is connected to the Internet, your server is a target. And most of us can not afford to stop operations because our server was attacked by hackers.

Cheers!

Follow

Get every new post delivered to your Inbox.