View source | View content page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Show collection (0 pages)
Collections help

Search

Projects:KPI Dashboard/Technical Documentation

Contents

Overview

This document outlines the technical specifications for the KPI Dashboard implementation's first phase. These Dashboards belong to the global Business Intelligence module Openbravo started with Pentaho and the 2.40 version.

This article won't explain how to configure the Dashboards, Go to the Get Started document for this purpose.

Supplement of information:

Functional Specifications

Get Started

Table Descriptions

Type Name Description
Setup SD_DASHBOARDS Setup for a specific Client and specific Organization. The ETL process inserts automatically data in this table if an active setup does not exist.
Run Control SD_PLRUNCONTROL The ETL processes will insert the run status for each area of analysis. The database responsible can have a look in this table for trouble shouting.

See Check processes status for more information

Company Area SD_GETCOMPANYMETRICS

SD_COMPANYACTIVITY

Year To Date, Realized Last Year and Percentage of increase/decrease compared with the Last Year to Date.

Revenues, Expenses and profits calculated for the 12 last periods.

Customers Area SD_GETCUSTOMERMETRICS

SD_CUSTOMERSACTIVITY

Year To Date, Realized Last Year, Pipeline (not implemented), Quarter to Date (not implemented) and Percentage of increase/decrease compared with the Last Year to Date.

Revenues and Rank for the 12 last periods.

Sales Area SD_GETSALEMETRICS

SD_SALESACTIVITY

Year To Date, Realized Last Year, Pipeline (not implemented), Quarter to Date (not implemented) and Percentage of increase/decrease compared with the Last Year to Date.

Revenues and Rank for the 12 last periods.

Products Area SD_GETPRODUCTMETRICS

SD_PRODUCTACTIVITY

Year To Date, Realized Last Year and Percentage of increase/decrease compared with the Last Year to Date.

Revenues and Rank for the 12 last periods.

Warehouses Area SD_GETWAREHOUSEMETRICS

SD_WAREHOUSEACTIVITY

Year To Date, Realized Last Year and Percentage of increase/decrease compared with the Last Year to Date.

Revenues and Rank for the 12 last periods.

Configuration

We choose to retain The Key Performance Indicators (KPIs) built on 5 main axes:

Each axis will have many Key Performance Indicators, the development will have a high priority of modularity in order to be able to change them easily if we note a gap between what we provide and what our customers really need (by role and industry).


Company

KPI Manual Activation/Desactivation Forecast Current Year
All Axes Yes (All KPIs) N/A
Revenue Yes Yes
Expenses Yes Yes
Profit Yes Yes

Customers

KPI Orientation Manual Activation/Desactivation Setup available
All Axes Pipeline

Forecast

Nice to have

Should have

Yes
Top 5 customers n/a Yes Yes

Sales People

KPI Orientation Manual Activation/Desactivation Setup available
All Axes Pipeline

Forecast

Nice to have

Should have

Yes
Top 5 sales people n/a Yes Yes


Product Category

KPI Orientation Manual Activation/Desactivation Setup available
All Axes Pipeline

Forecast

Nice to have

Should have

Yes
Top 5 product category n/a Yes Yes

Warehouses

KPI Orientation Manual Activation/Desactivation Setup available
All Axes Pipeline

Forecast

Nice to have

Should have

Yes
Top 5 warehouses n/a Yes Yes


Alerts

Type Axis How Activation/Desactivation
Increase Company

Customers

Sales People

Product Category

Warehouses

Percent of Last Year to Date Yes
Decrease Company

Customers

Sales People

Product Category

Warehouses

Percent of Last Year to Date Yes


Periods

The dashboards could present results of periods from the beginning of the year or beginning of the fiscal year.


ETL Process

Java Script

package org.openbravo.dashboards.salesdashboard;
import org.openbravo.scheduling.ProcessBundle;
import org.openbravo.erpCommon.ad_process.ProcedureProcess;
public class SD_CallProcedures extends ProcedureProcess {
@Override
protected void init(ProcessBundle bundle) {
  setSQL("CALL SD_CallProcedures(?, ?)");
  String client = bundle.getContext().getClient();
  String organization = bundle.getContext().getOrganization();
  String[] params = new String[] { client, organization };
  String[] types = new String[] { "in", "in" };
  setParams(params, types);
}}

Main PLSQL Script

(......)	
SD_PutSetupData (v_AdClientId,v_AdOrgId);
SD_GetFiscalCalendar (v_AdClientId,v_AdOrgId);
SD_CompanyGraphs (v_AdClientId,v_AdOrgId);
SD_PutCompanyMetrics (v_AdClientId,v_AdOrgId);
SD_PutSalesMetrics (v_AdClientId,v_AdOrgId);
SD_SalesGraphs (v_AdClientId,v_AdOrgId);
SD_PutCustomersMetrics (v_AdClientId,v_AdOrgId);
SD_CustomersGraphs (v_AdClientId,v_AdOrgId);
SD_PutProductMetrics (v_AdClientId,v_AdOrgId);
SD_ProductGraphs (v_AdClientId,v_AdOrgId);
SD_PutWarehouseMetrics (v_AdClientId,v_AdOrgId);
SD_WarehouseGraphs (v_AdClientId,v_AdOrgId);
(.....)

PLSQL Scripts

The whole Dashboard logic is therefore built on 12 scripts PL/SQL.

Script Called by Call Sequence Source tables Target tables
SD_CallProcedures (PL) SD_CallProcedures (Java) N/A N/A N/A
SD_GetFiscalCalendar SD_CallProcedures (PL) 1 AD_ORG, C_YEAR, C_PERIOD SD_PLRUNCONTROL, SD_FISCALCALENDAR
SD_CompanyGraphs SD_CallProcedures (PL) 2 C_PERIOD, C_YEAR, AD_CLIENT, FACT_ACCT, C_CONVERSION_RATE, C_ELEMENTVALUE, C_VALIDCOMBINATION, AD_ORG, AD_ORGTYPE SD_PLRUNCONTROL, SD_COMPANYACTIVITY
SD_PutCompanyMetrics SD_CallProcedures (PL) 3 SD_FISCALCALENDAR, C_CONVERSION_RATE, C_PERIOD, C_YEAR, AD_CLIENT, FACT_ACCT, C_ELEMENTVALUE, C_VALIDCOMBINATION SD_PLRUNCONTROL, SD_GETCOMPANYMETRICS
SD_PutSalesMetrics SD_CallProcedures (PL) 4 SD_FISCALCALENDAR, AD_ORG, C_CURRENCY, AD_USER, AD_CLIENT, C_BPARTNER C_INVOICESCHEDULE, C_ORDER, C_ORDERLINE, M_PRODUCT, C_CONVERSION_RATE SD_PLRUNCONTROL, SD_GETSALEMETRICS
SD_SalesGraphs SD_CallProcedures (PL) 5 SD_FISCALCALENDAR, AD_USER, AD_CLIENT, AD_ORG, C_CURRENCY, C_BPARTNER , C_INVOICESCHEDULE, C_BPARTNER, C_INVOICESCHEDULE, C_ORDERLINE, M_PRODUCT, C_ORDER, C_CONVERSION_RATE SD_PLRUNCONTROL, SD_SALESACTIVITY
SD_PutCustomersMetrics SD_CallProcedures (PL) 6 SD_FISCALCALENDAR, AD_CLIENT, C_ORDERLINE, C_ORDER, C_CONVERSION_RATE, AD_ORG, AD_ORGTYPE SD_PLRUNCONTROL, SD_GETCUSTOMERMETRICS
SD_CustomersGraphs SD_CallProcedures (PL) 7 SD_FISCALCALENDAR, AD_CLIENT, C_ORDERLINE, C_ORDER, C_CONVERSION_RATE, AD_ORG, AD_ORGTYPE SD_PLRUNCONTROL, SD_CUSTOMERSACTIVITY
SD_PutProductMetrics SD_CallProcedures (PL) 8 SD_FISCALCALENDAR, AD_CLIENT, C_ORDERLINE, C_ORDER, C_CONVERSION_RATE, AD_ORG, AD_ORGTYPE, M_PRODUCT_CATEGORY SD_PLRUNCONTROL, SD_GETPRODUCTMETRICS
SD_ProductGraphs SD_CallProcedures (PL) 9 SD_FISCALCALENDAR, AD_CLIENT, C_ORDERLINE, C_ORDER, C_CONVERSION_RATE, AD_ORG, AD_ORGTYPE, C_CURRENCY, M_PRODUCT_CATEGORY SD_PLRUNCONTROL, SD_PRODUCTACTIVITY
SD_PutWarehouseMetrics SD_CallProcedures (PL) 10 SD_FISCALCALENDAR, AD_CLIENT, C_ORDERLINE, C_ORDER, C_CONVERSION_RATE, AD_ORG, AD_ORGTYPE, C_CURRENCY, M_WAREHOUSE SD_PLRUNCONTROL, SD_GETWAREHOUSEMETRICS
SD_WarehouseGraphs SD_CallProcedures (PL) 11 SD_FISCALCALENDAR, AD_CLIENT, C_ORDERLINE, C_ORDER, C_CONVERSION_RATE, AD_ORG, AD_ORGTYPE, C_CURRENCY, M_WAREHOUSE SD_PLRUNCONTROL, SD_WAREHOUSEACTIVITY
SD_PutSetupData SD_CallProcedures (PL) 12 SD_GETCOMPANYMETRICS, SD_GETPRODUCTMETRICS, SD_GETCUSTOMERMETRICS, SD_GETWAREHOUSEMETRICS, SD_GETSALEMETRICS, SD_GETCUSTOMERMETRICS SD_PLRUNCONTROL, SD_DASHBOARDS

ETL Diagrams

ETL1-2.jpg

ETL3.jpg

Improvements

Retrieved from "http://wiki.openbravo.com/wiki/Projects:KPI_Dashboard/Technical_Documentation"

This page has been accessed 12,345 times. This page was last modified on 8 June 2012, at 05:28. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.