Imagine trying to keep up with the evolving needs of higher education institutions while ensuring your systems are cost-effective and scalable. This was the challenge faced by our client, a company providing a suite of software solutions for educational institutions. They needed to modernize one of their core components—an Operational Data Storage (ODS) system, which essentially served as a Data Warehouse for preparing data for BI (reporting), originally built on Oracle—by migrating to PostgreSQL in AWS. The primary goal of moving away from Oracle was to reduce the high licensing costs that burdened their customers.
Initially, our client’s system allowed customers to modify the core solution through direct access, resulting in separate, unique instances of the system core. These customizations made it impossible to migrate a single core version of the solution, requiring each client to be migrated individually due to the unique modifications they had introduced.
In this blog post, we will describe our solution for modernizing the client’s system into a true SaaS (Software as a Service) product with a unified core for all customers, while preserving each client’s customizations as individual configurations. To illustrate our approach, we present the migration of one specific tenant.
Problems with the initial system architecture
Our client’s ODS Data Warehouse, part of their broader platform, aggregated operational data from various systems, including customer-specific databases and third-party platforms. Initially, the ODS was entirely on Oracle, relying heavily on Oracle DB Links to connect different databases and enable data sharing. This tightly coupled approach made the system inflexible and complicated to scale or integrate with new data sources.
Data flowed through several stages:
- Staging tables were used to ingest and store data temporarily for preprocessing.
- Composite views transformed and aggregated the data to prepare it for further analysis.
- The transformed data was then stored in Composite tables for efficient querying and reporting by BI tools.
- Reporting views provided insights for BI tools like Power BI, Oracle BI, and Tableau. BI tools accessed These views directly, which allowed modifications but also led to inconsistencies across client instances.
Each client institution (e.g., universities or colleges) could directly access and modify its database. This flexibility, though initially beneficial, led to significant challenges in maintaining consistency across thousands of instances of the same core system. Customizations were widespread, with clients creating their own tables, views, and procedures, resulting in slightly different versions of the base product for each customer. This made updates, migrations, and optimizations complex and time-consuming.
Finally, the Oracle-dependent setup restricted scalability and ease of integration with other systems, placing a significant financial burden on our client and its customers—educational institutions that often operate within tight budgets.
Given these challenges, our client made a strategic decision to transition their ODS system from Oracle to PostgreSQL on AWS, modernize the architecture, and implement it as a SaaS solution. In this model, clients interact through APIs rather than direct database manipulation, preventing future inconsistencies and simplifying updates.
Migration implementation process
To ensure the successful migration of the client’s ODS Data Warehouse from Oracle to PostgreSQL on AWS, we followed our proven Migration and Modernization Methodology. The migration process for this specific project can be summarized as follows:
Envisioning the Future State
We began by collaborating with the client to understand their business objectives and the technical requirements of the new environment. A detailed assessment of the existing Oracle-based system allowed us to define workloads, ensuring all interconnected elements were fully accounted for in the migration plan.
In the modernized architecture, we transitioned the ODS system to PostgreSQL on AWS, leveraging cloud-native features and cutting licensing costs. The transformation was more than a migration; we introduced a flexible architecture capable of supporting diverse data sources.
Instead of using Oracle DB Links for data ingestion, we transitioned to an Amazon S3-based Data Lake using Parquet files, a columnar storage format ideal for handling large datasets efficiently. This change enhanced flexibility in integrating diverse data sources. The Data Lake served as a central repository, and the Parquet format allowed efficient handling of large datasets, providing a more scalable solution compared to the tightly coupled DB Links.
We moved the staging area to PostgreSQL and introduced AWS Batch, a serverless solution orchestrated by Python, for ETL processes. This allowed for a more decoupled and flexible architecture, making it easier to modify ETL processes independently of the core system. The use of AWS Batch provided on-demand scalability for data transformation, which was a significant improvement over the previous architecture.
Database components and ETL transformations
We used AWS SCT (Schema Conversion Tool) to convert composite views and tables from Oracle to PostgreSQL. Post-conversion, we optimized these views manually to improve performance, enhancing query efficiency in PostgreSQL. With over 350 composite views and tables, we rigorously tested each conversion and optimization to ensure high performance. Additionally, we configured an ETL process between the composite views and tables, with AWS Batch managing data transformations across 350 ETL pipelines corresponding to each view and table. We converted reporting views with AWS SCT and optimized them similarly to composite views.
Introducing SaaS principles through API Integration
A significant transformation in this project was moving to a SaaS model. Previously, clients had direct access to the Oracle database, allowing modifications. This flexibility was a double-edged sword, as it introduced maintenance complexities.
With the new system, direct database access was replaced by API-based access. Clients could no longer modify the core database schema directly but could interact with the data through a set of well-defined APIs. This change brought several benefits:
Consistent core system: All clients now use the same core version of the ODS Data Warehouse, which makes updates and maintenance much more manageable for our client.
Scalable customization: Most customizations in the original system focused on either custom data integrations or tailored reports and analytics. The new system further facilitated these by allowing clients to implement custom solutions through integrations, even though direct database modifications were no longer permitted. To support higher education institutions, our client provided an iPaaS solution specifically designed for this sector, enabling institutions to create and manage their own integrations and custom workflows without altering the core system. Additionally, they offered a reporting and interactive dashboard system, allowing clients to tailor reporting to their specific needs. Instead of granting BI systems direct database access, we introduced an API layer to standardize access to reporting data. This approach ensures consistency, reduces maintenance costs, and provides a secure interaction between BI tools like Power BI, Oracle BI, and Tableau with PostgreSQL through APIs.
Application modifications
The shift to a SaaS model required modifications to the existing applications. Application queries were refactored, and connectivity logic was adapted to communicate seamlessly with the new PostgreSQL-based architecture.
Conclusion
By adopting a modern, flexible, and cloud-native architecture, we helped our client reduce costs, improve scalability, and simplify maintenance, while transitioning to a true SaaS model. This transformation allows them to manage all clients from a single standardized version more effectively.
If you need expert assistance in comprehensively modernizing large-scale solutions, get in touch with us. Our team is ready to help you transform even the most complex legacy systems into modern, scalable solutions.
