OpenERP Migrations from v5 to v6 Using Pentaho ETL

As OpenERP6 is not long from being released, yet it brings so many useful features, we couldn’t wait for the official OpenERP 5 to OpenERP 6 migration script to be released and what a better way to do that than Pentaho Data Integration kit!

So first steps first – create the database via the OpenERP GTK. This installs the base module and populates some db tables with initial data.

Second step – install the needed modules, including any new ones chosen as well as the custom ones if any. In our specific scenario, we had a light custom module, old voucher_payment module and PowerEmail in addition to the other basic modules - accounting, HR, etc. Our custom module was compatible with the new OpenERP version, so no problems there. However we decided to make use of the new official accounting_voucher module instead of the old one as well as use the email_template instead of the PowerEmail. This would require some remapping and additional transformational steps, but nothing is impossible with Pentaho ETL. Once the above is done, time to make a backup of the new database as well as the old one.

Third step - database and relationships analysis, followed by the design of migrations steps. In doing so, it is best to design and separate each stage in a Pentaho ETL jobs as per the picture on the left.

Each Job consists of a series of transformation steps that are organised to follow the database relationship.
By following this logic, it ensures swift debugging as well as ability to reuse parts of the script for various installations with minimum effort.

Next step - time to design the migration scripts.
Again, we decided to split each step of the Job into many transformations for each individual table migrated. An example of our first Job is below:

Although tedious, this will later bring in the benefits of easily plugging in various other steps when customisations are needed. Designing the script for each table needs taking care of other related tables in the database and mapping/transforming them accordingly.

As the newly created database contains already a fair bit of data, including the ir_ data populated, we didn’t want to overwrite it, but simply map some our old data to the new values using various lookup and filtering steps as well as adding any additional references as required and then populating the wkf_ tables with old references mapped to the new ids . An example transformation of how that is done is below. May look complex, but it ensures that the migration is done in the way that does not affect any references already written in the new database, so to avoid any possible bugs etc.

Once all transformation steps have been designed, one final step that will be needed is setting the sequences values in the ‘information_schema’ according to the last rows from the main database as in the image on the left.

When everything is done correctly, hit the start button on the main transformation job and watch the data migrate itself – always a marvellous show for us at Smartmode!

Please enquire with us should you require to migrate from OpenERP v5 to v6. Prices start at £500 per company.