› Data sources › Use OData Feed as data source

Use OData Feed as data source

Use OData (short for Open Data Protocol) to expand Insight data source connectivity to web-based data sources.

Insight supports OData 4.0.x to execute queries. More information is available on the OData website.

Define OData as data source

  1. Perform one of the following steps:

    • On the Guide in the main panel, click Add new data source.

    • On the Documents Tree, right-click Data Sources and select New Data source.

  2. Assign a name to the data source and click OK.
  3. On the Database type menu, select OData Feed.
  4. On the Provider name list, select the applicable connection type.
  5. Enter the URL to specify a path to the data source.
  6. Optional. Enter the user name and password. For OData, only Basic authentication is supported.
  7. Click Connect to run a test connection to ensure that Studio can connect to the database. If a connection is established, the circular indicator turns green.
  8. To add the required metadata from the OData service, click Add Table to enter the table name manually, or click Get tables to request a list of available tables and select the tables from the list. Insight supports the following property types:

    • Primitive (string, numeric, Boolean, Date)

    • Complex (property types that contain other subtypes)

    Insight does not support collection properties.
  9. On the Actions toolbar, be sure to save the updated data source.

OData-based records and metrics

Use OData as a data source for stored and on-demand records, and metrics. In this case, Insight selects the required data from an OData table without aggregation and transformation, inserts data into a temporary table in the Data database, and then aggregates data from the temporary table

JOIN (UNION) OData source for records

It is possible to Join or Union data from multiple OData methods. In this case, Insight executes individual requests to each method. Data from each method is inserted into a temporary table in the Data database, and all data is joined in the Data database.

Derived fields for OData-based records and metrics

OData supports derived calculation starting from 4.0.1 version, but Insight is unable to provide the same support due to limitations on the provider's side.

Therefore, all derived fields are calculated in Data database without regard to the Process in source DB option: Insight reads data from the OData service, inserts data into a temporary table in Data database, and then calculates the derived fields while adding data to the Main record table.

Aggregations and transformations for OData-based records and metrics

All aggregations and transformations are performed on the Data database side.

Filters for OData-based records

Insight adds all filters to the $filter query part.

To ensure achieving the expected result, verify that the required filter is supported in the selected OData source. Otherwise, you can use the following workaround:

  1. Create a temporary OData-based on-demand record where you use the OData supported filters.
  2. Create a new record and add the OData-based record from the previous step to use all filters.

Functions for OData-based records

The IF-THEN-ELSE function is supported only with OData version 4.0.1.

In a filter condition, Insight maps the following functions to the OData standard functions.

String functions:

  • CONCAT

  • CONTAINS

  • POSITION_OF_SUBSTRING (OData function: indexof)

  • LENGTH

  • SUBSTR

  • LOWER

  • UPPER

  • TRIM

  • LIKE_PREDICATE (OData function: matchesPattern)

Datetime functions:

  • CURRENT_DATETIME

  • GET_YEAR

  • GET_MONTH

  • GET_DAY

  • GET_HOUR

  • GET_MINUTE

  • GET_SECOND

Math functions:

  • CEIL

  • FLOOR

  • ROUND

Convert functions:

  • CAST

  • CONVERT

  • TO_CHAR

  • TO_DATE

  • TO_FLOAT

  • TO_INT

Geometry functions:

  • Geometry_Intersects

Custom query for OData-based records

It is only possible to use options supported by provider. Click Test to verify that the options are available and the query can be executed. You can enter custom queries for OData-based records in the form of an URL or an array of query options:

  • https://services.odata.org/TripPinRESTierService/Airports?$select=Name,IcaoCode,IataCode,Location&$top=3 

  • {
         "entitySet": "Airports",
         "$select": "Name,IcaoCode,IataCode,Location",
         "$top": "3"
          }