Universal Connector Configuration Guide

Before you start setting up a connector, ensure:

• You have Data Admin permissions in One Model.
• You can access Data > Sources from the One Model menu.
• You have the API reference information of the data source you wish to connect to. Note: APIs that return data in CSV format should use the CSV Connector.

Getting Started

This is a general guide to configuring a Universal Connector for a custom data source. Universal Connector configuration guides for specific data sources may also be useful references.

The prerequisites for building a One Model custom integration with any application that has an API are:

• The application must have a public API.
• You know what type of authentication the API requires - Basic, Bearer Token, OAuth, or Header Token - and have the required usernames and secret keys or passwords for that authentication type.
• You have the various URLs (base and endpoint), token URLs, and scopes (if applicable) as required for the API. This is typically found in the API documentation published by the application's owner.
• You know what endpoints are available, how they are organised, and what data is available from them.
• The endpoints you need are available via GET requests. GET is the standard method for retrieving data from an API without modifying it, and is the most common method for read-only data extraction.
• You are aware of any requirements or expectations of the data source when requesting data from the API.
• The endpoint returns data in JSON format. (Note: see Configuring the CSV Connector for APIs that return data in CSV format.)

Most data sources will have documentation describing the type of authentication used by the API, how to set up access credentials, where to access the API and its endpoints, any information that must be passed when retrieving data, and how the queried data will be returned. Having this documentation available when configuring a connector is strongly recommended.

Be selective about which endpoints are needed from the API - data retrieved from one endpoint may make another unnecessary. For example, if an endpoint provides a list of all departments and their details (/departments), it may be redundant to also include an endpoint that queries each department individually (/departments/{departmentid}). The API documentation can help with comparing what is retrieved from each endpoint.

For security reasons, any Password, Client Secret, and/or Token (depending on the method of authentication) will need to be re-entered each time the connector is saved.

Additional Information

Destructive Processing

Destructive processing treats the API's current response as the single source of truth, replacing everything that was there before. When running destructive processing, a full historical retrieval of all endpoint data is completed and then loaded into the target tables after all existing records are deleted. Typically an initial run of a newly built connector would be a destructive run. For subsequent runs, the size of the dataset returned in the API's response should be a consideration - the larger the response, the longer a destructive run will take.

Incremental Processing

When running incremental processing, the connector retrieves only the data that has been modified or added since the last successful run. Incremental processing is intended to have a smaller API response footprint than a destructive run, and to avoid requesting data that has already been retrieved. To successfully isolate changes, the connector requires keys and parameters to define the exact timeframe and record set to be retrieved. A key is needed to identify changed records, and a parameter (such as a date filter) is needed to limit the data returned. If either is missing, the endpoint will run in destructive mode even if the overall extraction is set to incremental.

Configuring Connector Details

Configuring Authentication Types

Configuring Endpoints

Endpoints represent specific resources within the API.

"data": [
    {
      "employeeId": "12345",
      "firstName": "Jane",
      "lastName": "Doe",
      "emplStatus": "Active"
    }
  ]

Configuring Endpoint > Pivot Paths

Pivot Paths takes the JSON API response and transforms it into tables by flattening objects into rows and splitting arrays into tables.

Pivot Paths are not mandatory and may not be needed in your configuration. If unsure, leave empty.

Pivot Paths are useful where the JSON contains a list of records as an object, using a record identifier (such as an employee ID) as the property name rather than as a property value. A pivot path on the endpoint will treat the nominated object as if it were an array, converting each key into a row instead. The original key is preserved in a column called om_pivot_key, so the identifier is not lost.

For example, the following JSON response for TableA shows employees 4 and 5 using their identifiers as property names. Without a Pivot Path, all objects and properties are stored with property names prepended with their parent names, resulting in employees_4_lastChanged as a column name within TableA:

{
    "table": "tableA",
    "employees": {
        "4": {"lastChanged": "..."},
        "5": {"lastChanged": "..."}
    }
}

Using Pivot Paths reconfigures the JSON into a format more easily read into tables and columns, with om_pivot_key preserving the identifier:

{
    "table": "tableA",
    "employees": [
        {
            "om_pivot_key": "4",
            "lastChanged": "..."
        },
        {
            "om_pivot_key": "5",
            "lastChanged": "..."
        }
    ]
}

Configuring Endpoint > Headers

Headers let the API know who you are, what format you're requesting, and how to handle the delivery of data. Authorization tokens or credentials are automatically included and do not need to be configured here.

Review the API documentation for:

• Any required custom headers - these may be in a reference table or a "headers" section, and typically accompany keys that start with X-.
• Example curl commands that start with -H, which indicates a header requirement. Copy the key (the part before the colon) and the value (the part after) into the Header section of the connector.

Configuring Endpoint > Parameters

Parameters pass filters or modifiers with the API request to ensure the returned dataset contains only what is needed. 

Review the API documentation for "filters" or "query parameters" to determine each endpoint's parameter names, types (e.g. string, date), whether they are required, and what each parameter does. If a required parameter is omitted, the API request will return an error. It is also worth reviewing optional parameters to determine whether the default response (used when the parameter is omitted) is acceptable.

Configuring Endpoint > Keys

Key/s identify the set of data each incremental load replaces. 

A key tells the connector which set of data each load replaces (note: It isn’t a unique identifier for each row). When an incremental run loads data, every key value in the new data replaces the data previously loaded with that key value. Data with key values not in the new load is left as it is. Choose the key to match how the endpoint retrieves its data:

• Data retrieved by date (e.g. a daily usage or cost report, requested one day at a time): use the date field. Each request returns all of the records for that date, so the date identifies the full set. When the run retrieves a date again, all of that date’s records are replaced together.
• Individual records retrieved by modified date (e.g. employees, filtered on updated_at): use the record’s identifier, e.g. employeeId. Each record is returned in full, so each record is its own set and an updated record replaces its earlier version.

Because whole sets are replaced, an incremental run can retrieve the same period again without duplicating data. This is useful when the data source can revise records after they’re first available, e.g. by using a Variable such as ProcessedTo:-30d.

When choosing a key:

• Don’t add extra keys. Use only the field that identifies the set the endpoint retrieves. Adding further fields may cause a mismatch with how the data arrives and will produce incorrect data.
• Don’t use a key broader than what each request retrieves. If an endpoint retrieves one day at a time but is keyed on an identifier, each run replaces all of that identifiers previously loaded days with one day’s data.
• The full set must always be returned. A key only works if the API cannot return part of a set. If it can, the partial set will replace the complete one already loaded.
• The key must be in the response. Choose a field from the API response, not a value that’s only sent in the request.
   

Configuring Endpoint > Transformers

Transformer Type: Translating

A Translating Transformer takes a value from a response (the Selector) and inserts it into the next request.

Transformer Type: Output

An Output Transformer takes a value from a response (the Selector) and inserts it as a parameter for dependent endpoints.

Transformer Type: Incrementing

An Incrementing Transformer takes a value from a response (the Selector), increments it, and inserts it into the next request.

Configuring Endpoint > Terminator

A Terminator stops requesting data from the API when a specified condition is met. 

The connector has two built-in termination behaviours that are always active and do not require a Terminator to be configured:

• If the API response returns no results, processing stops automatically.
• If the same URL is queried twice in a row, processing stops automatically.

Use a Terminator to set an additional limit on requests and prevent the connector from making unnecessary API calls. When the Terminator logic evaluates to true, the connector will finish its current task (saving the data it has received) and close the connection.

Configure a Terminator when the API signals the end of available data in a way other than returning an empty result - look for a has_more: false property, a null next_cursor, or a total page count in the API documentation. When the Termination Value matches the nominated Selector value, the connector will complete its current task and close the connection.

Configuring Variables

Variables are a flexible way for your One Model connector to insert dynamic values into API requests without the maintenance of continually updating the connector. Variables are used in Parameter values and allow the API to filter its response so that only records meeting the variable condition are included.

Variables allow for the following keywords:

• CurrentTime - uses the start date of the API run.
• ProcessedTo - uses the Processed To date of the data source. Processed To is the date data has been extracted to for the data source; the current value can be seen in the main connector details for the data source.

Date calculations
A date can be moved from either keyword across day/s, week/s, month/s or years.  Eg: ProcessedTo:-5d uses the date five days before Processed To.

Common uses

Incremental runs: create a Date Range variable with From ProcessedTo and To CurrentTime, and use it in a Parameter on the API’s updated_at parameter. The API request will be: return only records modified since the last time the connector successfully ran.

Incremental runs that look back: some data sources revise data after it is first available, e.g. usage or cost reports that can change for up to a month. Setting From to ProcessedTo:-30d retrieves the last 30 days again on every incremental run, so revisions are picked up. The endpoint’s Key makes sure the data retrieved again replaces what was loaded before, rather than duplicating it.

Rolling date ranges: use CurrentTime:-1y instead of a fixed From date for APIs that only keep a limited history, so the range never starts before the data available. Use CurrentTime:-3d instead of a fixed To date for APIs that take a few days to make data available.

Variable Type: Date Range or Number Range

Variable Type: Date or Text

You have configured your Universal Connector data source. You can now:

• Perform a destructive API run from the Data Source page.
• Monitor your API run from Data > Sources > View API Runs. For additional information on API runs, see the guide to Understanding the API Run Page.
• Monitor successful API runs as they transition into Data Loads. For additional information on data loads, see the guide to Data Loads.
• For questions, contact your One Model Customer Success team.