Retail Distribution
Management

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.

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)