Does anybody know how to add sequential number to OLAP table?

Hi @jumbo.szk,

I think what you're looking for is INDEX_ORDER (celonis.com). Be aware that this index is generated based on the source data and thus not linked to the source data. If you add new data to your model, it is possible that Line #3 now becomes Line #4. However, it should help in real-life disussions.

Kind regards,

Jan-peter

Hi,

To get exact same table but with indexes would be:

1) getting table from celonis with Pycelonis:

celonis = get_celonis(url='<URL>', key_type='APP_KEY', permissions=False)

datamodel = celonis.datamodels.find('<DATA_POOL_ID>')

q = pql.PQL()

q += pql.PQLColumn("Table", "column1")  

q += pql.PQLColumn("Table2", "column2") 

df = datamodel.get_data_frame(q, chunksize=10000)

df= dfreset_index() # make sure indexes pair with number of rows

df

2) using code, that should add index column

df[df.index.name] = df.index

3) pushing it back with

data_pool.create_table(table_name=<old_table_name>,df_or_path=df,if_exists="drop")

I know that you don't have to write same PQL queries: probably you can get whole OLAP table with single command, but I didn't test that, link: https://celonis.github.io/pycelonis/1.7.1/reference/celonis_api/process_analytics/analysis/#celonis_api.process_analytics.analysis.BaseAnalysisComponent.data

I though about using LAG/LEAD functions instead of index_order proposed by Jan-peter, or doing it in SQL but I cannot find clean, and easy solution for such problem.

The Index_Order is generated on the table in the data model, and is therefore not affected by filters in the analysis. Therefore, setting the rank in the data pool/model itself using SQL/Python wouldn't be of additional value in my opinion, especially if you add the rank as a record in your Knowledge Model using the PQL statement (it can then be reused in analyses).

If you want to do this in SQL however, Vertica's Rank/Row_number can be used (see RANK [Analytic] (vertica.com)). However, in all situations (also in Python), the index might shift if new data is added or removed, since it just iterates over rows. This can be partly helped by set a order (on date for example), but still then it shifts if earlier rows are removed.

Thanks for your reply, I understand current Celonis functionality has number but those will be effected by sorting and filtering, those are not autmatically re-calculated in OLAP table.

(plz let me know if im wrong.)