OData Extensions for Data Aggregation Ralf Handl SAP

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

Where do we want to go Dimensions (4) Country (2) Category (2) Measures (2) Country Netherlands Food Amount (SUM) Customer USA Non-Food Amount (MAX) Category Product Country Category Netherlands Food 2 Non-Food 3 USA Food Non-Food Amount (SUM) 14 5

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

GET ~/Sales ID 1 2 3 4 5 6 7 8 Category Non-Food Non-Food Product Paper Sugar Coffee Paper Sugar Paper Country USA USA USA Netherlands Customer Amount Joe 1 Joe 2 Joe 4 Sue 8 Sue 4 Sue 2 Sue 1 Sue 2

GET ~/Sales? $apply=aggregate(Amount) Amount 24

GET ~/Sales? $apply=groupby((Product), aggregate(Amount)) Product Paper Sugar Coffee Amount 8 4 12

GET ~/Sales? $apply=groupby((Product), aggregate()) Product Paper Sugar Coffee

GET ~/Sales? $apply=groupby((Product)) Product Paper Sugar Coffee

GET ~/Sales? $apply=groupby((Product), aggregate(Amount)) &$orderby=Amount desc Product Coffee Paper Sugar Amount 12 8 4

GET ~/Sales? $apply=groupby((Product), aggregate(Amount)) &$filter=Amount ge 8 Product Coffee Paper Amount 12 8 Product Paper Sugar Coffee Paper Sugar Paper Amount 1 2 4 8 4 2 1 2

GET ~/Sales? $apply=filter(Amount ge 8) /groupby((Product), aggregate(Amount)) Product Coffee Amount 8 Product Paper Sugar Coffee Paper Sugar Paper Amount 1 2 4 8 4 2 1 2

GET ~/Sales? $apply=groupby((Product), aggregate(Amount, Amount with max as Max. Amount)) Product Coffee Paper Sugar Amount Max. Amount 12 8 8 4 4 2

GET ~/Sales? $apply=groupby((Product), aggregate($count as Sales. Count)) Product Coffee Paper Sugar Sales. Count 2 4 2

GET ~/Sales? $apply=groupby((rollup(Country, Customer), rollup(Category, Product)), aggregate(Amount)) Food Sugar USA Coffee Non. Food Paper 14 2 12 5 5 Joe 6 2 4 1 1 Sue 8 8 4 4 Netherlands 2 2 3 3 Sue 2 2 3 3 Country Customer Category Product USA USA USA Netherlands USA USA Netherlands Joe Joe Sue Sue NULL NULL Joe Sue Sue NULL Non-Food Food Food Non-Food Food Non-Food Paper Sugar Coffee Paper Sugar Paper NULL NULL NULL Amount 1 2 4 8 4 2 3 2 12 5 2 1 6 1 8 4 2 3 14 5 2 3

GET ~/Sales? $apply=groupby((Product, Country), aggregate(Amount)) /groupby((Country), topcount(1, Amount)) Product Paper Sugar Coffee Sugar Paper Country USA USA Netherlands Amount 5 2 12 2 3 Product Country Coffee USA Paper Netherlands Amount 12 3

GET ~/Sales? $apply=groupby((Product, Country), aggregate(Amount)) /groupby((Country), M. Top. Count. And. Balance(N=1, A='Amount')) Product Paper Sugar Coffee Sugar Paper Country USA USA Netherlands Amount 5 2 12 2 3 Product Country Coffee USA Paper Netherlands Amount 12 7 3 2