Skip to main content

Celonis Product Documentation

Transformation Performance Optimization Tips

Every transformation (data preparation and cleaning) in the Celonis Event collection module is executed in the Vertica database. For each query submitted to Vertica, the query optimizer assembles a query execution plan, a set of required operations for executing a given query. This plan can be accessed and analyzed by running the EXPLAIN statement (Reading Query Plans).

When creating transformation queries, every choice has an impact on performance. Understanding the impact gives us the possibility to apply the best practices, avoid anti-patterns and improve overall query performance.

Taking into account other best practices (Best Practices: Transformation), the following complementaryguidelines for improving overall query performance should be considered:

A. Check table statistics

  • Very easy to implement and significantly improve query performance

  • Automatically added for all raw tables after extraction

  • For each temporary table, statistics should be created explicitly by adding the SELECT ANALYZE_STATISTICS('TableName')

  • Check if all relevant tables have statistics by running the EXPLAIN statement

B. Check temporary (auxiliary) tables:

  • Content - only necessary data, nothing more

  • Columns used for joins should be the first columns in create table statement (e.g. MANDT, VBELN, POSNR) or have an explicit ORDER BY clause at the end of the statement

  • Non-key, custom columns (e.g. Schema_id, source name, etc.) should not be placed at the beginning of the table (affects the sorting and later joins)

  • ANALYZE_STATISTICS ('TableName') statement added after the create table statement

C. Check the query statements validity

  • Avoid SELECT DISTINCT

  • Make sure that tables are joined properly (to avoid duplication / cartesian product)

  • Make sure that the entire key is used for joins (e.g., MANDT not ignored)

  • Join only necessary tables and check if all are really used

  • Revise the flow of logic, consider other approaches to achieve the same result

D. Reconsider view/ temp tables

  • Look for views with big complexity and try replacing them with a temp table (persisted results). It might affect APC and transformation time, but bring stability and overall faster transformation+data model load time

  • Consider implementing staging tables to enhance data model tables creation and loading

1. Update table statistics (Vertica: ANALYZE_STATISTICS)

Table statistics are analytical summaries of tables that assist the query optimizer in making better decisions. They provide information about the number of rows or value cardinality in the table.

For tables extracted using Celonis extractor (i.e., “Raw” tables), statistics are automatically updated for each table after the extraction. For custom or temporary tables created within transformations, it is necessary to create statistics explicitly.

To do that,add on the SELECT ANALYZE_STATISTICS ('TABLE_NAME'); after each "Create table" statement, and database will gather statistics in the background.

trans_optimiz_tip.png

Image 1: Example statements for creation of temporary table and statistics

One of the ways to check if all tables in certain queries contain statistics is by reading the query execution plan (EXPLAIN function). If there is “NO STATISTICS” next to a table name, the given table has no statistics. In that case, you need to add the SELECT ANALYZE_STATISTICS (‘TABLE_NAME’); statement after creating this table. Once you ran the statement and generated statistics you can check again using the EXPLAIN function.

Access Path:

+-STORAGE ACCESS for VBFA [Cost: 798, Rows: 7K (NO STATISTICS)] (PATH ID: 1)

Example of a query plan where a table “VBFA” does not have statistics

Two scenarios with corresponding query costs are shown in the screenshot below. In the first scenario, the O2C_VBFA_V temporary table is created without statistics, whereas in the second one, statistics have been created for the same table.

57541090.png
57541091.png

Another way to get a list of tables without statistics is by running the following query:

Check for tables without gathered tables statistics

SELECT anchor_table_name AS TableName FROM projections WHERE has_statistics =FALSE ;

If this query returns any result, you should go to transformation script that creates the respective table and add the SELECT ANALYZE_STATISTICS ('TABLE_NAME'); after the "Create table" statement, save the transformation, and database will gather statistics in the background.

Note

Table statistics are crucial for query execution plans with HASH JOINS. They enable the query optimizer to choose the smaller table to produce the HASH table (instead of the bigger one). In most scenarios, this prevents the “inner join did not fit into memory” error.

Table statistics additional read

2. Create well-designed temporary tables (Sort for Merge join)

The temporary, or auxiliary table in this article refers to any table that is created in addition to tables extracted from the source system (raw tables). Those temporary tables (e.g. TMP_VBAP_VBAK, TMP_CDPOS_CDHDR, etc.) are created in the transformation phase.

When creating temporary tables, one should think about the purpose of the table and its relations with other tables (joins). A well-designed temporary table:

  • Has a clear purpose, for example, to pre-process or join data, filter out irrelevant records, calculate certain columns that will be used later in transformations, etc. The sense of purpose should be validated, to confirm that the creation of temporary tables has a positive impact on overall performance.

  • Contains only necessary data (e.g. only really required columns and records)

  • Is sorted by the columns most often used for joins with other tables (e.g., MANDT, VBELN, POSNR). To ensure that, put those columns at the beginning of the create table statement or add an explicit ORDER BY clause at the end of the statement.

  • Doesn’t contain custom/calculated fields in the first columns. The first columns should be the ones that are used for joins (key columns). If this is not the case, Vertica will have to perform additional operations while running the queries, which will extend execution time.

  • Contains the Update statistics statement after the create table statement

60361560.png
57541087.png

Image 2: Inadequately designed temporary table (left) vs Well-designed temporary table (right)

Table Sorting

  • If the table is not explicitly sorted during the creation statement (by adding an “ORDER BY”), the table (projection) will by default be sorted by the first eight columns in the order defined by the creation statement.

MERGE and HASH joins

Vertica uses one of two algorithms when joining two tables:

  • Merge join- If both tables are pre-sorted on the join column(s), the optimizer chooses a merge join, which is faster and uses considerably fewer resources.

  • Hash join - If tables are not sorted on the join column(s), the optimizer chooses a hash join, which consumes more memory because Vertica has to build a sorted hash table in memory before the join. Using the hash join algorithm, Vertica picks one table (i.e. inner table) to build an in-memory hash table on the join column(s). If the table statistics are up to date, the smaller table will be picked as the inner table. In case statistics are not up to date or both tables are so large that neither fits into memory, the query might fail with the “Inner join does not fit in memory” message.

Vertica Projections

Unlike traditional databases that store data in tables, Vertica physically stores table data in projections. For every table, Vertica creates one or more physical projections. This is where the data is stored on disk, in a format optimized for query execution.

Currently, two projections are used in EMS:

  • Auto-projection (super projection) created immediately on table creation. It includes all table columns,

  • If the primary key is known (set up in the table extraction setting), Vertica creates additional projection based on it.

Additional information:

3. Join tables using the entire key

Alongside the creation of a well-designed temporary table, it is essential to make sure that two tables are adequately joined by the entire join predicates (keys). For example, the MANDT (Client) column is often unduly excluded from join predicates which can have a very negative impact on query performance, ignoring potential MERGE JOIN even when both data sets are properly sorted.

57541093.png
57541092.png

Image 3: The impact of using the partial and entire keys when joining two tables

4. Revise the necessity of all tables within the query

Users often join the same table more than once within one query. Even if the join is identical, it is executed multiple times, leading to increased execution time and risk of error. For example, while creating the Data Model table O2C_VBAP, beside the main case table (VBAP), temporary tables with essentially identical records are often needlessly joined (e.g TMP_VBAP_VBAK, _CEL_O2C_CASES). Usually, the idea behind those redundant joins is filtering or adding some calculated columns (e.g., converted net value).

Best practice:

  • Evaluate the business needs and only use necessary tables and columns

  • Revise the flow of logic and consider alternatives to achieve the same (e.g. by using smaller tables such as original (raw tables)

  • In situations where joining tables with very similar data (e.g. VBAP with TMP_VBAP_VBAK) is unavoidable, make sure that join is ofMERGEkind.

5. Evaluate the impact before replacing temporary or Data model tables with a view

Using views instead of tables might negatively impact transformations and data model load performance.While a temporary table stores the result of the executed query, a view is a stored query statement that is being executed only when invoked.

Each option has advantages and disadvantages and making the right choice highly depends on the table size and structure and query complexity. While replacing a table with a view reduces required storage (APC), it could negatively affect the overall performance and significantly increase total execution time. If the query contains complex logic in terms of types of joins and conditions, the best practice will most likely be the creation of a well-designed temporary table, by fully taking into account previously described recommendations. If the query simply selects all the records from some raw table (e.g. VBAP) or applies simple conditions, a view might be a more reasonable choice. All things considered, one should analyze the specific scenario, ideally, test both approaches and choose the one which is more performant in terms of total transformation and data model load time.

5.1 Implement staging tables for Data model loading

For tables that are being loaded into the Data Model, the best balance in terms of required storage and performance of the entire pipeline is to combine the temporary table (i.e. Staging_table) with a view, as shown on the diagram below.

The statement (example below) that creates the staging table should include all the joins and functions required to calculate or reformat the column values. The staging table should contain only the primary keys and calculated/derived columns.

Subsequently, a data model view is created (e.g. O2C_VBAP), which joins the “raw data” table (i.e. VBAP) with the transformed data table (i.e. O2C_VBAP_STAGING). This approach allows us to achieve better performance without significantly affecting the APC.

57541094.png

Staging table statement example

CREATE TABLE "O2C_VBAP_STAGING" AS
(
        SELECT

        "VBAP"."MANDT", --key column
                "VBAP"."VBELN", --key column
                "VBAP"."POSNR", --key column 
        CAST("VBAP"."ABDAT" AS DATE) AS "TS_ABDAT",
        IFNULL("MAKT"."MAKTX",'') AS "MATNR_TEXT",
        "VBAP_CURR_TMP"."NETWR_CONVERTED" AS "NETWR_CONVERTED",
        "TVRO"."TRAZTD"/240000 AS "ROUTE_IN_DAYS"
        FROM "VBAP" AS "VBAP" 
        INNER JOIN "VBAK" ON 1=1
            AND "VBAK"."MANDT" = "VBAP"."MANDT"
                    AND "VBAK"."VBELN" = "VBAP"."VBELN"
                AND "VBAK"."VBTYP" = '<%=orderDocSalesOrders%>'
        LEFT JOIN "VBAP_CURR_TMP" AS "VBAP_CURR_TMP" ON 1=1
            AND "VBAP"."MANDT" = "VBAP_CURR_TMP"."MANDT"
                AND "VBAP"."VBELN" = "VBAP_CURR_TMP"."VBELN"
                AND "VBAP"."POSNR" = "VBAP_CURR_TMP"."POSNR"  
                LEFT JOIN "MAKT" ON 1=1
                AND "VBAP"."MANDT" = "MAKT"."MANDT"
                        AND "VBAP"."MATNR" = "MAKT"."MATNR"
            AND "MAKT"."SPRAS" = '<%=primaryLanguageKey%>'
            LEFT JOIN "TVRO" AS "TVRO" ON 1=1
                        AND "VBAP"."MANDT" = "TVRO"."MANDT"
                        AND "VBAP"."ROUTE" = "TVRO"."ROUTE"
);

Data Model view statement example

--In this view the only one join, between the raw table (VBAP) and the staging table (O2C_VBAP_STAGING) is performed. All other joins, required for calculation of the fields should be part of the STAGING_TABLE statement

CREATE VIEW "O2C_VBAP" AS (
        SELECT
        "VBAP".*, 
                "VBAP_STAGING"."TS_ABDAT",
                "VBAP_STAGING"."MATNR_TEXT",
                "VBAP_STAGING"."TS_STDAT",
                "VBAP_STAGING"."NETWR_CONVERTED",
                "VBAP_STAGING"."ROUTE_IN_DAYS",
        FROM "VBAP" AS "VBAP" 
        INNER JOIN "O2C_VBAP_STAGING" AS VBAP_STAGING ON 1=1
            AND "VBAP"."MANDT" = "VBAP_STAGING"."MANDT"
                    AND "VBAP"."VBELN" = "VBAP_STAGING"."VBELN"
                AND "VBAP"."POSNR" = "VBAP_STAGING"."POSNR"
);