Skip to main content

Celonis Product Documentation

Transformations

Transformations are used to create event logs from the extracted data which are used by other cloud applications like Process Analytics. They also clean up, restructure and process data to be suitable as additions to the data model. Example for the latter are currency conversions, joined tables or name mapping (note: to configure that, check out the tab in the data model).

Transformations are written in SQL code. The SQL dialect to be used depends on the underlying database technology (see Environments below).

Environments
Celonis Execution Management System (Data in Cloud)

With "Data in Cloud" we use the Vertica database technology. This means that the transformations have to be written in Vertica SQL syntax which is very similar to PostgreSQL. There are important differences between the Vertica SQL and other SQL dialects with regard to writing SQL scripts to create a data model.

Here you can find the official documentation: https://my.vertica.com/docs

Celonis Execution Management System (Data in Place)

In a hybrid environment we use MSSQL (by Microsoft) or HANA (by SAP). Transformations therefore have to be written in SQL Server or HANA syntax respectively. For details on the syntax, please read the public documentation:

Interface
10944680.png

  1. Transformations contain two tabs: In the editor tab you can modify the contents of the transformation and in the parameter tab you can add and modify parameters (see the parameter section for details).

  2. The schema explorer displays all the available tables which were either extracted from data connections or they have been created in transformations. You can expand each table to see the available columns. You can then click on a column to insert it together with the table into the SQL editor on the right.

  3. The buttons on the top right of the schema explorer allow you to:

    1. Reload Schemas: The updates the schema explorer and shows the currently available tables. If an extraction happens while a transformation is open or if you create or delete a table within a transformation window, you need to click this button in order to update the schema explorer.

    2. Expand all: Shows all the columns for all tables.

    3. Collapse all: Hides the columns for all tables.

  4. The parameter section on the bottom left shows all available parameters of this transformation. By clicking on them you insert them with the correct syntax into the SQL editor on the right. The parameter list displays two pieces of information:

    1. The visibility type: private if both are grey, public if the "P" is highlighted and dynamic if the "D" is highlighted

    2. The data type is indicated on the right

  5. The toolbar on the top allows you to perform specific operations with regard to the content of the SQL editor:

    1. Undo: You can undo the last change in the SQL editor.

    2. Redo: You can redo a change in the SQL editor (revert from an undo).

    3. Execute: You can execute selected lines. Please note that this button is only available if a selection is made in the SQL editor.

    4. Export: The complete transformation script can be exported as a .sql file to the hard drive.

  6. The SQL editor contains the SQL statements that should be executed when the transformation is executed.

  7. The output panel shows results of queries that are executed within the transformation with the "Execute" button.

  8. Saving exists the transformation and saves the contents of the SQL editor.