Reward Management BI/DW Migration Oracle to Postgres and Redshift

Organization

Our client specializes in enterprise management software for the retail and petroleum wholesale sectors, delivering solutions that empower intelligent business operations. Serving more than 200,000 locations globally, they leverage cutting-edge ERP, logistics, fuel pricing, and marketing cloud solutions to drive increased volume, improved margins, and enhanced customer loyalty.

CHALLENGE

Our client initiated a multi-phase digital transformation strategy. In Phase I, the focus was on cloud adoption, which involved migrating legacy applications to AWS and transitioning the DW/BI Oracle database to PostgreSQL and Redshift. The subsequent phase aimed to reimplement the legacy application using modern technologies. However, the client lacked in-house expertise in AWS, PostgreSQL, and Redshift and sought a trusted partner for implementation, technical guidance, and recommendations. With XTIVIA’s support, they successfully migrated the on-premises legacy application to AWS and upgraded their databases to PostgreSQL and Redshift.

TECHNICAL SOLUTION

XTIVIA developed a comprehensive cloud Infrastructure as a Service (IaaS) and Platform as a Service (PaaS) solution to modernize the client’s legacy systems. The team updated the Hibernate data source within the existing legacy application. However, challenges arose due to numerous native SQL queries in Oracle SQL format and the lack of support for JSON data types in the existing Hibernate dialects.

To address these challenges, XTIVIA recommended the following solution:

• Rewrite native SQL queries in PostgreSQL format.
• Implement a custom Hibernate dialect for PostgreSQL.
• Develop custom Hibernate user types to support JSON data types.

Key Implementation Highlights:

• Provisioned and configured the AWS IaaS environment for the legacy applications.
• Set up the Redshift PaaS environment for database management.
• Migrated the on-premise Oracle database to PostgreSQL and Redshift.
• Rewrote stored procedures to align with PostgreSQL.
• Updated the legacy application to integrate the PostgreSQL data source.
• Refactored Hibernate native SQL queries into PostgreSQL-compatible queries.
• Developed a custom Hibernate dialect to support PostgreSQL.
• Implemented custom Hibernate user types to enable JSON data type support.
• Updated the Crystal Reports data source to Redshift.
• Developed test scripts and generated coverage reports to ensure a smooth migration process.

This solution provided the client with a modern, scalable infrastructure and seamless database migration while addressing the complexities of legacy system modernization.

BUSINESS RESULT

Migrated the legacy application from an on-premises environment to a cloud-based solution on AWS.
Delivered a rapid cloud migration solution for the legacy application.
Enhanced database performance by transitioning to Redshift.
Achieved cost efficiency through optimized cloud infrastructure.

KEYWORDS
AWS, Hibernate, PostgreSQL, RedShift

SOFTWARE
AWS Cloud, Hibernate, PostgreSQL, RedShift

XTIVIA needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at anytime, read our Privacy Policy here.