Empowered a large pharmaceutical company to improve the TCO, scalability and query performance by building a data lake on AWS and migrating Oracle DB applications to PostgreSQL Database
Migrating Pharmaceutical Reports Open-Source Success
About the Client
The Business Challenge
The client wanted to migrate reports, i.e., their existing Oracle-based (EDW, GDW) and Tableau reports to open-source software and tools. Additionally, with their new business model, the additional requirement of dashboards, reports, and enhancements around existing dashboards, data volume increased greatly. Moreover, with online access provided to every sales representative, the user base rose to 9000+. Therefore, instead of investing further into on-premises infra with licenses, the client wanted to migrate reports to On-Cloud open-source platform expecting to keep TCO in control with rising volume and requirement. Hence, scalability, performance, cost, and improvement of batch job processing were the key factor for the analytics modernization.
What Aptus Data Labs Did
Our team analyzed the client’s existing infrastructure, platform, tools, and taken-up tasks and:
- Designed and built a data lake architecture covering AWS infrastructure, AWS services, and open sources software based on the pros and cons of platforms along with cost implications
- Prepared data & application migration strategy and data pipeline for automation
- Setup and configured enterprise querying and open source-based reporting platform
- Completed design, development, migration, and testing of the migration
- Provided 24x7 support
The Impact Aptus Data Labs Made
Our approach helped the client to
- Reduce cost of system stack by using “Open source yet industrial grade” platforms
- Reduce time required for analysis, analytics, and reporting on “Structured and Unstructured data”
- Improve and accelerate analytics-driven decisions
- Prepare ground and infra for AI/ML and Advanced Analytics
The Business and Technology Approach
We followed a phased approach for the migration:
- Assessment: We conducted a thorough assessment of the application, compatibility, and architectural assessment.
- Architecture & Services – We built the architecture, framework and selected the AWS services and open source tools.
- Schema Migration: We used tools, such as Ora2pg, DMS, SCT, and in-house developed scripts for migration, which enabled faster schema migration. Since there were challenges to migrate some of the schemas like procedures, functions, and scripts were manually converted.
- Functional Testing: Before proceeding further, it was important to test the converted schema on a sample dataset. After that, we loaded a sample dataset in both Oracle and PostgreSQL databases test environment and tested to ensure the test results are identical. Additionally, any issues encountered during this functional test were addressed immediately.
- Performance Testing: The application might see some difference as few of the Oracle built-in transactions or features functionality would be slightly different in PostgreSQL. Hence, the application was tested and any differences revealed were addressed and fixed.
- Data Migration: Data was migrated in two phases. One using a snapshot for a cut-off period and then in the second phase did incremental loads.
Architecture
Tools Used
- AWS Platform
- Ora2Pg
- Oracle
- PostgreSQL
- AWS Schema Conversion Tool (AWS SCT)
- AWS Database Migration Service (AWS DMS)
- Our data accelerators
The Outcome
With spiraling license fees from Oracle, the potential upside in terms of cost savings was easy to appreciate. Moreover, this engagement not only provided the client with the evidence of skills and expertise that Aptus Data Labs could provide. Additionally, it also made it an obvious decision for the client to award Aptus Data Labs the contract for 24/7 Production Support.
- Saving the cost of 9000+ oracle end users
- Reducing the cost of support and streamlined 24x7 support through alert and automation
- Usage of hot, warm, and cold data storage concept for fast processing within a defined window and effective backup & recovery
- Migration of 900+ tables, 600+ stored procedures, 3.2TB data and 200+ daily job scripts
Related Case Studies
Achieving low-latency API-based queries with Mongo DB
Revolutionizing Pharma Analytics with AWS Data Lake
Boosting Performance with Apache Spark Migration
Unlock the Potential of Data Science with Aptus Data Labs
Don't wait to harness the power of data science - contact Aptus Data Labs today and start seeing results.
If you’re looking to take your business to the next level with data science, we invite you to contact us today to schedule a consultation. Our team will work with you to assess your current data landscape and develop a customized solution that will help you gain valuable insights and drive growth.
