# Towards a Domain-Specific Modeling Language for Extracting Event Logs from ERP Systems
## 5 Results
### Step 2--Defining SQL Query Patterns
#### 5.3.3 Select Transaction Instances  
Next, the transaction metaclass, which describes the event type and timestamp, is also transformed into a SELECT statement. Each transaction type has a reference to only one artifact type according to the association relationship of the metamodel. Thus, the SELECT query has answer variables, i.e., identifiers of a related artifact that denote occurrences of transaction instances, timestamp, and identifiers of the main artifact, which establish a relation between transaction instance and process instance, i.e., the case it belongs to. The FROM clause indicates which direct or indirect relation will be used in the join to select only transactions which belong to the main artifact instances. The type-level relation between two artifacts is set up in the DSML model. For example, the purchase debit indirect relation is specified and then used to query the data concerning book invoice transactions. We use the placeholder \"<<... >>\" for particular parts of the pattern which will contain the results of a subquery. The placeholder \"<<relation join>>\" (line 4 in Listing 9) subsumes the results of the query pattern for a type-level relation such as the one shown in Listing 8. Then, the resulting table needs to be joined with the table of the artifact to which the transaction is related, to get the timestamp of events. Additional conditions can be expressed in the where clause to distinguish transaction types or to filter the results. For example, the user can define an aggregation function to obtain only the first or last recorded transaction for the analysis. The transaction instances can be retrieved using the following query. ```
1:SELECTTransaction.ArtifactTypeRef.FieldName_IDAS[event_id],
2:Transaction.FieldName_timestampAS[event_timestamp],
3:R.mainArtifact.FieldName_IDAS[trace_ID]
4:FROM<relation join>> AS R
5:INNERJIONTransaction.ArtifactTypeRef.TableName AS REF
6:ON R.ArtifactTypeRef.FieldName_ID=
7:REF.Transaction.ArtifactTypeRef.FieldName_ID
8:WHERETransaction.conditon;
```  
**Listing 9** SQL query pattern that selects transaction instances. The number of attributes and tables used in the log extraction step depends on the elements and properties of the DSML conceptual model. To create a high-level overview of the procurement process, our domain experts suggest we consider the four relevant transaction types for process analysis (see Figure 5). Therefore, the four database tables were used in the log extraction step for this case study. The Purchase Header, Purch_Rcpt_Header, Purch_Inv_Header, and Change Log Entry database tables were used. The user is able to determine the additional constraints on which instance to consider within the conceptual model. For our analysis, we considered data recorded between 15 \begin{table}
\begin{tabular}{c c} \hline \hline
**Purchase Order (Document Number)** & **Purchase Receipt (Document Number)** \\ \hline PO1 & R1 \\ PO2 & R2 \\ PO1 & R3 \\ PO3 & R4 \\ PO3 & R5 \\ PO4 & R7 \\ PO5 & R8 \\ PO6 & R9 \\ PO7 & R6 \\ PO8 & R10 \\ PO10 & R11 \\ PO11 & R12 \\ PO12 & R13 \\ \hline \hline \end{tabular}
\end{table} 
Table 3: Excerpt of the purchase item direct relation instances. 06-2017 and 15-10-2017. The boundary date values for obtaining all event data in a certain timeframe are mapped from the timestamp attributes of the artifact type log metaclass. In total, 31 cases were extracted from the real data of ERP system for the main artifact Purchase Order. A fragment of the extracted event log is given in Table 4. Each line corresponds to an event.