Remote Query Setup for OData

OData (Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs. Refer to http://www.odata.org for more information about OData.

This section will explain how to setup OData type remote in Perfion application and will concentrate only on OData specific setup.

Perfion supports OData v4, but not all features are implemented. Perfion uses OData only for data reading and supports only basic data types.

Remote Connection

Remote connection will specify all parameters needed in order to connect to OData service.

There can be multiple parameters specified in order to connect to OData service. Some parameters are mandatory, some are optional. Some OData services can be secured by using different type of authentication methods and there are also different ways to retrieve data from OData service, e.g. using XML or JSON data formats.

Rules for specifying remote connection parameters:

• There can be multiple connection parameters specified, where each have the following format:

The parameter name is case insensitive and one can only define the parameter with the same name once in the connection string.

The parameter value is case sensitive and anything defined inside the double quotes including trailing and leading spaces will be treated as a value.

Note that parameter name and value should not contain the “; characters which are used to separate parameters in the connection string.

• There are two mandatory parameters for all OData type remote connections:

• Parameter order in general is not important, but there is one exception. The first parameter must always be “Provider” parameter, e.g. “Provider="OData";”.

• All parameter names are case insensitive.

• Parameter values can be case sensitive and also case insensitive. This will dependent on the type of parameter.

The Basic OData Service Connection Parameters

These are the basic parameters used when connecting to OData service.

OData Service Connection Parameters Using HTTP Basic Authentication

These parameters are used when connection to OData service use HTTP Basic authentication. Parameters provided below must be used together with basic parameters.

* Authentication credentials are mandatory, but there are 2 ways how one can define them. The connection string must have either UID/PWD parameters or Token parameter defined:

• Using User ID and Password. UID parameter if defined sets authentication to be done using UID and PWD parameters where both must be present. If UID is provided, then Token parameter will be ignored.

• Using authentication token. If UID parameter is not provided, then Token parameter can be used for authentication. In this case parameter PWD will be ignored.

Token value is colon separated user name and password credentials which are Base64 encoded. So if one has user ID and password defined as UID and PWD, then Token value will be (pseudocode):

OData Service Connection Parameters Using Windows Authentication

These parameters are used when connection to OData service use Windows authentication. Parameters provided below must be used together with basic parameters. There are two connection types used with Windows authentication:

• Local machine or local network connection. It uses user credentials who is connected to Windows machine.

• Remote network connection using custom credentials.

OData Service Connection Parameters Using Bearer Token Authentication

These parameters are used when connection to OData service use Bearer token authentication. Parameters provided below must be used together with basic parameters.

OData Service Connection Parameters Using OAuth Authentication

These parameters are used when connection to OData service use OAuth authentication. Parameters provided below must be used together with basic parameters. There are currently two connection types used with OAuth authentication:

• Via Azure Active Directory using client secret.

• Via Azure Active Directory using client credentials.

Select Statement

In Select Statement one can define the OData query to be executed to retrieve the remote data. To test if query works, we recommend first to test OData query to check that the connection works (e.g. in Postman or in a browser) and that the expected data is returned. When the query works as intended, it has to be setup in Perfion so that Perfion can link data from remote source to the data in Perfion. The query must be configured to dynamically return data based on context, e.g. in the grid or in Report Designer, so that only the relevant data is retrieved from remote service. After query is set up, one can run a test by pressing “Test” or F5 button to execute it.

General information about how Select Statement should be defined:

• OData query must be specified without base URI, which was defined in the remote connection. Example:

• Perfion.In block. In order to map the data from remote source to Perfion data, the OData query must be controlled by Perfion. Perfion.In block is used to dynamically generate and insert data values from Perfion in order to retrieve corresponding values from the remote service. The key data field has to be renamed to “KEYVALUE” and the variant field to “VARIANT”. Please refer to Perfion.In Block section for more information.

• All incoming data from remote source must be mapped to Perfion data. For this to work there are two requirements:

  • Remote data should have “KEYVALUE” data column present, which will be used as identifier for remote data. All values defined in this data column must be unique except if it is Cluster type query (refer to Cluster Type Data to see an example). The “KEYVALUE” data column may come directly from OData source or it can also be artificially mapped to one of the incoming data columns by using Perfion.In block. Please refer to Perfion.In Block for more information.

  • Key feature must be selected.

Perfion.In Block

Perfion.In block is a special Select Statement parameter, which allows Perfion to preprocess data so it is suitable to be used in OData query. It is used only with OData remotes. The Perfion.In block performs four different functions.

Perfion.In Block – KEYVALUE

Perfion.In block is used to rename selected remote data column name to “KEYVALUE”. OData queries are rather strict and does not allow data projection, i.e. to rename incoming data column names.

Perfion requires “KEYVALUE” column to be present in the incoming remote data and if such column does not exist in original OData data source, then Perfion will have no possibility to use map the data. In order to rename key data column to “KEYVALUE”, one must use the Perfion.In block in Select Statement.