Data Analytics Solution for Sales Analysis across 10,500 Stores

Customer

The Customer is a multinational FMCG corporation operating in more than 200 markets with 1 billion consumers and 60,000 employees across the globe.

Challenge

One of the Customer’s national branches was distributing 100 SKUs through a marketing channel comprising 10 large retail chains and 10,500 stores. To perform sales analysis, the Customer had to collect data from retailers in multiple files and formats, which significantly reduced productivity and did not provide analysts with a holistic view. Therefore, ScienceSoft was commissioned with a project to create a system that would process and unify data to deliver advanced retail analytics.

Solution

Business intelligence solution implementation by ScienceSoft embraced three modules:

• Web Client – a tool that is installed on the application server (IIS) and enables users to:
  • Upload, view and edit data in the system
  • Roll the uploaded files back
  • View uploading logs
  • Initiate the processing of data from DWH (Data Warehouse) to OLAP (Online Analytical Processing) cubes
• Data Warehouse – a database (MS SQL Server) that stores data and prepares it for further processing by an analytical engine. The DWH contains such data as ‘sales in’ (the quantity of products sold to a store), ‘sales out’ (the quantity of products sold from a store) and ‘stock’ (the quantity of products stocked in a store).
• Analysis Services – an analytical engine (MS SQL Server Analysis Services, or SSAS) that aggregates monthly (or weekly) data, stores it in a multidimensional model (OLAP cube) and transmits to the front-end application (Power Pivot for MS Excel). The cube has several dimensions, namely time, the ‘retail chain – store’ hierarchy, SKU category and others. Additionally, the engine calculates sophisticated KPIs – for example, sales growth in a particular store for a certain period of time.

Results

The Customer was satisfied with the solution as it provided a tool for an advanced sales analysis. The company is now able to identify sales trends, find out which SKUs and stores showed best performance, estimate growth potential as well as optimize sales and marketing activities. In cooperation with ScienceSoft, the Customer is also planning to implement elaborate data visualization.

Technologies and Tools

MS Windows Server 2008 R2 (64 bit), MS SQL Server 2008 R2, Entity Framework 6.1.1, MS SQL Server Analysis Services, .NET 4.5, ASP.NET MVC 4, Bootstrap 3.0.1, Power Pivot for MS Excel