A person reviewing a sales analytics chart on a desktop monitor

Data Analytics Solution for Sales Analysis across 10,500 Stores

Industry
Consumer Goods, Retail
Technologies
MS SQL Server, .NET
Business gains
Control of sales data of 100+ SKUs across 10 retail chains and 10,500 stores

About Our Client

The Client is a multinational FMCG (fast-moving consumer goods) corporation operating in markets around the world.

Challenge

One of the Client's national branches distributed 100 SKUs through a marketing channel of 10 large retail chains and 10,500 stores. To analyze sales, the Client had to collect data from retailers in many separate files and formats, which hurt productivity and gave analysts no holistic view. INNERLUXES was therefore engaged to build a system that would process and unify this data to deliver advanced retail analytics.

Solution

INNERLUXES's business intelligence solution comprised three modules:

  • Web client — a tool hosted on the application server (IIS) that lets users:
    • Upload, view, and edit data in the system.
    • Roll uploaded files back.
    • View upload logs.
    • Trigger processing of data from the data warehouse (DWH) into OLAP (online analytical processing) cubes.
  • Data warehouse — a MS SQL Server database that stores data and prepares it for the analytical engine. The DWH holds metrics such as "sales in" (quantity sold to a store), "sales out" (quantity sold from a store), and "stock" (quantity stocked in a store).
  • Analysis services — an analytical engine (MS SQL Server Analysis Services, SSAS) that aggregates monthly or weekly data, stores it in a multidimensional model (OLAP cube), and feeds the front-end application (Power Pivot for MS Excel). The cube has several dimensions — time, the "retail chain – store" hierarchy, SKU category, and others — and the engine also calculates sophisticated KPIs, such as sales growth in a particular store over a given period.

Results

The Client was satisfied with the solution, which provides a tool for advanced sales analysis. The company can now identify sales trends, see which SKUs and stores perform best, estimate growth potential, and optimize sales and marketing activities. The Client also plans to work with INNERLUXES on more 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.

MS SQL Server Case Studies .NET Case Studies Consumer Goods Case Studies Retail IT Case Studies BI Case Studies Data Analytics Case Studies