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. 

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

  1. Improved IT resource management 
  2. Better mobility and reduced dependency on premises 
  3. Modern technology and constantly updated ecosystem 
  4. 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.

[sf_button colour=”lightgrey” type=”bordered” size=”standard” link=”https://datasemantics.co/contact-us/” target=”_blank” icon=”” dropshadow=”no” rounded=”no” extraclass=””]Talk to us directly to know more details about the case study[/sf_button]

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): 

  1. Azure Cloud Platform doesn’t support some table functions, as compared to SQL Server on-premise DW.  
  2. Azure Cloud Platform doesn’t support some features of stored procedures on SQL Server on–premise DW. 
  3. The nature of tables is different on Azure DW, as compared to SQL Server on-premise DW. 
  4. Azure Cloud Platform doesn’t support Synonyms. They need to be converted to physical tables for Azure DW. 
  5. 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. 

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. 

Number of Tables: 390 Tables 

No of Views: 24  

No of Functions: 19  

Number of Stored Procedures: 115 

After a 10-week activity, Data Semantics re-engineered 800 GB of data to be migrated on Cloud. 

Architecture: On-Premise to Cloud Migration
Architecture: On-Premise to Cloud Migration

The Outcome 

The migration process of 800GB of data was completed within 12 weeks, as planned. Today, this global IT service provider accesses all their data from Cloud, to generate Business Intelligence (BI) reports in real-time.

Moving to an MPP System enabled fast querying, better data management and governance, thus increasing the velocity of generating Business Intelligence (BI) reports, especially in real-time.

Enterprise Data Warehouse Size: 800 GB Migrated 

Packages Migrated: 165 SSIS Packages 

Team involved: MSBI Developers, Architects, DBA Engineers  

Technology Stack: SQL Server, Azure Cloud Platform, Azure Data Factory, SSIS PolyBase, TSQL, Robotic Process Automation 

[sf_button colour=”accent” type=”standard” size=”mega” link=”https://datasemantics.co/contact-us/” target=”_blank” icon=”” dropshadow=”no” rounded=”no” extraclass=””]Contact Us to Know More[/sf_button]