The longest but ultimate guide for being a Pull-Up-Functions expert

#1

During my work with customers I have noticed that there is one specific chapter in PQL where people struggle the most: Pull-Up-Functions (PU functions). The reluctance can range from “It works but I don’t really understand why” to “I don’t even know what they are”. For some just hearing the word ‘Pull-function’ even creates nightmares :-). Therefore, I have now taken the time to create a guide that addresses this issue. The following guide on the one hand covers the technical foundation of PU functions. On the other hand, it provides example use cases for a better understanding of the concept. I am happy to get your feedback on this. Please do not see this guide as an official & comprehensive guide.

What are They Anyways?

The PU functions allow you to aggregate a column based on another table. You can define the parent table to which the child-tables entries are pulled, and you can explicitly define on which basis calculations are executed. A 1:n relationship between the parent and child table is required, meaning that each entry of the parent table can have multiple corresponding entries in the child table.

When do I Need Them?

Generally, three major use cases can be differentiated for PU functions: aggregating across different layers, aggregating as an input for dimensions and for joins between tables that do not have a common table.

1. Multilayer aggregation
Consider the following schema:

image
The schema indicates that each entry in Table 1 can have multiple corresponding entries in Table 2 and each entry in Table 2 can have multiple entries in Table 3.

image
If you want to aggregate values from Table 3 to the level of Table 1, you could pull up the values from Table 3 to Table 2, which then can be aggregated from Table 2 to Table 1.
Imagine you want to see the average number of activities per PO Item for each of the vendors in the LFA1 table. You have the following relationships between the relevant tables:

image
Then you need to pull the count of activities per PO Item from the Activity table to the EKPO table. You can do that with this PU function:

PU_COUNT(“EKPO”, “ActivityTable”.”_CASE_KEY”)

This will calculate the count of activities for each item in the EKPO table. In order to average this number according to the vendors, you can put an AVG() statement around this expression:

AVG(PU_COUNT(“EKPO”, “ActivityTable”.”_CASE_KEY”))

If you have “LFA1”.”NAME1” as Dimension, you should get something like the following table:

image

2. Aggregation as dimension
Normally, when adding data to a component the setup is quite strict: You add columns as dimensions, and you aggregate in KPIs. However, you can also use an aggregation as a dimension in Celonis. In that case, you need to ‘pull’ the values, that you want to aggregate, to the table which you want to base your calculation on. Once you have this dimension, you can then use a KPI that is based on this table.

Imagine you want to show the number of different currencies used on the vendor level and how many vendors have this number of different currencies. The currencies used are stored with the Purchase Orders in the EKKO table. Then you could find the number of distinct currencies for each vendor by using PU_COUNT_DISTINCT(“LFA1”,”EKKO”.”WAERS”) as the dimension. As the KPI you then use COUNT_TABLE(“LFA1”) in order find the number of vendors with that number of different currencies.
This will give you the following table:

image

3. Joins between tables that do not have a common parent table
Only 1:n (or 1:1, which are just a special case of 1:n) relations between tables are allowed in Celonis. The table on the n side of the relation is called a Parent table of the table on the 1 side.

Consider the following schema:
image

If you now want to join the table Child 1 with the table Parent 2, you will get the error: “No common parent between tables could be found”. The reason for this error is the fact that the Parent table of Child 1 is Parent 1, whereas the Parent table of Parent 2 is not Parent 1. In this case, you would have to use a PU function to aggregate the values from Parent 2 to the level of Child 2, because Child 2 and Child 1 share the same parent table and can be joined within Celonis.

The intuition, why a join between two tables, that do not share the same Parent table, can not be executed directly, is the following: You have a n:1 relation between Parent 1 and Child 2 and 1:n relation between Child 2 and Parent 2. Thus, Celonis knows how to join Parent 1 to Child 2 via the n:1 relation, and also how to join Child 2 with Parent 2 via the 1:n relation. However, since there is no such 1:n connection between Parent 1 and Parent 2 (but rather n:m), it is not clear how to join values from Parent 1 with values from Parent 2.

In order to perform such a join, you must pull the entries of Parent 2 to Child 2, because Child 2 can be joined to Parent 1.

This means you are reducing the dimension of Parent 2 from n to 1 by aggregating these values, such that the join can be realized in Celonis. Therefore, all PU functions return only one value for each item in the Child table (COUNT, SUM, MIN, MAX, AVG, FIRST, LAST, etc.).

Consider the tables EBAN and EKET. You cannot display entries from both tables in one OLAP table. The reason is the relation between these tables:

image

EBAN and EKET do not have a common parent table, but just a common child table (EKPO). The relation between EBAN and EKET is given by a n:1 relationship between EBAN and EKPO and a 1:n relationship between EKPO and EKET. Therefore, Celonis does not know which entries in those two tables belong together and the tables cannot be joined. Depending on what you need to show in your table, you have to pull the entries of one of those tables to the EKPO level.
Suppose you want to aggregate the values from the EKET table. The following picture shows how this aggregation is performed via a PU function.

image

The PU function will aggregate the values from EKET in such a way that one aggregated value is calculated for each row in EKPO.

EBAN and EKPO can be joined by default, as each entry in EBAN has only one join partner in EKPO. Since we pulled the values from EKET to the level of EKPO, we can now also join these values with EBAN.

For example, if we want to have the “EBAN”.”BANFN” and the Schedule Agreements Item Delivery Date “EKET”.”EINDT” in one table, you could use, for example, the minimum date for each EKPO entry, i.e. PU_MIN(“EKPO”,”EKET”.”EINDT”).

This will give you the following table:

image

Side Note: Filter Behavior
I would like to end this post with a short side note regarding PU functions: The way PU functions handle filters is different compared to the standard aggregation. In contrast to the standard aggregation, PU functions ignore filters, meaning that if a filter or a selection is changed, the result of
the PU function is not recalculated. Another difference to the standard aggregation is that it is possible to filter on the result of a PU function.

8 Likes
pinned #2