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.

Click here to business intelligence consultant

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.

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.

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.

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.

For More Information : tableau consultant rates

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.

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.