Segregation of dutie

Hi,

We want to use a dedicated Segregation of Duties dashboard to analyze cases where two activities are processed by the same person. In my olap table we have this columns :

PO Doc, User_name_Activity_EN, EVENTIME, Activity_EN, EVENTIME

See below the PQL for all columns :

PO Doc : “P2P_EKKO”.“EBELN”

User_name : “_P2P_ACTIVITIES”.“USER_NAME”
Activity_EN : (SOURCE ( “_P2P_ACTIVITIES”.“ACTIVITY_EN”, REMAP_VALUES ( “_P2P_ACTIVITIES”.“ACTIVITY_EN”, [ ‘<%= myActivity>’, ‘<%= myActivity2>’ ], [ ‘<%= myActivity>’, ‘<%= myActivity2>’ ], NULL ) )
EVENTIME:"_P2P_ACTIVITIES".“EVENTTIME”
Activity_EN : TARGET ( “_P2P_ACTIVITIES”.“ACTIVITY_EN” )
EVENTIME : “_P2P_ACTIVITIES”.“EVENTTIME”

Component Filter - FILTER SOURCE ( “”_P2P_ACTIVITIES".“USER_NAME”", REMAP_VALUES ( “_P2P_ACTIVITIES”.“ACTIVITY_EN”, [’<%= myActivity>’, ‘<%= myActivity2>’ ], [ ‘<%= myActivity>’, ‘<%= myActivity2>’ ], NULL ) ) = TARGET ( “_P2P_ACTIVITIES”.“USER_NAME” )

That doesn’t work…

The system said that the formula of the first column of Activity not exists…

I’ve try this formuls too :

In my first column of activity :

CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%= myActivity %>’
THEN ‘<%= myActivity %>’
ELSE NULL
END

In my second column of activity :
CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%= myActivity2 %>’
THEN ‘<%= myActivity2 %>’
ELSE NULL
END

But I’ve had des blanks lines… I want to have the information on one line.

I’ved put a filter, but the informations generated are not good.

Component filter : Filter ISNULL(CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%= myActivity %>’ THEN ‘<%= myActivity %>’ ELSE NULL END) =0 AND ISNULL(CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%= myActivity2 %>’ THEN ‘<%= myActivity2 %>’ ELSE NULL END) =0;

See below the formul of my variables :

#1 - myActivity :
Variable Type : Static Value
Satic Value : “_P2P_ACTIVITIES”.“ACTIVITY_EN”

#2 : myActivity2 :
Variable Type : Static Value
Satic Value : “_P2P_ACTIVITIES”.“ACTIVITY_EN”

Could you help me, please ?

Hi Audrey,

thanks for reaching out! Can you check for “EKPO”.“EBELN” || “EKPO”.“EBELP” or using P2P_EKPO (…) as a first column?
We rebuilt your case, and came up with a solution (it is important to not mix up myActivity and myActivity2 in the brackets):

Variables : Create two new variables “myActivity” and “myActivity2” as variable type “Text/Replacement” - don’t fill in anything yet
Button Dropdown : Create two button dropdowns

  • First button dropdown: Title is myActivity: <%=myActivity%> , then below select Load Entries and insert “_P2P_ACTIVITIES”.“ACTIVITY_EN” in the formula , then select Write to variable myActivity
  • Second button dropdown: Title is myActivity2: <%=myActivity2%> , then below select Load Entries and insert “_P2P_ACTIVITIES”.“ACTIVITY_EN” in the formula , then select Write to variable myActivity2

After clicking on Done, you can click on the dropdowns now and select the activities (one per dropdown) you want to compare to eachother.

OLAP Table :

  • First column : use your case key (e.g. EKPO.EBELN || EKPO.EBELP)

  • Second column: SOURCE ( “_P2P_ACTIVITIES”.“ACTIVITY_EN” , REMAP_VALUES ( “_P2P_ACTIVITIES”.“ACTIVITY_EN” , [ ‘<%= myActivity %>’, ‘<%= myActivity %>’ ], [ ‘<%= myActivity2 %>’, ‘<%= myActivity2 %>’ ], NULL ) )

  • Third column: SOURCE( “_P2P_ACTIVITIES”.“USER_NAME” )

  • Fourth column: SOURCE( “_P2P_ACTIVITIES”.“EVENTTIME” )

  • Fifth column : TARGET( “_P2P_ACTIVITIES”.“ACTIVITY_EN” )

  • Sixth column: TARGET( “_P2P_ACTIVITIES”.“USER_NAME” )

  • Seventh column : TARGET( “_P2P_ACTIVITIES”.“EVENTTIME” )

Component filter:
FILTER SOURCE ( “_P2P_ACTIVITIES”.“USER_NAME”, REMAP_VALUES ( “_P2P_ACTIVITIES”.“ACTIVITY_EN” , [ ‘<%=myActivity %>’, ‘<%=myActivity %>’ ], [ ‘<%=myActivity2 %>’, ‘<%=myActivity2 %>’ ], NULL ) ) = TARGET ( “_P2P_ACTIVITIES”.“USER_NAME” );
FILTER (CASE WHEN SOURCE ( “_P2P_ACTIVITIES”.“ACTIVITY_EN” , REMAP_VALUES ( “_P2P_ACTIVITIES”.“ACTIVITY_EN” , [ ‘<%= myActivity %>’, ‘<%= myActivity %>’ ], [ ‘<%= myActivity2 %>’, ‘<%= myActivity2 %>’ ], NULL ) ) = TARGET( “_P2P_ACTIVITIES”.“ACTIVITY_EN” ) THEN 1.0 ELSE 0.0 END) = 0;

If an error message comes up, feel free to share it with us. Also, it could be interesting to know in which deployment scenario you are using Celonis.

  • Is it full cloud (source system = cloud, Celonis Intelligent Business Cloud)?
  • Is just the event collection in the cloud (so transformations / extraction tasks are triggered in the Celonis Intelligent Business Cloud)
  • Are you using a hybrid scenario (so your data is stored on-site and transformations / extraction are done on-site)?
  • Are you not using the Celonis Intelligent Business Cloud at all (so you are using a Celonis Process Mining on-premise version)?

Hope this helps!

Hi,

Fisrt of all, we are using the Celonis Intelligent Business Cloud.

In second time, I start my chart again and don’t know why the OLAP table never stop loading. If I change the apostropher for ', I receive an error message.

Hi Audrey,

thanks for providing us with detailed information. Does the same error occur when you plug in ‘Create Purchase Order Item’ instead of ‘<%=myActivity%>’ and ‘Record Goods Receipt’ instead of ‘<%=myActivity2%>’ ? To test this, you would have to adjust the second column (SOURCE…) and the component filter.

Coming back to my previous question: So, you are using the Celonis Intelligent Business Cloud as a frontend. Can you tell me in which scenario this is deployed and what the source system is? With scenario, I mean Uplink, Hybrid or Cloud. Going more into detail: In a Uplink scenario, the event collection (extraction, transformation and loading of data) happens on your servers. Hybrid scenario, the extraction, transformation and loading of data can be triggerd in the Intelligent Business Cloud but the calculations are done on your servers. Cloud scenario, everything happens in the Intelligent Business Cloud because the data is pushed into it beforehand. This would be an interesting information because updates on our PQL innovations / functions (like SOURCE TARGET) are released in the Cloud scenario first.

Hi,

I’ve the same error. But I try to search in my PQL reference and we don’t have the SOURCE - TARGET PQL.

But, I modified the formula of the second column and it’s seems like the REMAP_VALUE doesn’t work.

That are my formulas :
User_name : “_P2P_ACTIVITIES”.“USER_NAME”
Activity_EN : SOURCE ( <%=myActivity%>)
EVENTIME: SOURCE("_P2P_ACTIVITIES".“EVENTTIME”)
Activity_EN : TARGET ( <%=myActivity2%>)
EVENTIME : TARGET(“_P2P_ACTIVITIES”.“EVENTTIME”)

That seems working! Now, I’m trying to have the same user_name in the SOURCE and the TARGET to exclude the activities done by 2 different users. DO you have any formula for that?

We think we have the Uplink scenario. If we want to do a segration of dutie, we need to do what ? Becoming a hybrid scenario ? How long the system we have will be able to do SOURCE-TARGET PQL ?

image

Thank in advance.

Hi Audrey,

thanks for the input. Do you also know what Celonis version you are working on (4.0, 4.1,…)? Would be interesting to know, but REMAP_VALUES should work anyways.

How is the syntax of the text written in your text/replacement variables? Please ensure that you don’t use any quotation marks like " or '. Just write it like in the picture e.g. Create Purchase Order Item. Then try again and copy paste the following:

  • First column : use your case key (e.g. EKPO.EBELN || EKPO.EBELP)
  • Second column: SOURCE ( “_P2P_ACTIVITIES”.“ACTIVITY_EN” , REMAP_VALUES ( “_P2P_ACTIVITIES”.“ACTIVITY_EN” , [ ‘<%= myActivity %>’, ‘<%= myActivity %>’ ], [ ‘<%= myActivity2 %>’, ‘<%= myActivity2 %>’ ], NULL ) )
  • Third column: SOURCE( “_P2P_ACTIVITIES”.“USER_NAME” )
  • Fourth column: SOURCE( “_P2P_ACTIVITIES”.“EVENTTIME” )
  • Fifth column : TARGET( “_P2P_ACTIVITIES”.“ACTIVITY_EN” )
  • Sixth column: TARGET( “_P2P_ACTIVITIES”.“USER_NAME” )
  • Seventh column : TARGET( “_P2P_ACTIVITIES”.“EVENTTIME” )

Component filter:
FILTER SOURCE ( “_P2P_ACTIVITIES”.“USER_NAME”, REMAP_VALUES ( “_P2P_ACTIVITIES”.“ACTIVITY_EN” , [ ‘<%=myActivity %>’, ‘<%=myActivity %>’ ], [ ‘<%=myActivity2 %>’, ‘<%=myActivity2 %>’ ], NULL ) ) = TARGET ( “_P2P_ACTIVITIES”.“USER_NAME” );
FILTER (CASE WHEN SOURCE ( “_P2P_ACTIVITIES”.“ACTIVITY_EN” , REMAP_VALUES ( “_P2P_ACTIVITIES”.“ACTIVITY_EN” , [ ‘<%= myActivity %>’, ‘<%= myActivity %>’ ], [ ‘<%= myActivity2 %>’, ‘<%= myActivity2 %>’ ], NULL ) ) = TARGET( “_P2P_ACTIVITIES”.“ACTIVITY_EN” ) THEN 1.0 ELSE 0.0 END) = 0;

Also have a look at this:
help.celonis.cloud/help/display/CIBC/Segregation+of+Duties

You could also use a CASE WHEN workaround, if this is still not working. Let us know!

Hope this helps!

Hi,

We are using the 4.3 - Release, we only work on us system not in the Celonis Cloud.

Hi Audrey,

thanks for getting the information. Then I would suggest you try this workaround (we changed the code for second column and the component filter, so without REMAP_VALUES). Please be aware to just write e.g. Change Purchase Order Item into the text/replacement variables like myActivity and myActivity2 without any quotation marks:

Copy and paste this into your OLAP Table:

  • First column: use your case key (e.g. EKPO.EBELN || EKPO.EBELP)
  • Second column: SOURCE ("_P2P_ACTIVITIES".“ACTIVITY_EN”, (CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity %>’ THEN ‘<%=myActivity %>’
    WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity2 %>’ THEN ‘<%=myActivity2 %>’
    ELSE NULL END))
  • Third column: SOURCE( “_P2P_ACTIVITIES”.“USER_NAME” )
  • Fourth column: SOURCE( “_P2P_ACTIVITIES”.“EVENTTIME” )
  • Fifth column: TARGET( “_P2P_ACTIVITIES”.“ACTIVITY_EN” )
  • Sixth column: TARGET( “_P2P_ACTIVITIES”.“USER_NAME” )
  • Seventh column : TARGET( “_P2P_ACTIVITIES”.“EVENTTIME” )

Copy and paste this into your COMPONENT FILTER:
FILTER SOURCE ("_P2P_ACTIVITIES".“USER_NAME”, (CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity %>’ THEN ‘<%=myActivity %>’
WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity2 %>’ THEN ‘<%=myActivity2 %>’
ELSE NULL END)) = TARGET ( “_P2P_ACTIVITIES”.“USER_NAME” );
FILTER SOURCE ("_P2P_ACTIVITIES".“ACTIVITY_EN”, (CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity %>’ THEN ‘<%=myActivity %>’
WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity2 %>’ THEN ‘<%=myActivity2 %>’
ELSE NULL END)) <> TARGET("_P2P_ACTIVITIES".“ACTIVITY_EN”);

Hope that helps!

Hi,

That worked but seem have some problems :

#1 : in my column activity 1 I have also a part of activity 2
image
#2 : the filter seems not like functionned. I saw some activity exectuted by the same person but the results have also other names too. See below the message I have:
image

Thans a lot

Hi Audrey,

#1: This is on purpose. Because if activity “records goods receipt” appears before “create purchase order item” and action has been done by the same user, it is a violation.

#2: Can you please send us everything which is written in your component filter / sheet filter / analysis filter? Maybe there is some interference with another filter.

Hi,

See below the component filter :

Analysis :
FILTER “P2P_EKKO”.“RESWK” = ‘’;
FILTER “P2P_EKKO”.“EKGRP” != ‘998’;

Sheet : Nothing

Olap Table :
FILTER SOURCE ("_P2P_ACTIVITIES".“USER_NAME”, (CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity %>’ THEN ‘<%=myActivity %>’
WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity2 %>’ THEN ‘<%=myActivity2 %>’
ELSE NULL END)) = TARGET ( “_P2P_ACTIVITIES”.“USER_NAME” );

FILTER SOURCE ("_P2P_ACTIVITIES".“ACTIVITY_EN”, (CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity %>’ THEN ‘<%=myActivity %>’
WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity2 %>’ THEN ‘<%=myActivity2 %>’
ELSE NULL END)) <> TARGET("_P2P_ACTIVITIES".“ACTIVITY_EN”);

Hi Audrey,

The query looks good so far, but the OLAP columns seem to affect each other. Please go through the following procedure step by step to narrow the issue down:

  1. Have you added additional columns to your OLAP? If so, please provide us with the information and code as well. Also, disable the column you have added additionally and look for an error.
  2. Disable the analysis filter. If you do so, does the exact same error still occur?
  3. Disable the first component filter (OLAP) “…FILTER SOURCE… = TARGET…”. Does the same error still occur? Do the same with the second component filter.

Hi,

#1 :


No added column.

#2 : With no Filter analysis same error.

#3 : I did the test with the 2 components filters and I have the same error still occur. I tried whitout component filter (OLAP) and the message dissapear.

Thanks in advance.

Hi Audrey,

I am referring to my first point #1 ("… Also, disable the column you have (added) additionally and look for an error."): Please use the component filter (error will occur) and then go into the OLAP and disable all columns one-by-one and check if the error message disappears at some point. If so, please name the column which is responsible for the error.
You should have a little “eye” icon next to a dimesion to hide columns. Also see attached the screenshot.
image

Hey!

No matter which column I hide, the error appear.

Hi Audrey,

can you provide us with a screenshot of your data model? It seems like that the relation between the respective fields and columns is facing some issues.

Also please try the following:
In first column: instead of the purchasing document, use the “_P2P_ACTIVITIES”."_CASE_KEY"

For the component filter:
Replace <> with !=

Hi Audrey,

In Celonis versions prior to 4.5, you need to pass the SOURCE/TARGET configuration to every occurrence of SOURCE and TARGET in the filters.
So please try to add your configuration

(CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity %>’ THEN ‘<%=myActivity %>’
WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity2 %>’ THEN ‘<%=myActivity2 %>’
ELSE NULL END)

also to both TARGET occurrences inside your FILTER statement.

Best
David

Hi,
If i’m using this formula, I receive this error message.


thanks

Audrey

Hi David,

I’m not sure that I understand.
You want I put this formula (but ajusted) for each column in my OLAP table ?

Or I need to put this formula in by component filter of the OLAP table ?

thanks in advance
Audrey

Hi Audrey,

currently, you have those filters, right?

So what I mean is that you should try to add the same CASE WHEN statement you have in the SOURCE functions also to the TARGET occurrences, like this:

FILTER SOURCE ("_P2P_ACTIVITIES".“USER_NAME”, (CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity %>’ THEN ‘<%=myActivity %>’
WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity2 %>’ THEN ‘<%=myActivity2 %>’
ELSE NULL END)) = TARGET ( “_P2P_ACTIVITIES”.“USER_NAME”, (CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity %>’ THEN ‘<%=myActivity %>’
WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity2 %>’ THEN ‘<%=myActivity2 %>’
ELSE NULL END) );

FILTER SOURCE ("_P2P_ACTIVITIES".“ACTIVITY_EN”, (CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity %>’ THEN ‘<%=myActivity %>’
WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity2 %>’ THEN ‘<%=myActivity2 %>’
ELSE NULL END)) <> TARGET("_P2P_ACTIVITIES".“ACTIVITY_EN”, (CASE WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity %>’ THEN ‘<%=myActivity %>’
WHEN “_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘<%=myActivity2 %>’ THEN ‘<%=myActivity2 %>’
ELSE NULL END));

Cheers
David