OData Extensions for Data Aggregation Ralf Handl SAP
- Slides: 15
OData Extensions for Data Aggregation Ralf Handl, SAP Siva Harinath, Microsoft Hubert Heijkers, IBM Gerald Krause, SAP Mike Pizzo, Microsoft July 26 th, 2012
Where do we want to go Dimensions (4) Country (2) Product Group (2) Measures (2) Country Netherlands Food Amount (SUM) Customer USA Non-Food Amount (MAX) Product Group Product Country Product Group Netherlands Food 2 Non-Food 3 USA Food Non-Food Amount (SUM) 14 5
GET ~/Sales ID 1 2 3 4 5 6 7 8 Product. Group 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? $select=Product Paper Sugar Coffee Paper Sugar Paper
GET ~/Sales? $select=Product &$aggregate Product Paper Sugar Coffee
GET ~/Sales? $select=Product, Amount &$aggregate=Amount Product Paper Sugar Coffee Amount 8 4 12
GET ~/Sales? $select=Product, Amount &$aggregate=Amount &$orderby=Amount desc Product Coffee Paper Sugar Amount 12 8 4
GET ~/Sales? $select=Product, Amount &$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? $select=Product, Revenue &$aggregate=sum(Amount) as Revenue &$filter=Amount ge 8 Product Coffee Revenue 8 Product Paper Sugar Coffee Paper Sugar Paper Amount 1 2 4 8 4 2 1 2
GET ~/Sales? $select=Product, Amount, Max. Amount &$aggregate=sum(Amount) , max(Amount) as Max. Amount Product Coffee Paper Sugar Amount Max. Amount 12 8 8 4 4 2
GET ~/Sales? $select=Product, Amount &$aggregate=Amount , count(*) as Sales. Count Product Coffee Paper Sugar Amount 12 8 4 Sales. Count 2 4 2
$rollup=(Country, Customer), (Product. Group, Product) 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 Product. Group 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
Measures <Property Name="Amount" Type="Decimal"> <Type. Annotation Term="Data. Aggregation. Dependent. Measure"> <Property. Value Property="Default. Aggregation. Function" String="sum"> <Property. Value Property="Accepted. Aggregation. Functions"> <Collection> <String="sum"/> <String="max"/> </Collection> </Property. Value> </Type. Annotation> </Property>
Hierarchies <Property Name="Product. Group" Type="String" Nullable="false" /> <Property Name="Product" Type="String" Nullable="false" /> <Type. Annotation Term="Data. Aggregation. Leveled. Hierarchy"> <Property. Value Property="Name" String="Product. Hierarchy"/> <Property. Value Property="Levels"> <Collection> <String="Product. Group"/> <String="Product"/> </Collection> </Property. Value> </Type. Annotation>
Open Topics • • Links for „drilling into“ aggregated entities More hierarchy types: parent-child, ragged, … Hierarchy-based functions: descendants(), siblings() Expressions in $aggregate: sum(Revenue) - sum(Costs) as Profit • Split into $filter and $postfilter (WHERE/HAVING) • Merge $aggregate and $select, implicit $expand • Set functions for complex aggregations: ~/My. Set/$Select(city, sales)/$Aggregate(sales)/$Select(sales)/$Aggregate() • References to annotations for units/currencies/texts
