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.

  • 809 Topics
  • 2,124 Replies
809 Topics
marcuszandonellajellen.marc
Level 2
marcuszandonellajellen.marcLevel 2
asked in Data Ingestion & Infrastructure
Number of records per table (IBC Vertica)

Hello Celonis community,I want to determine the number of data records in vertica for all tables of a schema (SAP System).The easiest way should be via the system table tablesso I get all tables of a SAP connection, which also worksselect * from tables where table_schema = XYZbut unfortunately the following does not work:select t.table_name,(select count(1) from t.table_name) as table_row_countfrom tables as t where t.table_schema = 'XYZ-> Execution error: Not allowed to access schema tthe other way via a system table does not work reliably:SELECT table_schema,table_name,SUM(row_count) AS row_countFROM (SELECT table_schema,table_name,NVL(CASEWHEN NOT is_segmented THEN (row_count / node_cnt)::INTELSE row_countEND, 0) row_countFROM (SELECT t.table_schema,t.table_name,ps.projection_name,(SELECT MIN(is_segmented)FROM projections pWHERE p.projection_name = ps.projection_nameAND p.projection_schema = ps.anchor_table_schema) is_segmented,node_cnt,SUM(ps.row_count - NVL(dv.deleted_row_c

00
A
Level 2
M
Level 2
minakshi.malliLevel 2
asked in Data Ingestion & Infrastructure
Need to calculate time spent on Credit hold activity

Need to calculate time spent on Credit hold activity for order to cash process and populate a column of OLAP table but credit hold activity is denoted by 2 names in activity table 1)Set Credit Hold 2)Set Default Credit Hold.What should be the formula for thisI have used formula(CALC_THROUGHPUT(FIRST_OCCURRENCE[Set Default Credit Hold] TO LAST_OCCURRENCE[Release Credit Hold], REMAP_TIMESTAMPS("_CEL_O2C_ACTIVITIES".EVENTTIME, HOURS)))But I need to put below condition as well for same column(CALC_THROUGHPUT(FIRST_OCCURRENCE[Set Credit Hold] TO LAST_OCCURRENCE[Release Credit Hold], REMAP_TIMESTAMPS("_CEL_O2C_ACTIVITIES".EVENTTIME, HOURS)))

20
M
Level 2
G
Level 3
guturi.rajdeLevel 3
asked in Data Ingestion & Infrastructure
Healthcare Related Connectors

Hi, Are there any process connectors, data model and analysis in healthcare domain or pharmacy related ?

10
.
Level 3
R
Level 3
rahul.bhat11Level 3
asked in Data Ingestion & Infrastructure
Change Database connection for a table via Python

Hi Team, I am currently working on auto-assignment of the DBConnection to the table all at once in the front end via the python code. But i am not getting any functions which can help me with that. I am able to connect to the datamodel, fetch the table ,table data and change the table names too. The only thing i am lacking is to change the DBConnection of everytable from python. I could analyze from the data of the table fetched via python that each DBConnection has their own DBConnectionID , but i am unable to change that ID from python. Please let me know if that change is possible, if so, how to do it. Thanks in advance.

00
R
Level 3
M
Level 3
masha.galitLevel 3
asked in Data Ingestion & Infrastructure
Connecting a second SAP client

We have one SAP client connected already. Now we want to connect the second one, the same process (O2C). In this other client we have a separate business division, which we want to analyze separately. Current already existing analyses shouldnt be influenced by this new data load. We will probably also want to have some analyses to compare the business divisions, but we will definitely need to be able to easily create analyses separately. Will appreciate any guidance a lot. I will outline below my understanding and will appreciate comments to it. I understand that first of all we need to create the new connection. And from this instruction we only need to perform steps 1 and 6, because the server part is already there and it is not SAP client specific. Then, for the moment, since we have a data load running every night, I will add a filter on the case table (VBAP) to only take data from the first client (MANDT = CLIENTNUMBER). Because, if I understand correctly, otherwise with the next

00
maximilian.hoffm13
Level 3
S
Level 3
soham.potdaLevel 3
asked in Data Ingestion & Infrastructure
Conformance Checker

Hi Team,We are working with the conformance module in Celonis. I see an error msg popping in all my components saying that Your model contains duplicate activities. Activity A occurs in the model 2 times, Activity B occurs in the model 3 times Please try to create a model without duplicate activities as it may cause severe performance issueIn my reference model, I have used duplicate activities however they are a part of different sequence flows (e.g. two different paths after split using exclusive gateways) and also for some instances the activities can happen again in the sequence flow.Can you help me understand this error msg in detail and how exactly does it affect the performance issues?Thank you in advance!Soham Potdar

70
S
Level 3
S
Level 2
shiva.ponnaLevel 2
asked in Data Ingestion & Infrastructure
Error: Table “EKPO” is not a child of table “EKBE” - Three way match formula - PU_ function

Hi Team, I am trying to use standard Three way match formulas from App store. It is showing the below error message: Pull-Up-Function could not be executed. Table EKPO is not a child of table EKBE. Use a non Pull-Up-Function instead. In the data model, the tables EKPO (case table) and EKBE are joined on the columns MANDT, EBELN & EBELP. Could you please suggest what could be the cause of the issue?

20
D
Level 2
S
Level 3
soham.potdaLevel 3
asked in Data Ingestion & Infrastructure
Scatter Plot | Visualizing more than 2 KPIs

Hi Team,We want to visualize three KPIs corresponding to a particular dimension. We are thinking of achieving this in terms of the horizontal and vertical axis of a scatter plot (2 KPIs) and the third being the bubble size.I wanted to know whether there is a way to increase the size of the bubbles based on additional KPI in Scatter plot (other than the ones specified in horizontal and vertical data series) ? If not, is there a way to look at additional KPIs in scatter plot when you hover over the bubbles ?Thanks in AdvanceSoham Potdar

60
T
Level 2
.
Level 2
.14600952Level 2
asked in Data Ingestion & Infrastructure
“Cancelled Invoice” activity

Dear Celonis Community I am trying to prepare for Accounts Parables an analysis which would show the cancelled invoices. in my process explorer i dont have a dedicated activity Cancel Invoice How would you ensure to show them, what specific filters would you apply? Thank you Tatiana

00
C
Level 1
U
Level 2
urszula.jesseLevel 2
asked in Data Ingestion & Infrastructure
Operation Guide for IBC

Hallo, I am looking for the operation guide for IBC Celonis. I have found the operations guide for 4.3 but nothing for the cloud. I am looking for the solutions for SSO, Monitoring Solutions, Version control etc. There are some help pages about SSO but really rudimentary. Is there any pdf/documentation where I can find the topics. Many thanks in advance. Greetings, Ula Jessen

00
V
Level 1
A
Level 1
alfonso.fabraLevel 1
asked in Data Ingestion & Infrastructure
ARiBA DATAMODEL & PROCESSES

Hi to everyone, Im looking for info (links, books, Excel Models and/or counsels) about ARIBA Data model, for training pourposes. Im interested in Tables and Fields of the transactional Core in order to training a Model. Thanks in advance to anyone that could help Alfonso Fabra Nodotic (Spain).

00
C
Level 1
maximilian.hoffm13
Level 3
maximilian.hoffm13Level 3
asked in Data Ingestion & Infrastructure
Data Permissions - Ideas for 0-to-Many relationships

Dear Community, Im currently working on setting up data permissions on IBC. They work as excpected, but that is somehow a"problem" as well. Heres an example of my data model: grafik835223 5.33 KB The two tables are linked via two columns, a company code and an event id. Additionally, the two tables have a 0-to-many connection, meaning that there can be cases without a connection to the events table, and vice versa. I want to set up data permissions based on the company code. As far as I understand (and validated during testing), data permissions are always AND connected, and the tables are always inner-joined This means, when I set up data permission on column cases.company code, I will not be able to see the events x,y,z, because theres no respective case for them. Vice versa, setting up data permissions on events.company code, the will not be able to see case id 4, because theres no link either. Setting up data permissions on both tables will not lead to any other result du

10
C
Level 1
.
Level 2
.14600817Level 2
asked in Data Ingestion & Infrastructure
Question Marks in SQL Code (ID-column)

Hello dear community,when creating my event log, question marks appear unexpectedly in the ID column. This makes it difficult to write the code. Can anyone help me what this might be due to?Thanks for any help!!

10
.
Level 2
S
Level 3
soham.potdaLevel 3
asked in Data Ingestion & Infrastructure
Rounding week aggregation

Hi Team,We are currently trying to use round_week() function as a dimension. However, I see the function would aggregate the week values starting from Monday to Sunday as a week.Is there a possibility where I can represent the week to start from Saturday and end on Friday and aggregate the weekly values as per the same?Thanks in AdvanceSoham Potdar

20
S
Level 3
P
Level 1
praveen.kumar47Level 1
asked in Data Ingestion & Infrastructure
Rounding Time Unit based on Custom date slice

Hi ,I would like to know if there are any provision to consolidate the date for the specific date slice to use in the dimensions . For Eg 5 days or 3 days custom grouping etc .I require the functionality similar to ROUND_WEEK() to group date but i should be able to group based on the custom logic of 5 days or 3 days etc for the date range .Thanks ,Praveen

10
C
Level 1
S
Level 3
soham.potdaLevel 3
asked in Data Ingestion & Infrastructure
ML Workbench Data Pull

Hi Team,In the ML workbench, we are trying to pull data of a component from a particular analysisThe code that we are using is not able to pull data from some particular analysis. However, if we use the same code to pull the data from a different analysis belonging to the same workspace we are able to pull the data.Are there any Analysis settings that we need to look into for the data pull that might be causing this error.We are getting the following error while pulling the data from the analysis.image571669 70.7 KB

00
S
Level 3
.
Level 2
.14600940Level 2
asked in Data Ingestion & Infrastructure
Connect 1 table to 2 different tables

How can I connect 1 table( that connects all recored time) to two different tables? We have a ServiceNow Timecard table which stores all worked time on a task. But time can be spend on a request record as well as the underlying request task record. Im intressted to know how much time is spend for an request (taking time worked into account for the request record as well as the task record for the request) image1199680 43.2 KB The error i receive is: Data model load failed during load to engine: Could not load data model. Load failed. Error Message: Cycle in table joins detected. Cycle is within these tables: [sc_req_item, sc_task, time_card]. Celonis Support ID: ecb1aec8-c71d-42e8-bd91-41927de8ac51 It looks like Celonis is supporting a multiple table connection (I can make 2 different key relationships). Is this correct?

10
rita.kiss12
Level 4
D
Level 2
dom.tLevel 2
asked in Data Ingestion & Infrastructure
CASE WHEN for multiple conditions

Hi team,i am trying to write a CASE WHEN statement for a process explorer to show only the activities that satisfy a dropdown filter based on multiple selections.Sample dataset:(Case ID, Activity, Source,)Case1, Activity 1, Screen1,Case1, Activity 2, Screen1,Case1, Activity 3, Screen2,Case2, Activity 2, Screen1,Case2, Activity 4, Screen1,Case1, Activity 5, Screen3I would like to be able to filter in a custom process explorer the activities only from a particular screen (e.g. filter dropdown: Screen1 and Screen 3> only Activities 1, 2, 4 and 5 are shown on the process explorer).My current query doesnt allow me to select multiple conditions, and doesnt work with the IN function as well:CASE WHEN BOOKS_USER_05JUN.SOURCE LIKE <%=Source_Process%> THEN BOOKS_USER_05JUN.ACTIVITY ELSE NULL ENDBest,Dominic

70
A
Level 2
.
Level 2
.14600841Level 2
asked in Data Ingestion & Infrastructure
Touchless Sales Order Item count

Hi Team,I am trying to calculate the touchless order where have defined in dimension1)COUNT_TABLE(VBAP)in KPI section have defined SUM(CASEWHEN (PU_COUNT(VBAP,"_CEL_O2C_ACTIVITIES".USER_TYPE,"_CEL_O2C_ACTIVITIES".USER_TYPE IN (<%= AutoTypes %>)AND _CEL_O2C_ACTIVITIES.ACTIVITY_EN IN (<%=AutomationActivities%>))= PU_COUNT(VBAP,"_CEL_O2C_ACTIVITIES".USER_TYPE, _CEL_O2C_ACTIVITIES.ACTIVITY_EN IN (<%=AutomationActivities%>)))THEN 1.0ELSE 0.0where AutomationActivities and AutoTypes defined in varibaleAutoTypes has only A which is manual so ideally i want count of records which are manually done and will be calculating touchless based upon the below logictotal number of SO item-(KPI logic- count of manual orders)But am not able to get the count , could you please check and help me on the same.

50
A
Level 2
.
Level 4
.14600775Level 4
asked in Data Ingestion & Infrastructure
Static Variables

Hi All,I would like to use a static variable for a % calculation, so that when filters are applied, the denominator will stay static. The issue that i am facing is that i would like the static variable to be static based on the local market and not the sum for all local markets.Such as below:Please let me know if this is possible using the static variable, and if not, please advise if there is another way i can achieve this.All the best,Anna

30
D
Level 2
D
Level 1
daphne.van.lLevel 1
asked in Data Ingestion & Infrastructure
Export analysis via PyCelonis with CPM4.4

I have a question regarding the usage of PyCelonis for a single user account with Celonis 4.4. In the documentation it says that PyCelonis allows for a connection with the IBC and CPM4.5.Are there other options for the single user license to extract the analysis details in python?

00
C
Level 1
S
Level 3
soham.potdaLevel 3
asked in Data Ingestion & Infrastructure
Count if the instances of Activity A are followed by other activity within a particular scope

Hi Team, We are trying to identify and associate flag to a particular activity based on whether there were some other set of activities post it or not till the next instance of the activity. So for example we want to associate a flag to Activity A if in a case journey activity B, C, D or E are occuring any time between the next occurence of Activity A within the case. We intend to track this for each instance of activity A taking place within a case and associate a flag whether the activities B,C, D or E took place or not in an OLAP table. We are looking for any way in which we can set the scope from the instance of activity A taking place till its next instance and check within this scope whether the other activities took place. Thanks in Advance. Soham Potdar

50
C
Level 1
H
Level 2
holger.meissLevel 2
asked in Data Ingestion & Infrastructure
Strange Results in Ratio KPI

Hello,ive the following issue:The PQL Formula Ratio of Cases flowing through a dedicated activity is used to determine the % of cases that do contain activity Remove Delivery Block. Putting this formula into a number element - works fine. Shows 20%.The same 20% appears when the activity Remove Delivery Block is marked in the process explorer.In my example i have 340 cases, where 68 do have Remove Delivery Block.The 340 cases to have 4389 activities in total, the 68 cases having the acitvity Remove Delivery Block do have 1229 activities.If the KPI Ratio is put into an OLAP Table and shown with dimensions like Company or Sales Org the Ratio moves from 20% to 28%. I figured out that the formula is changing from a case % (68 out of 340) to an activity related calculation (1229 out of 4389) - this does always happen if any characteristic that is joined to the activity table is shown in the OLAP Table.In addition - the same applies - the other way around if sum(vbap.net_order_value) is takin

10
D
Level 2
C
Level 3
chanti.pLevel 3
asked in Data Ingestion & Infrastructure
Count of distinct & non distinct

Hello Everyone,I need some assistance on the below.Im using a table(Tab1) with 2 million records. Im looking for Purchasing Document Number (EBELN) which has more than 1 entry and i need the count of Unique Document Numbers and the count of Documents having more than 1 entry.Im using the below Case statementCASE WHEN COUNT (DISTINCT Tab1. EBELN) > 1 THEN 1.0 ELSE 0.0 ENDthis gives me 1 if the record is unique and 0 if not unique.I need the total count of uniques & non uniques. When i try the below im getting an error saying Aggregate expressions cannot be nested.SUM ( CASE WHEN COUNT (DISTINCT Tab1. EBELN) > 1 THEN 1.0 ELSE 0.0 END )Appreciate the assistance.Thanks!!Regards,Chanti

20
C
Level 1
.
Level 4
.14600833Level 4
asked in Data Ingestion & Infrastructure
CALC_CROP for multiple activities

Hello everyone,i was wondering whether CALC_CROP functions with PU_FIRST or PU_LAST.what I want is to count a number of specific activities between the process start to one of the other activities (whichever of them comes first);so i would like to use smth likePU_COUNT(CASES, ACTIVITIES.ACTIVITY,ACTIVITIES.ACTIVITY IN (A,B,C,D) ANDCALC_CROP(CASE_START TO PU_FIRST(CASES,ACTIVITIES.ACTIVITY, ACTIVITIES.ACTIVITY IN (E;F;G)), ACTIVITIES.ACTIVITY)=1))however it doesnt work. Any ideas?Best,Maria

20
V
Level 1

Badge Winners

  • Celonis Technical Expert
    Clarahas earned the badge Celonis Technical Expert
  • Solution Creation Expert
    Clarahas earned the badge Solution Creation Expert
  • Qualified to Build Views
    Clarahas earned the badge Qualified to Build Views
  • Celonis Certified Data Engineer
    dhruv.suvar11has earned the badge Celonis Certified Data Engineer
  • Value Realization Expert
    julia.portehas earned the badge Value Realization Expert
VIEW ALL
Community Code of Conduct