Queries for Building Kie Server Dashboards

Business Central is a great tool to create Dashboards as presented in KieLive#8 Authoring Dashboards in Business Central. In that video, datasets were mentioned, but there was no example of Kie Server datasets. In this post, we describe how to create Kie Server datasets and share some useful queries to create datasets for business dashboards.

Creating Kie Server DataSets

A Kie Server dataset runs remote queries on a Kie Server installation and organizes the results in datasets that can be used in Business Dashboards. The requirement for this post is to have a Kie Server connected to Business Central. Fortunately, in jBPM server installation we already have a setup configured for you, with a sample kie server connected to Business Central.

In order to create a dataset we must first access the Datasets tool from Admin tools in Business Central, see

Kie Server datasets are also called Remote Server datasets. When you access the Datasets editor and click on New Data Set you will see the Execution Server type in the list.

Once you select Execution Server you will be prompted to enter more information about the dataset, which are:

UUID: The dataset identifier which is also used as the query name on the Kie Server side;

Name: An user-friendly name for the dataset;

Data Source JNDI Name: This field can be ignored. It is derived from SQL datasets, which is not used here;

Server Name: The Kie Server configuration for the servers connected to BC;

Query Target: Set it as CUSTOM to use free SQL

Query: The SQL query used to retrieve data.

After you fill the fields, click on Test you will see the query result in a table, and in next to save. Then you will be able to use this dataset to create dashboards.

To build the query you need to have an understanding of the jBPM tables schema, which would take some time if you are a beginner.

In order to help you to get insights about your running Kie Server, let’s share some useful queries in this post and explain it and then how you can import all queries to your installation.

Kie Server Useful Queries

You can retrieve a lot of insights from Kie Server. In this section, we will describe some useful and common queries:

• Nodes updates at the last minute: This query can be used to check which processes nodes were active only at the last minute, this can be used to monitor a process activity for a period of time. You can change to any period of time

• Task status as categories: A task status is in a column specific to it. In this query, we transform the row value into a column. With this query, status can be used as a chart category.

It is possible to have task status as categories

• Nodes execution time and hits: Heatmaps are introduced in Business Central 7.48.0 and it needs to be filled with queries that give any information about a process’s nodes. With this query, we have the node execution time and the total hits and both information can be used with the Process Heat Map Component.

The node execution data can be used to build heatmaps

• Task Variables Values: This dataset contains all human tasks and variable values. It is useful to check a specific task user input.

Task Variable Values output

Import queries

If you don’t want to create queries manually you can import a ZIP we made available and it contains all the described queries and it may be updated later with new queries. Notice that the ZIP also contains queries used internally by Business Central, but in public mode so you can build your own Dashboards.

Download ZIP from Github

When you import the queries and go to the datasets page you may notice an error. This happens because the server template name probably was not found in this installation. There are at least two ways to workaround this problem:

• Correct the server template name in the dataset editor

Importing and editing queries to correct the server template

• Modify dataset JSON files directly from the ZIP files to set the correct server template. By the way, you can delete and remove files as you want.

Changing server template id directly in the JSON file

NOTE: When testing a query you may face the error from JIRA RHPAM-3359. Ignore the error, save the dataset and re-open it. This is a known issue when testing datasets related to the columns ordering and it should be fixed in a later version.

Error that may happen when testing a dataset

Conclusion

In this post, we shared how to create Kie Server datasets and how to import useful queries to your installation. You can check this post content also on Youtube

Next post let’s finally introduce the new built-in Heatmap component.