Skip to main content

Defining Basic KPIs

This page introduces key performance indicators (KPIs) in Business Intelligence and describes how to define KPIs that use hardcoded queries.

For a comparison of KPIs with other kinds of model elements, see Summary of Model Options.

Also see Defining KPIs with Filters and Listings and Defining Advanced KPIs.

For information on defining KPIs based on text analytics, see KPIs and Dashboards.

Also see Accessing the BI Samples.

Introduction to KPIs

A KPI is a class based on %DeepSee.KPIOpens in a new tab. In most cases, a KPI uses a query and displays a result set. (In other cases, a KPI only defines actions; see Defining Custom Actions.)

Ways to Use KPIs

Like pivot tables, KPIs can be displayed on a dashboard, within a widget.

You can also use the MDX %KPI function to retrieve values for a KPI. As a consequence, you can define calculated members that are based on KPIs.

To access the values of a KPI value from ObjectScript, use the %GetKPIValueArray() method of the KPI class. For an example, see Defining a KPI with a Hardcoded Query.

Comparison to Pivot Tables

KPIs are similar to pivot tables in many ways, but provide additional options that are not available for pivot tables. One difference is that a KPI can use an SQL query; this is important because SQL queries and MDX queries are suitable in different scenarios. In some cases, an SQL query is more efficient, and in such cases, you should use an SQL query within a KPI.

For additional differences and similarities between KPIs and pivot tables, see Summary of Model Options.

Requirements for KPI Queries

In most cases, a KPI uses either an MDX query or an SQL query. There are rules about the form of the query; these rules are imposed by the structure of a KPI result set (discussed in a later section).

  • If the query uses MDX, note the following requirements:

    • The query must use members for rows. You can have nested rows.

    • The query must use measures for columns.

    • The query cannot include nesting for columns.

  • The query must return numeric values.

  • If the query returns more than 1000 rows, only the first 1000 rows are used.

You can use queries that do not follow these rules. To do so, you must parse the result set and directly specify properties of the KPI instance. For information, see Defining Advanced KPIs.

Also note that if you display the KPI in a meter, only the first row of the KPI is used.

Choosing Between MDX and SQL

SQL queries and MDX queries are suitable in different scenarios, and in some cases, an SQL query is more efficient.

MDX is generally more suitable when you are aggregating across large numbers of records. In contrast, when you are not aggregating at all, or when you are aggregating only at a low level, SQL performs better. For example, consider the following pivot table:

A pivot table with PatientIDs in the rows and columns for Patient Count, Age, and Allergy Count.

In this pivot table, each row represents one row in the source table. The equivalent SQL query would be faster.

Structure of a KPI Result Set

The result set of a KPI is organized into series and properties.

A KPI series is a row. The following example shows nine series (displayed on the KPI test page, introduced later in this page). Each series has a name, which is shown here in the first column.

A KPI result set, where each series represents a city and the properties are Patient Count and Population.

A KPI property is a data column. The previous example shows a KPI with two properties.

For KPIs based on MDX queries, a series often corresponds to a member of a level, and a property often corresponds to a measure.

Defining a KPI with a Hardcoded Query

To create a simple KPI that uses a hardcoded query, do the following in Studio:

  1. Select File > New, select the Custom tab, and then select New Business Intelligence KPI.

  2. Specify the following required values:

    • Package Name — Package to contain the KPI class.

    • Class Name — Short class name of the KPI class.

  3. Optionally specify the following additional values:

    • KPI Caption — Not used.

    • KPI Name — Logical name of the KPI.

    • Description — Description of the KPI, to be saved as comment lines for the class.

    • Domain — Localization domain to which this KPI belongs; for details, see Implementing InterSystems Business Intelligence.

    • Resource — Resource that secures this KPI. For information on how this is used, see Setting Up Security.

    • Source Type — Specifies the source of the data for this KPI. Select either mdx or sql. (For information on manual, see Defining Advanced KPIs.)

    • Properties — Type the names of the properties of this KPI (the column names of the result set). Type each property on a separate line.

    • Filters — Type the names of any filters to be used in the KPI query. See Defining KPIs with Filters. Type each filter name on a separate line.

    • Actions — Type the names of any actions to be defined in the KPI. See Defining Custom Actions. Type each action name on a separate line.

    You can edit all these values later as well.

  4. Select Finish.

    The wizard generates a class definition like this:

    Class MyApp.KPI.MyKPI Extends %DeepSee.KPI
    {
    
    Parameter DOMAIN = "MyAppDomain";
    
    Parameter RESOURCE = "KPI_Resource";
    
    /// This XData definition defines the KPI.
    XData KPI [ XMLNamespace = "http://www.intersystems.com/deepsee/kpi" ]
    {
    <kpi xmlns="http://www.intersystems.com/deepsee/kpi"
     name="MyKPI" sourceType="mdx"
     caption="MyCaption"
    >
    <property name="PatCount" displayName="PatCount" columnNo="1"/>
    <property name="AvgAge" displayName="AvgAge" columnNo="2"/>
    </kpi>
    }
    

    The XData block defines the KPI. In the XData block, <kpi> is an XML element. This element starts with the opening <kpi and ends with the closing angle bracket. xmlns, name, sourceType, and caption are XML attributes. Each attribute has a value. In this example, the value of the sourceType attribute is mdx.

    The class also includes stub definitions for several methods; by default, these do nothing. For details, see Defining KPIs with Filters and Defining Advanced KPIs.

  5. Within the <kpi> element, add one of the following attribute specifications:

    mdx="MDX query"
    

    Or:

    sql="SQL query"
    

    Where MDX query is an MDX SELECT query or SQL query is an SQL SELECT query. (Use the mdx option if you chose mdx in the wizard, and use the sql option if you chose sql.)

    For example:

    <kpi xmlns="http://www.intersystems.com/deepsee/kpi"
     name="MyKPI" sourceType="mdx" 
     mdx="SELECT {MEASURES.[%COUNT],MEASURES.[Avg Age]} ON 0, HomeD.H1.City.MEMBERS ON 1 FROM patients"
     caption="MyCaption"
    >
    

    You can add the attribute specification anywhere between the opening <kpi and the closing angle bracket. The attribute specification can be on its own line, as shown here, or it can be on the same line as other attributes. Within the XData block, Studio provides assistance as you type.

    For requirements, see Requirements for KPI Queries, earlier in this page.

    For information on MDX, see Using InterSystems MDX and InterSystems MDX Reference.

  6. Optionally specify class parameters, as described in the next section.

  7. Compile the class.

  8. Select View > Web Page.

    You then see something like the following:

    A KPI Test Page in Studio, showing the KPI class name, any Filters, the MDX Query, and the KPI values.

    The Series column indicates the name of each series. This name is available as a label when you display this KPI in a scorecard.

    To the right of those columns, this table has one column for each <property> of the KPI. This column shows the current value of that property, for each row in the KPI.

The KPI test page provides a convenient way to test the KPI before using it. You can also use the %GetKPIValueArray() method of the KPI class. For example:

SAMPLES>set status=##class("HoleFoods.KPIYears").%GetKPIValueArray("HoleFoods.KPIYears",.pValues,$LB("Value"))
 
SAMPLES>w status
1
SAMPLES>set status=##class("HoleFoods.KPIYears").%GetKPIValueArray("HoleFoods.KPIYears",.pValues,$LB("Value"))
 
SAMPLES>w status                                                                1
SAMPLES>zw pValues                                                              pValues(1)=$lb("2010")
pValues(2)=$lb("2011")
pValues(3)=$lb("2012")
pValues(4)=$lb("2013")
pValues(5)=$lb("2014")
pValues(6)=$lb("2015")

For details, see the class reference for %DeepSee.AbstractKPIOpens in a new tab.

Specifying Class Parameters

You can specify some or all of the following class parameters in your KPI class:

DOMAIN
Parameter DOMAIN = "MyAppDomain";

Specifies the localization domain to which this KPI belongs; for details, see Implementing InterSystems Business Intelligence.

FORCECOMPUTE

Specifies whether the system should always recompute the values in this KPI when this KPI is used within an MDX query (that is, via the %KPI function). The default is false; when that query is rerun, the system uses cached values instead.

If the KPI uses external data, it may be useful to set FORCECOMPUTE equal to true.

LABELCONCAT

Specifies the character used to concatenate labels for an MDX-based KPI that uses CROSSJOIN or NONEMPTYCROSSJOIN for rows. The default is a slash (/).

PUBLIC

Controls whether the KPI is available for use in scorecards and other dashboard widgets, as well as for use with the MDX %KPI function. If you want to hide the KPI from use in dashboards, add the PUBLIC class parameter to the class, with the value 0.

RESOURCE
Parameter RESOURCE = "KPI_Resource";

Specifies the resource that secures this KPI. For information on how this is used, see Setting Up Security.

For the ASYNC class parameter, see Defining Advanced KPIs.

Specifying Ranges and Thresholds for Speedometers

Within the definition of a KPI, you can specify its range and threshold values, for use in speedometers. To specify these values, edit the <kpi> element and specify the following attributes:

  • rangeLower — Default lowest value to display in the meter.

  • rangeUpper — Default highest value to display in the meter.

  • thresholdLower — Default lower end of the threshold for this KPI. The threshold area is displayed in contrasting color.

  • thresholdUpper — Default higher end of the threshold.

For example:

<kpi name="KPIForRangeDemos"
sourceType="mdx" 
mdx='SELECT MEASURES.[%COUNT] ON 0, AgeD.[All Patients] ON 1 FROM PATIENTS' 
rangeLower="0"
rangeUpper="900"
thresholdLower="20"
thresholdUpper="800"
>

<property name="Patient Count" columnNo="1" />

</kpi>

When displayed in a speedometer, this KPI looks as follows (by default):

A dial-shaped speedometer, with the needle pointing to 900 (the maximum) and a box at the bottom showing a value of 1000.

Notice the value box in the speedometer displays the actual KPI value (1000), even though it is higher than the value of rangeUpper.

You can also set the range and threshold values programmatically, which is useful if hardcoded values are not appropriate. See Defining Advanced KPIs.

Note:

When you configure a scorecard on a dashboard, you have the options Lower Threshold, Upper Threshold, Lower Range, and Upper Range. Note that the KPI attributes rangeLower, rangeUpper, thresholdLower, and thresholdUpper do not affect these scorecard options.

Disabling the %CONTEXT Filter

As noted earlier, you can use the MDX %KPI function to retrieve values for a KPI. For MDX-based KPIs, the %KPI function has an optional parameter (%CONTEXT) that passes context information to the KPI. By default, this context information is applied to the MDX query as a filter clause. To disable this automatic behavior, override the %GetMDXContextFilter() method as follows:

Method %GetMDXContextFilter() As %String
{
    Quit ""
}
FeedbackOpens in a new tab