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 in Visual Studio CodeOpens in a new tab, do the following:

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

  2. Specify information about your new KPI, as prompted:

    1. Enter a fully-qualified name for the new class, including the package name and optional sub-package name using dot notation (that is, Package.Subpackage.Class).

    2. Provide an optional description for the KPI class, to be saved as comment lines for the class.

    3. Specify a logical name for the KPI, including the folder name.

    4. Specify the localization domain to which this KPI belongs. For details, see Implementing InterSystems Business Intelligence.

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

    6. Select the data source for the KPI. Options are mdx, sql, or manual. (For information on manual, see Defining Advanced KPIs.)

    You can edit all these values later as well.

  3. 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">
    </kpi>
    }
    

    The XData block defines the KPI. In the XData block, <kpi> is an XML element. This element starts with the opening <kpi> tag and ends with the </kpi> tag. 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.

  4. Optionally, specify the following additional elements within the <kpi> element:

    • Properties — Type the names of the properties of this KPI (the column names of the result set). See <property>. 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.

  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.

    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. To view a test page for your KPI, select the Test KPI link which appears before the XDATA block when you are viewing the compiled KPI class in a VS Code editor pane.

    The link opens a tab in your web browser, displaying 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