Ropardo IS: Porting BI from Oracle to PostgreSQL
“Information is provided whenever asked for in a consistent, precise and integrated manner!”
A complete Business Intelligence Suite – Ropardo: IS – is a Java application using Oracle as the backend database. As a BI solution, Ropardo: IS is covering the following main processes: it extracts enterprise data from various data sources, transforms the raw data into its own data warehouse and provides comprehensible business information in form of reports and charts, allowing detailed business analysis.
The extension of the company asked for better organization and performing IT systems.
Ropardo AG, headquartered in Germany was looking to improve its B.I software, that is to make it run on top of PostgreSQL database, freeing it from being tied up to a single database product. Ropardo S.R.L, having pre-qualified resources, with strong Oracle, PostgreSQL and Java skills, was challenged to accomplish this task, in a fast and cost-effective manner.
Migration of the Oracle backend database to PostgreSQL. The database migration involved about two hundred tables and views, and several PL/SQL complex functions. There were many technical issues like Syntax Differences, Data Types equivalence, Date/Time Processing, Built-in Functions, Null Values handled differently, Window/Analytical functions, PL/SQL vs. PL/pgSQL Differences, etc. Because no feasible solution for an automated migration was available, the team members had to perform a manual database migration, relying on their strong Oracle and PostgreSQL skills. Ropardo team had to make sure that the resulting PostgreSQL database was equivalent, and thus expecting no impact to the frontend applications.
Porting the Ropardo: IS Java modules. This was a smooth process, the team being well prepared in all of the technologies involved. Obviously, support for Oracle was not dropped, instead a mechanism to select the proper SQL dialect for Oracle or PGSQL was implemented.
Very important were the testing and deployment phases. They were necessary to ensure that the behavior of the resulting B.I. system, running on top of PostgreSQL, was identical to the original system.
- detailed economic analyses at all company levels through KPI standard indicators and its own indicators
- drill-down function to display in-depth data
- analysis of deflective elements to identify weak points faster
- rapid determination of products with more or less success and the difference between them
- ad-hoc reports and warning reports
- automated generation of standard reports
- multi-dimensional analysis of current commercial activities with up-to-date figures, turnover evolution and values of the previous year
- definition of individual limit values and planning figures serving to warning messages
- open import interface facilitates data take over from various sources
efficient migration ensuring application and data integrity.
functional and look-and-feel equivalence with the original system.
open source database backend as an alternative to Oracle.
improved availability and scalability of the application