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 

Understand mechanism of Pull up function

 Posted on 2021-05-15  |  2 minutes  |  349 words  |  Kazuhiko Takata

Pull up aggregation function, called PU function, like PU_COUNT, PU_SUM, PU_AVG, PU_MAX, PU_MIN looks similar to Standard aggregation function like COUNT, SUM, AVG, MAX, MIN. In Celonis, it is important to understand that output of PU function is not KPI but dimension. You can see Understand Difference between Dimension and KPI. If you know about SQL, you can imagine that PU function is similar to window function in SQL. As other functions, PU functions add dynamic column in grouping table. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function 

Customize Process Explorer

 Posted on 2021-05-08  |  3 minutes  |  477 words  |  Kazuhiko Takata

Process explorer in Celonis Analysis is default application to analyze process. First you can see main process pattern (called happy path), count of each step (activity) and count of path between two steps (connection). You can show minor activities and connections, or switch throughput time instead of count of connection. I think there are two ways to customize Process explorer. Change activity name Add more KPI against activity / connection Change activity name If you want to add detail information to each activity, you can change activity dimension. [Read More]
PQL  Process Analytics  Studio  Analysis  Process Explorer 

Understand Difference between Dimension and KPI

 Posted on 2021-05-01  |  3 minutes  |  476 words  |  Kazuhiko Takata

If you are beginner of creating Celonis Analysis, I believe you will first create OLAP table to understand how Celonis PQL works with data model. Points are, Dimensions are grouping key to calculate KPI, normally they are string (character) columns KPIs are calculated figures based on specific numeric columns, or count of rows of any columns, it must be numeric value in either cases Columns used as dimensions and KPIs are inplicitly joined based on data model Below screenshot is the quite simple example to show Dimension, KPI, and data model. [Read More]
PQL  Process Analytics  Studio  Analysis