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:
- See Also: Data Integration with Pentaho and ETL Wikipedia
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:
- Company
- Customers
- Product Category
- Sales People
- Warehouses
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
Improvements
- Iframes consideration (performance issue, obsolescence)
- Internet Explorer constraints : SetAttribute not available (no information on Mouse movements)
- Show Scrollbar if necessary (and only if necessary)
- Openbravo CSS for characters
- Drill-down ability
- Business Intelligence Icon