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:
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)
