How we use OData
Power of the data at your fingertips
OData is an open standard for accessing and working with data in Web APIs. You can read about the standard on the OData Site
A brief introduction to how Flex uses OData
Most services in Flex adopt the OASIS OData standard for interrogation (and editing)
This provides a consistent, convention driven means of accessing data, and self-describing schema that permits 3rd party tools to dynamically discover the schema and assist users in querying records.
Examples of tools that support OData querying include, Microsoft Excel, PowerBI and much of the Office 365 eco-system - meaning Flex can integrate with these tools out the box.
All the core OData features are advertised through the OpenAPI interface. However, some features do require knowledge of the OData syntax to utilise the full functionality.
At its simplest OData can be considered SQL over HTTP, permitting a structured query language for accessing data over the internet.
OData Querying
Building OData queries is a matter of setting options on the Query String. If using a generated client from our OpenAPI these query-string parameters are available as properties that can be populated according to the OData conventions.
Basic Use-cases
OData provides the fundamental ability to select, filter, order, and page data from the data source. It also support more advanced concepts such as expansion (similar to a Join in SQL), and aggregation.
Select / Projection : $select
In order to reduce the amount of data queried and delivered (reducing internet payloads and server processing) use of the $select parameter allows consumers to define the fields required.
Field name are currently case-sensitive
Omitting a $select returns all fields is the SQL equivalent of ‘Select * from …’
Filtering; $filter
The $filter operation is used apply filtering to a data set – in a similar means to a WHERE clause in SQL.
Filtering supports a sophisticated grammar supporting operators such as
| Operator | Description |
|---|---|
| eq | Equals |
| ne | Not Equals |
| lt | Less than |
| gt | Greater than |
| le | Less than or equal |
| ge | Greater than or equal |
| any | Where any child item matches some criteria |
| all | Where all child items match some criteria |
| and | Where all conditions apply |
| or | Where any of the conditions apply |
| not | Used to negate a condition |
| has | Bitwise filtering of enum flags |
For a general introduction see https://www.odata.org/getting-started/basic-tutorial/
For more in depth details see http://docs.oasis-open.org/odata/odata/v4.0/errata03/os/complete/part2-url-conventions/odata-v4.0-errata03-os-part2-url-conventions-complete.html#_Toc453752358
Filtering also support a number of built-in functions including:
| Function | Description |
|---|---|
| contains() | To test if a text value contains some value |
| startswith() | To test if a text value starts with some value |
| endswith() | To test if a text value ends with some value |
| toupper() | Convert the text to upper case |
| tolower() | Convert the text to lower case |
| [other date and math operators] | See OData Docs |
Paging : $top, $skip
Paging allows large data sets to be sent and displayed in the client application in defined windows.
Odata allows the page size to be set with the $top operator. Pages can be navigated to using the $skip operator to skip over the appropriate number of records.
A sort order should be provided when paging.
If unspecified, the default page size is 1000 records.
For performance reasons the maximum page size is 1000 records. Larger data sets can be access using multiple queries and use of the $skip operator
Count; $count
Because of paging, the number of results returned may be less than the overall set of matching result.
To implement paging controls, and provide feedback to the end user, it’s useful to know how many records match a query in total. The $count operator when enabled returns the overall count of matching entities as part of the results.
This can be used to calculate the number of pages, and provide navigation through the results
Sorting: $orderby
Most properties can be used for sorting a result set, using the $orderby operator. Columns can be sorted by ascending and descending order.
Composite Sorting can be by providing multiple fields - comma separated.
When no sort is defined the sequence may be undefined and unstable.
Setting a $orderby value will guarantee consistent results
Navigation/Expansion: $expand
Expansion allows related resources to be included in the results. It’s loosely analogous to a JOIN in SQL.
Using the $expand operator enables a parent entity or a collection of child entities to be included in the results in a single query -which is much more efficient than making multiple queries.
E.g.
Example Navigation use cases
Find all Spaces and include their Components
Find all Zones including the top 10 Spaces that
When expanding collections those child collections can also have $select, $filter, $orderby, $top, $skip and $count operators applied to precisely define & limit the result set.
You can additionally expand child items (but be careful not to overload the system with excessively large data sets)
E.g.
Find all Components , including the Spaces they are in and the Floor they belong to
/Components?$filter=<somefilter>&$expand=Spaces($expand=Level)
Advanced Querying
Portfolio level querying
The default querying in the Flex Aim model is at “portfolio level” – i.e across all visible assets and models.
If no filter is provided the results will be of all entities in models where the current user has access to see in the current tenant (based on permissions). That permits conceptual queries such as
‘Find all spaces across all my assets with a classification of SL_20_15_50 : Meeting rooms and an Occupancy > 10’
More typically additional filtering will be employed to limit results from a specific model, or set of models under an asset. E.g.
Spaces?$filter=AssetModelId eq 1234
# All rooms in Model 1234
Spaces?$filter=Model/AssetId eq 5678 and Model/IsCurrent eq true and Model/SegmentName eq “Architecture”&$expand=Model
#All rooms in the latest “Architectural” models linked to Asset 5678
Advanced Filtering
The OData query system provides some very sophisticated querying, especially when utilising the $expands capabilities. A few examples include:
Find all Windows that don’t have a Type associated
Find all Spaces where no Components classified as Pr_40_50_28 : Extinguishers
Find all Doors that don’t have a Fire-Rating defined
Aggregates
OData supports grouping and aggregation, enabling consumers to use the server to calculate totals, sums and averages (etc) of values without downloading all the data. E.g.
Find the total Area of all Rooms on the 3rd Floor.
Find all duplicate named Component types in a model (in thise case we are both aggregating and filtering on the aggregate result)
ComponentTypes?$apply=filter(AssetModelId eq 1234)/groupby((Name), aggregate($count as Count))&$filter=Count gt 1
Updated over 1 year ago