A Query Language for Workflow Logs

This article makes the following technical and scientific contributions:

This article is most related to the work on querying workflow logs using FPSPARQL [3, 4]. There are several areas that FPSPARQL and our work differ. Although both can express queries on workflow logs, the input for FPSPARQL queries are graphs (pre-constructed from logs) while ours are sequences of (native) log records. Also, queries in this article finds all workflow instances satisfying specified conditions, FPSPARQL queries formulate properties without focusing on individual instances. For temporal conditions, FPSPARQL uses regular expressions, while ours uses BPMN constructs. In terms of language style, both are declarative, but FPSPARQL is based on SPARQL while ours is based on SQL. While FPSPARQL relies on SPARQL, this article adapts the relational query optimization framework with a new cost model. The cost model faithfully reflects the evaluation cost (subject to the modeling accuracy) and allows for other potential optimization means. For example, it was used to guide the development of an incremental evaluation of log queries.¹ Finally, the performance study [3] aimed at understanding usability in terms of query evaluation time; this article attempts to an understanding of effectiveness of the query optimization techniques including the new cost model.

This article is also related to References [16, 22, 28, 29, 36]. [36] also queries a pre-converted graph from the log. Requiring preprocessing of workflow logs is double wedged sword: It could be potentially very powerful if a rich set of relationships are represented in the graph; however, queries are restricted to only the extracted relationships and thus limits the usefulness.

In contrast, the logs in query frameworks reported in References [16, 22, 28, 29] and this article are in the native format (relational or noSQL), which makes it immediately and widely applicable in workflow analytics applications. However, a key omission in References [16, 22, 28, 29] is the limited ability to formulate constraints on data being manipulated by workflow (while they focus exclusively on temporal relationships between activities). They may be useful for, e.g., workflow model discovery, but not clearly as effective for log exploration. Reference [22] uses linear temporal logic to express such properties; Interestingly, queries in Reference [16] may involve aggregate functions, along with activity type, timestamps, and so on, along with temporal “precedence” relations. Reference [29] (and full version [28]) on which this article is based uses BPM gateways the key constructs to express temporal relations, aiming at ease of use by workflow modelers who must have already familiar with BPMN. This article significantly extends References [28, 29] by (1) allowing constraints on data associated with activities in the log, (2) an algebra and an evaluation algorithm based on the algebra, (3) the IQL language, (4) a cost model and optimization techniques, and (5) an experimental evaluation.

This article is also related to complex event processing [1, 2, 10, 19]. Search processing language SPL [8] is designed for the log management system Splunk, focussing on big data searching, filtering, and analysis, with basic operations like sorting, filtering, insertion, and deletion. In comparison, IQL in this article focuses more on complex temporal relationships between log records.

Also related are Online Analytic Processing, temporal relationships, and relational query optimization studied in the database community. The conventional framework for workflow analysis extracts data from various databases storing fragments of workflow log using ELT and loads data into a data warehouse for analysis [15, 35]. ETL aims at specific types of analysis queries centered around summaries over data cubes [5, 11, 12], it depends heavily on data selection/filtering. Composing queries then requires detailed knowledge of the underlying workflows’ data structures and imposes a steep learning curve on the end users of the analytical process (e.g., project managers).

Query conditions often involve activities performed during workflow execution and the effects left by the activities, temporal relationships between two or more activities. Existing temporal data models and query languages [13, 26] focus on data with temporal aspects and temporal relationships between data values or rows in tables. Recent developments such as SPARQL-ST [20] also focus on RDF data elements and their temporal relationships. Languages to specify temporal properties for data-centric workflows combine first-order logic and temporal logic [7, 14], they state directly properties of data changes over time and are immediately usable for workflow analytics.

SQL database query optimization was studied extensively [25]. This article essentially adapts the cost-based optimization method to process centric query language. Although the optimization approach is similar, cost model is new and the experimental evaluation shows that there is a “new life” for the old relational optimization techniques, specifically for temporal operators.

The recent work [24] uses SQL constructs to inductively construct process models based on a set of source models. Although it is slightly related as far as research problems and methodology are concerned, an appealing problem could be to integrate (elements of) IQL in their model construction process, using the process instances in a log as source models.

This article is organized as follows. We introduce key notions of workflow log, “incident pattern,” and “incident instance” in Section 2, and IQL in Section 3. We present the query evaluation algorithm in Section 4 and examine in Section 5 operator properties, derive the cost model, and discuss query optimization. We report experimental evaluation in Section 6; we conclude the article in Section 7.

As query evaluation is based on operator evaluation methods, we first discuss the evaluations for the four operators. Due to the differences of notions between this article and Reference [28], the evaluation algorithms in this section are adapted and extended from the ones presented there. We summarize the complexity of evaluating each operator in Lemma 4.1.

Note that operators consecutive, sequential and parallel have the same complexity and result size bound in the worst case (first two items of Lemma 4.1), but usually the consecutive operator is cheaper than sequential operator, and sequential operator is cheaper than parallel operator. This is naturally reflected by the strictness of temporal constraint of the three operators. The consecutive operator has the most severe restrictions, though the worst case is when all incidents for the right operand are consecutive with (happen right after) all incidents for the left. An algorithm for the sequential operator can be optimized to stop earlier when the constraint does not satisfy and there is no need to look into the later part of the input. For the exclusive operator, there may be incidents that satisfy both and . The algorithms in this article postpone deduplication till a later step of extracting query answers.

The evaluation algorithm presented in this article extends our earlier algorithm [28] in two aspects. First, due to the fact that incident patterns in the earlier algebra [28] do not contain constraints on data values, the evaluation algorithm in Reference [28] only maintain and manipulate i-assignments (i.e., activity timestamps). The algorithm in this article deals with both i-assignments and a-assignments (for data values) simultaneously. Consequently, the algorithm adds steps that checks data constraints to only keep the a-assignments (and coupled i-assignments) only when the data constraints are satisfied. Second, the evaluation (algorithm) in this article does not produce incidents directly, and an additional step is used to generate every incident for a pattern if there is an exclusive operator evolved. Because i-assignment requires all variables in a pattern to be mapped to a log sequence number, the algorithm for exclusive operator won’t produce a complete incident for a pattern (only one branch is chosen and the other is dropped). Consider the following queries as an example, “which students get 2,000 balance after they get a referral or update a referral, and see a doctor afterwards?” A pattern expressing this query is : GetRefer: UpdateReferbalance: SeeDoctor. An incident, according to Figure 2, is where and . However, the algorithm for exclusive operator does not generate a full assignment. It just produces part of the assignment without assigning a log sequence number to Y, because if X is chosen as the execution branch, then Y is dropped. Also, evaluation in reference [28] is simpler, since patterns do not involve data.

Because the result generated by an operator evaluation algorithm may not be a complete incident, we call the result generated “witness assignment.” A witness assignment is a subset of an assignment that satisfies the temporal relationship and conditions defined in a pattern. Correspondingly, the complement set of the assignment is the mappings dropped when we choose a branch at an exclusive operator. In Section 4.2, we discuss how to generate an assignment from a witness assignment.

Given an IQL query and a log, our evaluation framework consists of the following four steps. The first step parses the pattern in WHERE clause and turns the pattern into an “incident tree.” An incident tree represents an evaluation plan. In the second step, the incident tree produced in Step 1 is converted into an “optimal” one. Then the third step uses a bottom-up evaluation strategy to generate all incidents to the pattern using the log. Finally, the last step extracts the query answer from every incident.

With the use of witness assignments, the query evaluation steps can be reformulated as follows. In the third step, we generate all the witness assignments using a bottom-up strategy. In the final step, all incidents are constructed based on witness assignments, and query answers are extracted from every incident. For example, in the pattern described in the above section, the two witness assignments, based on the log example in Figure 2, are and . To generate incidents, for variables that do not occur in witness assignments, map them to available values. Here are the corresponding incidents for , and .

Before introducing our query evaluation framework, we define the notion of an incident tree.

Definition. An incident tree is a binary tree with three types of nodes, namely operator, condition, and activity, that satisfies all of the following:

The first step of the evaluation framework is to convert a query into an incident tree. In an incident tree, every subtree corresponds to a pattern. In Step 3 of evaluating an incident tree, we use a bottom-up strategy, i.e., leaf nodes are processed first and the root is evaluated last. More specifically, we process nodes according to post-order traversal of the incident tree. The witness assignments for each subtree are generated and passed to its parent. The parent node then filters the witness assignments from its child nodes based on its node type and label. If the current node is an operator node, depending on the label of this node, then the corresponding algorithm (omitted in the article) is used to generate witness assignments. This process repeats and ends at the root node. At the last step, for a set of witness assignments, every variable that is not in this set but is in the pattern is assigned a value. If the new mapping satisfies the definition of candidate assignment, then it is then an incident. Finally, query results are assembled from the final set of incidents according to the SELECT clause.

The above (expression complexity) is not surprising. The average size of assignments generated for each activity is . By Lemma 4.1, the operator and have the largest result size among the four operators. Especially in the case that all the log records are from the same workflow instance, then the operator has the result size exactly. For a pattern with all operators are , the size is , which is exponential on n. For the other two operators and , the result size is quadratic, respectively. With the data conditions, the parallel operator is still the most expensive.

An initial step for optimization is to examine properties concerning incident operators, including commutativity, associativity, distributivity on operator nodes and other properties on condition nodes. Such properties allows equivalent expressions to be considered for optimization plans. Some of the results concerning incidents presented in below extend from the results reported in References [28, 29]. However, patterns in References [28, 29] contain no data nor data conditions while the results provided in this section have data conditions. The “equivalence” () notion is naturally extended from the dataless one [28].

Consider operator “” as an example when are atomic, i.e., single activities . Assume and , where are LSN’s. If , then we have , , and . According to the definition of i-assignment, it follows that .

Proof of Theorem 5.3: We sketch the proof of operator “” in the following, proofs for other operators are similar and thus omitted. The goal is to prove that given arbitrary patterns and a log L, for each i-assignment and each a-assignment , iff .

Suppose . Then and . (Conditions may occur in .)

and

Based on and , .

The main idea of the above proof is to reason the temporal relationship () between the incidents. The proof of the other direction is essentially the same.

The following example illustrates that data conditions may move across expression boundaries (but not across activities) while maintaining equivalence.

From the above theorem, (1) it does not matter which order we take to evaluate if the two operators are either consecutive or sequential, (2) distributivity rules permits alternative expressions that may have lower cost on evaluation. and (3) the conditions can always be pushed through into the lowest level if the operators are one type of consecutive, sequential, or exclusive.

We omit the detail proofs here but note that most of the proofs are similar to the ones in Reference [28] except the following. Item 3 of Theorem 5.5 holds because of the following. Given three patterns , and , for each i-assignment and each a-assignment , , , and .

In the next subsection we introduce a cost model to estimate the evaluation cost of a pattern to serve as the basis for query optimization.

Although IQL uses process modeling constructs in formulating query conditions on log record sequences, there is a close resemblance between IQL query evaluation and SQL query evaluation. Naturally, we study IQL optimization using a cost-based method (similarly to SQL [25]). A core of this approach is to develop an accurate cost model.

Based on Lemma 4.1, the cost of each operator depends on the cardinality of incidents from its operands and their temporal relationship. To estimate the cost of each operand, we develop a cost model with three sets of parameters as shown in Figure 5. For each activity a, is the total number of log records reflecting the execution of activity a in a log. It captures cardinality of atomic incidents. As directly reflects the order of log records, we use another set of parameters to capture the temporal relationship based on is-lsn. For each activity a and a natural number t, is the number of log records with reflecting activity executions of a in a log L, i.e., the number of activity a executing as the tth activity in a workflow instance. For example, based on the hospital referral example, because GetRefer occurs as the 2nd activity in all three workflow instances. The last set of parameters is to reflect the instance information of each activity, because we are interest in incident within one workflow instance. For each activty a, is a set of wids. The wids are the ids of instances where a occurs.

By accumulating with all numbers t, we get a histogram showing number of log records of activity a occurring as the tth activity over all the workflow instances. From this histogram, we can easily get the distribution of where an activity occurs within a workflow instance. For example, in the hospital referral example, GetRefer always starts as one of the early activities, so it always has small is-lsn. However, GetReimburse may probably starts with much larger is-lsn. These histograms of all activities are collected before any query arrives and are used in our optimizer for estimation.

In the following, we outline how we estimate the cardinality of incidents of a pattern. For now, we only consider the case where all log records within a log are from one workflow instance.

First, we introduce some notations to represent the properties of incidents that we would like to use to help estimation. We assume each incident is a random event, and each of the following notation is a random variable. (1) , number of incidents of pattern e in the log L. (2) , begin of incident with pattern e, (3) , duration of incident with pattern e. Here, we also assume (2) and (3) are independent from each other, i.e., when an incident happens is independent from how long it lasts. And every atomic incident is independent from each other, i.e., when an activity happens is independent from when other activities happen. If we can estimate the distribution of (2) and (3), then we can estimate (1) by aggregation of (2) (or (3)) on t. Then we can use (1) to estimate the computation cost of an incident operator. For this purpose, these parameters are first estimated for atomic incidents (leaf level in an incident tree) and are then propagated to complex incidents. Finally, the size of a query answer and the total cost of evaluating a query can be estimated using a bottom-up traversal of an incident tree. We now discuss how to estimate (1), (2), and (3) for each incident.

To propagate the estimations to the root of an incident tree, we need to provide an estimation method for each type of nodes, activity node, operator node and condition node. For condition node, we use a fixed rate to filter the candidates here. Practically, this rate can be learned and tuned using machine learning techniques. For the remaining two cases, we start from activity node.

For an atomic pattern a, we use maximum likelihood estimation to estimate the distribution of its begin and duration. That is, use our observation from the current log to estimate the behavior of activity a on start. Therefore,We assume the duration for each activity is 1, then

For an operator node with label , the corresponding pattern is . The start can be estimated in the following:By definition of , is equivalent to ( has log sequence number t), ( has duration ), ( happens immediately after finishes). This leads to the first step in Equation (1). At the second step, by our assumption, begin and duration are independent. Here we further assume is independent from . This holds if and have no overlaps on activity names. For simplicity, we still treat and to be independent when they have overlap. Note that each of the terms , , is already estimated for and . If (or ) is atomic, then the estimation is done at the leaf node. Otherwise, the estimation is propagated from child nodes. Thus, is estimated. Similarly, we can estimate the duration of by the following:

The cardinality of is then estimated aswhere . Here represents the likelihood of a pair of and satisfying the definition of . For the remaining case of incident operators, the idea is similar. The only difference is the condition that represents the different temporal relationship between two incidents using start and duration. We give some explanations here and details are skipped. For sequential operator, the condition should satisfy that happens after , the start of is the start of , and the duration of is from the start of to the end of ; for exclusive operator, there is no temporal constraint on and , and the start and duration of are either the ones of or that of ; for parallel operator, there is no temporal constraint, the start of is the minimum of the start of and the start of , and the duration of is the duration from the minimum start to the maximum end.

We now extend the estimation for cases where there are multiple workflow instances. Recall that in the definition of incidents, we also have the constraints that all the log records should have the same . To extend the above estimation, we add a factor that represents the constraint on . Given two pattern , and their incidents respectively, represents the probability of any two incidents from and respectively having the same . To estimate this probability, we assume that the incidents of a pattern are uniformly distributed over all the instances where the incidents occur, for example, if a pattern e has 10 incidents () and all the incidents occur within workflow instance with and . Then 5 incidents of e occur in the instance with and 5 other incidents of e occur in the instance with . Then we add a new property to each incident. For a pattern e, is a set of ’s. The ’s are ids of instances where the incidents of e occur. If e is an atomic pattern, then is collected as the parameter in Figure 5. If e is , where , then is estimated as the conjunction of and . Then can be estimated by . The corresponding extension to the estimation of begin and duration of e are as follows:

Once we obtain the estimation of incident parameters, the next step is to estimate the cost of each operator. By extending Lemma 4.1, we define the following. Notice that not like other operators, the cost estimation for consecutive operator is not consistent with Lemma 4.1. The reason is the complexity in the lemma is the worst case but it is not a common case. We assume that the incidents for and have random starts and ends, then the complexity is close to linear time (one scan through each set). To provide a more precise estimation for a common case, we use instead of for consecutive operator.

Definition. Given (incident) patterns and with estimated cardinality of incident (instances) and , the cost of a pattern e is

To estimate the cost of an incident tree, we use our cost model to evaluate the cost based on a post-order traversal. At each node, we consider the current node type and apply the cost estimation methods defined at the end of Section 5.2, finally the cost will be accumulated at the root. The details are shown in Algorithm 1.

To enumerate the rewriting space of a pattern, we traverse its incident tree from the root. At each node, we enumerate all the rules introduced in Section 5.1 to generate new incident trees. New incident trees are added to frontier and will be processed later to generate more incident tree candidates. Among these trees, we pick the one with least cost as the optimal. The details are provided as Algorithm 2.

We conjecture that enumeration of all rewritings to a query is NP-hard. We analyze the properties of incident operators and build the foundation of our conjecture. Theorem 5.3 means that, for a query with only operator types and , we can execute the operators in any order to obtain the (identical) results. For a query with n such operators, there are different incident trees. So the search space is . Obviously, the search space grows when and are involved. Because the order of the operands can be changed based on Theorem 5.1.

To derive the “optimal” evaluation plan for a query, we provide some heuristics here to help avoid searching the whole space of the problem. In the following, we go over the rules introduced in Section 5.1, and discuss what heuristics to apply when one of the rules can be applied. Let be arbitrary patterns, for the incident rule in

To make the optimizer more efficient, during the search process, when an incident rule can be applied, we use the above corresponding heuristics to determine if we should stop expanding on the current candidate.

In this section, we discuss several factors affecting query evaluation cost: the number of the log records for an activity, the operator type, and the number of operators. We conducted three experiments regarding each of the factors one by one.

The first experiment is conducted by evaluating query type T1 with varying numbers of log records for that activity. In Figure 7, the x-axis denotes the number of log records, and the y-axis means the evaluation time for the query. Figure 7 shows the evaluation time of the single activity query increase linearly in the number of log records. The linearity is expected, because a linear scan is used on the log to collect the log records for an atomic pattern.

The second focus is on incident operator types. Figure 8 shows the evaluation time on five datasets of queries with one operator and two activities (T2), respectively. We randomly generated 10 queries for each operator type. The average evaluation time is shown as the y-axis.

From the performance on each dataset, we can tell that consecutive operator has the best performance and parallel operator has the worst performance, which is consistent of Lemma 4.1. Sequential operator has better performance than parallel operator. On most datasets, consecutive operator has better performance than exclusive operator.

In Lemma 4.1, sequential operator has the same complexity as parallel operator. But the former has much better performance in Figure 8. This is because there are expected pruning based on time constraints in sequential operator. But in parallel operator, there is no constraint on log sequence number. The performance gap between consecutive operator and exclusive operator differs in different datasets, because the running time is dependent on the size of operands. We suspect this advantage will be enlarged for more complex queries, since there would be much less intermediate results generated for consecutive operator.

Finally, we study how number of operators affects the evaluation performance. We randomly generate the operands for each query and 10 queries for each operator type. The 10 queries are evaluated on dataset 2007 for 10 times, respectively. The average of the running times are shown in Figure 9.

Figure 9 shows that the running times of the three operators, consecutive, sequential, and exclusive, increases slowly as the number of operators grows. However, the parallel operator has exponentially growing running time based on the number of operators.

This observation is reasonable, because the operators consecutive and sequential have constraints on log sequence number, and exclusive operator has linear evaluation complexity, but the parallel operator does not filter candidates and has quadratic evaluation complexity. The exponential increase demonstrates Theorem 4.4.

When a query becomes complex, there is a big difference between different evaluation plans in terms of actual cost. In this section, we discuss the accuracy of cost estimation.

We evaluate the cost estimation accuracy on different query evaluation plans on the 2007 dataset. (The results for other datasets are quite similar.) Figure 10 shows the comparison between estimate cost and actual cost measured by normalized complexity. To show the general trend comparison between estimate and actual costs, we randomly generate four queries. Figure 10(a), (b), and (c) show the costs of evaluation plans for a query with five operators, respectively, the query used in Figure 10(d) has six operators. For each query, the evaluation plans are evenly placed on the x-axis ordered by the real cost incrementally.

Overall, Figure 10 shows that estimate costs have similar trend with actual costs in all queries studied. In Figure 10(a), estimates and actual costs overlap with tiny differences (the estimate cost is higher in the tail, i.e., when the actual cost is high). In Figure 10(b), both costs have the same general trend, though estimate cost starts lower than real cost. Figure 10(c) shows few places where estimate costs are lower than the actual ones. Figure 10(d) shows a gap between estimate and real costs at the tail. Note that there are significant jumps in the plan costs; this is due to 1 or 2 operator(s) whose placement(s) in the plan is(are) significant in affecting the overall plan cost.

The error margin of cost estimation is less desirable. However the error margin does not seem to reduce the effectiveness of our query optimization, because it can always choose the best evaluation plan. We discuss the effectiveness of query optimization in the next subsection. Overall, although the cost estimation is not accurate for some evaluation plans, the cost estimation has the same trend as real cost. It means that the best plan with lowest actual cost would probably have lowest estimate cost. Thus the optimizer can utilize the estimate cost to select optimal evaluation plans.

We also conducted experiments to evaluate optimizer performance. We evaluated the optimizer on two aspects. First, we study how likely we can get a best plan with the optimizer. Then we show the impact of our optimizer on reducing the evaluation time for a query.

In our experiments, we randomly generated 150 queries with three, four, and five operators, respectively. We then used performance tolerance to loosen the constraint on “best” evaluation plan. Here, performance tolerance is defined as the ratio . If a query evaluation plan has an actual cost that is within performance tolerance, then we consider it as a best evaluation plan. For example, if performance tolerance is set to 0.1 and the lowest real cost is 10.0, then a query evaluation plan with real cost 10.5 is considered as a best evaluation plan. The optimizer accuracy is measured by the percentage getting the best evaluation plan with different performance tolerance for 150 queries with different number of operators. Figure 11 shows the results.

From Figure 11, given a performance tolerance, the accuracy for queries with three operators is the highest, and the accuracy for queries with 5 operators is the lowest. When the performance tolerance increases, the accuracy also increases. Note that when performance tolerance is 0, the average accuracy is 0.72; when performance tolerance is 0.01, the average accuracy reaches 0.88; when performance tolerance reaches 0.5, the accuracy rises to 0.93.

The accuracy for queries with lower number of operators has higher accuracy. This is because with more operators, the incident tree gets deeper and the estimation error propagates more. Also, Figure 11 suggests that setting performance tolerance to 0.2 would yield a high accuracy on average.

Finally, we analyze the effectiveness of optimizer on reducing query evaluation time. Figure 12 shows the box plot of reduced evaluation time for queries with different number of operators. We randomly generate 20 queries for each number of operators on trauma patient dataset 2008. Each query is evaluated 10 times without and with optimization, and the average evaluation time is treated as the evaluation time of a query. The effect of optimizer is evaluated with reduced percentage of evaluation time on each query, defined as the ratio with each box for the 20 queries with corresponding number of operators.

Figure 12 shows up to evaluation time reduction as a result of optimization. The largest percentage is with the queries of four operators. And on average we have around evaluation time reduced.