Logging Properties Definitions
Views Alarms Time Dimension Scheduling User Roles Operations
Clustering Import / Export
IntroductionA 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.
AlarmsEvery 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 KPIsTo 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:
Period Type and Time TypeThe period type and time type together decide how time will be treated for the KPI. They have the following effect:
Fact Table TypeThe 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 CSVThis 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)
DimensionsYou 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.
ValuesValues 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.