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. Dimension is vendor code LFA1.LIFNR that is group key to calculate KPI COUNT(EKKO.MANDT) that is count of rows of Purchase order table (MANDT can be changed to any columns in EKKO). I just set up columns in different tables then you can see two tables (vendor master LFA1 and purchase order EKKO) are joined automatically.
Next screenshot is the proof of count KPI. I hide KPI then add one more dimension, purchase order number EKKO.EBELN. You can see first vendor code 0000000002 that has 4 rows of different purchase order number. Dimension is grouping key of KPI so 0000000002 that is seen in 4 rows are merged to 1 row if I set count KPI, instead when I do not display KPI then 4 rows are displayed as they are.
When I use functions to operate columns in data model, the idea is the same. In the next screenshot I will add one more KPI using deletion flag EKKO.LOEKZ that is originally string column and it is converted to numeric value by CASE WHEN ... THEN 1.0 ELSE 0.0 END function judging this column is NULL or not, finally use SUM to sum up numeric value by vendor. You can see vendor 0000001000 has 1528 active PO on the other hand PO count is 1530 (means there are 2 deleted PO).
Similar to previous case, I hide two KPIs and add dimensions of EKKO.LOEKZ and column of judging resust whether PO is active or not. It is important that total count of rows (rows of joining LFA1 and EKKO) is not changed after applying function to EKKO.LOEKZ. You can imagine one more numeric column Active PO is added to EKKO dynamically, so that each Purchase order has Active PO value (active is 1 and inactive is 0). When I write KPI that include data model columns, firstly result of function is expaneded to EKKO dynamically, then sum up value by dimension.
Even when you use other component (charts and single KPI component), the idea is the same. So I recommend you to start from OLAP table to visualize each step of KPI calculation and test your PQL. Also this idea is applicable to all data model tables including activity tables.
Kaz
This post’s program can be downloaded here then push to your environment by content-cli.