Business systems

OData, the API of business systems

SAP, SuccessFactors, Microsoft Dynamics and many other business systems answer in OData. What a service root and $metadata are, how $filter, $select, $expand and paging work, v2 against v4, and what to test.

Ask a company's HR, finance or sales system for data and there's a good chance it answers in OData: SAP (S/4HANA, Gateway, SuccessFactors), Microsoft Dynamics 365 and Business Central, SharePoint, and many more. OData is REST with rules: every service is shaped the same way, so once you can read one, you can read them all.

The service root and its tables

An OData API starts at its service root, an address such as https://api.example.com/odata/v4. Under it are entity sets, which behave like tables: Employees, Departments, Products. Each entity in a set is a row, with properties (columns) and a key that picks one:

GET /odata/v4/Employees             every employee (a page at a time)
GET /odata/v4/Employees('E1001')    one, by its key
GET /odata/v4/Departments(5)        a number key has no quotes

$metadata: the API describes itself

Add /$metadata to the service root and the service answers with an XML file listing every entity set, each property with its type (Edm.String, Edm.Int32, Edm.DateTime…), which are keys, and navigation properties: the links between sets, like an employee's Department. Tools read it to show you the tables and fields, and to build queries for you. It is to OData what an OpenAPI file is to a REST API.

Asking for exactly what you need

Query options go after the ?, and all start with $:

OptionDoesExample
$selectonly these properties$select=FirstName,LastName
$filteronly matching rows$filter=DepartmentID eq 1 and Active eq true
$orderbysorted$orderby=LastName desc
$top, $skiphow many, from where$top=10&$skip=20
$expandinclude linked entitiesDepartments(5)?$expand=Employees
$counthow many match, in total$count=true (v2: $inlinecount=allpages)

Filters compare with words, not symbols: eq, ne, gt, ge, lt, le, joined with and and or. Text has functions: startswith(LastName,'P'), contains(Email,'sales') (v2 says substringof('sales',Email)). Text values go in single quotes.

Paging: the answer is rarely everything

A service gives a page at a time, often 50, 100 or 1,000 rows, and a link to the next page: @odata.nextLink in v4, __next in v2. A test that reads only the first answer and counts the rows is counting one page. Ask for $count to know the total, and follow the next links to read everything.

v2 and v4 look different

The same employee, from the same service, in its two versions:

// v4
{ "value": [ { "EmployeeID": "E1001", "HireDate": "2012-01-01", "Salary": 42000 } ], "@odata.count": 140 }

// v2
{ "d": { "results": [ { "__metadata": { … }, "EmployeeID": "E1001",
  "HireDate": "/Date(1325376000000)/", "Salary": "42000.00" } ], "__count": "140" } }

In v2 the rows sit in d.results, dates are /Date(milliseconds)/ and decimals and counts come as text. In v4 they're in value, with ISO dates and real numbers. SAP Gateway and SuccessFactors mostly speak v2; Dynamics and newer SAP services, v4. Checks written for one break on the other, so know which one you're testing.

Signing in, and writing

Most services want Basic sign-in or OAuth. SAP services that let you change data also ask for a CSRF token: first a GET with the header X-CSRF-Token: Fetch, then the token from its answer on the POST, PATCH or DELETE. A write without it fails with 403, whatever your permissions.

What to test

  • The filters you rely on, at their edges: a date on the boundary, an empty result, text with an apostrophe ('O''Brien': doubled inside quotes).
  • Paging: the total from $count matches what you read by following every next link.
  • Permissions: a user without access should get 403, or fewer rows, or a property left out. Business systems often hide rows and fields rather than refuse.
  • What isn't allowed: some properties can't be filtered or sorted on, and say so with a 400. A test can expect that error.
  • Speed: $select keeps answers small; a wide $expand can turn one fast request into a slow one. Measure before you ship a query that runs every few minutes.

Key takeaways

  • An OData service has a service root, entity sets like tables, and a $metadata file that describes every one of them.
  • $select, $filter, $orderby, $top, $expand and $count ask for exactly what you need, in the URL.
  • Answers come a page at a time. Use $count and the next links before you count anything.
  • v2 and v4 shape the same data differently, so your checks must match the version.

In the app: Connections: browse and query OData, Salesforce and NetSuite · Signing in

Next: The Salesforce REST API

A question these lessons don’t answer? Write to support@apitestcenter.com — we add lessons for the questions people ask.