Queries

To compose a query you must define the constants, dimensions, measures, and filters in a widget.

Dimensions represent the category of information that you want to track, such as activities, referrers, pages, country of origin, product category, and other items whose attributes are often non-numerical. A dimension produces a GROUP BY clause in the underlying SQL query.

Measures indicate the quantities that you want to measure such as number of visits, page views, hits, bounce rates, and other items that are quantified numerically.

Filters directs Analytics to the cases on which you want to run the query. A filter produces a WHERE clause in the underlying SQL query.

Functions available to configure widgets

As Measures are displayed in the widget, insert custom queries to specify what you want to analyze. For example, you can compose a measure query using Operators, Functions, and Dimensions.

The following image displays a sample of the avaliable list of dimensions, measures, and filters:

Query custom functions

Along with the standard SQL operators AVG, COUNT, MAX, MEDIAN, MIN, and SUM, IBM Process Mining provides specific operators and functions that are specifically designed for process analysis.

IBM Process Mining facilitates the use of following functions:

  • casecost(): This computes the cost of a case, according to the defined cost model. This custom function is a CASE property.
  • leadtime(): This custom function is a CASE property and it returns the lead time of the case that the event belong to. The leadtime () function indicates the timespan between the start time of the first event and the end time of the last event.
  • distinct(): This statement returns distinct values of a dimension.

Example: For each product, count the number of resources that run at least one activity on a case that involves that product.

SELECT COUNT( distinct ( RESOURCE ) ) , PRODUCT
FROM EVENTLOG
GROUP BY PRODUCT
  • casedistinct(): It is a special distinct function that takes CASE context into account. This function is useful for computing aggregations on CASE properties or on “denormalized properties”.

Example: For each region, compute the average case lead time.

select avg(casedistinct (leadtime())), Region
from EVENTLOG
group by Region
  • date() (year(), month(), dayOfMonth(), hourOfDay(), minute(), second()): This indicates the date functions that provide insights about the occurrence of a specific dimension value within a specific timeframe.

Example: For each month, compute the count of distinct products purchased.

SELECT MONTH(STARTTIME) count(distinct(product))
FROM EVENTLOG
GROUP BY MONTH(STARTTIME)


Measures available to configure widgets

Constants

Following are the numerical measures that you can use when defining queries:

Measure Description
BUSINESS_PATHTIME The working time between the two activities specified in the Activity Interval clause. The the business hours and calendar settings affect the value for this path time. Multilevel processes can have more than one BUSINESS_PATHTIME value. For example, three orders can be invoiced in the same invoice at the same time.
CASEVARIANT
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
ENDTIME
EVENTCOST The cost of an activity, computed according to the Cost Model defined in the project settings.
FILTERED_CASES The total number of cases filtered by the combined set of filters from IBM Process Mining and the Analytics dashboard. This measure is not affected by the WHERE conditions. For more information on setting filters in IBM Process Mining, see Filters.
FILTERED_EVENTS The total number of events filtered by the combined set of filters from IBM Process Mining and the Analytics dashboard. This measure is not affected by the WHERE conditions. For more information on setting filters in IBM Process Mining, see Filters.
INCLUDED_CASES The total number of cases filtered by the combined set of filters from IBM Process Mining, the Analytics dashboard, and the WHERE conditions.
LEADTIME
PATHTIME The lead time between the two activities specified in the Activity Interval clause. Note that the business hours and calendar settings does not affect this path time. Multilevel processes can have more than one PATHTIME value. For example, three orders can be invoiced in the same invoice at the same time.
RESOURCEVENTCOST It is the component of the event cost (EVENTCOST) that depends on the resource.
SERVICETIME The service time of an activity.
STARTTIME
STDEVENTCOST It is the component of the event cost (EVENTCOST) that does not depend on the resource.
TOTAL_CASES The total number of cases from IBM Process Mining.
TOTAL_EVENTS The total number of events from IBM Process Mining.
WAITTIME The wait time of an activity.

Custom metrics

You can insert custome meterics in an query only if you have already define it in the Manage tab of the process. For more information on defining a custom meteric, see Custom metrics overview.

You can insert the custom metrics in the analytics query by selecting the required custom metrics in the following fields in the "Widget setting" wizard:

  • Dimensions
  • Measures
  • Filters

Custom Metrics in Dimensions

You can also view the list of available custom metrics in the Show help section by clicking the Show help button.

Custom Metrics in Dimensions

Custom fields

You can insert every custom field in the query. When you create an Analytics widget using the custom fields, you must enter the custom field names in double quotes (" ").

Custom Fields

Measures default value

When a measure is not defined for a widget, the COUNT(*) measure is added by default.

Query example

Using the following query, the widget displays the average service time of each activity for each resource to identify the resource that performs better and the related activities. The analysis excludes the “Wait” activity.

The following SQL query indicates the widget configuration:

SQL query example