Migrating from On-Premise Data Warehouse to Cloud: Challenges, Architecture and Use Case

The Situation 

A multinational provider of Digital, IT Services and Solutions with over 20,000 employees, headquartered in California, USA, were accessing their data from an on-premise data warehouse to generate Business Intelligence (BI) reports. 

Considering the advantages of a cloud-based data warehouse, the company wanted to move their on-premise data warehouse to a modern cloud-based data warehouse. Data Semantics advised the company to choose Microsoft Azure Data Warehouse for its benefits and ease-of-migration. 

Visit here: power bi dashboard

Benefits of migrating to Cloud (Microsoft Azure Data Warehouse): 

Improved IT resource management 

Better mobility and reduced dependency on premises 

Modern technology and constantly updated ecosystem 

Decreased IT expenditure in maintaining legacy systems 

Although the above benefits are more generic in nature, the important reasons to migrate to Cloud are the benefits of moving to an MPP (Massively Parallel Processing) System from an SMP (Symmetrically Parallel) Systems.

Talk to us directly to know more details about the case study

The Problem 

Although migrating to Azure data warehouse cloud is always a better option, Cloud platforms have a new set of limitations and challenges, compared to the existing SQL Server on-premise data warehouse of the company. 

Challenges in migrating to Cloud (Microsoft Azure Data Warehouse): 

Azure Cloud Platform doesn’t support some table functions, as compared to SQL Server on-premise DW.  

Azure Cloud Platform doesn’t support some features of stored procedures on SQL Server on–premise DW. 

The nature of tables is different on Azure DW, as compared to SQL Server on-premise DW. 

Azure Cloud Platform doesn’t support Synonyms. They need to be converted to physical tables for Azure DW. 

Limitations on Functions are a major obstacle. 

Click here to get more info about business intelligence consulting services

The Objective 

Migrate about 800 GB of Data from the on-premise SQL Server data warehouse to Azure Cloud Platform within a span of 12 weeks by working around the limitations and challenges, to maximize the benefits of having a cloud-based data warehouse.

The Solution 

The Chief Architect at Data Semantics identified that, the existing on-premise data had to be re-engineered for migrating to Cloud (MS Azure Cloud Platform), considering the way the data is handled on legacy systems. 

The legacy on-premise data warehouse functioned in a rather, traditional ecosystem where the lack of APIs was a major challenge. Additionally, it was difficult to spell out rules, sequential activities, and required outcomes for every data migration step due to the existing legacy systems not matching up to Cloud ecosystem and capabilities. 

For more info visir here: tableau dashboard

The Data Semantics team segregated and handled each element of the database manually, in order to facilitate error-free functioning of the data. These DB elements had their own migration challenges, which were worked on individually by the team members. intelligence consulting services