Create Additional Entry to Button Dropdown

 Posted on 2021-07-31  |  3 minutes  |  500 words  |  Kazuhiko Takata

I looked at the Celopeers post that had issue when variable input is blank (NULL) then PQL using this variable had error. I already used work around below to skip FILTER execution if variable is null. But I also felt troublesome in two points. First is this is not officially documented so myself need to instruct to my colleagues. Second is more important, I would like to unfilter this selection if variable is not set, but there was no way to do it. [Read More]
PQL  Process Analytics  Studio  Analysis  Process Explorer 

Categorize and Name Activity

 Posted on 2021-07-24  |  3 minutes  |  471 words  |  Kazuhiko Takata

In the previous post Transform Source System Tables to Minimize Data Model Tables, I recommended to convert some kind of source system tables to Activity. Today I focus on Activity and would like to share my way how to categorize and name Activity. First point is to split Activity name to two parts, more general part and detail part. For example, in SAP ECC or S4HANA Order to Cash process, general Activity name is Create Sales Order when data committed in VA01 transaction. [Read More]
Data Model  Data Integration  Transformation  PQL  Process Analytics  Studio  Analysis  Pull Up Function  Process Explorer 

Utilize N-M relationship between Activity and Dimension Tables

 Posted on 2021-07-10  |  2 minutes  |  313 words  |  Kazuhiko Takata

When you determine data model structure by yourself, basically you should follow snowflake schema writtern in Understand how Tables are joined in Data Model. From case table perspective, n side is case table, and 1 side is another dimension table. On the other hand, activity table is the first case that 1 side is case table. If you determine second dimension table that 1 side is case table, please note that you can not use both activity and that dimension table at once because those tables are N-M relationship via case table. [Read More]
PQL  Process Analytics  Studio  Analysis  Data Model 

Maintain Saved Formulas effectively

 Posted on 2021-07-03  |  3 minutes  |  485 words  |  Kazuhiko Takata

In the last post Handle NULL efficiently in Aggregation Function, I used saved formulas to split long and complex PQL to reusable components. Today I would like to share my best practice to use saved formulas. For example I would like to ananlyze throughput time between arbitrary two activities. As below screenshot I created three dropdown buttons, switching time unit (sec, min, hour, day etc.) and two activities (from / to). [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function 

Handle NULL efficiently in Aggregation Function

 Posted on 2021-06-26  |  3 minutes  |  552 words  |  Kazuhiko Takata

I looked at many PQLs that can be simplified if they know about NULL handling well. Today I would like to tell how to handle NULL efficiently in Aggregation Functions (COUNT,SUM,AVG etc.). Today I would like to use O2C process to explain my case. I determined KPI Send Invoice within a day after Ship Goods, because sales company will Ship Goods then should Send Invoice immediately. Same as previous posts, first I would like to create OLAP table to look into the cases. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function 

Understand how Tables are joined in Data Model

 Posted on 2021-06-19  |  3 minutes  |  566 words  |  Kazuhiko Takata

In the Understand Difference between Dimension and KPI post, I mentioned that Columns used as dimensions and KPIs are inplicitly joined based on data model, but I did not mention how to join tables in data model. Today I would like to say about it. Celonis Data Model support snowflake schema that consists of multiple tables, each table pair join as 1:N relationship. Normally activity table is top of N side and dimension tables including case table are bottom of 1 side as pyramid hierarchy. [Read More]
PQL  Process Analytics  Studio  Analysis  Data Model 

Calculate Multi Dimensional KPIs

 Posted on 2021-06-12  |  3 minutes  |  441 words  |  Kazuhiko Takata

In the last post Recognize Record to Calculate KPI, I showed issue of count duplication when I merged two different dimentional KPIs. Today I will tell how to calculate it correctly. I will start from changing OLAP table to hide USER_TYPE and ACTIVITY_EN for calculating KPIs grouped by customer master. And I filtered by customer K1 as previous post. Finally I added SUM function to both Rework Time and Reminder Time. [Read More]
PQL  Process Analytics  Studio  Analysis 

Recognize Record to Calculate KPI

 Posted on 2021-06-05  |  3 minutes  |  463 words  |  Kazuhiko Takata

Using Pull up function, you can calculate various kind of KPIs. I would like to tell in this post is taking care the record (dimensions) to calculate each KPI especially when you unite multiple KPIs to same component. In this example, I will use Order to Cash process again and first I would like to estiamate time of rework. If rework Activity is operated by manual user, this rework is estimated 1 minute for example. [Read More]
PQL  Process Analytics  Studio  Analysis 

Use Pull up function as Subquery

 Posted on 2021-05-29  |  2 minutes  |  420 words  |  Kazuhiko Takata

In the last post Determine First Time Right by Pull up function, I broke down PQL of FTR step by step. Today I will explain more complex KPI in similar way. By the way you may know Subquery that enables to pass result of SQL to parts of another SQL. PQL can also do similar things by Pull up function. In this post, for example I would like to calculate Average rework count after Delivery in Order to Cash process, and definition of rework is same as previous post. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function 

Determine First Time Right by Pull up function

 Posted on 2021-05-22  |  2 minutes  |  337 words  |  Kazuhiko Takata

As a first example of Pull up function, I will show you First Time Right (FTR) that means process without rework activities (link). To determine whether each case is FTR or not, first I should determine rework activities, then judge if each case has rework activities or not. In this example I determined that rework activity name is started from Change. PQL to find such a string pattern is Change %, using wildcard % after Change. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function