OData Extensions for Data Aggregation

1. OData Extensions for Data Aggregation Ralf Handl, SAP Hubert Heijkers, IBM Gerald Krause, SAP Mike Pizzo, Microsoft Martin Zurmühl, SAP 20 December 2013

2. Where do we want to go

3. Simple Composable Query Language $apply= transformation/ transformation/ … Transformations Subset topcount(…), topsum(…), …, bottompercent(…), filter(…), identity Reshape aggregate(…), expand(…) Fork & Merge groupby(…, sequence) , concat(sequence, sequence, …) Custom Functions sequence

4. GET ~/Sales

5. GET ~/Sales?$apply=aggregate(Amount with sum as Total)

6. GET ~/Sales?$apply=groupby((Product), aggregate(Amount with sum as Total))

7. GET ~/Sales?$apply=groupby((Product))

8. GET ~/Sales?$apply=groupby((Product), aggregate(Amount with sum as Total)) &$orderby= Totaldesc

9. GET ~/Sales?$apply=groupby((Product), aggregate(Amount with sum as Total)) &$filter= Totalge 8

10. GET ~/Sales?$apply=filter(Amount ge 4) /groupby((Product), aggregate(Amount with sum as Total))

11. GET ~/Sales?$apply=groupby((Product), aggregate(Amount with sum as Total, Amount with max as MaxAmount))

12. GET ~/Sales?$apply=groupby((Product), aggregate($count as SalesCount))

13. GET ~/Sales?$apply=groupby((rollup(Country,Customer),rollup(Category,Product)),aggregate(Amount with sum as Total))

14. GET ~/Sales?$apply=groupby((Product,Country), aggregate(Amount with sum as Total)) /groupby((Country),topcount(1,Total))

15. GET ~/Sales?$apply=groupby((Product,Country),aggregate(Amount with sum as Total)) /groupby((Country),M.TopCountAndBalance(N=1,A=‘Total'))