How to: Using an OData API for data integration
The following guide is an introduction to setting up data integration activities in Dynamicweb using an OData API.
Importing data with OData API
Importing data using the Open Data Protocol (OData) requires adding the relevant endpoint in Settings > Integration > Endpoint Management.
To do so, click "Add endpoint" in the navigation bar and follow these steps:
- Provide the endpoint with a Name and an optional Description
- Insert the URL of the desired OData API
- Set Type to Get
- Choose Endpoint Authentication at the bottom of the page (See next section)
- Save and Close
Please notice that cloud instances of Business Central requires the use of OAuth 2.0 to authenticate endpoint interactions. You can read a guide on establishing an OAuth authentication in this guide.
If you are establishing an integration activity between a locally hosted Business Central enviroment or a sandbox enviroment you can make do with a simpler authentication such as a WebServiceAccesKey.
Endpoint authentications can be managed in Settings > Integration > Endpoint Management.
When using OData integration, you might not be interested in importing every record of a given object type.
To filter in the imported data, you can add one or more query parameters to the endpoint. The applied filter will affect all interactions with the endpoint. If you want to apply a filter to specific integration activities only, you should instead apply the query parameter in the integration activity settings.
A query parameter consists of a key and a value such as the examples in the table below.
Filters the data provided by the endpoint, so that only records where the Item_No attribute starts with the string IN1 is included.
Only selects the first 10 records of the object type.
Once a query parameter has been added to the endpoint and the endpoint itself has been saved, the full URL (including the added query parameter) will be visible in the endpoint editor.
There are many more ways to filter the OData provided by the endpoint. A complete documentation for OData filter expressions can be found here:
Please notice that the endpoint provider does not allow users to filter on time specific attributes such as "Last_Date_Modified", "LastModifiedDateTime", "Order_Date", "LastDateTimeModified". If you want to filter on these attributes you will need to use the Delta Replication mode of your integration activity.
Setting up a data integration activity for imports
When the endpoint has been set up, the next step is to add an integration activity in
Follow the steps below to add an integration activity.
Data column mappings can be managed in the activity settings menu by following these steps:
- To add a source/destination column mapping, click "Add column mapping" in the action bar. This will add dropdown menus for Source column, where all attributes selected in the source settings will be visible (or all attributes of the entity if no select statement is applied to the activity).
- Select the corresponding Destination column from the dropdown menu to the right to establish the column mapping.
- Further column mappings for entity attributes may be added by repeating the previous steps.
- Conclude the activity setup by clicking "Save and close" or "Save and run" to perform the import job immidiately.
Integration activity modes
|Delete||This mode compares primary keys between records in the endpoint and in Dynamicweb. Records in Dynamicweb that do not appear in the endpoint will be deleted.|
By locating the time of the last succesful run this mode will import new records only. No records are deleted from Dynamicweb when this mode is used.
Note: Delta replication converts all Datetime attributes to UTC-time to ensure correct comparison across timezones.
|First page||Imports the first page of records only. The maximum amount of records on a page is specified in the maximum_page_size setting.|
|Full replication||This mode imports all records and deletes nothing in Dynamicweb. Activities with this mode should only be run once.|
Running last request locally
Whenever Dynamicweb makes a request to an endpoint it stores the response locally. Only the last response is saved though, so any stored response will be overwritten, when a new request is made.
If you want to execute an integration activity using the locally stored response instead of querying the endpoint you can check the "Run last request" checkbox at the bottom of the activity source settings page.
The activity will then check for a locally stored response file and if such a file exists it's content will be imported.
High water mark
Whenever an OData integration activty catches an error, a HighWaterMarkFile is automatically created and saved locally.
When the activity is then run again, it will check the HighWaterMark path for records previously failed to be imported, and if it detects any, a process of importing these without calling the API will be initiated.
Since the endpoint provider iterates over each exposed object of the endpoint, it will store all records in the database that it sees before an error. This essentially means that the exposed records of the API will be split into two in case of an error: Succesfully imported records in the database and a list of records not succesfully imported in the HighWaterMarkFile.