OData Querying

There are several ways of querying information out of the case management system. One of those ways is directly invoking queries against the generated sql views. However these views don't perform well with many cases and/or many properties and are not dynamic. A new way has been introduced to tackle this problem: OData querying.

The OData endpoint for querying case information is a dynamic endpoint (meaning its metadata will change whenever changes are made on lifecycles and/or its properties). The metadata document can be found at https://your-core-service.domain.net/odata-dynamic/$metadata.

Benefits

Let's start by summarizing why you should use the OData querying instead of the sql views.

Security

A very important thing to note is that, when using the SQL views, security is completely bypassed. You will always get all case information requested by the query. Memberships of the current user are completely ignored.

With OData, this is no longer true. OData will always take memberships into account even if you disabled the lifecycle setting Membership Check Enabled. This results in the fact that cases, where the current user is not a member of, will no longer be returned.

When using OData querying in a synchronisation or method job, no user is present to check case security against. In this scenario, the framework will use the Ometa Scheduler client for security. This client is marked as Ometa administrator by default and will have access to all cases. Keep this in mind when configuring synchronizations as a configurator.

Note

An Ometa administrator will always get all cases.

Performance

In almost any case, the OData querying will be the better choice regarding performance. It handles filtering, sorting, paging, ... way smarter than the generated sql views.

For instance: if you do some filtering, sorting and/or paging in ADM, those options will ultimately be processed in the SQL query itself if you go via the OData endpoint. This is not the case when using the sql views. If you want the same behavior in those sql views, you'll have to alter the interface script (which will never work 100% because you don't know what options the end user will use in the ADM).

Configuration

Profile

An out-of-the-box profile is installed which you can use to query case information: OData - Ometa Case Service. No fields need to be configured on this profile. It is ready to use.

Method

You can create your object & method the usual way based on the information you'd like to fetch (e.g.: lifecycle properties). The method needs to be configured using the template OData - Ometa Case Service.

The external name of the output fields must match the name of the fields in the metadata document of the service. Normally these names will be the same as your lifecycle property names. However it is possible that some characters are replaced due to the fact that the OData protocol doesn't support them. An example of this is the 'space' character which will be replace by '_x0020_'. Please refer to the metadata document for the correct external field names.

Interface Script

The name of the entity set is the lifecycle you'd like to fetch cases from. The interface script follows the OData interface syntax.

This could be your interface script if you'd like to fetch cases from the lifecycle 'Orders' with a default filter on the lifecycle property 'Amount' which must be greater or equal than 10.000.

Copy

EntitySet=Orders
Operation=MultiGet
Filter=Amount ge 10000

Performance

The performance of querying case information heavily depends on the way you make your requests.

Especially when there are tens of thousands of cases and dozens of properties, performance will degrade if you don't pay attention to some guidelines:

Sql Server

Always make sure your indexes are not too fragmented and the SQL server performs well. We wrote an article about how to keep your sql server performant.

We cannot stress this enough! If your indexes are fragmented, you will lose a lot of performance!

Remove Unused Fields

If you don't use some output fields, remove them from your method. This will cause the OData interface to adjust its $select operation and eventually will cause a more performant query against the SQL server by omitting that field.

Prevent Big Queries

This actually applies to almost all scenarios, but try to prevent querying too much data. Nobody needs thousands of rows in a realtime application.

Be fast = be smart!