Celonis joining (based on foreign key) an array on a non aray

Celonis joining (based on foreign key) an array on a non aray

Hello,

I am having the challenge to join a table containing an array on a table that contains the array its values a example

Table 1
Case key | array

1 | (“test”,“arr”)

Table 2
key | value

test | 5
arr | 4

As an inspiration the following example can be taken:

Is such a join based on a array possible in Celonis. What would be a possible way to achieve this?

Hi Paul,

this kind of join is not possible directly in Celonis. However, you could perform the join on database level in SQL and store the result into a table or view. You can then add this to your datamodel.

Depending on your source database, this might work differently. I found an example for calculating it in mysql: https://stackoverflow.com/questions/13280875/join-on-array-of-ids-in-sql

If you don’t have a database system as your data source or you cannot figure out how it’s done on your database system, you could also use python to calculate the join and push the resulting table into the datamodel using our python API.

Hope that helps!

Cheers,
David

Sadly that idea will not work, let me explain why. That idea assumes that the tables are not connected via Foreign keys. This is something we want, to fully obtain the data in Celonis.

Consider the following case table I described above, if I make it

Case key | array

1 | test
2 | arr

Now the case key is not unique anymore, and thus cannot be considered as a case table. The only way to maintain its uniqueness is to explode the array like:

Case key | array_value_1 | array_value_2


1 | test | arr

This of course is not to pretty looking, moreover, the join will become a little tricky, since the values will now come from multiple columns.

Hi Paul,

sorry, I missed the fact that you’re talking about the case table.
I think the problem is that your data is not in a normalized form (https://en.wikipedia.org/wiki/Database_normalization). This is not only a problem in Celonis, but also not desirable in any other relational database system.
In order to at least bring it to the first normal form, you could:

  • split your array column up into multiple columns (works well if the array has a fixed length), or
  • you create a mapping table Mapping in addition to your case table:

Case Table:

CaseID
1

Mapping:

CaseID key
1 test
1 arr

Table2:

key value
test 5
arr 4

As Celonis doesn’t support N:M joins, the tables need to have a
CaseTable (1) – (N) Mapping (M) – (1) Table2
relationship. This works if the key attribute of Table2 is the primary key of that table.

So if you create such an additional Mapping table in SQL on the database side and connect the 3 tables with their foreign keys in the data model, it should work.

What do you think?

Cheers,
David

Thanks David, this will resolve the mentioned challenge :).