Optimization of the Synapse environment to facilitate data warehousing for a large international retail organization.

Summary

The company (client) is a major British multinational retailer with headquarters in London, England, that specializes in selling clothing, home products and food products. They operate a family of businesses, selling high-quality own-brand products in the UK and internationally, from 1,509 stores and over 100 websites globally. Through their stores, support centres, warehouses, and supply chain they serve nearly 30 million customers each year.

Challenge

The client’s existing data-warehousing environment was slow and prone to inconsistencies. There were no means available to generate reports or any kind of insights from the stored data. The client wanted improvement in the data operational speed and additional capabilities of admin dashboard, reports, and insights generation in its environment.

Solution

Cloudaeon examined the existing platform and decided to optimize it through Azure Synapse Analytics service, with a view to implement data integration, enterprise data warehousing and big data analytics at one go. All the database processes were shifted to Synapse to make data retrieval faster and to optimize query performance and workload management. Several different dashboards were designed for administration, report generation, and insights generation. Further, documentation was prepared to train the client’s data analysts to use the new environment.

Technical aspects of the solutions

· Prepared Synapse Health dashboard to monitor Synapse’s or server’s health.

· Prepared Capacity dashboard for auditing and tracking of resource utilization.

· Prepared Admin dashboard for maintaining overall control over the operations.

· Converted un-optimized tables to optimized ones to speed up data access.

· Defined various maintenance activities – CCI rebuild, stats collection process, indexing, etc.

· Prepared documentation (user guides) for data analysts and data engineers.

Tools used

· SQL Server Management Studio (SSMS)

· Power BI

· Azure portal

· Azure Synapse Analytics

Business value

With the help of this optimization, the client gained better control over its data warehousing environment. The performance of all the operations related to data access and storage was improved by a huge margin. The dashboards provided additional functionalities of administration, and reports and insights generation. These dashboards provided the client with crucial information to optimize its business operations. The documentation, prepared by Cloudaeon’s experts, helped client’s data analysts and administrators to learn and operate the new environment. All these improvements lead to the optimization of client’s business operations and further helped in cutting down the operational costs.