API Profile

Description

Configure an API Profile as a source for data integration tasks to extract data from custom APIs using standard API profile definitions.

Connection String Templates

Connection Notes

• You can use an API Profile connection as an incremental source where the API profile has been configured to support it.

How to Connect an API Profile to Loome Integrate

This covers the core patterns and operational rules needed to successfully build, deploy, and troubleshoot API Profiles for Loome Integrate.

Overview

An API Profile allows Loome Integrate to consume REST APIs as relational tables without custom code.

The profile consists of:

• RSD files: Define tables, endpoints, data mappings, transformations, paging, and query logic.
• RSB files: Define authentication and reusable scripting components.
• APIP file: Compiled deployment package used by the Loome API Profile connector.
• Loome Connection Configuration: Connection string and profile settings used during execution.

API Profile Developer Checklist:

• One endpoint = one RSD table
• Implement pagination wherever possible
• Use query slicing for parent-child endpoints
• Use other:xPath on every response field
• Apply allownull() for sparse JSON fields
• Stamp slicer values when formats may differ
• Use columnsize="65535" for long text
• Parse _query.criteria for incremental filtering
• Enable Verbosity=5 during development
• Build → Test in Loome → Review Logs → Repeat

Recommended Best Practices:

• Structure
  • One endpoint per RSD
  • Separate authentication into RSB files
  • Comment complex transformations
  • Use descriptive names
• Performance
  • Always paginate
  • Use incremental loading
  • Minimise API calls
  • Prefer query slicing over manual nested loops
• Security
  • Never hardcode credentials
  • Use ProfileSettings
  • Store OAuth tokens securely
  • Always use HTTPS

Steps:

• Development Workflow
• Step 1. Create a Profile
• Step 2. Build Tables with RSD Files
  • Core Design Principles
  • Data Mapping
  • Nested JSON
  • Value Transformations
  • Pagination
  • Incremental Loading
  • Query Slicing (Recommended)
  • Flattening Nested Arrays
  • Authentication Options
• Building the Profile
• Connecting in Loome Integrate
• Recommended development settings
• Troubleshooting

Development Workflow

Step 1. Create a Profile

Using VS Code:

1. Run Create New API Profile task.
2. A profile structure is generated:

MyProfile/

│

├── src/

├── build/

├── debug/

└── README.md

Purpose of folders:

Step 2. Build Tables with RSD Files:

Each API endpoint becomes an RSD file. An RSD contains:

Schema:

<api:info title="Users">
<attr name="Id" xs:type="integer" other:xPath="id"/>
<attr name="Name" xs:type="string" other:xPath="name"/>
</api:info>

Defines:

• SQL table name
• Columns
• Data types
• JSON extraction paths

Execution Logic:

<api:script method="GET">

<api:set attr="uri"
value="[_connection.BaseURL]/users"/>

<api:set attr="RepeatElement" value="/"/>

<api:call op="apisadoExecuteJSONGet">
<api:push/>
</api:call>

</api:script>

Defines:

• Endpoint URL
• HTTP method
• JSON location
• Returned rows

Core Design Principles:

Table Name

The exposed SQL table name comes from: <api:info title="Users"> and not from: users.rsd

One endpoint is equal to one table. Avoid combining unrelated endpoints in one file.

Recommended approach:

• users.rsd
• orders.rsd
• products.rsd
• locations.rsd

Data Mapping:

Basic Mapping:

JSON:

{
"id": 1,
"name": "ABC"
}

Mapping:

<attr name="Id" other:xPath="id"/>

<attr name="Name" other:xPath="name"/>

Nested JSON

JSON:
{
"contact": {
"email": "abc@test.com"
}
}

Mapping: <attr name="Email" other:xPath="contact/email"/>

Deep Nesting <attr name="Latitude" other:xPath="address/geo/lat"/>

Arrays “tags”: [“A”,“B”] <attr name="PrimaryTag" other:xPath="tags/0"/>

Value Transformations

You can make inline transformations.

Example: <api:set attr="Email" value="[Email | trim() | tolower()]"/>

Useful formatters:

For example: <api:set attr="Status" value="[Status | equals('Active','1','0')]"/>

Pagination

Always implement pagination when available.

Offset Paging

API pattern: ?limit=500&offset=1000

Configuration:

<api:set attr="EnablePaging" value="true"/>

<api:set attr="pageoffsetparam" value="offset"/>

<api:set attr="pagesizeparam" value="limit"/>

<api:set attr="pagesize" value="500"/>

Page Number Paging:

API pattern: ?page=2&limit=500

Configuration:

<api:set attr="EnablePaging" value="true"/>

<api:set attr="pagenumberparam" value="page"/>

<api:set attr="pagesizeparam" value="limit"/>

Incremental Loading:

Loome pushes SQL filters to the profile.

Example: WHERE ModifiedDate > '2025-01-01'

The profile should extract this filter using: [_query.criteria]

Then pass the value to the API endpoint.

Benefits:

• Faster loads
• Reduced API calls
• Better performance for large datasets

Query Slicing (Recommended)

Query slicing is the preferred approach when one endpoint depends on values from another.

Key Rules:

• Use {ColumnName} inside URIs.
• Use [ColumnName] elsewhere.
• Every slicer column must have: other:filter="{ColumnName}"

Example:

Reservations

↓

ReservationDailyRates

The child table automatically loops through parent IDs.

Child Configuration:

<api:info
title="ReservationDailyRates"
other:queryslicercolumn="ReservationId"
other:slicetable="Reservations"
other:sliceforeignkey="Id">

Column: <attr name="ReservationId" other:filter="{ReservationId}"/>

URI: <api:set attr="uri" value=".../reservations/{ReservationId}/dailyRates"/>

Flattening Nested Arrays

When child arrays must become rows.

Example JSON:

{
"batch": {
"transactions": [...]
}
}

Configuration: <api:set attr="DataModel" value="FLATTENEDDOCUMENTS"/>

<api:set attr="JSONPath" value="$.value;$.value.Transactions"/>

Use absolute paths: other:xPath="/json/value/Transactions/Amount"

Never: other:xPath="Amount"

Authentication Options

• API Key <api:set attr="http.header:Authorization" value="[_connection.APIKey | prefix('Bearer ')]"/>

• Basic Authentication <api:set attr="auth.scheme" value="basic"/>

• OAuth 2.0

  • Usually configured through the connection string rather than custom code.

Example:

AuthScheme=OAuth;
OAuthVersion=2.0;
OAuthGrantType=CLIENT;
OAuthClientId=...
OAuthClientSecret=...
OAuthTokenURL=...

Custom OAuth flows can be implemented in a separate: GetOAuthAccessToken.rsb

Building the Profile

You can then build the profile.

Run: Build API Profile: <Profile Name>

Output: build/MyProfile.apip

Connecting in Loome Integrate

In Loome Integrate, go to Connections at the top-right dropdown menu.

Create a new connection.

Choose ‘API Profile’ in the connector list.

Choose whether it will be available in all projects or only in your chosen projects.

Provide your connection string. In Loome, replace the profile in the connection string with the name of your own API profile and profile settings.

• Basic connection: Profile=C:\Profiles\MyProfile.apip;

• With settings: Profile=C:\Profiles\MyProfile.apip; ProfileSettings= 'BaseUrl=https://api.example.com; APIKey=xxxx';

Choose an agent and submit the connection.

Recommended development settings:

Logfile=C:\debug\api-profile.log;
Verbosity=5;
Location=C:\debug;
CacheLocation=C:\debug;

Troubleshooting

Common Causes of Production Failures

These are the most common causes of production failures.

1. Silent Row Loss During Slicing

   • Symptom:
     • API returns data
     • Logs show rows
     • Loome receives zero rows
   • Cause: Slicer value does not exactly match returned value.
   • Fix: Stamp the value manually.
     • <api:set attr="BranchID" value="[BranchID]"/>

2. Column Shifting / Incorrect Data

   • Occurs when optional JSON fields appear in some rows and not others.
   • Always: <attr other:xPath="..."/> for response fields.
   • And apply: <api:set attr="SomeField" value="[SomeField | allownull()]"/> before: api:push/

3. Long Text Truncation

   • Strings default to about 2000 characters.
   • For large text:

<attr
name="Description"
xs:type="string"
columnsize="65535"/>

4. Table vs View

   • If only GET exists: <api:script method="GET">
   • It may be classified as a View. You can add unsupported POST/MERGE/DELETE stubs if it must appear as a Table.

5. Expected API Errors

   • For slicer loops where some combinations legitimately fail: <api:catch code="*"/> inside: <api:call>
     • This avoids aborting the whole load.

Troubleshooting Checklist

You can check these fields when troubleshooting for these issues:

• No rows returned, then check:
  • Endpoint URL
  • RepeatElement
  • JSONPath
  • Slicer placeholders
  • Stamp values
• Wrong values in columns, then check:
  • Missing other:xPath
  • Sparse JSON
  • Missing allownull()
• Authentication failures, then check:
  • Credentials
  • ProfileSettings
  • OAuth configuration
  • HTTP 401/403 in logs
• Profile not found, then check:
  • APIP exists
  • Path is correct
  • Agent can access file