Skip to main content

Celonis Product Documentation

Transformation Templates across Multiple Source Systems

If you are using multiple source systems in the purchase requisition jobs, it could be that some systems require different logic to other systems, or that some systems do not have the same fields as others. This is completely fine, and there is a lot of flexibility in the event collection process to support this. Below are a few scenarios that might come up.

Scenario 1: Tables are missing in one source system compared to another

It is also very possible that one source system doesn’t contain the same tables as the other (think about cases where one source system is for Procurement and Production, and the other source system for Sales and Distribution). This is completely fine and can be resolved easily by:

  1. First, in the source system-specific data job, simply disable that transformation

  2. Then in the global jobs, remove (or do not add in the first place) the union all to that source system.

Scenario 2: Columns are missing in one source system compared to another

The third scenario is that one table has more fields than the other, for example, additional attributes specific to the business use cases of that source system. If this is the case, there are two possible approaches.

First approach:

  1. If the field is just an additional attribute and not used in other calculations/logic processing, you can use the SELECT * to ensure all possible columns of that table are available in the local jobs of the different systems.

  2. Then in the global jobs, rather than using SELECT * FROM …, you would instead select the required columns for that table from where they all exist, and in case they do not replace the table.field with NULL (for string) or 0.0 (for floats/ints). The NULL vs String is important in PQL later: if you are summing up that field and a NULL is present, then this can return strange errors.

   SELECT       
        'SOURCE_SYSTEM_ID_1' AS SOURCE_SYSTEM_ID
       ,"_CEL_MM_ACTIVITIES"."FIELD_1"       
        ,"_CEL_MM_ACTIVITIES"."FIELD_2"
       ...       
    FROM <%=DATASOURCE:SAP_ECC%>."_CEL_MM_ACTIVITIES" AS "_CEL_MM_ACTIVITIES"    

    UNION ALL

    SELECT
       'SOURCE_SYSTEM_ID_2' AS SOURCE_SYSTEM_ID
       ,"_CEL_MM_ACTIVITIES"."FIELD_1"
       ,0.0 AS "FIELD_2"
       ...   
    FROM <%=DATASOURCE:SECOND_SOURCE_SYSTEM%>."_CEL_MM_ACTIVITIES" AS "_CEL_MM_ACTIVITIES"

Second approach:

If the fields are required for logical processing and calculations, then this is a case where it makes sense to ‘Fork’ the template transformation. This would be done by copying the transformation to a regular task and then adding the specific logic required for those fields.

It would also be possible to then make this new transformation a template as well, such that it could be used in other source systems where the same logic is required, while the old template is still used in the other source systems.