Retail Distribution

High Level Design


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.


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


  • 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


The following resources will enable you to design your dashboard and data model with sample data and then apply it to your own data. Note that you will need to have a previously installed version of Sisense (you can use the free trial version if you’re not a customer).

Sample data and dashboard examples (direct download)