Extractor Builder Basics

Basics

Welcome!

Welcome to the Extractor Builder Basics course! This course will endeavor to teach you all you need to work with the Extractor Builder. After completing this course, you will be able to:

Understand the Extractor Builder’s capabilities. Know when to use the Extractor Builder. Be able to build and customize an extractor based on Application Programming Interface (API) specifications.

This course will take you about 40 minutes to complete.

Prerequisites: This course assumes you have a basic understanding of APIs. A free online resource such can suffice to bring you up to speed.

Let’s get started!

Personal Training Environment Required

This course contains hands-on exercises that require a personal Celonis Training Environment, also called a "Celonis Team".

Not sure if you have one? Click below and we'll either create one for you or show you how to access your existing Team if you have one. Please make sure to disable adblockers on this page if the button is not working for you.

Check / Create my training environment

Basics The Basics What it is

The Extractor Builder is a tool to set up an extractor from scratch or customize an existing one, using a low-code guided user interface that relies on source system REST APIs (what are REST APIs?).

The prerequisites

Here is a list of criteria needed for you to be able to connect to a source system using the Extractor Builder.

Your system should expose REST APIs that are reachable from the Celonis platform. The source API should use GET or POST requests (REST APIs, OData standard (V2 and V4), GraphQL). Note that POST requests are mostly for the GraphQL standard and meant to read data. The API expects basic, bearer, OAuth, or API Key authentication. If necessary, you can also add custom authentication methods. (Check out this resource for a primer on authentication types and their differences.) The API response is in JSON or XML format. The API is synchronous - meaning it receives a response immediately after sending a request, without needing to perform other tasks in the meantime. The API expects pagination as one of the standard methods: Page by Page, Limit and Offset, Next Page URL in Response, Next Page Token in Response, Web Linking, Custom Parameter Incremental, or no Pagination. When to use it

There are two general scenarios when you should consider using the Extractor Builder:

No existing extractor: There is no existing extractor for your source system and your source system allows access to source data via REST APIs. Customize existing extractor: There is an existing extractor but you would like to customize* it to your needs. You could for example: add new endpoints to retrieve additional data add columns to existing endpoints refine other authentication or endpoint configurations.

*Note this is only possible for extractors built using the Extractor Builder.

Keep in mind the Data Ingestion API as a parallel option when there are no out-of-the-box extractions. It generally is the preferable options for data tools, or data lakes such as Informatica, Talend, Snowflake and so on, where a push of data is preferable over a pull of data as is done with extractors.

Where to start

The entry point to create an extractor is in the data connection selection under "Create custom REST API Extractor".

On the next screen, you have one of three options:

Create from Template: This is to re-use an existing Celonis Extractor built using the Extractor Builder. Import: This is if you exported a custom Extractor from another data pool or Celonis Team and want to re-use it. Create from Scratch: This is really if you want to start from scratch and build something new.

Basics The Extractor Builder User Interface

The Extractor Builder's user interface works in a guided fashion from top to bottom to help you build extractors step by step.

Click on the tabs below to get an understanding of each area’s function.

Variables

Define reusable values for your extractor that are configured for every data connection. For example the API URL or other important connection variable.

Authentication Data Connection Endpoints

Watch the video on the next page for a quick run through of building a simple extractor.

Basics Extractor Configuration vs Extraction Configurations

An important point to remember is the difference between an extractor configuration and an extraction configuration.

Extractor Configuration

Where: In the Extractor Builder.

Purpose: Define how to extract data and the general scope of which tables can be extracted.

Prerequisite: Source system REST APIs

Extraction Configuration

Where: In data jobs.

Purpose: Define what data to extract and when to extract it using schedules

Prerequisite: An existing Extractor

As you build extractors, this distinction will quickly become clearer.

Basics Working with Extractor Templates

Before jumping into the tool yourself, it’s important you also understand what happens when you create or customize extractors.

Custom Extractor Templates

When you create a new extractor, it becomes a template you can re-use within your Celonis environment. When you add a system connection to an extractor, the connection is tied to the template. Any changes you make to the template will affect the connections linked to it.

Note that custom templates are data pool-specific. If you want to re-use a custom template in another data pool or Celonis environment, you can use the export / import function.

Celonis Extractor Templates

If you add a system connection to a Celonis-built template, then your connection is tied to this template and receives all the Celonis updates. If you want to customize this Celonis extractor, then a copy of the template gets created and your connection is now based on a separate custom template.

In other words, youcan only customize custom templates.

Editing a connection’s template

To edit a connection’s extractor template, simply click the menu button (the three dots button) and select customize.

If the template is Celonis-built, you will receive a prompt to create a copy. By creating a copy, you create a custom template that will no longer receive automatic Celonis updates.

Connections are not part of the template but you create a connection based a template.

Basics Exercise - Configure a Simple Extractor

It’s your turn. Configure a simple endpoint using the Extractor Builder.

Your goal is to build an extractor for Celonis’ Login History API. You can see documentation for this API by adding "swagger-ui/index.html?urls.primaryName=Team#/" after the "cloud/" of your team URL.

https://{your-team-name}.training.celonis.cloud/swagger-ui/index.html?urls.primaryName=Team#/

If you’re feeling confident, use the basic instructions. If you need a bit more guidance, then follow the step by step guide.

Basic Steps

Activate your login history by going to Admin & Settings and Login History and toggling “enable login history”. Log out and log back in once to get at least one login result. Add “/swagger-ui/index.html?urls.primaryName=Team#/” after the celonis.cloud of your Celonis Team: https://{your-team-name}.training.celonis.cloud/swagger-ui/index.html?urls.primaryName=Team#/ Build the extractor using the Login API endpoint “/api/external/authentication-events” For authentication, use OAuth. You can generate the key in your Celonis Team under Admin & Settings → Applications. Refer to documentation on OAuth for the steps. Connect to your training team. Add your login history endpoint with pagination and select “content” as your response root. Save your extractor and run your extraction in data jobs.

Step by Step STEP INSTRUCTIONS

Activate Login History

Activate your login history by going to Admin & Settings and Login History and toggling “enable login history”. Log out and log back in once to get at least one login result.

Create new extractor

In Data Integration go to any existing data pool or create one if necessary.

Create a custom extractor by clicking Connect to Data Source or Add Connection and then Create custom Rest API Extractor.

Give it a name, e.g. Login History API

Variables

On the next tab, Variables, you can keep everything as is. You only need the API URL here and it already exists.

Authentication

For authentication, take a deep breath and follow the steps below. These steps are based on OAuth developer documentation here.

Generally you need to set up your authentication and create a client ID and secret. Let's start with the authentication.

Select OAuth and Client Credentials.

Now on to the client ID and secret creation. To get the client ID and secret, open a separate tab for your team and go to Admin & Settings and then Applications. Click on + Add New Appplication and then OAuth Client.

Give it a name. Select Client secret basic. Click Define Scopes.

On the next screen, under Team, select "team.login-history:read". Click Create and save both your ID and secret in a separate file or tool (Notepad).

With your application created, you now need to give it permissions.

Go to Applications, select your new client, then Admin & Settings and activate permissions for login history.

Save and go back to your extractor builder tab.

Now adjust your Authentication on the following three points:

1. Add /oauth2/token to the Get Token Endpoint field.
2. Disable send request via request body.
3. Add a scope key with "team.login-history:read" as the value.

Save and continue to the Data connection tab.

Data Connection

Ready to connect? Enter your training team URL up to the "cloud" along with your client ID and secret. Click on Save.

If there is an issue double check the steps in the previous step as well as whether there is an extra slash in your API URL or the Token Endpoint field.

Start endpoint.

Endpoint name and URL

In the Endpoints tab, click "Add" next to endpoint.

Call it "Login History" or a name of your choice.

Add the following endpoint to the API URL variable "api/external/authentication-events"

Click save.

Finish endpoint

Scroll down and click generate response.

Select “content” as the response root.

Copy the "pageNumber", go to Pagination above and select "Page by Page Pagination".

Enter "pageNumber" as the page parameter.

Scroll down and preview your results.

Save and click Done at the top right.

Run extraction

You can stop here but if you’re feeling motivated go ahead with an extraction.

Create a new data job based on your connection.

Add an extraction and add your table.

Run it and you are done!

Debrief

The essence of creating an extractor is to understand your API. This includes its URL, authentication, pagination, response root and more.

Basics Accelerate your Work with the Extractor Builder AI Assistant

Though currently in private preview, keep an eye out for the Extractor Builder AI Assistant that will handle much of the heavy work of building an extractor in the near future.

_Media:_

• https://fast.wistia.net/embed/iframe/4dwwghciax?videoFoam=true

---

Advanced Configuration Advance Configuration Overview

In this lesson, let’s delve into more advanced configurations including:

response management and data structures, filters and delta loads, dependent endpoints, custom authentication and more!

Advanced Configuration Managing Responses

Many API endpoint responses will have a hierarchical structure. For example this domain registry endpoint returns an array (square brackets []) within the collection (curly brackets {})

{ "domain_name": "example.com", "registrar": "reserved-internet assigned numbers authority", "whois_server": "whois.iana.org", "updated_date": 1628924504, "creation_date": 808372800, "expiration_date": 1660363200, "name_servers": [ "a.iana-servers.net", "b.iana-servers.net" ], "dnssec": "signeddelegation" }

The square brackets automatically create a nested table.

To ensure this nested table’s records are connected to the parent table, you need to define a primary key in the parent table. This primary key is then added as a foreign key in the nested table.

Should you not need the nested table, you can remove it from the response.

Dealing with data types

Celonis automatically recognizes the data types from sample or generated responses. That said, you may want to change these types to fit your needs. Here is a quick rundown of the available data types:

String Integer Datetime Date Float Boolean Text Managing non-200 responses with response rules

For more APIs, the response code is 200. This is the code Celonis accepts by default and means success. For some cases, you will get another code in the 200s, 300s, or 400s. Response rules help handle these exceptions or errors to tell the extractor what to do when these codes surface.

For example if a positive response is 203, then you can add a response rule to simply skip and continue the extraction.

In other cases, the code may be 200 but the error is returned in the http response body or json body. For this, you can tell the extractor to stop the extraction by catching the value.

This overview of responses is a good starting point if you're looking to learn more about the meaning of different http statuses.

Advanced Configuration Filters

Many endpoints allow for basic filtering. For example, the audit log or login history endpoints from Celonis allows you to enter a "from" and  "to" time parameter.

This is a request parameter you can add as either a static value using parameters or as a filter to edit in your extraction. We strongly recommend you use the Filter

Filtering via Query Parameters

A query parameter filter is fairly straightforward. You select the query parameters filtering method and enter your dynamic parameter's name and format.

This parameter then appears in your extractions and you can easily use it in this case as a generic or delta filter.

Using Filtering instead of Static Parameters

Though you could technically plug some of your filters as static parameters, doing so takes away visibility of the filter on the in the extraction. Therefore only use request parameters when they are permanent and do not need to be visible. Plugging in a date in a static parameter is not recommended.

Filtering Capabilities Depend on your Endpoint

If your endpoint is a POST call or an OData API call, other filtering possibilities automatically open up in the endpoint. Make sure to refer to our API filtering documentation for more information.

Use Extraction Parameters with your Filters

Once your filter is setup, you can combine it with extraction parameters to set up e.g. delta extractions. For example you can match a "from" filter with an extraction parameters that picks up the latest value from a table's timestamp.

The extraction parameter then gets set as the filter's value:

Let's move on to dependent endpoints.

Advanced Configuration Dependent Endpoints

A dependent endpoint takes another endpoint’s output as its input. This can be either in the endpoint’s URL or passed as a request parameter.

For example you may want to get a list of countries, and for each country retrieve an inflation rate. So your first endpoint would be /country and give you a list of countries (most likely with a filter to restrict the results).

From there you could create a dependent endpoint /inflation and pass in the country endpoint's iso2 column as a parameter.

Depending on the endpoint’s setup, it could also be passed in the URL:

Advanced Configuration Custom Authentication

The Extractor Builder is designed to accommodate any type of API authentication method. You can find the full list in our documentation. When adding an authentication, make sure to refer to the API’s documentation and find the best fit in the list of Celonis options.

Occasionally, you may have to create a custom authentication. Here you can define the entire authentication request including :

The authentication request type, URL, headers, and parameters The location of token in response How the token is used in endpoints

Advanced Configuration Exercise - Configure Advanced Options

This exercise is optional. Challenge yourself to master a few of the advanced options! Let's work with the free endpoints from the API Ninja.

The Challenge

You center of excellence needs ongoing fresh data on US cities and their populations. Build an extractor that does the following:

Extracts data on the US and its cities with a minimum population of 2 million.

Use the country endpoint with the name parameter and "United States" as the value. Use the city endpoint as an endpoint with the country parameter and "US" as the value. Add two filters on your city endpoint for min_population and max_population. You should be able to define the population ranges at extraction. Add the geocoding endpoint as a dependent endpoint to city, using the city endpoint's name column as a parameter. Make sure nested tables in your responses have foreign keys to the parent tables.

Your extractor should also:

Have response rules to catch 500 errors and stop the extraction if the error happens. Have custom authentication for X-API-Key. You could use the out-of-the-box option here but we want a challenge! Have a look at X-API-Key documentation. Use a variable for your x-api-key value. Use this key (note that if its limit is reached, you may have to create your own free account on the website). PXS7ICPIwrhtyRzjwJRmyA==r8PJlUVfcRXDzrxP Guidance needed?

You can download a sample of this extractor from resources and import it. To import, follow the steps to create a new extractor and use the import function. You'll need to add the key above to create a connection and make it work.

---