In my previous two blogs, I have made a start with the study of the BAM components. I have started with the Data Objects (BAM Data Objecten, BAM Calculated Fields). In this post I will talk about Queries.
From the Oracle Documentation:
A business query or query is a request for data that matches specified conditions. A query can fetch from a data object once, on a schedule, or continuously.
A Flat SQL Query is a simple table of data fields and their values. You select the data object and then select the data fields.
- I will use this type, among other things, to show the number of days a task is open.
A Group SQL Query query is an analysis of one or more numeric data fields, called measures, grouped by non-numeric data fields called dimensions. For example, a group SQL query might analyze cases grouped by department. You select the data object and then select the measures and dimensions.
- I will use this type also multiple times. For example to show Today’s Task Summary.
A Tree Model Query, written in SQL, is an analysis of one or more measures grouped by a hierarchy of dimensions. For example, a tree model query might analyze cases grouped by Judicial system, Department, Team and Employee. You select the data object and then select the measures and a hierarchy.
- I will use this type to make the Organizational Hierarchy visible.
A Continuous Query is an analysis of data collected in real time. For example, a continuous query might fetch the call volume for a call center every ten minutes. There are multiple continuous query templates, which perform these analyses. Continuous queries are written in Continuous Query Language, or CQL. Unlike SQL, which operates on data in a database or cache and responds to client requests, CQL operates on data in-memory and pushes the results to clients
- I will use this type to give real time information about the current number of open tasks (Monitor Count Template) that might affect the average number of days a task is open (as part of a KPI Watchlist).
As shown in the following table you cannot use any type of query for every kind of Business View.
For the POC project I created the following Queries:
|1||Organizational Tasks||Tree Model|
|2||Number Of Open Tasks Issue||Group SQL|
|3||Today’s Tasks||Group SQL|
|4||Yesterday’s Tasks||Group SQL|
|6||Open And Closed Tasks||Group SQL|
|7||Days Open Task List||Flat SQL|
|8||Organizational Parts||Group SQL|
|10||Open Task Monitoring||Continuous SQL|
|11||Open And Closed Tasks Without Timing||Group SQL|
At the end of this post you can find a link to download the queries. I will not go through all the details of these queries but I will limit this to the relevant aspect. For every query I will reference to the related requirement (BAM Projects requirements).
So let’s start. First the ‘Organizational Tasks’. I need this for requirement 3. Most Interesting part here is that I have used the Organisational Hierarchy which is specified on Data Object level (BAM Data Objecten). Because I’m only interested in open tasks there is a filter to select them.
Next: the group query ‘Number Of Open Tasks Issue’. There is nothing special about this query, it just gives back one number. The number of currently open tasks. Used for requirement 2 and 11.
Third, the group query ‘Todays Tasks’.
This query select information about currently open tasks or tasks closed today. Selected fields are all calculated fields (BAM Calculated Fields). This query is used for requirement 3 and 4.
The fourth query is more or less the same, but then it give the information of yesterday (also requirement 3 and 4).
The fived query ‘Urgency’ is also more or less the same. Now the urgency / due date related information is selected (to support requirement 3, 7 and 8).
Number 6: ‘Open And Closed Tasks’. This group query is used to get a graphical representation of opened and closed tasks on a daily basis (use for requirement 2, 3, 5, 6 and 8).
As you can see, I have used the Activity Start Time as Dimension. There are 2 options to use time related dimensions. As Time Series or Time Groups. I have used Time Series.
- Use Time Series — Displays the data from the first datetime data point available in the data object to the last in the configured time interval.
- Use Time Groups — Displays data grouped into a set number of time intervals. For example, if you select Month from the time unit list, all data from January from all years where data is available are grouped in one data point on the chart.
In the filter I have used a parameter. The parameter ‘Judicial System’ is updatable at runtime by the enduser. This give the possibility to filter the selected cases (requirement 6). For more details about the parameter see BAM Parameters.
7: Flat query ‘Days Open Task List’. Used for requirement 1, 9 and 10.
Two things for this query. First the ‘Duration Icon’. This is a calculated field that contains a piece of HTLM code that differs depending on the value of the ‘Days Opened’ calculated field. Second, the ‘Task Url’ contains a part of url that is used to open the associated humantask. Usage of this url is implemented at the view level (link to the post as soon as it’s there).
Than we have group query ‘Organizational Parts’ (requirement 2, 3, 6 and 8). Similar as for query 6 this query is also using the ‘Judicial System’ parameter.
Query number 9 ‘Logos’ is a special one (for the benefit of requirement 6, 8 and 10). I tried to display a big image on the dashboard that was different for each ‘Judicial System’ (to personalise the view a little bit). This is a basic flat query that uses the ‘Judicial System’ parameter.
Next is the ‘Open Task Monitoring’ continuous query (used for requirement 11 and 12). I want to monitor the number of open tasks (over the last 2 weeks), and when the total number exceeds 65 I want to signal this. I implemented it as shown below.
Operation of this query is quite unclear. When exactly is a signal given? Impact is hard to perceive because this can only be done indirectly via e.g. a KPI or Alert. Some test and research works indicates that the following occurs:
Every minute is determined if there is something changed (new or changed records) in the data. And if that is the case a count of the number of open tasks takes place. If this turn out to be more than 65 a signal is given.
Because this is a continuous query (in CQL) the query is handle a bit different by BAM. The CQL queries are stored and monitored separately. The SQL statement is extended with a ReportingCache destination.
A continuous query can initiate an alert action. As an alternative, you can configure an alert to watch a continuous query as its triggering event. I will describe this alternative as part of the BAM Alerts post.
And finally the group query ‘Open And Closed Tasks Without Timing’ (used for requirement 6, 8 and 11). I will use this query to make a KPI visible (BAM KPIs).
In the next post I will do the KPIs.
You can download the Queries and the other related objects here.