# OData Queries

CSOD's Data Exporter APIs use the OData protocol. For more information regarding the OData protocol, please go to http://www.odata.org/. Cornerstone is currently using OData version 4.0. Please note that not every OData option is available. The sections that follow describe specific functionality exposed in DEAPI.

# Base Queries

After you have your authentication token, you can begin making API calls to retrieve your data by sending a GET request to https://{environment}.csod.com/services/api/x/dataexporter/api/objects/{core_object}. A base query, without any filters applied, will retrieve all data in the core object for that environment.

TIP

  • After you have thoroughly tested your solution and are ready to go live in a production environment, you should strive to call an unfiltered base query for each object once in order to establish a local baseline, then only request changes thereafter. This lets you take advantage of the speed and efficiency of smaller data packages moving forward. See the sections on Filtered Queries and OData Change Tracking below for more information.

# Extended Metadata

You can inspect the definition and capabilities of each core object by sending a GET request to https://{environment}.csod.com/services/api/x/dataexporter/api/objects/$metadata. The response will contain an OData compliant XML document that you can parse to generate a local data dictionary. It also describes how you can interact with various objects, such as whether change tracking is supported, which fields can be used in filters, etc. This metadata document includes:

  • Core object description elements, such as ObjectPublicName, ObjectDescription, Object Heading, and ObjectIsUserDefined (i.e., whether object contains custom fields or not).

  • Core object key field(s). Key fields that uniquely identify a record in the object's data set.

  • Core object index definitions. Fields used as indexes in the RTDW. Only fields defined in an index can be used with the $filter querystring parameter.

  • Field definition elements, such as FieldPublicName, FieldDescription, data type, whether the field is nullable, etc.

  • Navigation / Relationship definitions, which can be used to determine which tables can should joined in your local data store after you have retrieved your API data. URLs are also available that expose graphical diagrams that describe the relationships between tables.

  • Miscellaneous object capabilities, such as OData change tracking.

As you build, test and troubleshoot your DEAPI-based data solution, you should regularly reference the in-line documentation exposed by this metadata. Common problems may include running filters using non-indexed fields, or attempting OData change tracking capabilities on objects that do not support this functionality yet.

# Selecting Fields to Return

A core object may contain more fields than you need for your business use case. While you could retrieve and store all of these fields with the thought that you could eventually use them, this comes with some additional expense, not only with the additional time needed to serialize and transport the data, but also the additional storage space on the receiving end. It's important to review your use case and what you plan to do with the data, and then retrieve only what you need rather than getting everything. The OData $select query string element can be used to retrieve specific fields of data from a core object, for example:

https://{environment}.csod.com/services/api/x/dataexporter/api/objects/users_core?$select=user_id,user_ref,user_name_last,user_name_first,user_status_id

TIP

  • You should reduce your payload at the source by using $select, $filter and/or OData change tracking functionality (latter two options discussed in the following sections). Doing so will yield significant performance gains in response and processing time from the service.
  • Most browsers and applications have a max URL of 2,083 characters. This is important to keep in mind as you build out your solution.

# Filtered Queries

# $Filter Overview

The OData $filter query string element can be used to retrieve subsets of data from a core object, for example:

https://{environment}.csod.com/services/api/x/dataexporter/api/objects/users_core?$filter=_last_touched_dt_utc ge cast('2022-08-01', Edm.DateTimeOffset)

which is also equivalent to

https://{environment}.csod.com/services/api/x/dataexporter/api/objects/users_core?$filter=_last_touched_dt_utc ge 2022-08-01T00:00:00.000Z.

Multiple filter criteria can be combined in a single query by using the 'and' or 'or' keywords, such as:

https://{environment}.csod.com/services/api/x/dataexporter/api/objects/users_core?$filter=_last_touched_dt_utc ge 2022-08-01T00:00:00.000Z and _last_touched_dt_utc lt 2022-08-02T00:00:00.000Z.

The table below lists the various filter operators supported by DEAPI.

URL Option Syntax
Equals {baseURL}/objects/users_core?$filter=user_id eq 78
Not Equals {baseURL}/objects/users_core?$filter=user_id ne 78
Greater Than {baseURL}/objects/users_core?$filter=user_create_dt gt cast('2022-08-01', Edm.DateTimeOffset)
Greater Than or Equal {baseURL}/objects/users_core?$filter=user_create_dt ge cast('2022-08-01', Edm.DateTimeOffset)
Less Than {baseURL}/objects/users_core?$filter=user_create_dt lt cast('2022-08-01', Edm.DateTimeOffset)
Less Than or Equal {baseURL}/objects/users_core?$filter=user_create_dt le cast('2022-08-01', Edm.DateTimeOffset)
Logical 'AND' {baseURL}/objects/users_core?$filter=user_create_dt le cast('2022-08-01', Edm.DateTimeOffset) and user_id eq 78
Logical 'OR' {baseURL}/objects/users_core?$filter=user_create_dt le cast('2022-08-01', Edm.DateTimeOffset) or user_id eq 78
Logical 'NOT' {baseURL}/objects/users_core?$filter=not (user_id eq 78)
'IN'* {baseURL}/objects/users_core?$filter=user_id in (1, 2, 3)
NOT 'IN'* {baseURL}/objects/users_core?$filter=not user_id in (1, 2, 3)

*Maximum of 64 comma-separated list of primitive values can be passed per operand. Combined primitive values across all operands cannot exceed 1500 characters.

WARNING

  • You must use 'and' and not '&' (the ampersand symbol) when combining multiple filter criteria... if you use the '&' symbol, subsequent parts of the filter criteria will be interpreted by the request handler as a new query string parameter and will likely be rejected as a malformed url. A similar mistake while testing is to omit the '$' in front of the $filter keyword (or other keywords like $select). In this situation, the request handler will recognize a properly formed url query string and will forward the request, but 'filter' by itself is not recognized as an OData keyword and your filter criteria will not be applied. The response will contain an unfiltered data set, which may not be readily apparent until it is inspected carefully.

# DEAPI Filter Rules

Unlike RAPI, which lets you filter by any field regardless of performance impact (and many times those queries will fail if performance is bad enough), DEAPI restricts which fields can be used in a filter to ensure adequate performance and delivery to the end user. Continuing the previous ELT paradigm discussion, the goal of DEAPI is not to provide "Query as a Service", but to provide a pipeline that lets you load data into your own store where you can further query, filter and analyze that data.

In order to filter on a field, two conditions must be met:

  1. The field needs to be marked as filterable in the metadata (FieldIsFilteringAllowed = true).

  2. An index with that field must exist for the core object (found under ExtendedMetadata.Indexes collection of each object). If an index includes multiple fields, then a user can filter on all or any fields starting from the first indexed field.

Generally speaking, if an index contains a specific field that field should also be marked as FieldIsFilteringAllowed = true. However, the opposite is not necessarily true: it is quite common for a field to be marked as FieldIsFilteringAllowed = true but not be included in an index. That is because indexes were created only for fields that CSOD's reporting teams felt were most likely to be used as filters (as indexes take space and maintaining them affects performance). However, nothing prevents customers from creating additional indexes on their local stores if the business need arises... so the FieldIsFilteringAllowed property can largely be considered informational for such use cases. Indexes will be your primary informational tool to determine how you can filter DEAPI objects directly.

Filtering index rules:

  • If an index contains fields A,B,C, then you can filter on either (A), (A, B), (A, B, C) or even (C, B, A), but not (B, A), (B, C), or (C). Note: a different field order like (C, B, A) is only allowed if ALL fields in the index are present. Sorting (using $orderby) has a similar requirement, but it is sensitive to index field ordering and direction. If an index has A, B, C, you can sort by (A), or (A, B), or (A, B, C), but not (B, A), (B, C) or any other combination. You can sort by (A desc, B desc) but not (A desc, B) or (A, B desc).

  • If an object has multiple indexes, fields from different indexes cannot be combined. All fields being requested in the query should be covered by a single index. For example, if you have two indexes with fields A,B,C and D,E a single query cannot filter on (A,B,C,D,E).

In the event an invalid filter request is made, an error message alerting you of the condition will be given.

# OData Change Tracking

Cornerstone rolled out DEAPI OData 4.0 change tracking functionality for inserted, updated, and deleted data in the August 2022 release starting with the 'user_ou_core' and 'user_ou_ multi_core' objects, with support for additional objects following soon thereafter. Prior to this Release, customers had to manually supply date/time filters in the URL to retrieve new and updated information (see previous section on '_last_touched_dt_utc'), and there was no way to be informed of deleted data for GDPR compliance. With OData 4.0 change tracking, users will find a delta link with an encrypted token at the end of their payload response. This token can be used within the next 15 days to receive any inserts, updates or deletes to/from the dataset represented by the original API query. After the changed data set is received, a new delta token is provided and should be used for the next data call (assuming your other filter criteria does not change).

:::Tip To determine if a particular core object supports OData 4.0 change tracking, inspect the results of the $metadata endpoint. If ExtendedMetadata.ObjectSupportsChangeTracking = true for that object, then you can proceed with the steps below. :::

To use OData change tracking:

  1. Insert Prefer: odata.track-changes into the HTTP header to request that the endpoint return a delta link. The response header will include Preference-Applied: odata.track- changes to indicate that the preference is applied. Subsequent pages will automatically reapply Prefer: odata.track-changes. If an object does not support delta change tracking, you will receive a '501' (Not Implemented) error.

  2. Process the response as you normally would, using the @odata.nextLink to page through the result set (see the next section on paging). The last page of the result set will include a delta link with a delta token (e.g., "@odata.deltaLink":"...user_ou_core?deltatoken=xxxx"). This delta token is an encoded representation of the original query and a timestamp. You should retain this delta link/token for your next request and only replace it when you receive the next delta link/token. If the request or response is interrupted or otherwise unsuccessful, or if you encounter an error while processing the records locally, can use the last token to request the delta result set again. You do not need to keep track of timestamps. You can repeat this step indefinitely, using the next delta link provided, as long as a delta link is used within 15 days.

  3. If more than 15 days passes before you use the delta token, you will receive a '410' (Gone) error and will need to retrieve the full data set starting with step 1 again. Because 'Tombstone records' that represent deleted application data expire after 15 days and are cleaned out of the RTDW on a routine basis, any data retrieval that exceeds that time period cannot guarantee to be a true change tracking data set.

Delta result sets may include two types of records:

  • Records deleted since the timestamp in the delta token. Record will include an 'id' field (which may consist of multiple composite key fields) and a 'reason' field with a value of 'deleted'. The 'id' field can be used to delete the corresponding record (if it exists) in the customer's local database.

  • Records inserted or updated since the timestamp in the delta token. Note: the payload does not distinguish whether the record was inserted or updated... customers should continue to use their current MERGE or UPSERT process used to process DEAPI data.

CSOD Reporting Environments