The objective: standardizing on an abstraction

We are a team from Microsoft that is responsible for internal data and operations.  As you can imagine, there are a variety of services modeling various aspects of operation (Inventory, Support Teams, Supply Chain, Telemetry, Incident Handling, etc.) and each are implemented in different manners and with various technologies.  Each service exposes its own API (for automated processes and scripting) and its own UI (tools for manual interaction).  Sooner or later that becomes a “Tower of Babel” problem (too many languages) so people stop understanding each other.  We use OData as a standardized abstraction to cope with it. Here is why.

Information services store data which model the real world, and help us to create, manipulate and search this data. The better we understand “what’s inside”, the easier we can use those services. We describe the real world using words from our language - nouns (things), adjectives (properties of those things), verbs (actions), prepositions (relations between things) etc.  OOP languages offer a hierarchy of classes (and their instances), properties, methods and references to model our thinking.  OData attempts to combine an OO approach (resources as classes, their instances having a Create, Read/Update, and Delete lifecycle, and relationships as navigations), with an uniform search language similar to SQL.  OData permits defining actions and functions (class-specific methods).  The OData protocol is built on top of ubiquitous platform-independent HTTP.  All that sounds promising!  Also, the RESTful idea that every “thing” has a unique URL (“reference”), and once you follow that URL you can get the details about that “thing”, is very appealing too as compared with complex instructions about how to connect to a service and what to tell it in order to get the “thing”.

With that in mind, we have made a plan to provide OData façades to all services, thus exposing a uniform model of thinking and a uniform API (on the wire), as well as a uniform experience for C# and JavaScript code writers.  But what about UI, can we provide a uniform UI to browse any business data?

The problem: generating syntactically correct OData queries and interpreting the results

As an example, suppose someone is interested in finding information about a specific employee.  They are told that they can find what they are looking for at https://services.odata.org/Northwind/Northwind.svc/Employees(1).  This is what they will retrieve as a result:

[gist id="261b948a9d18562d600e" file="NancyBlob.xml"]

What an awful lot of obscure XML!  Eventually they can reach the section which really matters:

[gist id="261b948a9d18562d600e" file="Nancy.xml"]

They cannot easily read this, nor can they click on the URL about Employees(1)/Orders to see what Orders Nancy created because Atom format does not compose absolute URLs for links (navigations, in OData terminology).

A programmatic client might need all those details of the Atom format in order to interpret it, but I am not a program, I am a human being, and I want readable data!  The same thing happens with a naïve question such as “What is this OData endpoint all about?”  The OData answer is behind the $metadata URL https://services.odata.org/Northwind/Northwind.svc/$metadata:

[gist id="261b948a9d18562d600e" file="Metadata.xml"]

It might tell you about Customers, Products, Categories, and other related things… if you know how to read it!  That exercise is left to the reader.

Another problem: suppose I want to find the orders, dated earlier than last week and with shipping address containing “12”.  Having thoroughly read all the https://www.odata.org documents (as we all do before even touching the keyboard ;-)), I may be tempted to compose a URL like this:

https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=OrderDate le 2013-08-07 and substringof(ShipAddress, '12') eq true

That will return an error message because the Edm.DateTime literal is ill-formed; I should have entered OrderDate le datetime'2013-08-07'.  However, even with the corrected date format:

https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=OrderDate le datetime'2013-08-07' and substringof(ShipAddress, '12') eq true

The service returns an empty list:

[gist id="261b948a9d18562d600e" file="EmptyList.xml"]

The list is empty because the order of the parameters for substringof was incorrect (see https://www.odata.org/documentation/odata-version-2-0/uri-conventions/).  The correct URL should in fact be https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=OrderDate le datetime'2013-08-07' and substringof('12', ShipAddress) eq true which yields all the orders (click the URL to see them all).

[gist id="261b948a9d18562d600e" file="Results.xml"]

The last example of a difficult query for a naïve user to build is the following: “Who is the employee that placed the order number 10258?”  The correct URL would be:

https://services.odata.org/Northwind/Northwind.svc/Employees?$filter=Orders/any(x: x/OrderID eq 10258)

And the answer is Nancy (see the first XML example above).

But if I ask, “what are all the orders placed by the employee with id 1?” the URL would look like this:

https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=Employee/EmployeeID eq 1

Now, one example uses the format Orders/any(x: x/OrderID eq 10258) and the other the format Employee/EmployeeID because the relationships between the two entities are not the same (1 to many and many to 1).  If the user does not know this, it will be incredibly hard to build a correct query (e.g. https://services.odata.org/Northwind/Northwind.svc/Employees?$filter=Orders/OrderID eq 10258 does not work).

Our solution: a simple to use, graphical OData query builder

So we decided to present naïve users with a simple to use ODataQueryBuilder.  The steps to use it are very simple:

  1. Choose an endpoint (you can customize which endpoint you want to use, and even cache the $metadata, if you prefer).
  2. Select the entity that you want to query and how many results you want.
  3. Put some conditions on the search.
  4. And optionally sort the results by some value.

Done.  The query has been built and if you press “Search” it will be run and display the results!

image

The ODataQueryBuilder gives the user the ability to quickly construct relatively complex queries with zero a priori knowledge of OData syntax and rules.  The ODataQueryBuilder provides dropdown lists to avoid typos.  When users select a filter, they are shown choices like “before”, “after”, etc. and other familiar prepositions such as “less than” or “less than or equal to” and even shorthand values like “yesterday” or an “hour ago” which are typical time points in the operation world (The suggested values can be easily tweaked to accommodate anyone’s need).

image

The ODataQueryBuilder also answers the question “What is this OData endpoint all about?” because it exposes lists of all resource types, their properties, and navigation properties.

image

For example, one of the previous hard-to-build queries (I want to find the orders that have an order date earlier than last week and a shipping address that contains the value “12”) would look something like this:

image

As we simplify the process of creating an OData query, we also display the results in a human-readable, tabular, presentation of the OData responses (no XML):

image

Expansion ($expand) is modeled by presenting the list of navigational links from which you can select the navigator and expand the list of related items as shown above.

And the results are shown as nested tables:

image

We have decided to favor simple and intuitive UI versus “completeness”; we do not claim that any OData filter can be composed using this UI.  For example, all Where conditions are “and”-ed (and there is no UI to “or”), but the majority of naïve user filters can be composed this way, even such non-trivial ones as navigational conditions (see examples above).  You could try to compose them mentally and type them into the browser without mistakes or you could let the ODataQueryBuilder do them for you.

 

Conclusion

We use our ODataQueryBuilder to construct queries against any OData service we provide and users can optionally navigate the results directly.  For this we stand up a basic HTML page that configures the ODataQueryBuilder with a list of service endpoints we want to give users access to.  The users can select an endpoint, use the query builder to construct the query, and then view and drill down into the results through the navigation links.

Our ODataQueryBuilder is able to talk to any OData service, not just a known or specially built service.  The source code is in JavaScript and the instructions on how to use and configure the ODataQueryBuilder can be found in the open-source publication.