Business Intelligence, integrating OpenERP and Mondrian

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

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

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

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!

Related Articles

• Alan Bell: OpenERP vs Lotus Domino (theopensourcerer.com)
• OpenERP offers Cloud Option To Their Customers (cloudave.com)

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!

Related Articles

• OpenERP offers Cloud Option To Their Customers (cloudave.com)
• Rafael Carreras: The party is over (blogs.fsfe.org)

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.

Related Articles

• Drupal 7 Faces Challenges From ERP, CRM (informationweek.com)

Server security in OpenERP

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!

Related Articles

• Official Ubuntu Server Book (i-programmer.info)

First steps in OpenERP 6.0. Creating and configuring the company

Image via Wikipedia

The best way to know OpenERP is to try it. Developing a proof of concept. That is how you will get to know what are the possibilities of this system, what it takes to configure it, get a good grasp on the community and the time it takes to implement it. You can do it alone or guided with a consultant. Both ways work, a consultant will save you a lot of time.

So, how do you start? Well, the first step is to install it and have it running. I will not go into the details on how to install it, I can only recommend buying a VPS Linux server (there are two or three which have are excellent and are affordable). Then you can follow the detailed steps on how to install OpenERP on the documentation pages.

What I am going to talk about now is what you should do first in order to know the system. This is covered in the OpenERP book in the First Steps in OpenERP. You should read it (I encourage you to buy the book since it is an excellent read and for $40 it will save you lots of hours). The idea is to configure a system that buys products from suppliers (and pays them), manages the stocks of those products and sells those products to customers (while collecting their payments). Pretty much what every other company in this world does in order to make a living.

What should we do first? Well, I assume you already have OpenERP up and running. So we are going to set up the company. You do this by selecting the Administration > Configuration > Companies menu (in OpenERP 6.0):

Click on Companies and you will see the list of the companies already configured in your system. Click on the New Button to create a new one. You will see a form like this:

This form has a header and six tabs. On the header you only need to enter the Company name. Then in the General Information tab you should be able to enter the main currency for the company (more on this in later posts), header and footer information and the company logo (which will be printed in the many forms that are used by OpenERP).

You have two more tabs for customizing the Header and footer, along with its internals. We will not get into those items at this time. If you go to the Configuration tab you should be able to configure some parameters for the system, such as the Timesheet range or the Reserve Profit/Loss account. But this is taken care of in more detail in the Parameters section of the system. Same with the Other tab.

Skip the Other and Overdue Payments tabs since they are not necessary to configure by now.

After you have configured the company, you can go to the next section, which is configuring account types. But we will go into that in a latter post.

Cheers,

Related Articles

• Configuration parameters in OpenERP (erpmoldeo.wordpress.com)
• Drupal 7 Faces Challenges From ERP, CRM (informationweek.com)

Configuration parameters in OpenERP

One of the first things you need to configure when you start working with OpenERP is setting its parameters right. Parameters are default values taken by the system while it performs certain operations. For instance, which default pricelist to use while creating a Sales Order. Or the account to debit while creating an invoice.

You can find the Parameters list in the Administration >> Configuration >> Configuration Parameters menu option

You will see a list with over ten parameters; parameters such as account_expense, account_receivable, product_pricelist, etc. Just select a parameter and assign a value to it. I will give you an example. You want your new Sales Orders and Invoices to use a new Pricelist (named Promotional Pricelist). In order to do so, you can select the property_product_pricelist and assign it the value of Promotional Pricelist. Then save it. After that, all your new invoices and sales orders will use the Promotional Pricelist.

You can also create new properties you can use in your custome modules. Just enter a name for it, select the Company which will be using it, the fields the parameter will be applied to, and then which value (and value type) can be assigned to it.

Hope this information helps,

Connecting fingerprint readers to OpenERP

A client asked us to connect its fingerprint reader to the Human Resources module in OpenERP. In order to do that we just released the Beta version of a new module, which is named “clock_reader”. This module is able to connect to the fingerprint reader F5, which is distributed by Digito-Uno in Buenos Aires. We understand, but we have not tested it yet, it should be able to connect to the fingerprint reader G3T, commercialized by Asensio Sistemas in Rosario, Argentina.

The module is able to read more than one fingerprint reader, which can be connected from different sites in the company; or might have many readers in only one input, in case you need to have a backup device or need more throughput.

Well, below are some module screenshots (in spanish):

The module creates a new “clock” object that is listed under the option “Clock” in the Human Resources > Configuration menu.

This object has the following attributes; the clock name, the URI to the reader (more on that later), the model or driver, a location and whether it is active or not.

In order to read the fingerprint reader, you need to select the menu item “Clock read” in the Human Resources > Attendances menu item.

After selecting that option, you will see a confirmation dialog asking you to confirm the action. Once the fingerprint reader is read, you will see a window with the attendances captured and the errors that might have ocurred in the process.

This module was designed to handle more than one fingerprint reader model, but so far we were able to test it only with the F5 model. If you would like to add a new model, you have to develop a new python class with the following interface:

• def __init__(self, uri): Allows you to construct the clock class. The URI is an string that describes the port the application is connecting to. For instance, as the reader allows you an UDP connection, the URI to use is udp://192.168.1.20:9999
• def test(self): Checks the reader connection. Returns false if it is not possible. In the clock class we created for the F5 reader it is convenient to set a timeout value, to avoid an infinite loop. We are using a 3 second timeout value.
• def connect(self): Readies the class for the operations to follow. It can also return device information. For instance, the firmware version number.
• def attendance(self): iterative function which in every iteration returns information on the reader ID, time and whether the record belongs to someone entering the premises or not. It might also return the input method, such as reader, card or keyboard.
• def clean(self): erases the records stored in the reader.
• def disconnect(self): disconnects from the reader.

With a class that complies with this interface, the capture system will capture every active reader and will add the attendances to the hr_attendance module in OpenERP.

Regarding the reverse-engineering needed to develop more fingerprint readers, you can look at two python programs at the test directory in the module.

The first file, research.py, has a function for reading the captures made with Wireshark or any other program that exports the captured information in XML PDML (readpackets). It also has functions for checking that the conection is not encrypted (plothistogram) and for searching patterns (dotplot and align).

The second file, server_f5.py, is a virtual clock that takes a capture (there are three examples in the data directory) and listens so the client you are programming can connect to and query it.

You can find the module at Launchpad:

https://code.launchpad.net/~cristian-rocha/openerp-argentina/trunk/