Case Study: Migration of On-premise SQL Server EDW to AWS Redshift

Overview

A leading book publishing firm approached VerticalServe, a renowned consulting company, to migrate their on-premise SQL Server Enterprise Data Warehouse (EDW) to Amazon Web Services (AWS) Redshift. The primary objectives were to enhance scalability, improve performance, and reduce the overall infrastructure costs while maintaining the quality and integrity of their analytics.

Challenge

The book publishing firm faced several challenges in migrating their EDW to AWS Redshift, including:

1. Developing historical and incremental data pipelines.
2. Leveraging AWS Redshift as a data warehouse.
3. Implementing AWS Glue and EMR for analytics jobs.
4. Utilizing Amazon S3 for data lake storage.
5. Integrating Tableau for dashboard visualization.
6. Deploying Amazon Athena for raw data exploration.
7. Supporting store, shelf, warehouse, and book fair analytics.

Solution

To address these challenges, VerticalServe designed and implemented the following solutions:

1. Historical and Incremental Data Pipelines:

VerticalServe developed historical and incremental data pipelines to migrate the existing data from the on-premise SQL Server EDW to AWS Redshift. This approach ensured that all data, including historical records and new transactions, was seamlessly transferred to the new platform without compromising data integrity.

2. Redshift as a Data Warehouse:

AWS Redshift was chosen as the data warehouse solution for its scalability, high performance, and cost-effectiveness. Redshift allowed the book publishing firm to store and process large volumes of data efficiently.

3. Glue and EMR for Analytics Jobs:

AWS Glue was used for data integration, data cataloging, and ETL processing, while Amazon EMR was implemented for running complex analytics jobs. This combination enabled the firm to process and analyze data quickly and cost-effectively.

4. S3 for Data Lake Storage:

Amazon S3 was utilized as the data lake storage solution, enabling the firm to store and manage raw and processed data. S3 provided a scalable and cost-effective storage solution that could grow with the company’s data needs.

5. Tableau Integration:

Tableau, a powerful data visualization tool, was integrated with AWS Redshift to create interactive and dynamic dashboards. This enabled the book publishing firm’s business users to visualize and explore data insights easily.

6. Amazon Athena for Raw Data Exploration:

Amazon Athena, an interactive query service, was deployed to enable users to explore raw data stored in the S3 data lake. Athena allowed users to analyze data using SQL without the need for complex ETL processes or data movement.

7. Store, Shelf, Warehouse, and Book Fair Analytics:

The new analytics platform supported various types of analyses, including store, shelf, warehouse, and book fair analytics. This comprehensive approach provided the book publishing firm with valuable insights into their supply chain, sales performance, and market trends.

Results

The migration of the on-premise SQL Server EDW to AWS Redshift by VerticalServe resulted in the following outcomes for the book publishing firm:

1. Enhanced Scalability:

The new cloud-based infrastructure provided improved scalability, allowing the firm to grow and manage their data needs more efficiently.

2. Improved Performance:

The combination of AWS Redshift, Glue, and EMR resulted in significant performance improvements, enabling the firm to process and analyze data faster than before.

3. Reduced Infrastructure Costs:

The migration to AWS Redshift reduced infrastructure costs by leveraging the cost-effective and scalable cloud storage and compute resources.

4. Comprehensive Analytics:

The new platform supported various types of analytics, providing valuable insights into the firm’s operations and market trends.

5. User-friendly Data Visualization:

The integration of Tableau allowed business users to visualize and explore data insights easily, empowering them to make data-driven decisions.

About:

VerticalServe Inc — Niche Cloud, Data & AI/ML Premier Consulting Company, Partnered with Google Cloud, Confluent, AWS, Azure…50+ Customers and many success stories..

Website: http://www.VerticalServe.com

Contact: contact@verticalserve.com

Successful Case Studies: http://verticalserve.com/success-stories.html

InsightLake Solutions: Our pre built solutions — http://www.InsightLake.com