High Level Design

Overview

This BI solution is meant to help a retail analytics business improve its performance in terms of profitability, with a focus on distribution processes. Like any other business, the main objective of retailers is to achieve continuous improvement in that area. Other factors, such as back office management costs (inventory, procurement, production etc.), shipping and product attributes are beyond the scope of this solution.

Dashboard Example (sample data)

Click on the image to open and interact with the dashboard:

Retail Distribution Management Dashboard

Goals

Identify opportunities to improve the company’s profit in regards to distribution processes.

Objectives

  • Identify key factors that have the largest effect on profitability
  • Improve sales force performance

KPI Architecture

Objectives KPIs Measures Data Source
Identify key factors that have the largest effect on profitability Online vs offline orders Online vs offline orders contribution to the gross profit SalesOrderHeader; SalesOrderDetail; Customer; Product; Date
Online\offline per country\subcategory\year
Best seller Profit contribution per category\subcategory\product
Sales Incentives ROI Comission-performance correlation SalesOrderHeader; SalesOrderDetail;Customer; Product; Date
Bonus-performance correlation
Discount-performance correlation
Improve sales force performance Salesperson performance Top 10 Salespeople SalesOrderHeader; SalesOrderDetail;SalesPerson; Product; Date
Profit Growth YoY

Dashboard Hierarchy

Data Requirements

# Source Table Name Table Details (type, # rows, key fields)
1 MS SQL SalesOrderHeader Fact, 31K, SalesOrderID
2 SalesOrderDetails Fact, 121K, SalesOrderID
3 Store Dim, 701, BusinessEntityID
4 SalesTerritory Dim, 10, TerritoryID
5 SpecialOffer Dim, 16, SpecialOfferID
6 SalesPerson Dim, 17, BusinessEntityID
7 Product Dim, 504, ProductID
8 Person Dim, 19K, BusinessEntityID
9 ProductSubcategory Dim, 37, ProductSubCategoryID
10 ProductCategory Dim, 4, ProductCategoryID

Data Modeling (Elasticube Design)

Suggested data model for retail distribution analysis