Switching from Oracle to PostgreSQL

We, consultants, aren’t so techie as developers are and changes take a bit longer. During last couple of months I’m introducing myself to PostgreSQL: installing sources, developing, installing database client, etc.

Just in case you are in a similar situation:
– On config/Openbravo.properties file, bbdd.sid parameter is a different concept from what you are used to in Oracle. In case you have an environments and want to install a second one, bbdd.sid must be different. Otherwise, when executing ant install.source your first database user will be erased. Conclusion, when using PostgreSQL, per each OpenbravoERP installation you will need a different database.sid. This is, bbdd.sid concept is different in Oracle and in PostgreSQL.

– When accessing database throw a terminal-based front-end, Oracle uses SQL+ while PostgreSQL uses psql. They are very similar and really helpful when accessing OpenbravoERP server using ssh connection.

Please feel free to comment any additional main differences when switching from Oracle to PostgreSQL.

September 11, 2009 at 11:50 am 3 comments

Data migration, watch out!

Usually, nor sales responsibles neither consultants give data migration task the importance they should during the sales process, but they consume a really hugh amount of not planned time.

Migration tasks include: extracting, transformating and loading which are also known as ETL tasks.

As we all agree, the effort required to migrate the data of a small enterprise who manages all their activity using spreadsheets is not the same as a large company with a previous ERP system that wants to keep all its historical activity.

Therefore, I would like to enumerate few points to be taken into consideration,  before delivering a proposal, for a correct effort evaluation:

  • Master data & historical data: Master data or reference data(business partners, products, accounting plan, assets, banks, taxes, production plans, etc.)  is what really makes sense to migrate instead of typing them manually. Historical data (orders, receipts, shipments, invoices, bank statements, amortizations, accounting entries, etc.) can be usually reviewed on the old system, so probably we could convince the customer it is not really necessary. Somehow, it is important to realize a historical data migration always requires a master data migration, and also note a historical data migration can take 4 times the master data migration. (20% & 80%).
  • Accounting data & management data: There is also also difference between migrating just the accounting data or having to migrate all the invoices, bank statements, daily cashes, etc. in order to create the accounting data by the new application. Accounting data migration may take 20% of time than management data will.
  • Period of time: Although the period won’t change the scope of the migration task, for sure, more defects and inconsistencies will be found as larger is the period. If we ask to the customer about the migration period, they will always say: “all existing information”.
  • Extraction and transformation tasks included & excluded: Too often, when talking about migration we forget extraction and transformation tasks. Sometimes it is clear we do not know about the old system or the customer does not want to give us access to it, so it is clear we can’t deal with the extraction and transformation, but anyway, it is important to make it clear who is responsibe for data extraction and transformation. Not extracting data correctly when we have invested time on it can be a big issue. I’ll always try to ask for extracted and transformed data, ready to be loaded.
  • Extracted data consistency and validation: If we can’t avoid extraction and transformation tasks, make sure the customer validates what you are going to migrate. It is useful to deliver lists or spreadsheets containing the final migration result before we finally load it so the customer can validate it.

As I tried to explained, migration offering needs to be detailed as possible in order to avoid future issues or project deviations from proposal.

January 19, 2009 at 8:38 pm 2 comments

Playing with POI (I)

Although the title sounds strange 🙂 it has been a pleasure discovering POI!

As I mentioned a couple of weeks ago on a post, POI is an Apache project for spreadsheets generation. I had the opportunity to know about this new tool while advising Microgenesis in some of their current projects. Their customers required them quite difficult dynamic reports, thus, they decided to use POI.

This two, showing below (click on the image if you do not see the animation), are some of the very interesting customized reports developed for their customers:

Openbravo examples using POI

Accounting Report:

Generated dynamically with the posted document’s information, allows the user have a general view of the accounting information during the last 5 years and also drilling down to each account’s amount. It is also possible to include some accounting ratios (liquidity, profitability, activity, profit margin, etc.) for a financial statement analysis and facilitate a better understanding of the information.

Assets Report:

Generated dynamically with all assets and amortizations information. Gives a whole idea of an enterprise assets value and situation for the selected year.

Both examples are generated as spreadsheets with all its functionalities: the user can change the information managing different scenarios and situations, create charts or graphics, edit and include new formules, etc.

I will give some technical explanation and code examples on next posts.

P.D.: You can also find some more POI examples here.

December 26, 2008 at 4:10 pm Leave a comment

Aware Oracle 11g, login is case sensitive

During the years, Openbravo has run on top of different Oracle database versions: Oracle 9i, Oracle 10g, etc. and now Oracle 11g. While this is an additive change for the Community Edition and Openbravo Basic Network, which continue to support Oracle 10g R2 and PostgreSQL (8.1.4 or 8.3.5) as well, Oracle 11g is now embedded in Openbravo SMB Network One for 2.40.

Of course, and I am not a database administrator neither a developer though, from my honest opinion many new interesting functionalities and bug fixing are included in this new release: database catching functionalities, virtual columns, etc. up to 482 new characteristics.

As always happens, many new features require us to change the way we usually do things, for example: Oracle 11g is case sensitive when
login. What does this mean? You could spend the whole morning trying to access to your database (even locking your database user) if you don’t realize Oracle 11g is now case sensitive…

ORA-01017: invalid username/password; logon denied

When I went through, I learnt the below:

1-Oracle 11g is case sensitive

This means, you need to introduce your database user and password with the correct case. To make sure how your database is configured, using
SQL*Plus as database system user:

SQL> SHOW parameter sec_case_sensitive_logon;

If you want to disable/enable this function (it is enabled by default), using SQL*Plus as database system user:


This is a new parameter on this release, not an existing parameter’s new default value.

2- sqlcase can change your password when creating a new user

Furthermore, it is important to realize there is another parameter that can change your new database user’s password:

sqlcase MIXED

sqlcase accepts three different values: UPPER (changes password to uppercase), LOWER (to lower case) MIXED (keeps the password as it is).

Depending this parameter’s value, when creating a new database user, it changes the password’s case. And this can also confuse yourself and avoid to success the login.

3- Unlocking the database user

The third action I did, was unlocking the locked database user 😦

Also as database system user:


I hope it helps, and you don’t spend the time I spent with this little issue…

December 9, 2008 at 12:27 pm 1 comment


Woof, Woof, … the dog is not barking! is people on the office sharing files each other. 🙂
Woof is a small application for simply file exchange, based on Python. It is very simple and useful to share files with people around. Instead of sending via mail, messenger or skype, using a pendrive, … just Woof!

Have a look to this screenshot to understand how it works.

Woof screenshot

Woof screenshot

– You can see how to activate Woof.
– Once woof is executed, anyone can download the file I am sharing using the browser while you get the complete log of all downloads.

Thanks to Jaime, who talk us about it! Since them I have impressed many people with this little toy.

November 27, 2008 at 10:43 am Leave a comment

Lists, Reports and Dynamic analyzers

Always have been said an ERP implementation project has 4 main hot issues: FIREs (Forms, Interfaces, Reports and Extensions). And I will like to go a bit further on reports issue.

As the technology has improved and new platforms have been developed new and much more sofisticated reports can be done. The users know it, and they want to have more detailed and feature full reports.

Nowadays, and from a consultant point of view, reports should be classified properly:
1- List: A list of activity or master data. Including some columns. An being able to filter by any column on the list. Not difficult to achieve.
For example: A list of all the invoices ordered by invoice date and including business partner, invoice date, base amount, tax amount and total amount.
2- Report: Detailed information, properly structured, ready to make a decision. Information filtered by dates, customers, products, organizations, etc. and also totalized per period and business partner. Not as easy as a list.
For example: A report which shows all the cash forecast depending on may pending payments and receivables.
3- Dynamic analyzers: It is not enough having the data well structured on the report. The users wants to be able to play with the data the report toke out of the system. They want to build an scenarios with data provided through all the activity, and be able to change some parameters before they make the decisión.
For example: A Dynamic analyzer where the systems puts an actual forecast, in which the user can change some parameters (credits the user may extend, or payments the user would pay later, etc.) and automatically the new forecast is build.

Thus, it is important to take into account what the user is expecting when asks for a report.

P.D: For the first two report types, Jasper is a good solution. For the third, I am investigating POI. I let you know something about, soon!

November 16, 2008 at 4:30 pm 2 comments

Project cycle: Implementation & Improvements

During the first commercial steps of a project, the potential customer often thinks in a single project phase: Implementation.

Sooner or later, nearly all Openbravo users are asking for a second phase: Improvements.

It is logical. During the implementation phase, the customer’s key users only think on basic needs, functionalities they manage at the moment. But once the application has gone live, they start realizing improvements. (sometimes too early, and this can become a problem to finish the project).

But why? We can consider many reasons, but lets explain the ones I have seen:
– The main reason is that some key users change their mind and feel the ERP as a useful functional tool for area information gathering and report.
– The laws change and new information must be managed.
– For different reasons the department’s user keys change (personnel rotation, internal movements, etc.). New managers implement new processes that must be updated on the ERP.
– External advisers propose process improvements.
– Users suddenly change the way they work, and forget what they use to manage through the application, so a new functionality review is needed.
– The business requires new processes.
– Etc.

At this point, few customers are coming to realize they prefer a proactive service instead of a reactive service. They don’t just want support for maintenance and punctual issues, but also a periodical consultant service to squeeze all the application functionalities making improvements continuously, checking actual processes, etc.

Implementing an ERP is not a single phase, it is worth to invest money on process improvements than wasting time analyzing enterprise information manually.

March 17, 2008 at 11:54 am 3 comments

Older Posts Newer Posts


  • 1356

  • Feeds