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

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:

Company KPIs Issue

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.

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.

Warehouse Area

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.