Skip to main content

Data Ingestion & Infrastructure

Discuss and ask questions about data integration, task mining, admin settings, and PQL extractors to optimize your infrastructure.

  • 759 Topics
  • 2,040 Replies
759 Topics
A
Level 1
andrew.liebeLevel 1
posted in Data Ingestion & Infrastructure
Quick Tip!

Quick Tip!  For faster run times, move filters from the where clause into the appropriate inner join. The top example is an optimized version of the bottom code. Let me know if you have any other tips or questions!  

01
S
Level 1
3 years ago
M
Level 2
mel.schic11Level 2
asked in Data Ingestion & Infrastructure
I would like to connect two fields that have an indirect N:N relation. Is this something which I can do via a PU_Function?

Within an OLAP table, I would like to use both the Delivery Line Item Date ("VBEP"."EDATU") and the shipment gross weight ("LIPS"."BRGEW") Does anyone know how I can apply a PQL function to combine these tables? Please find the error below. I looked into the PQL documentation but I can't find the solution. Error in formulaNo common table could be found. The tables ["VBEP"] and ["LIPS"] are connected, but have no common table. This means that they do not have a direct (or indirect) 1:N or N:1 relationship. Join path: [VBEP]N <-- 1![VBAP]!1 --> N[VBFA]N <-- 1[LIPS]. For more information on the join path, search for "Join functionality" in PQL documentation.

11
D
Level 1
3 years ago
.
Level 2
.146001173Level 2
asked in Data Ingestion & Infrastructure
From where I can find data about the last execution of a data job using Pycelonis?

Dear Team,  Please give me an advice about how I can get the timestamp which represents when was the last execution of a specific data job Thanks in advence!Regards, Roman

01
D
Level 1
3 years ago
L
Level 3
luis.magalLevel 3
asked in Data Ingestion & Infrastructure
How to apply Conditions to Filters?

Example: CASE WHEN YEAR("X"."y") >= 2019 THEN FILTER ROUND_DAY("X"."y") != ROUND_DAY("A"."b") OR ROUND_DAY("X"."y") != ROUND_DAY("M"."n")OR ROUND_DAY("A"."b") != ROUND_DAY("M"."n") ELSE FILTER ROUND_DAY("A"."b") != 2019-01-01 OR ROUND_DAY("M"."n") != 2019-01-01 END Neither CASE WHEN nor IF worked for me. I'm trying in a Component Filter, though a solution on a Sheet level could also solve my problem.

03
joos.buijs
Level 6
3 years ago
A
Level 3
agustín.muñoz12Level 3
posted in Data Ingestion & Infrastructure
Hi everyone!

Hi everyone!I have a doubt about a calculation related to the duration between activities. Normally the CALC THROUGHPUT formula is used to calculate durations between 2 activities. However, I need to calculate the duration between more than 2 activities in a single KPI (e.g. the duration between activities A and B to activity C, or from A and B to C and D), but I can't get the formula.  I already have tried the following formula to calculare the duration between activities 'A' and 'C' to 'B' AVG (CASEWHEN PROCESS EQUALS 'A' TO ANY TO 'B' THEN CALC_THROUGHPUT(FIRST_OCCURRENCE['A'] TO LAST_OCCURRENCE['B'],  REMAP_TIMESTAMPS("activity_table"."timestamp"))  WHEN PROCESS EQUALS 'C' TO ANY TO 'B' THEN CALC_THROUGHPUT(FIRST_OCCURRENCE['C'] TO LAST_OCCURRENCE['B'],  REMAP_TIMESTAMPS("activity_table"."timestamp", HOURS)) ELSE 0 END )  Can you help me?  Thank you v

01
joos.buijs
Level 6
3 years ago
P
Level 1
praveen.reddy.gLevel 1
posted in Data Ingestion & Infrastructure
tcscboprod.us-2.celonis.cloud

Hi, We have installed Extractor for JDBC connection for Oracle inside Oracle Data base Server, oracle DB server installed in a Physical server, during connection test with uplink connector we are getting below error. 2021-07-12 14:31:32.680 INFO [,,,] 30305 --- [uplink-thread-0] c.celonis.uplink.manager.UplinkManager  : Uplink could not connect: 'I/O error on POST request for "https://tcscboprod.us-2.celonis.cloud/uplink/api/public/uplink": Read timed out; nested exception is java.net.SocketTimeoutException: Read timed out', waiting 3000ms before retrying --EOM-- We have enabled the port & IP for source and IBC Please suggest for possible solution to resolve the above error

01
A
3 years ago
A
Level 3
agustín.muñoz12Level 3
asked in Data Ingestion & Infrastructure
Hi!I have a doubt about a calculation related to the duration between activities.

Hi!I have a doubt about a calculation related to the duration between activities. Normally the CALC THROUGHPUT formula is used to calculate durations between 2 activities.However, I need to calculate the duration between more than 2 activities in a single KPI (e.g. the duration between activities A and B to activity C, or from A and B to C and D), but I can't get the formula. I already have tried the following formula to calculare the duration between activities 'A' and 'C' to 'B' AVG (CASEWHEN PROCESS EQUALS 'A' TO ANY TO 'B' THEN CALC_THROUGHPUT(FIRST_OCCURRENCE['A'] TO LAST_OCCURRENCE['B'],  REMAP_TIMESTAMPS("activity_table"."timestamp"))  WHEN PROCESS EQUALS 'C' TO ANY TO 'B' THEN CALC_THROUGHPUT(FIRST_OCCURRENCE['C'] TO LAST_OCCURRENCE['B'],  REMAP_TIMESTAMPS("activity_table"."timestamp", HOURS)) ELSE 0 END )  Can you help me? Thank you very much!!

04
A
Level 3
3 years ago
M
Level 3
mariana.gnask11Level 3
asked in Data Ingestion & Infrastructure
Throughput Time differs in OLAP table and other Components

Hi! I am using the same simple formula to calculate the average throughput time in the OLAP table and in the Gauge Component. OLAP table has dimensions (e.g. Job Family). The case is a Vacancy ID, and there are 2 basic tables in the data model: dimensions table (containing Job Family attribute) and facts table (activity table for the Vacancy status changes) where Vacancy ID is a key. So the data model is pretty simple as well. Could you please help me to figure out why I am constantly getting different results when I am comparing throughput time calculated based on OLAP table selection with the throughput time calculated by a single component (e.g. Gauge or Throughput Time Search)?  Throughout Time formula: TRIMMED_MEAN(CALC_THROUGHPUT(ALL_OCCURRENCE['Process Start'] TO ALL_OCCURRENCE['Process End'], REMAP_TIMESTAMPS("GTO_Application_Workflow_csv"."CHANGE DATE", DAYS)))  This topic can be related to https://www.celopeers.com/s/question/0D50700000GC6p

13
M
Level 3
3 years ago
S
Level 1
stephan.brendLevel 1
asked in Data Ingestion & Infrastructure
Does Celonis include case sorting to compare one activity to a second activity from the subsequent case?

I would like to compare an attribute (e.g. price) of one activity from e.g. caseID 123 with another activity from the following caseID e.g. 124 - is there any way to solve this problem in Celonis?   

01
janpeter.van.d
Level 8
3 years ago
M
Level 3
mariana.gnask11Level 3
asked in Data Ingestion & Infrastructure
Why there is a difference in throughout times calculated for a bucket created by using a PU_COUNT function and a bucket created by using a CALC_REWORK function?

Hi! I have a use case when a specific field within 1 case can be changed multiple times during the case lifecycle (e.g. fields containing metadata around the Vacancy case). The aim is to figure out what is the influence of specific field changes on the Vacancy throughput time. For this, I am using range buckets calculated based on the number of specific field changes per Vacancy.  I've created 2 tables with buckets: the first table allocates a Vacancy to a range bucket (OLAP table dimension) using PU_COUNT function and the second table does the same using CALC_REWORK function (all formulas are provided below). Then I calculated the # of vacancies in each bucket and the average # of field changes in a bucket (both tables show the same result). Then I am adding throughput time calculation to the tables, and the result is the same at a first glance. But when I hide the column with average # of field changes in a bucket from the second table, throughput time is recalculated. Please, se

03
joos.buijs
Level 6
3 years ago
V
Level 3
vincent.freemLevel 3
asked in Data Ingestion & Infrastructure
How to add WEEKDAYS only to a date?

Hi, is there a way to add weekdays to a date?E.g. Goods Issue date is on a Fridayand we need to add 2 weekdays (Monday through Friday are weekdays)to the Goods Issue Date, so that we end at a Delivery Day of Tuesday (Friday + 2 weekdays)ADD_DAYS would give us Sunday as output, and ADD_WORKDAYS only works with a workday calendar (needs TFACS table which we do not have)REMAP_TIMESTAMP even with the ADD_DAYS function would bring us to Monday as the next weekday.

02
V
Level 3
3 years ago
M
Level 3
mariana.gnask11Level 3
asked in Data Ingestion & Infrastructure
Is there any way to calculate the correlation coefficient with PQL?

We have multiple cases when we want to make sure there is an explicit correlation between two data series apart from visually representing those series on a graph. Is there any simple KPI/PQL formula we can use, or do we need to go with a workaround deriving corr from calculating standard deviations etc?

12
M
Level 3
3 years ago
.
Level 1
.14600967Level 1
asked in Data Ingestion & Infrastructure
Hi There, I am trying to Filter on component level. The filter has two parts and looks like this: 1.Filter : Filter all cases, which contain only activities, which does NOT match user_type = A FILTER PU_COUNT ("AP_VIM_CASES" , "_CEL_AP_ACTIVITIE

Hi There, I am trying to Filter on component level. The filter has two parts and looks like this: 1.Filter : Filter all cases, which contain only activities, which does NOT match user_type = A FILTER PU_COUNT ("AP_VIM_CASES" , "_CEL_AP_ACTIVITIES"."_CASE_

11
joos.buijs
Level 6
3 years ago
S
Level 2
shrishti.kanch11Level 2
posted in Data Ingestion & Infrastructure
Hello,

Hello, I am looking for something similar to a lookup function in Celonis. Is it something possible? I have a child table "X" linked to Parent table "A" another child table "Y" linked to same parent table "A". Both links are based on different keys. I want to lookup a value from X to Y is it something possible as in is there PQL formula which could help me do that? Thanks.

23
D
Level 1
3 years ago
P
Level 1
praveen.reddy.gLevel 1
posted in Data Ingestion & Infrastructure
We are trying to connect to SAP ECC using uplink connector, post Test Connection got successfull, tried to create Data Job and trying to load tables but not moving ahead from log 'Job not yet started in SAP.

We are trying to connect to SAP ECC using uplink connector, post Test Connection got successfull, tried to create Data Job and trying to load tables but not moving ahead from log "Job not yet started in SAP. Current status: SCHEDULED", so it could be issue in SAP RFC module, unable to understand the issue please suggest.

01
D
Level 1
3 years ago
P
Level 2
paul.perkuLevel 2
asked in Data Ingestion & Infrastructure
I have to build up a own event log on SQL database. This worked but I have events with the same timestamp within the same case. So sometimes the event aren't in the right order in the process explorer.

Do you have a tips how I can create the sorting numbers? Do I have to create the sorting number while i running the procedures to create the event log entries afterwards when the event log is created? But I don't know how to define the sorting number with SQL.

01
A
3 years ago
.
Level 3
.14600782Level 3
posted in Data Ingestion & Infrastructure
Best 20% and worst 20 % Cases based on their duration

Best 20% and worst 20 % Cases based on their duration Hi everyone, we are looking for a solution to the following problem. We would like to get the best 20% and worst 20% of the Cases based on their duration, including all cases that fall between the scope, e.g. in a pie chart.So we could filter on the specific cases that are in the defined scope and as an output we would like so see them individually (with Case Key and their specific duration) in an OLAP table. Does anyone know what formula to use to get the relationship to each other?

01
D
Level 1
3 years ago
P
Level 1
paolo.scalaLevel 1
posted in Data Ingestion & Infrastructure
Does anyone know how can you remove disk space (hence reduce APC consumption) removing specific rows from a table?

Does anyone know how can you remove disk space (hence reduce APC consumption) removing specific rows from a table? I would need to remove permanently rows from a table based on a specific sub-query. I have limited space available so i cannot use operations that require, even for a small period of time, twice increase in space occupation Thanks in advance  

02
P
Level 1
3 years ago
J
Level 3
jasmin.wolff11Level 3
posted in Data Ingestion & Infrastructure
UTC TO LOCAL TIME

UTC TO LOCAL TIMEHello, I'm curious if anybody else has already faced the following issue: For some tables, timestamps are stored in UTC in SAP, although in display mode of transaction timestamp is shown in local time. This leads to UTC timestamps for activities in Celonis and can cause confusion of end users.  I already tried SQL best practise to transfer UTC in local time but standard operations/ local time zones are disabled in Event Collection. SELECT GETDATE() only provides UTC (Vertica base)getutcdate() is not supported (Vertica base)SELECT * FROM sys.time_zone_info is not allowed/missing rights (SQL Server base) It would be better to avoid hard coded transformations due to summer and winter time in Europe which will lead to maintaining effort. Does anybody else has an idea how to transfor UTC in local time?  Looking forward to hear your experiences!Greetings from Germany,Jasmin

15
A
3 years ago
janpeter.van.d
Level 8
janpeter.van.dLevel 8
posted in Data Ingestion & Infrastructure
API Key vs.

API Key vs. Bearer Token for Data Push API Hi all,  We are trying to push data towards the EMS using the Data Push API. In our tests with Postman, we were able to do this, but we found out that the API key in the EMS should be used as a Bearer token.In the application we use, a Bearer token can only be obtained by requesting it from a authorization endpoint.Moreover, we cannot use the Celonis API key as a REST API key authorization, since you need a key name for that (and this is not the key name in the EMS). Has someone of you experienced the same issue, and is there a workaround for it?  Kind regards,Jan-peter

29
janpeter.van.d
Level 8
3 years ago
V
Level 4
vasco.caron11Level 4
posted in Data Ingestion & Infrastructure
Good morning everyone,

Good morning everyone, Is there a way for me to extract more than 100 rows of data from the Transformations / Event Collection workspaces? For some context, we don't have access to the SAP environment from where we are extracting the data; we do, however, need to provide a considerable sample of the data we're working with to validate with the team. If I do a regular SELECT * FROM CDPOS for example, I will only be able to get 100 rows of data at a time Thank you in advance! P.S: Apologies if this is not posted in the appropriate place!

03
J
Level 1
3 years ago
joos.buijs
Level 6
joos.buijsLevel 6
asked in Data Ingestion & Infrastructure
Calculate activity status duration, ignoring other activities' status changes

Hi Celopeers :) We have a specific use case, based on JIRA data. For a case a limited set of activities (~7, JIRA stories) need to be performed. For each activity (/story) we know when it was ready for pickup, in progress, and done (or rejected). If we would like to calculate the total time the activity/story spent in the status 'in progress' I was thinking of something like this: SUM( CASE WHEN "TABLE_STATUS"."STATUS" = 'In progress'   THEN HOURS_BETWEEN("TABLE_STATUS"."STATUSDATETIME",ACTIVITY_LEAD("TABLE_STATUS"."STATUSDATETIME")) / 24 --to get fractional days   ELSE 0.0 END) Where the dimension could be the different activity/story names for instance. However, the activity_lead often is a very different activity (/JIRA story), hence the calculation fails.I also cannot use a process pattern matching using first and last occurrence, as an activity can be 'in progres' multiple times and I would need to

36
joos.buijs
Level 6
3 years ago
F
Level 3
fabio.formiLevel 3
asked in Data Ingestion & Infrastructure
I want to transform values for example „Materialnumber“ which are in a Column group by „HandlingUnit“ order by „Position“ into a string of comma-separated values (see example)

I´ve seen that in the Vertica documentation there is a function called „LISTAGG“, but it seems that celonis do not support this function. Thanks in advance for your help. 

27
janpeter.van.d
Level 8
3 years ago
A
Level 3
anjith.ak11Level 3
asked in Data Ingestion & Infrastructure
How is "_CELONIS_CHANGE_DATE" created ?

I want to understand the rationale behind creation of the automatic data extraction field: "_CELONIS_CHANGE_DATE"

08
A
3 years ago
.
Level 1
.14600834Level 1
asked in Data Ingestion & Infrastructure
Event collection - OTC Data Job Error

Hi all, Id like to run the data job in O2C process conenctor after i connect IBC and SAP ECC. The extraction is done, followed by Transformation but failed, details as below - Error transforming data: SyntaxErrorException: [Vertica]VJDBC ERROR: Relation BSAD does not exist. Celonis Support ID: 473a50a9-aa4b-4f3e-8fc1-b3e280d9ce33 Or you can refer to my screenshot. Actually the data extraction for BSAD Table is done, so im confused why this error shows. image2362697 54.5 KB image18841072 177 KB

03
A
3 years ago

Badge winners

  • Qualified to Build Analyses
    xin.shihas earned the badge Qualified to Build Analyses
  • Celonis Certified Data Engineer
    laura.walku11has earned the badge Celonis Certified Data Engineer
  • Celonis Technical Expert
    alessio.vasinhas earned the badge Celonis Technical Expert
  • Qualified to Build Action Flows
    alessio.vasinhas earned the badge Qualified to Build Action Flows
  • Qualified to Manage Implementation Projects
    eric.gring11has earned the badge Qualified to Manage Implementation Projects
Show all badges
Terms & Conditions

ANY QUESTION?

Talk to an expert
FAQs
Contact us

MORE CELONIS

Academy arrow_outward
Documentation arrow_outward
Support Center arrow_outward
Partner Portal arrow_outward
Marketplace arrow_outward

CHECK US IN

LinkedIn
YouTube
Instagram
Facebook
Imprint
Disclaimer
Privacy Notice
Cookie Notice
Terms and Conditions
Celonis © 2024