Projects:KPI Dashboard/Get Started

Contents 1 Overview 1.1 Install the Sales Dashboard module 1.2 Check the KPI Dashboard menu 2 Setup 2.1 Fiscal calendar 2.2 ETL process 2.3 Setup Activity Centers 3 Troubleshooting 3.1 Missing Data as of 3.2 Check the ETL Processes 3.3 No Setup available 3.4 Dashboard Audit 3.5 No Data Available 3.6 No Customer KPIs available 3.7 No Sales KPIs available 3.8 No Products KPIs available 3.9 No Warehouse KPIs available 3.10 Deeper analysis 3.11 Data As Of 3.12 Company KPIs Issue 3.13 Rolling graphs issue 3.13.1 Company Area 3.13.2 Customer Area 3.13.3 Sales People Area 3.13.4 Product Area 3.13.5 Warehouse Area 3.13.6 Bar graph issue

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:

Functional Specifications

Technical Specifications

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

Check the KPI Dashboard menu

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:

Fiscal Calendar

Open/Close Period Control

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.

Category: Projects ERP