Creating Queries within SAP Signavio Process Intelligence

Objective

After completing this lesson, you will be able to create your individualized analysis

Creating Basic Queries

Now that we have understood the SiGNAL syntax, we want to learn how we can code within SAP Signavio Process Intelligence.

Watch the following video to see how to create a widget with a basic query using SiGNAL.

Now, let's get into our questions. We want to use SiGNAL to obtain results on our orders and customers. Continue below to review the questions.

What's the total order amount of all cases?
What's the average order amount from standard customers?
Which City has the Most Orders, split by Type of Goods? Show it in a stacked bar chart.

SiGNAL Expressions

Expression	Definition	Example
Arithmetic	With the arithmetic operators, you can execute mathematical operations with numeric values.	Add, Subtract, Multiply, Divide, Modulo SELECT discount / price * 100 FROM THIS_PROCESS
Comparison	Comparison operators can be used in filters to determine which records to select.	Less than, Greater than, Less than or equal to, Greater than or equal to, Equal to, Not equal to
Logical	A logical expression connects Boolean expressions using logical operators and can be evaluated to return a true or false value.	AND:TRUE if both conditions are true IN:TRUE if the list on the rights side contains the value on the left side NOT: Negates a boolean expression OR:TRUE if at least one of the two conditions is true
Conditional	Conditional expressions return values dependent on the evaluation of Boolean conditions.	SELECT IF(condition, then, else) or SELECT IF(country='DE', 1.19, 1.0) * price
Literal	A literal is a fixed value of a certain type. SIGNAL supports literals of several different types.	Boolean Literal Date and Timestamp Literal Duration Literal Number Literal String Literal

Note

More information about Expressions on SAP Signavio User Guide:

SiGNAL - Order By and Limit Clauses

The Order By function helps you sort the result set based on specific criteria, allowing you to organize data in a meaningful way. The Limit function enables you to control the number of records displayed in the result set, ensuring you focus only on the most relevant data.

Screenshot showing a sample for Order by and Limit functions.

SiGNAL - Alias

Aliases help you assign temporary names to columns in your result set, making them easier to read and understand. This is especially useful when applying aggregate functions in a query.

Without an alias, the system automatically generates a name based on the column and the operations used in the expression. By using aliases, you can create clear and meaningful column headings, improving the readability of your query results.

Screenshot showing sample aliases Customer Id and Order Amount

Subquery

A subquery is a query that is nested inside a SELECT statement, or inside another sub query. You can use a sub query anywhere an expression is allowed to retrieve data on event-level.

They differ in terms of the level at which they operate and the type of data they provide.

General subqueries operate at case level and return tables.
Event-level subqueries operate at event level and return scalar values.

In a table, case attributes are shown under the following heading: case_id, order amount, and city. Event attributes are shown under the following headings: event_name, end_time, system user.

Note

To learn more about the subqueries, visit the SAP Signavio User Guide

DATE_TRUNC

DATE_TRUNC can be used to limit timestamps to specific units of time by removing detailed information. This simplifies timestamps to broader time units such as: year, quarter, month, weekday, hour, minute, second, or millisecond.

NOW()

NOW() helps calculate the duration between the current time and a given timestamp.

DURATION

DUATION allows you to evaluate whether a duration meets or exceeds a specified threshold. Supports time-based string formats like: '3weeks', '4days', '3hours', '5minutes', '6seconds', and '7milliseconds'.

Creating Advanced Queries

It's time to build on your understanding of basic queries and take it to the next level with advanced queries. We'll explore new types of clauses and operators, and by the end, you'll apply what you've learned through practical exercises.

SiGNAL: FILTER Clause

FILTER (WHERE) allows you to filter an input for an aggregation function.

Use case: Filter a subset of cases or values to compare with a total.

SELECT expressions, FILTER (WHERE conditions), FROM table/process.

SiGNAL: Matching Operators

Matches allow you to filter cases based on a specific task sequence, indicated by the term MATCHES. SiGNAL supports the following matching operators:

Matching operator	Description
A → B	"A" directly followed by "B" (can be omitted)
A ~ B	"A" indirectly or directly followed by "B"
^ A	Sequence starts with "A" in any specific case
A NULL	"A" vaule is NULL
NOT A	All values that are not "A"
A NOT B	"A" directly followed by a value that is not "B"
A NOT (B\|C)	"A" followed by a value that is not "B" or "C"
(A\|B)	"A" or "B"
B $	Sequence ends with "B" in any specific case
A ANY	"A" occurs anywhere in the case

Example

SiGNAL: BEHAVIOR MATCHES Operator

The BEHAVIOUR MATCHES operator allows to filter MATCHES even further, with expressions not related to task sequence. If you want to match only cases where a specified standard sales process is followed, then using MATCHES suffices. For example, let's say a standard sales process includes the events "Receive Customer Order", "Receive Payment", and "Ship Goods Standard" strictly in that order and with no other events preceding or following.

BEHAVIOR MATCHES extends this concept by allowing the inclusion of extra filtering criteria. For example, in addition to matching cases that follow the standard process, we might also want to include only cases where the order was shipped after a specific time, even if the earlier ones followed the standard process. BEHAVIOR MATCHES allows us to do so.

Next lesson