Skip to main content
Solved

SET operation tree too complex error when uploading OCEL 2.0 P2P log with 1:N relationships in OCDM (event type)

  • April 9, 2026
  • 1 reply
  • 38 views
H
Level 2
Forum|alt.badge.img

Hi everyone,

I am currently working on a Bachelor's thesis on Object-Centric Process Mining (OCPM) using Celonis (academic license). For my analysis, I am uploading the publicly available OCEL 2.0 P2P simulation log (https://www.ocel-standard.org/event-logs/simulations/p2p/) into the Celonis OCDM using the provisional upload procedure via splitter.py. This procedure generates SQL files per object and event type, which are then inserted as transformations in the Objects & Events feature. (chrome-extension://efaidnbmnnnibpcajpcglclefindmkaj/https://ocel-standard.org/provisional_celonis_upload_procedure.pdf)

Now I have the following problem:

When executing the Data Job, 9 out of 10 event types upload successfully — including larger ones like CreateGoodsReceipt (~4,000 events, ~48,000 lines of SQL). However, the event type ExecutePayment consistently fails with:

"The query contains a SET operation tree that is too complex to analyze. Please review your query and try again."
 


ExecutePayment involves 4 related object types:
- Payment (involves one, 1:1)
- InvoiceReceipt (involves one, 1:1)
- GoodsReceipt (involves many, 1:N — up to 4 per event, 2,437 total relationships)
- Purchaseorder (involves many, 1:N — up to 4 per event, 1,992 total relationships)

 

Because of this, in addition to the attributes transformation, I also had separate relationship transformations for GoodsReceipt and Purchaseorder: 

 

 

So far i tried:

  1. Splitting transformations into smaller parts (~1,500–2,000 lines each) → still failed
  2.  Rewriting transformations using VALUES syntax instead of UNION ALL chains → failed​​​​​​
  3. Deleting the two 1.n relationships → worked

So when I remove the two 1:N relationships, the Data Job executes successfully. However, I would really like to include these relationships in my analysis, as they are a key argument for demonstrating the advantages of OCPM over traditional process mining — specifically the convergence behavior where multiple goods receipts lead to a single payment.

I also have 1:N relationships working fine in another event type (ApprovePurchaseRequisition → Material), so I am not sure why it fails specifically for ExecutePayment.

Thats why i have the questions if:

1. Is there a known limit on the number of related object types per event type in the OCDM?
2. Is this limitation specific to the academic license?
3. Does anyone know why a 1:N relationship works for one event type but not another?
4. Is there a recommended workaround for this error?

 

Any help or insights would be greatly appreciated! Happy to share screenshots or additional details.

Thanks in advance 😊
Hendrik

Best answer by William

Hi Hendrik,

Interesting project 😊. To answer your questions: 

1. No, to our knowledge there is no such limit officially and your sample process is certainly not large.
2. No - that said non-productive Celonis instances (such as academic teams) have less powerful underlying databases which could potentially be the issue.
3. This would require a little bit of troubleshooting in your instance. Clearly the database has an issue handling the relationship scripts. If possible please attempt to narrow down the issue to see if it's one or both scripts. 
4. Since rescripting does not seem to help in the object-centric transformation, we would recommend one these three options: 

  1. Create the table within your data pool first (we can this ‘pre-processing’, then use it in your object centric transformation. E.g. create a transformation (In global schema or a specific connection) , run the transformation but with  CREATE TABLE statement  in front. Transformatinos outside of the object-centric editor have a slightly different setup and may be able to handle it. 
  2. If option 1 does not work you can create a csv or parquet file upfront with your sql statements. You can do this by running your statement in a SQL client (e.g. db beaver) and exporting the results.
  3. Or use python to convert your sql statement, e.g. something in this direction:
import pandas as pd
import sqlite3

# 1. Paste the giant SQL statement here
sql_query = """
SELECT 'event:176' AS "ID", '2022-04-16' AS "Time" UNION ALL
SELECT 'event:212' AS "ID", '2022-04-18' AS "Time"
-- ... (all the other unions)
"""

# 2. Use a temporary in-memory database to "run" the SQL
conn = sqlite3.connect(':memory:')
df = pd.read_sql_query(sql_query, conn)

# 3. Save it as Parquet
df.to_parquet('manual_events.parquet')

From there (option 2 or 3) you can upload it to your instance and then refer to the pre-made table in your relationship scripts. 

We hope this helps 

William

    1 reply

    W
    Celonaut
    • William
    • Answer
    • April 10, 2026

    Hi Hendrik,

    Interesting project 😊. To answer your questions: 

    1. No, to our knowledge there is no such limit officially and your sample process is certainly not large.
    2. No - that said non-productive Celonis instances (such as academic teams) have less powerful underlying databases which could potentially be the issue.
    3. This would require a little bit of troubleshooting in your instance. Clearly the database has an issue handling the relationship scripts. If possible please attempt to narrow down the issue to see if it's one or both scripts. 
    4. Since rescripting does not seem to help in the object-centric transformation, we would recommend one these three options: 

    1. Create the table within your data pool first (we can this ‘pre-processing’, then use it in your object centric transformation. E.g. create a transformation (In global schema or a specific connection) , run the transformation but with  CREATE TABLE statement  in front. Transformatinos outside of the object-centric editor have a slightly different setup and may be able to handle it. 
    2. If option 1 does not work you can create a csv or parquet file upfront with your sql statements. You can do this by running your statement in a SQL client (e.g. db beaver) and exporting the results.
    3. Or use python to convert your sql statement, e.g. something in this direction:
    import pandas as pd
    import sqlite3

    # 1. Paste the giant SQL statement here
    sql_query = """
    SELECT 'event:176' AS "ID", '2022-04-16' AS "Time" UNION ALL
    SELECT 'event:212' AS "ID", '2022-04-18' AS "Time"
    -- ... (all the other unions)
    """

    # 2. Use a temporary in-memory database to "run" the SQL
    conn = sqlite3.connect(':memory:')
    df = pd.read_sql_query(sql_query, conn)

    # 3. Save it as Parquet
    df.to_parquet('manual_events.parquet')

    From there (option 2 or 3) you can upload it to your instance and then refer to the pre-made table in your relationship scripts. 

    We hope this helps 

    William

      Community Code of ConductAccessibility statement