We‘ve recently published the first version of the OData Extension for Data Aggregation. As its name suggests, it is an extension to OData 4.0 that allows requesting aggregated data without violating the basic OData interaction paradigms.

 

In a nutshell it adds a single system query option $apply that takes a sequence of transformations:

 

$apply= transformation / transformation /

 

The specification defines three types of standard transformations:

  1. Subsetting
    • topcount(), topsum(), toppercent()
    • bottomcount(), bottomsum(), bottompercent()
    • filter()
    • identity
  2. Reshaping
    • aggregate()
    • expand()
  3. Forking & Merging
    • groupby(, sequence )
    • concat( sequence, sequence,)

 

In addition service-defined custom functions can be used as set transformations.

 

This provides you with a simple and powerful query language that covers all of the use cases we came up with, and then some.

 

Here are some examples, based on a fictitious Sales entity set:

  • Aggregate one property, grouped by another

 

$apply=groupby((Product),aggregate(Revenue))

 

  • Request distinct values of a property

 

$apply=groupby((Product))

 

  • Aggregate a property

 

$apply=aggregate(Revenue)

 

  • First filter, then group and aggregate

 

$apply=filter(Revenue lt 1000)/groupby((Product),aggregate(Revenue))

 

  • Count input entities per group

 

$apply=groupby((Product,Country),aggregate($count as NumberOfDeals))

 

  • First group and sum, then group differently and average the sums calculated in the first step

 

$apply=groupby((Product,Month),aggregate(Revenue))

      /groupby((Product),aggregate(Revenue with average

                                   as MonthlyAverageRevenue))

 

 

And a lot more I won’t mention here, just have a look at the draft specification.

 

Your feedback is welcome!

–Ralf

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

    1. Ralf Handl Post author

      Hi Eric,

      SAP HANA groups and aggregates implicitly: if you define an entity set based on a calculation view, the view knows which properties it can aggregate, and which properties it can group by. If you then request

      GET ~/Sales?$select=Product,Amount

      it will automatically group by Product and aggregate the Amount.

      Regards!

      –Ralf

      (0) 
      1. Cassie Liu

        Hi Ralf,

        I’m really interested in “groupby” or “select distinct” in gateway odata service. And you told that “define an entity set based on a calculation view” will solve the the question in sap HANA. But I don’t understand how to define an entity set based on a calculation view.

        So could you explain it in detail?

        Thank you~~~

        Best regards,

        Cassie.

        (0) 

Leave a Reply