View source | View content page | Page history | Printable version   

Projects:KPI Dashboard/Get Started

Contents

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

1-SearckObx.png
2-BrowseFileSystem.png
3-InstallConfirmation.png
4-AcceptLicense.png

More information: http://wiki.openbravo.com/wiki/ERP/2.50/ModularityVideos/Install_Module

5-RebuildSystem.png

6-RestartServlet.png


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

TS13 NoSetupAvailable.jpg

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

PSched SetupMenu.png
TS3 ExecProcessScheduler.png

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.

PSched Setup small3.jpg

PSched Monitor.jpg

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.


Default Setup.jpg

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

Menu Setup.jpg

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.

Dashboard Setup.jpg


For each activity center, you have to configure the following values:

Enjoy KPI Dashboard.jpg

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

TS10 ErrorProcessMonitor.jpg


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.


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.

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

TS11 ActiveSetup.JPG

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

TS12 ActiveAudit.JPG

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

TS0 EmptyPage2.jpg

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

TS4 CustoKPI Debug.JPG

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

TS4 CustoKPI0.JPG

TS4 CustoKPI 2.JPG

No Sales KPIs available

TS7 SalesKPI Debug.JPG

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

TS7 SalesKPIx.jpg

TS7 SalesKPI 2.jpg

No Products KPIs available

TS7 ProductsKPI Debug.JPG

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

TS7 ProductsKPI2x.jpg

TS7 ProductsKPI2 2.jpg

No Warehouse KPIs available

TS7 WarehousesKPI Debug.JPG

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

TS7 WarehouseKPIx.jpg

TS7 WarehouseKPI 2.jpg

Deeper analysis

You have below a few tips to check directly in your database the sentence SQL used by the Dashboards.

Data As Of

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

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

SELECT * FROM SD_PLRUNCONTROL WHERE  NAME='SD_CompanyActivity'
AND AD_CLIENT_ID = '1000000'
AND AD_ORG_ID = '1000000'
SELECT TYPE, PAST_PERIODS, AMOUNT FROM SD_COMPANYACTIVITY 
WHERE AMOUNT <> 0
AND AD_CLIENT_ID='1000000'
AND AD_ORG_ID='1000000'

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

BarLine Relation.jpg
BarLine Relation2.JPG

RollingGraph.jpg

Company Area

SELECT PAST_PERIODS, AMOUNT FROM SD_COMPANYACTIVITY 
WHERE TYPE='R'
AND AD_CLIENT_ID='1000000'
AND AD_ORG_ID='1000000'
ORDER BY PAST_PERIODS
SELECT PAST_PERIODS, AMOUNT FROM SD_COMPANYACTIVITY 
WHERE TYPE='E'
AND AD_CLIENT_ID='1000000'
AND AD_ORG_ID='1000000'
ORDER BY PAST_PERIODS
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

TS2 GraphBarEmpty.JPG

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.

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

This page has been accessed 12,515 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.