Getting Started
  Overview
  Manual Install
  Configuration
    Startup Properties
    General Properties
    Logging Properties

Definitions
  Dimensions
  Datasources
  KPIs
    CSV KPIs
    Freeform KPIs
  Reports
  Views

Alarms

Time Dimension

Scheduling

User Roles

Operations
Database Schema
Data Migration
Clustering

Import / Export

KPIs

Introduction

A KPI in KPI Drill is very similar to a fact in data warehousing. Every KPI you create using KPI Drill generates a fact table underneeth the hood. The fact tables are identical for the different KPI types. The difference between the KPI types is how data is loaded into them. The different KPI types are:

ETL KPIs: Load their data from defined Datasources (CSV files). In other words, responsible for importing data into the database from CSV files.
Freeform KPIs: Populate from anywhere accessable through SQL including other KPIs and remote databases.

As there are two types of KPIs in the system, this section will explain the generic form of a KPI in KPI Drill, ie details which are common to both KPI types. Specifics relating to different types are left the the respective sections.

Alarms

Every KPI has the ability to raise an alarm. Raising the alarm differs for each KPI type, however what happens once the alarm is raised is the same for each KPI type. Raising the alarm is described in each KPI section accordingly. What happens after the alarm is raised is discussed in the section alarms.

Creating KPIs

To create a KPI, select 'Create->KPI ' from the defintions at the right hand side of the GUI. A dialog appears with many tabs depending on the KPI type. The first tab is always 'KPI' and is almost identical based on the KPI type. The KPI tab is explained below. Other tabs are left to the sections on the relevent KPI types.

KPI Attributes:

Name Name for the KPI. Has no functional usage apart from describing the KPI.
Description Human readable description of this KPI. Has no functional usage apart from describing the KPI.
Period type The granularity of the period to split the KPI data into. Support periods are hourly, daily and monthly. The data will be processed in units of this period, eg if period is daily, then this KPI will typically run once daily.
Time Type Whether time should be a dimension or inline within the KPI fact table.
Fact Table Name The name to give the fact table. For example, if carsales is provided here, the fact table will be called KPI_CARSALES.
Fact Table Type Whether to use a heap table or an index organized table for the KPI fact table.
Save to CSV When set to true, each time the KPI is run, it's results will also be saved to a CSV file as well as in the fact table.
Dimensions The dimensions that this KPI will use.
Values The values that this KPI will have.

Period Type and Time Type

The period type and time type together decide how time will be treated for the KPI. They have the following effect:

Period Type Time Type Effect
Hourly Dimension The KPI fact table will have one time column that will reference the table TIME_HOUR.
Hourly In Line The KPI fact table will have four time columns: YEAR (4 digits eg 2010), MONTH (1..12), DAY (1..31) and HOUR (0..23).
Daily Dimension The KPI fact table will have one time column that will reference the table TIME_DAY.
Daily In Line The KPI fact table will have three time columns: YEAR (4 digits eg 2010), MONTH (1..12) and DAY (1..31).
Monthly Dimension The KPI fact table will have one time column that will reference the table TIME_MONTH.
Monthly In Line The KPI fact table will have two time columns: YEAR (4 digits eg 2010) and MONTH (1..12).

When in line time is used, the data can be aggregated directly on the time columns. When a time dimensions table is used, the kpi fact table needs to be joined onto the time dimension table in order to aggregate over time.

Fact Table Type

The following section is complex and not needed to get started. If you want to skip it, just use heap tables and time as a dimension. Otherwise...

Heap is the default Oracle table type and is best for large datasets similar to data warehousing in operation. Loading data into heap tables is fast. IOT (Index Organised tables) give faster response times on certain reports (more on this below) when time is inline, but do not perform as well for ad-hoc queries and are not suitable to very large tables. Data loading into IOT tables is also slower than heap, however this can in the long run be quicker if fewer additional indexes are required in the table (as an index is built in so additional indexes may be fewer).

A report that will run very fast against an IOT table with inline time will have:
1. No filter on non-time dimensions.
2. Time filter is between a start and an end date (or be an exact date match).

If both points above are true, then all the data needed for the report is stored in consecutive blocks with the database and Oracle will do an index range scan to bring the data back. If dimension aggregation is done, and the dimensions in the report are the first dimensions mentioned in the KPI, then the report will also be quicker as Oracle will be able to calculate the aggregations as it does the scan (as the dimension combinations will be stored in sequence).

If you don't know whether to use heap or IOT, then use heap to be on the safe side, as IOT's are not typically used in data warehousing.

Save to CSV

This is used to allow CSV files be created for the KPI on the fly. When a KPI has finished running and this feature is enabled, then the details for this KPI are exported to CSV file for the just ran period.

The folder to which the file goes is configured as a system parameter.

The format of the file is as follows:

<KPI_NAME>_<YEAR>_<MONTH>[_<DAY>[_<HOUR>]].csv

Where:
KPI_NAME = Name of the KPI
YEAR = Period Year
MONTH = Period Month
DAY = Period Day (only present for daily and hourly KPIs)
HOUR = Period Hour (only present for hourly KPIs)

Dimensions

You get to pick dimensions for a KPI when creating the KPI only, as this decides the structure of the underlying fact table that gets created.

For set dimensions, a column with a key into the set dimension is part of the KPI table.

For unique dimensions, a column will store the dimension value directly as a varchar.

Dimension mapping and loading is now discussed. This will make more sense after you read the sections on ETL KPIs and Freeform KPIs.

Mapping

Applies to CSV KPIs and Freeform KPIs only when using set dimensions. Each of these KPI type work around SQL that extracts dimensions. If the dimension is to be extracted as a value (typical for loading data into the database) then 'value' should be selected here. If the dimension is to be extracted based on id, then 'id' should be selected here.

For example, suppose loading an CSV KPI from a datasource where there is a column in the CSV called color, which matches a color value (eg the string 'green') to a color in our color dimension table. The ETL will read 'grean' from the file, look the value up in the dimension table, and place the returned value into the KPI fact table row. In this situation, the mapping is value.

As another example, consider a Freeform KPI that aggregates another (already loaded) KPI from inside KPI Drill. The aggregation is done on the KPI table only (without joining onto the dimension table) thus the id of the dimension is returned. In this situation, the mapping is id.

Allow New

Applies to CSV KPIs and Freeform KPIs only when using set dimensions AND the mapping is value. This means when running a CSV or KPI, and a value is provided that is NOT in the dimension table, should the value be added to the dimension table on the fly. If yes, it is added, if no then the running KPI fails.

Summary of Mapping and Allow New

As to what happens with regard to mapping and allow new is a bit confusing with all the different dimension types and KPI types. To help give an understanding, below is a summary table.

Dimension KPI Mapping Allow New Action
Unique - - - N/A - Dimension values and loading does not apply to unique dimensions as dimension tables are not used.
Set ETL or Freeform ID N/A Dimension value taken from the SQL is expected to be the id of the dimension. If the ID doesn't exist in the dimension table, the running KPI fails.
Set ETL or Freeform Value Yes Dimension value is taken from the SQL in the form of the string value, which in turn is used to look up a dimension ID in the dimension table. If the dimension is not found in the dimension table then it is added on the fly.
Set ETL or Freeform KPI Value No Dimension value is taken from the SQL in the form of the string value, which in turn is used to look up a dimension ID in the dimension table. If the dimension is not found in the dimension table then the running KPI fails.

Values

Values are easy compared to dimensions. There is only one type of value, and that's a numeric column on the fact table. When defining KPIs, you specify the names of these values only. No other definition is required.
      
KPI Drill Home