Projects:KPI Dashboard/Get Started
Overview
This document outlines the manual actions required to install and setup your KPI Dashboard. You also have a section Trouble Shouting to help you resolving issues that could occurring.
Remark 1: Internet Explorer does not support some javascript syntax to manage the mouse position. We recommend to use Firefox. |
Remark 2: This module has been tested on Oracle. It should not have any issue on PostGreSql, but the QA test is still pending. |
Supplement of information:
Install the Sales Dashboard module
More information: http://wiki.openbravo.com/wiki/ERP/2.50/ModularityVideos/Install_Module
- Enter the application with your system administrator user, in order to see the Module Management menu entry (General Setup\Application\Module Management).
- Go to the tab “Add Modules” and click the the “Browse File System” button.
- Select the module file you downloaded before and continue the installation clicking on the “continue” buttons (with license agreements).
- Select the module called KPI Dashboard and Rebuild your system.
- Check the build process has completed successfully and restart the servlet container (tomcat web context).
After that you will be redirected to the login Page. The KPI Dashboard module should be available. Please see the following sections to setup your module
After installing and rebuilding your application, you will see a new entry in your menu: Business Intelligence\KPI Dashboard.
Depending on your role, you can also see the setup pages: Business Intelligence\KPI Dashboard Setup.
Warning: No KPIs will be available before executing the ETL process and Setup the Activity Centers for each analytical axis.
A specific message No Setup available would otherwise appear in place of the KPIs.
Setup
Fiscal calendar
The Sales Dashboard is using the fiscal calendar. If you don’t configure it, the dashboards will work fine, considering the 12 standard periods of the year.
For more information on how to setup a fiscal calendar:
ETL process
The KPI Dashboards are based on an ETL process (Extract, Process, Transform).
- Extract: Data from transactional and accounting tables.
- Transform: The data are cleaned, filtered, validated as regard business rules.
- Load: The data are loaded into the staging tables.
Openbravo allows you scheduling a process, we will use this important feature the schedule the ETL process, for example every night, to have up to date data every mornings.
Important note: The ETL process will automatically detect your current organization (AD_ORG_ID) and client (AD_CLIENT_ID). These values will be used to Export the transactional data from original tables to staging tables. As a consequence, please be sure to execute this process with the correct access. |
1 - Go to General Setup/Process Scheduling/Process Request
2 - Create a new record if necessary and select the process “SD_PutCompanyMetrics”. This ETL process will build a cube of data using the transactional and accounting tables. All the information will be inserted in Staging Tables in order to avoid any problem of performance. You will find a deeper information about these tables in the section Trouble Shouting.
3 - Select “Run Immediately” if you want to execute the process to test your data, but the idea is to execute it periodically, every night at 11h59 for example in order to have the new KPIs every morning.
For instance have a look to the screenshot below if you want creating the cube every weekday nights at 11h59PM.
4 - Go to General Setup/Process Scheduling/Process Monitor
After executing your ETL process, have a look at the Process Monitor to ensure the process completed successfully.
You should normally have now a default setup in your page Business Intelligence\KPI Dashboard Setup.
If you don't have any setup available or if the process has a status Error, please go to the trouble shouting section for this ETL Process.
A run control table can help you identifying the origin of the issue.
Setup Activity Centers
The KPI Dashboard must be configured by activity center (Company, Sales, Customer, Product, and Warehouse). The setup page is available opening the page KPI Dashboard Setup.
The version 1.0 does not allow a specific security access to the centers, as regard your role. You have access o not to the sales dashboard, but the data available (data access level) will of course depends on your role.
For each activity center, you have to configure the following values:
- Forecast
- Satisfactory
- Good
- Alert Increase
- Alert Decrease
Troubleshooting
Missing Data as of
Check if you have a value in Data as of {DateValue}. If not, you probably forgot to schedule you process to extract data from transactional tables. See Chapter deeper analysis if required.
Check the ETL Processes
If you already executed the ETL process and its global status in the Process Monitor is “Successfully completed”, and you don't see your default setup or you miss data in your KPI Dashboard, you can go deeper looking in database the Run Control Table SD_PLRUNCONTROL.
- Check the Run Control table for your specific client and organization (1000000 in our example):
SELECT * FROM SD_PLRUNCONTROL WHERE AD_ORG_ID = '1000000' AND AD_CLIENT_ID = '1000000' ORDER BY sequence
You must have the following processes with a Successfully Completed or Not Required status.
- 0 SD_CallProcedures (Main script, call all the scripts below)
- 1 SD_GetFiscalCalendar
- 2 SD_CompanyGraphs
- 3 SD_PutCompanyMetrics
- 4 SD_PutSalesMetrics
- 5 SD_SalesGraphs
- 6 SD_PutCustomersMetrics
- 7 SD_CustomersGraphs
- 8 SD_PutProductMetrics
- 9 SD_ProductGraphs
- 10 SD_PutWarehouseMetrics
- 11 SD_WarehouseGraphs
- 12 SD_PutSetupData
If you don’t have these 13 processes, refer to the missing PL/SQL process for more information about the error. You can try executing it manually directly in your SQL editor to have more information.
For example:
On Oracle: EXEC GETFISCALCALENDAR (1000000,1000000);
On PostgreSQL: PERFORM SD_GETFISCALCALENDAR (1000000,1000000);
No Setup available
You don’t have any setup available for your current client and Organization. Go to Sales Dashboard Setup and verify you have at least one active configuration.
Dashboard Audit
An audit can help you to determine the origin of your issues. Most of the time you don’t see any graphs because you did not enter any Forecast values (required), you can have a first level of information activating the audit options:
Go to Sales Dashboard Setup>Company and check if the Audit option for the current setup is selected.
No Data Available
If after executing your ETL process and checking the status of the processes you still have an empty page (screenshot bellow) you can go to the section Deeper Analysis.
You maybe don’t have data for the analyzed period, You can validate this hypothesis looking directly at the Staging Tables (Technical documentation).
No Customer KPIs available
We suppose the ETL process ran successfully, for a period that contains data. Check you have at least one customer with the flag “Visible” and a forecast amount associated to this customer.
Security access issue | Setup issue |
No Sales KPIs available
We suppose the ETL process ran successfully, for a period that contains data. Check you have at least one sales person with the flag “Visible” and a forecast amount associated to this sales person.
Security access issue | Setup issue |
No Products KPIs available
We suppose the ETL process ran successfully, for a period that contains data. Check you have at least one product with the flag “Visible” and a forecast amount associated to this product.
Security access issue | Setup issue |
No Warehouse KPIs available
We suppose the ETL process ran successfully, for a period that contains data. Check you have at least one warehouse with the flag “Visible” and a forecast amount associated to this warehouse.
Security access issue | Setup issue |
Deeper analysis
You have below a few tips to check directly in your database the sentence SQL used by the Dashboards.
Data As Of
- Identify your Organization and Entity (Client):
SELECT AD_ORG_ID, AD_CLIENT_ID, NAME FROM AD_ORG WHERE isactive='Y'
In the examples bellow we fixed ad_client_id=ad_org_id=1000000
- Check the last ETL process date (Data as of) for this Organization and Entity:
SELECT isversionactive, LOWER ((SELECT TO_CHAR (MAX (cntrl.updated), 'DD MON YYYY HH:MI') FROM sd_plruncontrol cntrl WHERE setup.ad_org_id = cntrl.ad_org_id AND setup.ad_client_id = cntrl.ad_client_id) ) AS dataasof FROM sd_dashboards setup WHERE setup.updated == (SELECT MAX (updated) FROM sd_dashboards sub WHERE isversionactive = 'Y' AND sub.ad_org_id = setup.ad_org_id AND sub.ad_client_id = setup.ad_client_id) AND SETUP.AD_ORG_ID = '1000000' AND SETUP.AD_CLIENT_ID = '1000000'
Company KPIs Issue
- Check your Run Control Table:
SELECT * FROM SD_PLRUNCONTROL WHERE NAME='SD_CompanyActivity' AND AD_CLIENT_ID = '1000000' AND AD_ORG_ID = '1000000'
- Check the table SD_COMPANYACTIVITY: You must have at least on month with revenues in the past 12 months. If don’t have output rows, try to re-execute the ETL process being sure you have data available for the current period, as regard your client and Organization.
SELECT TYPE, PAST_PERIODS, AMOUNT FROM SD_COMPANYACTIVITY WHERE AMOUNT <> 0 AND AD_CLIENT_ID='1000000' AND AD_ORG_ID='1000000'
- Check the table SD_GETCOMPANYMETRICS: You must have data for the fields LastYTD and RealizedLastYear. If the amount is zero (you don’t have data for the last exercise), you won’t be able to activate the alerts, based on a percentage (decrease or increase) of the Year To Date amount as regard the Last YTD amount.
The field Realized Last Year will allow you to have a supplementary horizontal bar.
SELECT TYPE, PERCENTAGE, YTD, LASTYTD, REALIZEDLASTYEAR FROM SD_GETCOMPANYMETRICS WHERE AD_ORG_ID = '1000000' AND AD_CLIENT_ID = '1000000'
Rolling graphs issue
Company Area
- Revenues
SELECT PAST_PERIODS, AMOUNT FROM SD_COMPANYACTIVITY WHERE TYPE='R' AND AD_CLIENT_ID='1000000' AND AD_ORG_ID='1000000' ORDER BY PAST_PERIODS
- Expenses
SELECT PAST_PERIODS, AMOUNT FROM SD_COMPANYACTIVITY WHERE TYPE='E' AND AD_CLIENT_ID='1000000' AND AD_ORG_ID='1000000' ORDER BY PAST_PERIODS
- Profits
SELECT PAST_PERIODS, AMOUNT FROM SD_COMPANYACTIVITY WHERE TYPE='P' AND AD_CLIENT_ID='1000000' AND AD_ORG_ID='1000000' ORDER BY PAST_PERIODS
Customer Area
SELECT NAME, PAST_PERIODS, AMOUNT FROM SD_CUSTOMERSACTIVITY WHERE AMOUNT <> 0 AND AD_CLIENT_ID='1000000' AND AD_ORG_ID='1000000' ORDER BY NAME, PAST_PERIODS
Sales People Area
SELECT NAME, PAST_PERIODS, AMOUNT FROM SD_SALESACTIVITY WHERE AMOUNT <> 0 AND AD_CLIENT_ID='1000000' AND AD_ORG_ID='1000000' ORDER BY NAME, PAST_PERIODS
Product Area
SELECT NAME, PAST_PERIODS, AMOUNT FROM SD_PRODUCTACTIVITY WHERE AMOUNT <> 0 AND AD_CLIENT_ID='1000000' AND AD_ORG_ID='1000000' ORDER BY NAME, PAST_PERIODS
Warehouse Area
SELECT NAME, PAST_PERIODS, AMOUNT FROM SD_WAREHOUSEACTIVITY WHERE AMOUNT <> 0 AND AD_CLIENT_ID='1000000' AND AD_ORG_ID='1000000' ORDER BY NAME, PAST_PERIODS
Bar graph issue
- Check Run Control Table for NAME=’SD_CompanyActivity’
- Check the table SD_CompanyActivity
This graph does not give you the past 12 months rolling revenues. It presents the amount of revenues or revenues/expenses from the beginning of the year (or fiscal year) till the current day. The graph bars will always be equal to zero for the future periods. You must have at least a value for the months between the beginning of the year and the current month.