Transform Source System Tables to Minimize Data Model Tables


In the last part of previous post Utilize N-M relationship between Activity and Dimension Tables, I said just mimicing source system’s table structure to data model is not useful for Celonis data model. Also I feel loading time to data model is exponentially long when one more table is added. So minimizing data model table is good practice especially for real time analytics.

In case of SAP Order to Cash (O2C) scenario, case table is sales order item (VBAP). From VBAP perspective, sales order header VBAK can fully utilize as additional dimension table because VBAK is joined with VBAP as 1 side. In PQL, just choose VBAK column as dimension is same effect as choose VBAP column.

On the other hand, schedule line VBEP table that is joined with VBAP as N side can not utilize like VBAK. As discussed in previous post, VBEP and Activity table are N-M relationship so VBEP table should be filtered (e.g. first row of VBEP). Schedule line is managing date / time of delivery, so I recommend to convert VBEP content to activity table. For example, first row of VBEP is normally original request from customer, so you will generate Request delivery date activity from VBEP table in Data Integration (Transformation). In the end VBEP table is not loaded to data model but you can utilize activity record. If you would like to handle order quantity with Request delivery date activity, quantity field is added to activity table then use it in PQL.

One more example, partner VBPA table is joined with VBAP as N side. It contains sold-to party, ship-to party, bill-to party and payer rows against sales order item. Those are useful as dimension, so I recommend to convert these rows to additional columns of VBAP in Data Integration.

image

As discussed above, transforming source system table structure to Celonis data model is quite important design topic. To do so, of cource you need to think about what KPI is measured then which tables / columns are required for analysis.

Kaz


See also