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.

• KeyDataColumnEntityName is the name of OData entity to which the data parameter belongs to. This parameter is optional and only has to be used when referencing columns from the root entity and only if the parameter is not unique in OData service. Refer to the table above to see how this parameter can be used.

• KeyDataColumnName is the name of OData parameter, which should be used for key values (“KEYVALUE” column) and one must use an absolute path from the root entity. OData returns data in hierarchical structure and one can request data from several hierarchical levels at the same time. Perfion must know which entity a parameter belongs to, in order to find it during the data parsing and flattening procedure. There can be several parameters with the same name in different entities and therefore, it has to be defined explicitly. The rules how parameter name should be defined:

  • If parameter is in the root entity, then it can be used directly, but then the root entity name to which the parameter belongs should be defined as a prefix to the parameter

  • If parameter is not in the root entity, then it should use the path from the root entity as a prefix. Example:

• PerfionParameterForKeyFeature is one of reserved Perfion parameters for defining key feature: #<key feature ID>#, #@ID# or #@KEYFEATURE#. In order for Perfion.In block to know that this is a key parameter, it must be specified with one of those three key feature type parameters.

The use of Perfion.In block limits the way OData query can be defined. For example, one cannot define filter inside $expand, because it will use local parameter names to refer to entity parameters defined in $expand block.

We also recommend to limit the number of hierarchy levels and always to define filter parameters in global scope. It can be a challenging task to configure an OData query properly if data in different levels have 1-to-many or many-to-many relations, so we suggest to avoid it. Perfion can flatten hierarchical data, but then it becomes rather difficult to filter it, because OData in general is rather limited in this area.

Perfion.In Block – VARIANT

Perfion.In block is used to rename selected remote data column name to “VARIANT”. The same rules apply as for “KEYVALUE”, but then #@VARIANT# parameter must be used as the 2^nd argument in Perfion.In block.

Perfion.In Block – Data Conversion

Perfion.In block is used to convert a comma separated list of values into special format, which is supported by OData. Not all OData services support the use of the IN operator to check if parameter is equal to multiple key values at once. Instead the Perfion.In block prepares a special statement, which allows to check each key value one by one. Example:

Perfion.In Block – Data Formatting

OData have many data types and it is rather strict on how parameter values are defined in query when requesting data from OData service. Perfion.In block will use OData metadata to find out what is the actual parameter data type and then use special formatting to convert outgoing data. For example, if Perfion parameter is of numeric type, then it will not use quotes around the numbers. If the data is of text/string type, then it will use quotes around parameter values. It will also convert Boolean and Date type values to the format required by OData service.

OData Data Processing

OData data service delivers data, which is not table like data as compared to Web Service and Database type remotes. OData data is hierarchical and is not directly supported as data for remote. The OData data has to be flattened before it can be used in Perfion for remotes. Perfion will do this automatically and create a final flat table like data, which will include data from all hierarchy levels. Perfion supports complex data structures and will extract data from multiple hierarchy levels.

After flattening the data, all entities from different hierarchy levels will be placed in one table. The names of the columns becomes an issue in this case, because there can be several parameters in different entities, which will have the same name. To fix the problem, Perfion will rename all parameters using their absolute paths to the root entity and will use underscore (“_”) character to separate each entity in the path.

OData Data Type Conversion

OData have many data types and not all of them are compatible with Perfion. Perfion will convert data to one of data types used in Perfion trying to match it as close as possible to original.

Some data types are complex and some of the formats may have format definitions from 3^rd parties (e.g. not defined by OData standard). Perfion in most cases will provide raw data for such parameters, e.g. JSON object or XML element string representation (depends on which format was chosen for OData data transport, e.g. JSON vs XML).

We do not recommend mixing data types when binding data from OData to Perfion, because there could be issues when converting data from one data format to another. For example, if key values in OData is of integer type, but in Perfion key feature has String type, then there may be issues in those cases, when Perfion data will have some string values, which cannot be converted to integer values.

There can also be issues when data formats match (e.g. both are numeric), but they are not compatible, e.g. integer values can be converted to float values, but not necessarily in the opposite direction. For example, if OData data type is integer and Perfion feature is Numeric (Perfion uses float as data type for numeric values), then there will be no issues reading data from OData to Perfion. However, request for data from OData service when using key feature values, which are numeric, but have values with fractional part, could fail.

OData Remote Data Examples

Single Type Remote from Two OData Entities

A simple example using two OData entities, which have 1-to-1 relation, e.g. each product has one supplier. This is a Single type remote. Key value selection is done using Perfion.In block and we also use an extra “Rating” parameter.

Remote connection setup is shown in Figure 23 and remote query setup is shown in Figure 24.

Open

Figure 23: Remote Connection Setup - OData, JSON, no authentication

Open

Figure 24: Remote Query Setup - Example with Single remote

Remote data returned in Test mode you can see in Figure 24.

Data in Default mode when received from OData service will look like shown in Figure 25. In the table below you can see remote data in Default mode as it will become after processing in Perfion. Note that remote data in Default mode will depend on the context, so the data in table below is just one example using two key values (2 and 3).

Note that Supplier -> Address is a single OData parameter of complex type.

Cluster Type Remote with Complex Hierarchy

A more complex example using two OData entities, which have 1-to-many relation, e.g. each “Employee” has several “Orders”. This is a Cluster type remote. Key values selection and variant selection is done using Perfion.In block.

Remote connection setup is shown in Figure 26 and remote query setup is shown in Figure 27.

Remote data returned in Test mode you can see in Figure 27.

Data in Default mode when received from OData service will look like shown in Figure 28. In the table below you can see remote data in Default mode as it will become after processing in Perfion. Note that remote data in Default mode will depend on the context, so the data in table below is just one example using two key values (1 and 2).

OData service always returns all variants regardless of values provided in $filter. OData will return Employees, which have at least one of predefined variants, but will return each Employee data with all variants. Perfion will filter out all rows from result data with variants, which do not have values defined in configuration, e.g. in example above, only those rows will be kept, which have variant equal to 140.51, 136.54 and 55.28.

Remote Connection with OAuth Authentication

OData service usage when it is secured using OAuth is no different than using OData service without authentication. The only difference is in remote connection setup. The example of remote connection setup with OAuth to connect to Microsoft Dynamics 365 OData service is shown in Figure 29. Note that data is not real in the example.