Hi ​@maxime.psbpm,

Indeed, Celonis does not natively support 0:N → 0:N modeling, Celonis treats N:N as 1:N → 1:N, which can cause unintended data loss when filtering. This is because the default filtering logic in Celonis are INNER JOINS, where only matching records remain.

So in your case, This means contracts that do not have a linked asset get unintentionally filtered out.

To avoid losing unrelated contracts, in the past I modeled the assets in a separate table instead of relying solely on an intermediate table.

Your asset table would be something like this.

SELECT DISTINCT Asset 
FROM CONTRACT_ASSET 
WHERE Asset IS NOT NULL;

This table would then contain only the assets, without the link to contracts.

And then you would have to Modify your contract_asset table to ensure there are only valid links.

This ensures:

• Only actual contract-asset links exist in the join table
• Contracts without assets are handled separately.

In the end, you can apply the filtering on assets, not contract_assets. this ensures C3 and C4 aremain in the dataset because we didn’t filter them out through Celonis’ default INNER JOIN.

This is pretty hard to explain textually, apologies. If you need some more help, let me know.

Hi ​@Sverre Klein ,

Thank you so much for your detailed response! I really appreciate you taking the time to explain the logic behind Celonis' default filtering behavior and how it impacts many-to-many relationships. It definitely clarifies why contracts without linked assets are unintentionally filtered out.

However, I believe I may not have fully grasped the proposed solution. I have tried handling assets separately and vice versa, but unfortunately, I haven't been able to achieve the desired result. In fact, I initially added the NULL rows in the intermediary table as an attempt to address the issue, but even when keeping only the base rows, the problem persists.

To make sure we're on the same page, here’s a simplified example of my current data model, which still exhibits the filtering issue:

Table A (Assets):

Table B (Intermediary Table - Contract-Asset Link):

Table C (Contracts):

Current Modeling:

• Table A (1 ⟷ N) Table B (N ⟷ 1) Table C

As expected, when applying filters on assets in Studio (e.g., selecting one or more asset values), all contracts without linked assets (C3, C4) are also excluded, even though they should remain visible.

Would you be able to suggest a concrete solution using this model format? If we don't manage to resolve it this way, I’d be happy to connect directly if you think you could help further!

I really appreciate your time and insights—thank you again!

No worries ​@maxime.psbpm, let’s see if we can solve this.

We need to implement a FULL OUTER JOIN like behavior for your problem I think. This is to ensure we can decouple the dependency between filtering on assets and filtering on contracts.

I have looking back in my old SQL code how I did it, so let’s see if this also works for you. Mind that I am using external scripting for SQL, so you might have to see if the code works inside Celonis SQL.

First, we have to explictly include contracts that do not have assets in Table B.
 

CREATE TABLE CONTRACT_ASSET AS 
SELECT Asset, Contract FROM Table_B  -- Existing Contract-Asset pairs
UNION ALL
SELECT NULL AS Asset, Contract FROM Table_C WHERE Contract NOT IN (SELECT DISTINCT Contract FROM Table_B);

This ensures that C3 and C4 remain in the dataset from Table B. Mind that we replace table B for CONTRACT_ASSET here.

Second, we need contracts without assets as well to be preserved in Table A.

SELECT DISTINCT Contract, Asset
FROM CONTRACT_ASSET
WHERE Asset IS NULL OR Asset IN ('A1', 'A2'); →  (example filter)

Your model would then be:

• Link Table A (Assets) → CONTRACT_ASSET on asset
• Link Table C (Contracts) → CONTRACT_ASSET on contract

You will have to play around with this code to have it work for you. Hopefully this will lead to your intended behavior.

Thank you very much again! I fully understand the first part where we add assets without contracts and contracts without assets into the CONTRACT_ASSET table, but it didn’t work as expected. I had actually tried this before my first message in the conversation, but it didn’t work, and I was surprised.

However, I don’t really understand the second part with the query you proposed for Table A. Since (unless I’m mistaken) in Celonis, queries are only used to create tables and not to perform the JOINs, which are managed directly in the data model, it would be much easier for me if you could send me the three tables with the example I provided (Table A, Table B, and Table C with the same values and columns).

Also, I don’t think so, but just to confirm—does writing these queries modify the underlying data model?

​@Sverre Klein just tagging you here in case you didn't get the notification. :)