Skip to main content

Celonis Product Documentation

Replacing User IDs

You can use PQL to display the user name and / or email of Celonis Platform users, replacing the user IDs that are stored in augmented tables for components such as tasks, comments, and augmented attributes. By replacing the user ID, your app users can then see information such as the name of the task creator, who an assignee is, and which user made the latest updates to augmented atributes.

To replace the user IDs with user name and / or email address, you need to pull the desired data into a Data Pool. The data can then be added to a table in a Data Model, with the newly created table used to reference the required table using a LOOKUP operator.

An example of the LOOKUP operator in use:

lookup.png

To replace the user IDs, complete the following sub-tasks in order:

1. Creating an application key

The first task is to create an application key and assign it data permissions. This application key will be used to configure your data connection in a later task, granting the data connection permission to extract information from your Celonis Platform team.

  1. Click Admin & Settings - Applications.

  2. Click Add New Application and select Application Key.

    creating_an_application_key.png

  3. Add an application key name (an internal reference) and click Save.

  4. Copy the application key displayed for further usage. It will not be visible later for security reasons, so you will need to create another application key if you misplace the key itself.

  5. Click Permissions and edit Team permissions.

    edit_key_permissions.png

  6. Assign the following permissions to the newly created application key.

    • Use Login History API

    • Use Studio Adoption API

    • Use User Group Info API

  7. Click Save.

    assign_permissions.png
2. Creating a data connection

You now need to create a data connection within a Data Pool, enabling you to read information from your own Celonis Platform team. This requires the application key you created in the previous sub-task.

To create a data connection:

  1. Click Data - Data Integration and open the Data Pool you want to use.

  2. From the Data Pool diagram, click Data Connections - Add New Data Connection.

    add_data_connections.png

  3. Select Add Data Source and then search for and open Celonis Platform adoption.

    celonis_platform_adoption_search.png

  4. Configure the data connection using the following fields:

    • Name: An internal reference only.

    • API URL: Use the following format, replacing the team and cluster information from your team URL.

      https://<celonisteam>.<cluster>.celonis.cloud

    • AppKey: Paste the application key created in the earlier steps.

    configure_data_connection.png

  5. Click Test Connection and once a confirmation message appears, click Save.

    test_data_connection.png
3. Creating a data job with extraction and transformation tasks.

You now need to create a data job with both extraction and transformation tasks. By doing this, you're extracting data from your own Celonis Platform team and then transforming that data into readable information for yourself.

To create a data job with extraction and transformation tasks:

3a. Creating data jobs with an extraction task

  1. From your Data Pool diagram, click Data Jobs.

  2. Add a data job name (an internal reference only), select the data connection created in the previous task, and then click Save.

  3. Click Add Extraction.

    add_extraction.png

  4. Add an extraction task name (an internal reference only), select Visual Editor, and click Save.

    new_extraction_task.png

  5. Click Add Extraction.

    add_extraction_table.png

  6. Select 'celonis_members' and click Save.

    select_celonis_members.png

  7. Click Configure.

    click_configure.png

  8. Review the existing configuration, selecting additional data fields you'd like to use in your Views. Click Confirm to save the configuration.

    confirm_configuration.png

  9. Click Save and then return to your data job overview.

    return_to_data_job.png
3b. Creating and executing transformation task

You now need to create the transformation task:

  1. Click Add Transformation.

    add_transformation.png

  2. Add a transformation task name and click Save.

    new_transformation_task_name.png

  3. Enter and execute the following queries in order (you may need to click Save to access the Execute button)

    • Select members query:

      select * from celonis_members

    • Seperate name and email addresses query:

      DROP VIEW IF EXISTS "USER_LIST";
CREATE VIEW "USER_LIST" AS
SELECT
userID AS ID,
REGEXP_REPLACE(nameAndEmail, '.*\((.*)\)', '\1') AS EMAIL,
TRIM(SPLIT_PART("nameAndEmail",'(',1)) as NAME,
MU._CELONIS_CHANGE_DATE as _CELONIS_CHANGE_DATE,
MU.epoch as epoch
FROM "celonis_members" AS MU
ORDER BY ID, EMAIL;

    • Select from user list query:

      SELECT * from USER_LIST
    enter_command_1.png
4. Executing the data Job

After creating the data job with extraction and transformation tasks, you can now manually execute (or run) the data job:

  1. From the data job screen, click Options - Execute Data Job.

    options_execute.png

  2. Select Full Load and then click Execute Selection.

    execute_data_job_full_load.png

    The data job will now run, with the status displayed.

    successful_execution.png
Optional: Scheduling future data job executions

Schedules are used to automatically execute data jobs on a predefined basis, allowing you to control the day, time of day, and repetition of your executions. You may want to schedule your newly defined data job, ensuring that the user information is frequently updated.

When scheduling your data jobs, you have two options:

5. Add a new table to the Data Model

Now that a data job is defined with the required extraction and transformation, the next step is to load the table obtained into the Data model.

  1. Select the User_list table.

  2. Click Finish.

choose.png
6. Using Lookup operator to retrieve user name and email in place of UserId

Now that a new unrelated table is added to the Data Model, an augmented table is created in the KM whenever tasks, augmented attributes, and comments are created.

When tasks are created and assigned to someone (a user ID), you can replace the user ID in the augmented table using the LOOKUP Operator. This fetches the matching name and email from the table for that user ID

The PQL syntax to do this is:

**T1 - Augmented table , T2 - New unrelated table added to the DM

LOOKUP(
    "T1",
    "T2"."nameAndEmail",
    ("T1"."ASSIGNEE_ID", "T2"."USERID")
)

And an example of this:

lookup.png