How to create a KPI Document

How to create a KPI Document

In the following section it will be explained how to create a Kpi Document. First you need to define the parameters and LOVs secondly the document template and after you have to configure the new KPI Document on the platform.

SpagoBI parameters and LOVs

In the following paragraphs it will first be explained how to define a new Data Source, a new List Of Values and a new Parameter. Afterwards you will be guided through the definition of the LOVs and Parameters that you could need for your Kpi Document. This is just an overview, for all other information just take a look at the SpagoBI documentation.

Data Source definition

In order to create a Kpi Document you will first need to create a Data Source that will permit you to interrogate the database on which you want to calculate your KPIs.

Click on the Data Source menu item and you will enter into the list of existing Data Sources.

In order to create a new Data Source click on the "Add" button on the top right corner of the page and you will enter in a form like the following one:

Fill all the blank spaces with the credentials that you need to access your database. The Label and the Description are up to you. If you have already defined your Data Source in the file server.xml in apache_home/conf then you can use the type Jndi. Differently, use the type Jdbc. The Data Source in the file server.xml should look like this:

<Resource name="jdbc/spagobi" auth="Container"
          type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost/foodmart"
          username="root" password="admin" maxActive="20" maxIdle="10"
          maxWait="-1"/>

It's possible to test the Data Source with the Test button on the top right corner of the page. If ok, a pop up window will come up saying "OK". When done, save the Data Source configuration with the save icon.

LOVs definition

Click on the Lovs Management menu item and you will enter into the list of existing List Of Values.

In order to create a new LOV click on the "Add" button on the top right corner of the page and you will enter in a form like the following one:

Choose the type of LOV you want, for example a Query, write the query in the Query Definition Box. Test it and save it.

Parameters definition

Click on the Analytical drivers management menu item and you will enter into the list of existing Parameters.

In order to create a new Analytical Driver click on the "Add" button on the top right corner of the page and you will enter in a form like the following one:

Enter the required information and click on the "Save" button. A new form like the following one will appear:

Fill all the blank spaces and click on the magnifying glass to select the LOV you want to use. A pop up window with the list of existing LOVs will appear. Select the one you want. When finished click on the "Save" button.

LOVs and Parameters for KPI documents

In the following section it will be explained the meaning and how to create the standard LOVS and Parameters for a KPI Document.

Date parameter

This parameter allow the end user to select a Date at the moment he executes the KPI document. By selecting a date different from current date, the KPIs values will be presented at the moment of the Date selected.

DEFAULT: If this parameter is not present in the KPI document execution form, or it is present but the user decides to leave it blank, the current date and time will be taken.

To create the parameter you have to click on the menu item "Analytical drivers management" and on the "Add" button on the top right corner of the page. Once you are on an empty detail, fill the form with the data shown in the next screenshot and save:

Behaviour parameter

his parameter allow the end user to select the Behaviour type, for the computation rules, at the moment he executes the KPI document. The behaviour define when the computation engine has to update the KPI values. When a KPI value is computed it will be stored in the database with two dates: a begin date and an end date that defines the range of validity of the KPI value itself. This range is obtained from the periodicity attribute of the KPI instance, if defined, otherwise an infinite end time will be used. (the convention for infinite is December 31th, 9999).

The possible Behaviour values are the following ones:

• Display: The Kpi Engine will not calculate new KPI values but it will only show the ones already available in the database. Accordingly with the date parameter required, the engine will show the more recent kpi value (kpi begin date <= req. date).
• Default: For each KPI associated to the model instance, the KPI Engine, will check if at the requested date it's possible to find an already calculated value (kpi begin date <= req. date < kpi end date). If no valid value exists, a new value will be calculated at the requested date. If it find more than one value it will act like in the Display behaviour and it will show the more recent one from the requested date (kpi begin date <= req. date).
• Recalculate: The behaviour is the same as the Default one, except that all the KPIs that don't have a defined periodicity attribute will be recalculated anyway even if a valid value already exist.
• Force Calculation: All KPIs are recalculated at the requested date even if valid values already exist.

Before to proceed with the parameter definition the LOV has to be created. First, click on the menu item "LOVs management" and you will enter into the list of existing LOVs, and than click on the "Add" button on the top right corner of the page to enter the detail form. Fill all the blanks with the data shown in the figure and save:

If you look at the Sample configuration you can find a LOV called BEHAVIOUR-DISPLAY used for the end user in order to avoid them to execute KPI calculation but allow them only to see pre-calculated values. The Behaviour parameter configuration is:

And the two usage modes are:

Register Values parameter

This parameter allow the user to select whether or not to store the calculated KPI values into the database. The possible values are:

• True: All the new calculated values will be registered into the database.
• False: All the new calculated values will only be shown to the user, but will not be registered into the database.

As for the previous parameter is has been decided to setup two LOVs, one with both True and False values and another with the False value only (for end users).

The First LOV is on the next screenshot:

The parameter configuration is like the one of the Behaviour parameter, with the two usage modes:

Usage Modes:

Resources parameter

This parameter allow the user to select for which resources he wants to see the analysis. One or more resources can be selected at the same time.

DEFAULT: If this parameter is not present in the KPI document execution form or it is present but the user decides to leave it blank, the document will calculate and show the KPI Values for all the resources associated to the Model Instance represented in the requested KPI document.

The LOV used for the parameter is show in the next screenshot:

The query will retrieve all the resources associated to the defined Model Instance (here the Model Instance Label is used). The query is:

SELECT s.resource_id Resource_Id, r.resource_name, r.resource_descr, s.kpi_model_inst
FROM SBI_KPI_MODEL_RESOURCES s, SBI_RESOURCES r, SBI_KPI_MODEL_INST i
where s.resource_id =r.resource_id  && s.kpi_model_inst = i.kpi_model_inst && i.label = 'ad58d4cb-a07d-49f0-....';

The Label is taken from the Model Instance detail, Advanced tab:

The parameter definition is the same for all the roles (one usage mode).

Template file

The Document template has to be manually prepared following the structure defined and described below.

<?xml version="1.0" encoding="windows-1250"?>
<KPI model_node_instance='[Model Instance Label]' name='Title of the document'>
  <STYLE_SUBTITLE font='Arial' size='10' color='#6699FF' name='Subtitle of the document - Date: $ P{ParKpiDate}'/> 
  <STYLE_TITLE font='Arial' size='12' color='#000000'/>  
  <CONF>
    <PARAMETER name='display_semaphore' value='true'/>
    <PARAMETER name='display_bullet_chart' value='true'/>
    <PARAMETER name='display_weight' value='true'/>
    <PARAMETER name='display_alarm' value='true'/>
  </CONF>
</KPI>

You can make your own template by substituting 'Model Instance Label' with the Label of the root node of your model instance (the same used in LOV for the resources associated to a Model Instance), 'Title of the document' with the title you want to have for your document, 'Subtitle of the document' for the subtitle and of course you can change the style of title and subtitle. To print out the Date parameter value you can use the P{ParKpiDate} placeholder. The Conf part is used to define to show (true) or hide (false) some parts of the KPI Document report:

• display_semaphore: it's a small semaphore that show the color of the threshold range that the KPI value is in.
• display_bullet_chart: it's a chart representing the requested KPI value on it's whole threshold ranges.
• display_weight: the weight defined on the KPI instance.
• display_alarm: highlight if there is an alarm configured for the KPI.

KPI Engine

First of all check the existence of the KPI Engine: Click on the menu item "Engine Management" and you will enter in the Engines list page. Check if you have an engine called "KpiEngine".

In case the Engine is not present, add it yourself by clicking on the "Add" button on the top right corner of the page and fill the blanks as in the following figure:

Document folder

Every document needs a folder under the Functionalities Tree to be stored in. Create a KPI folder child of the Functionalities one.

Add a new folder by clicking on the "Insert" menu item that appears by clicking over the word "Functionalities"

Fill the form with the configuration as shown in the screenshot and set the actions for each role:

Document creation

In order to create a new Document click on the menu item "Documents development" and you will enter into the list of existing Documents.

Click on the "Add" button on the top right corner of the page and fill the first form information:

Here you have to insert the template created before, select in which folders of the Functionalities Tree you want to make the KPI Document available and click on the 'Save' Button. Here it is shown how to insert all the parameters already defined. You can insert them in the order you prefer. For each parameter you will have to click on the Tab "New..." and before going on with another one you have to click on the 'Save' button.

Your KPI Document is done.

Scheduled Execution

It's possible to schedule the execution of KPI Document in order to compute all the KPI values and save time for the user that will obtain the already computed values.

Model Import/Export

Export

The export of a KPI Document include:

• the referenced KPI Model Instance
• the KPI Model Definition on which the Model Instance is based
• the KPI Instances, used in the Model Instance, and their KPI Definitions
• the KPI Definitions used in the Model Definition
• the Data Sets and Thresholds definition used by the exported KPIs
• the Resource definitions associated to the Model Instance

• the KPI values already computed
• the KPI Dashoboard layout

Import

Pay attention, at the last step, where you can select to override or not the already existing items with the ones you are importing. This option affects all the exported items (Models, KPIs, Data Sets, Thresholds ...)