OData Extension for Data Aggregation Version 1.0

goldbashedΤεχνίτη Νοημοσύνη και Ρομποτική

15 Νοε 2013 (πριν από 3 χρόνια και 11 μήνες)

190 εμφανίσεις

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
1

of
30

OData Extension for
Data Aggregation

Version
1.0

Working Draft
01

0
7

Novem
ber
201
2

Technical Committee:

OASIS Open Data Protocol (OData) TC

Chairs:

Barbara Hartel

(
barbara.hartel@sap.com
),
SAP AG

Ram Jeyaraman

(
Ram.Jeyaraman@microsoft.com
),
Microsoft

Editor:

Ralf Handl

(
ralf.handl@sap.com
),
SAP AG

Hubert Heijkers

(
hubert.heijkers@nl.ibm.com
),
IBM

Gerald Krause
(
gerald.krause@sap.com
),
SAP AG

Michael Pizzo

(
mikep@microsoft.com
),
Microsoft

Additional artif
acts:

This prose specification is one component of a Work Product which also includes:



XML schemas: (list file names or directory name)



Other parts (list titles and/or file names)

Related work:

This specification replaces or supersedes:



S
pecifications repl
aced by this specification
(
hyperlink,

if available
)

This specification is related to:



R
elated specifications
(
hyperl
ink,
if available
)

Declared XML namespaces:



list namespaces
declared within this specification

Abstract:

This specification
add
s

basic grouping

and
aggregation
functionality
(
sum,
min
,
ma
x,
count
,

)

to
OData without changing any of the base principles
of

OData

Status:

T
his
Working Draft

(WD) has been produ
ced by one or more TC Members; it has not yet been
voted on by the TC or
approved

as a Committee Draft (Committee Specification Draft or a
Committee Note Draft). The OASIS

document
Approval Process

begins officially with a TC vote
to approve a WD as a Committee Draft. A TC may approve a Working Draft, revise it, and re
-
approve it any nu
mber of times as a Committee Draf
t
.



Copyright © OASIS Open

201
2
. All Rights Reserved.

All capitalized terms in the following text have the meanings assigned to them in the OASIS Intellectual
Property Rights Policy (the "OASIS IPR Policy"). The full
Policy

may be found at the OASIS website.

This document and translations of it may be copied and furnished to others, and derivative works that
comment on or otherwise explain it or assist in its

implementation may be prepared, copied, published,
and distributed, in whole or in part, without restriction of any kind, provided that the above copyright notice
and this section are included on all such copies and derivative works. However, this documen
t itself may
not be modified in any way, including by removing the copyright notice or references to OASIS, except as
needed for the purpose of developing any document or deliverable produced by an OASIS Technical
Committee (in which case the rules applica
ble to copyrights, as set forth in the OASIS IPR Policy, must
be followed) or as required to translate it into languages other than English.

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
2

of
30

The limited permissions granted above are perpetual and will not be revoked by OASIS or its successors
or assigns.

This document and the information contained herein is provided on an "AS IS" basis and OASIS
DISCLAIMS ALL WARRANTIES, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO ANY
WARRANTY THAT THE USE OF THE INFORMATION HEREIN WILL NOT INFRINGE ANY
OWNERSHIP RIGH
TS OR ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A
PARTICULAR PURPOSE.


odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
3

of
30

Table of Contents

1

Introduction

................................
................................
................................
................................
4

1.1 Termi nol ogy
................................
................................
................................
..............................
4

1.2 Normati ve References

................................
................................
................................
...............
4

1.3 Non
-
Normati ve References
................................
................................
................................
........
4

2

Back
ground

................................
................................
................................
................................
5

2.1 Moti vation

................................
................................
................................
................................
.
6

2.2 Design Principl es

................................
................................
................................
......................
6

2.3 Scope of Data

Aggregation by Example
................................
................................
......................
8

3

Aggregati on
-
Rel ated Extensions to OData Core

................................
................................
.........

10

3.1 $aggregate

................................
................................
................................
.............................

10

3.1.1 Distinct Values

................................
................................
................................
.................

10

3.1.2 Aggregated Val ues

................................
................................
................................
...........

11

3.1.2.1 Producer
-
Controlled Aggregation
................................
................................
................................
...................
11

3.1.2.2 Consumer
-
Controlled Aggregation
................................
................................
................................
.................
12

3.1.3 Boundary Conditions

................................
................................
................................
........

15

3.2

$rollup

................................
................................
................................
................................
....

15

3.3 Identi fying Aggregated Entities

................................
................................
................................
.

18

3.4 Processing Sequence (Sprint 2)

................................
................................
...............................

18

3.5 Cross
-
Joins (Sprint 3)

................................
................................
................................
..............

20

3.6 ABNF for Extended URL Conventions

................................
................................
......................

21

4

Aggregati on
-
Rel ated Annotations

................................
................................
..............................

23

4.1 Aggregatabl e Properties

................................
................................
................................
..........

23

4.2 Groupabl e Properties

................................
................................
................................
..............

24

4.3 Hier
archies (Sprint 2)
................................
................................
................................
...............

24

4.4 Functions and Actions on Aggregated Entities (Sprint 2)

................................
............................

26

5

Conformance
................................
................................
................................
............................

27

Appendix A.

Acknowledgments
................................
................................
................................
.....

28

Appendix B.

Non
-
Normati ve Text

................................
................................
................................
..

29

B.1 Subsidiary section
................................
................................
................................
...................

29

B.1.1 Sub
-
subsidiary section

................................
................................
................................
.....

29

Appendix C.

Revision History
................................
................................
................................
........

30


odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
4

of
30

1

Introduction

This specification
a
dd
s

the notion of
aggregation

to

OData without changing any of the base principles
of

OData
. I
t de
fin
es
a r
epresentation and semantics for aggregation of data
, especially:



S
emantics and operations for querying aggregated data
,



R
esults format for queries conta
ining aggregated data
,



Annotat
ions to indicate what can be aggregated, and how
.

1.1

Terminology

The key words “
MUST

,

MUST NOT

,

REQUIRED

,

SHALL

,

SHALL NOT

,

SHOULD

,

SHOULD
NOT

,

RECOMMENDED

,

MAY

, and

OPTIONAL
” in this document are to be interpreted as described
in
[RFC2119]
.

1.2

Normative References

[OData
-
CSDL]

OData
Common Schema Definition Language

(CSDL)
Version 1.0
. DD Month
2012. OASIS Committee Specification Draft 01.

http://docs.oasis
-
open.org/odata/odata
-
csdl/v1.0/csd01/odata
-
csdl
-
v1.0
-
csd01.doc
.


[RFC2119]

Bradner,

S.,


Key words for use in RFCs to Indicate Requirement Levels

,
BCP
14,
RFC 2119, March 1997.

http://www.ietf.org/rfc/rfc2119.txt
.

1.3

Non
-
Normative References

[POLA]

http://en.wikipedia.org/wiki/Principle_of_least_astonishment

[TSQL ROLLUP]

http://msdn.microsoft.com/en
-
us/library/bb522495.aspx

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
5

of
30

2

Background

OData
services expose a data model that describes the schema of the service in terms of the

Entity Data
Model (EDM)
, an Entity
-
Relationship model

that d
escribes the data and then allows

for querying that
data. The responses returned by an OData provider are based on that
exposed data model
and retain the
relationships between the entities in the model. Adding the notion of
aggregation

to

OData, without
ch
anging any of the base principles in OData

as is,
has two sides to

it
:

1.

Means for the
consumer
to
query
aggregated data
on top of any given
data model (for sufficiently
capable
data producers
)

2.

Means for the
producer
to
annotate

what data can be aggregated,
and what it can be aggregated
by, allowing
consumers
to avoid asking questions that the
producer
cannot answer.

It’s important to notice that
, while

each of these two
sides

might be valuable in
its

own

right

and can be
used independently of the other, thei
r combination provides additional value for
consumers
. The
descriptions provided by the
producer
will

help a consumer understand more of the data structure

looking
at the
service's exposed data model
. T
he
query extensions allow the
consumers
to
explicitly
express
the
desired aggregation behavior
for a particular query
. It
will also allow
consumers
to
formulate

queries that
refer to the annotations as shorthand.

As part of the extension we are introducing the following terms:



Aggregatable propert
y


a proper
ty for which the
values can be
aggregated,



Groupable property


a
property that can be used to define the aggregation scope.



Hierarchy


a
n arrangement
of groupable properties
whose values are represented as being
“above”, “below”, or “at the same level as
” one another.

The following diagram shows the
se terms applied to a simple model that we will use throughout this
document.


I
n

our terminology “Amount” is an aggregatable property and the properties of the Customer, Time,
Product, and ProductGroup entity

types are groupable. These can be arranged in three hierarchies:



Product

hierarchy based on
groupable properties of the
Product Group and Product

entity types

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
6

of
30



Customer

hierarchy based on Country and Customer



Time

hierarchy based on Year, Month and Da
te

In

this example we’ll presume that Amount has summation as its default aggregation behavior,
To help
the consumer of this service, the $metadata is accordingly annotated using the vocabulary
defined
in
section
4
.

If you have an
OLAP

background, you can view this as a

S
ales

cube


with
an

Amount

measure
” and
three “dimensions”; as these terms are heavily overloaded we avoid them

in this document
.

Both query extensions and descriptive annotations can be applied to
n
ormalized

as well as partly or fully
denormalized schemas.


Note that OData’s EDM does not imply a storage model; it may be a completely conceptual model whose
data shape is calculated on
-
the
-
fly for each request. The actual "entity
-
relationship shape" o
f the model

should be chosen to simplify understanding and querying data by the target audience of a service.
Different target audiences may well require differently shaped services on top of the same storage model:
beauty is in the eye of the beholder.

2.1

Mo
tivation

OData is an application
-
level protocol for interacting with data via RESTful web services.
It
represents
data as RESTful resources that make it easy for
consumers
to “browse” through this “web of data”,
following relations between data elements ("
entities") that are exposed as hyperlinks to other web
resources.

An OData service’s contract is defined by simple, well
-
defined conventions and semantics applied to the
data model exposed by the service, providing a high level of semantic interoperabilit
y between loosely
coupled
consumers
and
producers
.

In addition to this hypermedia
-
driven data access, OData offers query capabilities via a small number of
features (filtering, paging, projecting, and expanding along associations) that are by themselves
in
tentionally simple and can be freely combined into an astonishingly powerful language.

Adding simple aggregation capabilities to the mix of query features avoids cluttering OData services with
an exponential number of explicitly modeled “aggregation level
entities” or else restricting the
consumer
to a small subset of predefined aggregations.

2.2

Design
Principles

The design principles of OData are to:



Make it easy to implement and consume a basic OData service over a variety of data sources.
Rather than try a
nd expose the full functionality of all stores, define common features for core
data retrieval and update scenarios and incremental, optional features for more advanced
scenarios.



Leverage Data Models to guide
consumers
through common interaction patterns
rather than
force
consumers
to write complex queries against raw data



Define consistency across the protocol and a single way to express each piece of functionality

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
7

of
30

The design principles of OData extensions are to:



Ensure extensions do not violate the cor
e semantics of OData



Avoid defining different representations for common concepts across extensions



Ensure independent extensions compose well



Ensure
consumers
can ignore extended functionality and still query and consume data correctly

Extending OData to
support Data Aggregation should follow the following design principles:



Extensions for data aggregation should not break existing
consumers

and client libraries; existing
consumers

should be able to consume models containing predefined measures and annotat
ions
without understanding those annotations or additional semantics



Consumers

should trigger aggregation explicitly; unless the
consumer

does something different it
should get existing OData behavior.



The shape of the result should follow the shape of the

model: don’t break OData’s type system



Aggregat
ion

results consist of
entities
that
are normal OData entities. In particular;

o

They can be individually identified and addressed, i.e. have a self
-
link and a unique id

o

They can link to other entities



“Inherit
ed” links from the group they represent, and that still are meaningful for
the aggregate, or



New l
inks to “drill” to sets of less aggregated entities

o

They can link to actions and/or function that can be invoked on them



Supported aggregation behavior
can

be

described via metadata annotations



Consumers

should retain full control over
what gets
aggregat
ed
:

o

Specify a set of entities (entries) that are to be grouped into subsets and retrieved as a
single entity per subset representing the aggregate of that subse
t

o

Specify how these groups are formed

o

Specify how these groups are aggregated



Producers

should exhibit the most useful / minimally astonishing default behavior

(see
[POLA]
)




Extensions should work on “star” schemas as well as "flattened" (
denormalized) schemas



Extensions should work with “analytical” as well as “tabular” data providers

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
8

of
30

2.3

Scope of Data Aggregation by
Example

Using the model from section
2

we
add data
to illustrate the capabilities introduced in th
is extension.


The first

question

we’d like to ask is
:
which c
ustomer
s bought which products?


Which leads to the s
econd question:
who bought how much of what? This may be visualized as
a
cross
-
table:




Food



Non
-
Food






Sugar

Coffee


Paper

USA


USD

14

2

12

5

5



Joe

USD

6

2

4

1

1



Sue

USD

8


8

4

4

Netherlands


EUR

2

2


3

3



Sue

EUR

2

2


3

3

The data in this cross
-
table can be written down in a
shape
that more closely resembles the structure of
the data returned by OData in response t
o an aggregate query
:

Custo
mer/
C
ountr
y

Customer/
Name

Product/ProductGroup/
Name

Product/
Name

Amount

Currency
/
Code

USA

Joe

Non
-
Food

Paper

1

USD

USA

Joe

Food

Sugar

2

USD

USA

Joe

Food

Coffee

4

USD

USA

Sue

Food

Coffee

8

USD

USA

Sue

Non
-
Food

Paper

4

USD

N
etherlands

Sue

Food

Sugar

2

EUR

Netherlands

Sue

Non
-
Food

Paper

3

EUR

USA

NULL

Food

Sugar

2

USD

USA

NULL

Food

Coffee

12

USD

USA

NULL

Non
-
Food

Paper

5

USD

Netherlands

NULL

Food

Sugar

2

EUR

Netherlands

NULL

Non
-
Food

Paper

1

EUR

USA

Joe

Food

NULL

6

USD

USA

Joe

Non
-
Food

NULL

1

USD

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
9

of
30

USA

Sue

Food

NULL

8

USD

USA

Sue

Non
-
Food

NULL

4

USD

Netherlands

Sue

Food

NULL

2

EUR

Netherlands

Sue

Non
-
Food

NULL

3

EUR

USA

NULL

Food

NULL

14

USD

USA

NULL

Non
-
Food

NULL

5

USD

Netherlands

NULL

Food

NULL

2

EUR

Netherlands

NULL

Non
-
Food

NULL

3

EUR

Note that this result contains seven fully qualified aggregate values and fifteen rollup, or “subtotal”, rows
(shown in bold).

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
10

of
30

3

Aggregation
-
Related Extensions to OData Core

3.1

$aggregate

Aggregation behavior is triggered using the ne
w query option
$aggregate
.

It
allows

defin
ing

the shape
of the result

and how this shape is filled with aggregated values
.

3.1.1

Distinct

Values

Aggregat
ion means
dividing entities into groups and calculating one aggregated entity per group.
The

groups are defined by a list of (
grouping
) properties:


GET ~/Customers?$aggregate
=
$
group
by

Name

will return

[


{ Name: “
Luc
” },


{ Name: “
Joe
” },


{ Name: “
Sue
” }

]

Note that “Sue” appears only once although the customer base contain
s two different Sues.

The above result is “display
-
formatted JSON”, omitting the quotes around property names. It is not
intended to be valid OData JSON, and it is definitely non
-
normative.

We will use this n
otation throughout
the document.

The result als
o doesn’t contain the unique IDs for the result entities; these will be
introduced later.

Aggregation is also possible
across related entities
. Let’s look at customers that bought something
:

GET ~/
Sales?$aggregate=$groupby

Customer/Name

will return

[


{
Customer
: { Name: “
Joe
” }

},


{
Customer
: { Name: “
Sue
” }

}

]

The result
has the same
shape
as a standard OData
request

with a
$select

query option listing the
same proper
ties as
$aggregate

and
a
corresponding
$expand

query option to bring the required
related entities in
to

scope.

Note that “Luc” does not appear as he hasn’t bought anything and therefore there are no sales entities
that refer/navigat e to Luc.

However, even
though both Sues bought products, only one “Sue” appears in the aggregate result.
Including properties that guarantee the right level of uniqueness in the
$select

clause can repair that:

GET ~/Sales?$aggregate=$groupby

Customer
/Name,Customer/CustomerID

will return

[


{
Customer
: { Name: “
Joe

, CustomerID: “C1”

}

},


{
Customer
: { Name: “
Sue

, CustomerID: “C2”

}

},


{
Customer
: { Name: “
Sue

, CustomerI
D: “C3”

}

}

]

This could also have been formulated as

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
11

of
30

GET ~/Sales?$aggregate=$groupby

Customer


&$select=Customer/Name,Customer/CustomerID

Grouping
by a navigation property
brings all properties of the related entites into the result shape,
which

then can be projected partially away using the standard query option
$select
.

The

first question in the motivating example in section
2.3
, which customers bought which products,

can
now be expressed as

GET ~Sales?$
aggregate
=
$groupby

Customer/Name,
Customer/CustomerID,
Product/Name

and will return

[


{
Customer
: { Name: “
Joe

, CustomerID: “C1”

}
, Product: { Name: “Coffee”
}

}
,


{
Customer
: { Name: “
Joe

, CustomerID: “C1”

}
, Product: { Name: “Paper”
}

}
,


{
Customer
: { Name: “
Joe

, CustomerID: “C1


}
, Product: { Name: “Sugar”
}

}
,


{
Customer
: { Name: “
Sue

, CustomerID: “C2”

}
, Product: { Name: “
Coffee

}

},


{
Customer
: { Name: “
Sue

, CustomerID: “C2”

}
, Product: { Name: “Paper”
}

},


{
Customer
: { Name: “
Sue

, CustomerID: “C3”

}
, Product: { Nam
e: “
Paper

}

}
,


{
Customer
: { Name: “
Sue

, CustomerID: “C3”

}
, Product: { Name: “Sugar”
}

}

]

3.1.2

Aggregated Values

3.1.2.1

Producer
-
Controlled Aggregation

$aggregate

can take a comma
-
separated list of property names

that may be prefixed with a
navigation
path.
.
Th
e values f
or these properties
will be calculated using the
producer
-
defined default aggregation
behavior (summation in this example)
.

GET ~/Sales?$aggregate=Amount

$groupby

Customer/Country,Product/Name

will return

[


{ Custom
er: { Country: “Netherlands” }, Product: { Name: “Paper” }, Amount: 3 },


{ Customer: { Country: “Netherlands” }, Product: { Name: “Sugar” }, Amount: 2 },


{ Customer: { Country: “USA” }, Product: { Name: “Coffee” }, Amount: 12 },


{ Customer: { Country
: “USA” }, Product: { Name: “Paper” }, Amount: 5 },


{ Customer: { Country: “USA” }, Product: { Name: “Sugar” }, Amount: 2 }

]

The aggregated

values will be returned in the entity property whose values are aggregated, of course
without changing its ty
pe.

GET ~/Products?$aggregate=
Sales/
Amount

$groupby

Name

will return

[


{
Name
: “
Coffee

, Sales: [

{ Amount: 12
}

] }
,


{
Name
: “
Paper

, Sales: [

{

Amount: 8
}

] }
,


{
Name
: “
Pencil

, Sales: [] }
,


{
Name
: “
Sugar

, Sales: [

{ Amount: 4 }

] }

]

Note that aggregation does not alter the cardinality of the Sales navigation property, and that it always
returns a
n

array with
at most one

object
. If the
re are no “base” entities to be aggregated, the array is
empty.

Careful observers will notice that the above amounts have been aggregated across currencies, which is
semantically wrong. Yet it is the correct response to the question asked, so be careful wh
at you ask for.
The semantically meaningful question

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
12

of
30

GET ~/Products?$aggregate=Sales/Amount



$groupby

Sale
s/Amount,Sales/Currency/Code

will return

[


{
Name
: “
Coffee

, Sales: [

{ Amount: 12, Currency
: {

Code: “USD”
}

}

] }
,


{
Name
: “
Paper

, Sales:
[ {

Amount:
3
,

Currency: { Code: “EUR” }
}
,


{ Amount:


5, Currency: { Code: “USD
” }

}

] }
,


{
Name
: “
Pencil

, Sales: [] }
,


{
Name
: “
Sugar

, Sales: [

{ Amount:
2, Currency: { Code: “EUR” }
}
,


{ Amount: 2, Currency: { Code: “USD” }

}

] }

]

Also note that associations are “expanded” in a left
-
outer
-
join

fashion, starting from the target of the
aggregation request, before grouping the entities for aggregation. Afterwards the results are “folded back”
to match the cardinality:

GET ~/Customers?$aggregate=
$groupby

Country,Sales/Product/Name

returns the different products sold per country:

[


{
Country
:

“Netherlands”,

Sales: [
{
Product: { Name: “Paper”

},



{
Product: { Name: “Sugar”

}] }
,


{
Country
:

“USA”,

Sales: [
{
Product: { Name: “Coffee”

},


{
Product: { Name: “Paper”

},


{
Product: { Name: “Sugar”

}] }

]

3.1.2.2

Consumer
-
Controlled

Aggregation

3.1.2.2.1

Standard Aggregation Functions

Instead of using the producer
-
defined default aggregation
behavior
, the
consumer
may specify one of the
predefined aggregation functions
min
,
max
,
sum
,
average
,
count
,
distinctCount
:

GET ~/Sales?$aggregate=sum(Amount)

$
groupby

Customer/Country

will return

[


{ Customer: { Country: “
Netherlands


}
, Amount: 5

},


{ Customer: { Country: “
USA


}, Amoun
t: 19 }

]

And

GET ~/Sales?$aggregate=average(Amount)

$group
by

Customer/Country

will return

[


{ Customer: { Country: “
Netherlands


}, Amount: 1.
6666667

},


{ Customer: { Country: “
USA


}, Amount: 3.8 }

]

The aggregated

values will be returned in the entity property whose values are aggregated, of course
without changing its type.

In this example

Amount is an
Edm.Decimal

without a fixed
S
cale
.

If it were an integer or had a fixed
Scale
, the aggregated values would have been rounded

according
ly
, see section
3.1.2.2.3
.

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
13

of
30

3.1.2.2.2

Aliasing

When applying an aggregation function to a property, an alias name may be provided to
allow multiple
aggregates for a single property. The alias will introduce a dynamic property in the entity type of the
aggregated property used as the function argument to preserve the result shape. The alias must be a
SimpleIdentifier (see
[
OData
-
CSDL
, section 2
0
.2]
), and it must not collide with names of declared

properties of that entity type, nor with other aliases for properties of the same entity type.

GET ~/Sales?$aggregate=sum(Amount),average(Amount) as AvgAm
t




$groupby

Customer/Country

will return

[


{ Customer: { Country: “
Netherlands


}, Amount: 5, AvgAm
t
: 1
.
6666667

},


{ Customer: { Country: “
USA


}, Amount: 19, AvgAm
t
: 3.8 }

]

If the aggregated property is not part of the base set,
but of a related set, we need a slightly different
syntax.
The introduced dynamic property MUST always be in the same set as the original property:

GET ~/Products?$aggregate
=
sum(Sales/Amount)
,


av
era
g
e(Sales/
Amount) as
Sales/
Av
g
Amt



$groupby

Name

will return

[


{
Name
: “
Coffee

, Sal
es: [{ Amount: 12, AvgAmt: 6

}
] }
,


{
Name
: “
Paper

, Sales: [{ Amount: 8, AvgAmt: 2

}
] }
,


{
Name
: “
Pencil

, Sales: [] }
,


{
Name
: “
Sugar

, Sales: [{ Amount: 4, AvgAmt: 2 }] }

]

To
simplify the syntax of these queries and OData querie
s in general we propose

a “scoping” syntax
, see
ODATA
-
144
:

GET ~/Products?$aggregate=Sales/(sum(Amount),average(Amount) as AvgAmt
)



$groupby

Name

that also makes sense for other query options, e.g.
$select=Items/(Price,Currency,Quantity,Unit)
.

There’s no hard distinction betw
een
groupable
and
aggregatable properties
: the same property can be
aggregated and used to group the aggregated results:

GET ~/Sales?$aggr
egate=sum(Amount) as TotalSales

$groupby

Am
ount

will return all distinct amounts appearing in sales orders and how much money was made with deals of
this amount:

[


{
Amount
:

1,

TotalSales: 2 }
,


{
Amount
:

2,

TotalSales: 6 }
,


{
Amount
:

4,

TotalSales: 8 }
,


{
Amount
:

8,

TotalSales: 8 }

]

3.1.2.2.3

The St
andard Aggregation Functions
sum
,
min
,
max
,
average

The standard aggregation functions
min
,
max
,
sum
, and
average

take the name of a numeric property
(optionally with path prefix) as argument. The result property will have the same type as the argument
pro
perty if used without an alias name
.

For
min

and
max

this poses no problem.

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
14

of
30

The results of the aggregation function
sum

may in some cases exceed the limits of the type of the
aggregated entity property. In this case the
producer

MUST respond with
400 Bad R
equest

and a
meaningful human
-
readable error message.

If
sum

is
used with an alias name, the
producer

MUST choose a type for the
result
property that is
capable of
representing
the aggregated values.

This may require
switching to a

larger integer type, to

Edm.D
ecimal

with sufficient
Precision

and
Scale
,
or
to
Edm.Double
.

If

average

is used without an alias name,
the result will be rounded

to
the next number that can be
represented with t
he type of the aggregat
ed property.

If
average

is
used with an alias,
t
he
result

property will
be of type
Edm.Double
.

3.1.2.2.4

The Standard Aggregation Function
count

The aggregation function
count

has two overloads: the one without parameters counts the entities in the
group to be aggregated into a single entity. The other
takes the

name of a property as its argument

and
counts the no
n
-
NULL values of this property.

Both forms
must always specify an alias
, and the result

property will have type
Edm.
Decimal

with
Scale
="0"

and sufficient
Precision
.

GET ~/Sales?$
aggregate=count() as SalesCount

$groupby

Product/Name

would return:

[


{
Product
: {
Name
: “
Coffee


}, SalesCount: 2

},


{
Product
: {
Name
: “
Paper


}, SalesCount: 4

},


{
P
roduct
: {
Name
: “
Sugar


}, SalesCount: 2 }

]

When used with
a navigation property (with path if necessary)
it

count
s

the number of related entities:

GET ~/Products?
$aggregate
=count(Sales) as SalesCount

$groupby

Name

would return:

[


{
Name
: “
Coffee

, SalesCount: 2
},


{
Name
: “
Paper

, SalesCount: 4
},


{
Name
: “
Pencil

, SalesCount: 0
},


{
Name
: “
Sugar

, SalesCount: 2}

]

Note that, when specifying a navigation path, the cou
nt
MUST
appear on the object containing the final
navigation property (i.e., the parent of the entities being counted):

G
ET ~/ProductGroups?$aggregate=count(Products/Sales) as Products/SalesCount



$groupby

Name

would return:

[


{
Name
: “
Food

,

Products:[{SalesCount: 4
}
] }
,


{
Name
: “
Non
-
Food

, Products:[{SalesCount: 4
}
] }

]

This is consist
ent with the behavior of other aggregation functions: the aliased aggregated value is a
sibling of the original value.

If we add the product name to the select list, we get a similarly structured
result.

GET ~/ProductGroups?$aggregate=count(Products/Sales) as Products/SalesCount



$groupby

Name,Products/Name

would return:

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
15

of
30

[


{
Name
: “
Food

,

Products:[{
Name: “
Coffee
”,
SalesCount: 2
}
,


{Name: “Sugar”, SalesCount: 2}
] }
,


{
Name
: “
Non
-
Food

, Products
:[{Name: “Paper”, SalesCount: 4
}
,


{Name: “Pencil”, SalesCount: 0}]

}

]

3.1.2.2.5

The Standard Aggregation Function
distinctCount

The aggregation function
distinctCount

always
takes the name of a property as its argument. It
counts the distinct values of this property, omitting any NULL values. For navigation properties
and
collect
ion properties
it counts the distinct entities in the union of all entities related to entities in the group
.

It must always specify an alias, and the result

property will have type
Edm.
Decimal

with
Scale
="0"

and sufficient
Precision
.

The request

GET ~/Customers?$aggregate=distinctCount(Sales/Product)



as Sales
/DistinctProduct
s



$groupby

Country

returns the number of different products sold per country:

[


{
Country
:

“Netherlands”,

Sales: [
{
DistinctProducts: 2

}] }
,


{
Country
:

“USA”,

Sales: [
{
DistinctProducts: 3

}] }

]

Note

that DistinctProducts is located in the Sales entity as it is an aggregate of Sales/Product: as stated
above aliasing just

renames


a property, it does not relocate it.


3.1.3

Boundary Conditions

The aggregation behavior relies on the context specified by the
properties in
the
$
groupby

part of

$aggregate
. If for
any
of these properties the service provider is not able to group
same
values
in a
single aggregate entity, it
MUST

reject the request with an error response. It
MUST NOT

app
ly any
implicit rule to form aggregate entities indirectly by another property related to it in some way.

In the above example, although
product IDs

can be basically any string and amount values can be any
decimal value, if the provider is able to group t
heir values, then the request for their distinct value
combinations

GET ~/Sales?$aggregate=$groupby

Product/Name,Amount

would result in

[


{
Product
: {
Name
: “
Coffe
e
” }
, Amount: 4
},


{
Product
: {
Name
: “
Coffee
” }
, Amount: 8
},


{
Product
: {
Name
: “
P
aper



}
, Amount:
1

},


{
Product
: {
Name
: “
Paper


}
, Amount: 2 },


{
Product
: {
Name
: “
Paper


}
, Amount: 4 },


{
Product
: {
Name
: “
Sugar


}
, Amount:
2

}

]

3.2

$roll
up

The
$rollup

query option is used to
request additional
levels of aggregation
in addition to the most
granular level defined by
$aggregate
. Adding the
$rollup

query option results in add
ing additional
entries

to the result representing the ag
gregated values produced as a result of the rollup, in which
,
starting with the deepest level,
progressively more properties, based on
the specified named
hierarchies

(see section
4.3
)
or ad
-
hoc hierarchies (expressed as lists
of properties)
, are omitted from those entities.
odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
16

of
30

Note that properties are grouped, using
parentheses
,
to form a leveled
hierarchy along which the
aggregation needs to take place
,

and that hierarchies are
themselves
comma separated. Aggregations
will be pro
vided for the cartesian product

for the inte
rsections along these hierarchies.

Revisiting the second question of the motivating example in section
2.3

we can retrieve the first seven
rows of the desired result with the request

GET ~/Sales?$aggregate=Amount




$groupby

Customer/Country,Customer/Name,





Product/ProductGroup/Name,Product/Name
,


Currency/Code

resulting in

[


{ Customer: { Country: “USA”, Name: “Joe” },


Product: { Product
Group: { Name: “Non
-
Food” }, Name: “Paper” },


Amount: 1
, Currency: { Code: "USD" }



},

{ Customer: { Country: “USA”, Name: “Joe” },


Product: { ProductGroup: { Name: “Food” }, Name: “Sugar” },


Amount: 2
, Currency: { Code: "USD" }



},

{ Cus
tomer: { Country: “USA”, Name: “Joe” },


Product: { ProductGroup: { Name: “Food” }, Name: “Coffee” },



Amount: 4
, Currency: { Code: "USD" }



},

{ Customer: { Country: “USA”, Name: “Sue” },


Product: { ProductGroup: { Name: “Food” }, Name: “Coff
ee” },


Amount: 8
, Currency: { Code: "USD" }



},

{ Customer: { Country: “USA”, Name: “Sue” },


Product: { ProductGroup: { Name: “Non
-
Food” }, Name: “Paper” },


Amount: 4
, Currency: { Code: "USD" }



},


{ Customer: { Country: “Netherlands”, N
ame: “Sue” },


Product: { ProductGroup: { Name: “Food” }, Name: “Sugar” },


Amount: 2
, Currency: { Code: "EUR" }



},


{ Customer: { Country: “Netherlands”, Name: “Sue” },


Product: { ProductGroup: { Name: “Non
-
Food” }, Name: “Paper” },


A
mount: 3
, Currency: { Code: "EUR" }



}

]

To produce the missing fifteen subtotals we
add

a
$rollup

query option:

GET ~/Sales?$aggregate=Amount



$groupby

Custom
er/Country,Customer/Name,





Product/ProductGroup/Name,Product/Name
,


Currency/Code



&
$rollup=(Customer/Country,Customer/Name),



(Product/ProductGroup/Name,Product/Nam
e)

which
returns

the same seven entires shown above plus
additional
fifteen
entries representing the
aggregated subtotals
:



[


...


{ Customer: { Country: “USA”

},


Product: { ProductGroup: { Name: “Food” }, Name: “Sugar” },


Amount: 2
, Currency
: { Code: "USD" }



},


{ Cus
tomer: { Country: “USA”
},


Product: { ProductGroup: { Name: “Food” }, Name: “Coffee” },


Amount: 12
, Currency: { Code: "USD" }

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
17

of
30



},


{ Customer: { Country: “USA” },


Product: { ProductGroup: { Name: “
Non
-
Food” },
Name: “
Paper
” },


Amount: 5
, Currency: { Code: "USD" }



},


{ Customer: { Country: “Netherlands”

},


Produc
t: { ProductGroup: { Name: “
Food” }, Name: “
Sugar
” },


Amount: 2
, Currency: { Code: "EUR" }



},


{ Customer: { Country: “Netherlands”

},


Product: { ProductGroup: { Name: “Non
-
Food” }, Name: “Paper” },


Amount: 1
, Currency: { Code: "EUR" }



},


{ Customer: { Country: “
USA
”, Name: “
Joe
” },


Product: { ProductGroup:

{ Name: “Food” }

},


Amount: 6
, Currency: { Code: "USD"

}



},


...


{ Customer: { Country: “
USA”
},


Produc
t: { ProductGroup: { Name: “Food” }

},


Amount: 14
, Currency: { Code: "USD" }


}
,


...

]

The properties that are aggregated away

during rollup

are omitted from the response payload.

Note tha
t all properties referenced in the
$rollup

clause must be part of the
$select

clause but that
the
$select

clause might contain more properties by which we still group and that provide “context” for
the aggregations being returned.

Note that
$rollup

stops
one level earlier than
GROUP BY ROLLUP

in TSQL, see

[TSQL ROLLUP]
: per
hierarchy the leftmost property is never rolled up. That’s fine if the model contains a property for the “all”
level (having only a single value). Otherwise the pseud
o
-
property
$all

can be used to force rollup to the
point where the leftmost “real” property is rolled up:


&$rollup=(
Customer/Country,Customer/Name)
,


(
$all,
Product/ProductGroup/Name,Product/Name)

will return
five
additional
ent
ities rolled up across all
product groups
:

[


...


{ Customer: { Country: “
Netherlands”, Name: “Sue”
},


Amount: 5, Currency: { Code: "EUR" }


}
,


{ Customer: { Country: “
Netherlands”
},


Amount:
5, Currency: { Code: "EUR" }


}
,


{ Customer
: { Country: “
USA”
, Name: “Joe”

},


Amount:

7, Currency: { Code: "USD" }


}
,


{ Customer: { Country: “
USA”, Name: “Sue”
},


Amount: 12, Currency: { Code: "USD" }


}
,


{ Customer: { Country: “
USA”
},


Amount: 19, Currency: { Code: "USD" }



}
,

]

To rollup by the key of each related entity you can simply specify the name of the navigation property.
When rolling up by key, all key fields for the related entity must be present in the $select list.

How does $rollup w
ork together with $top,
$skip
, and $inlinecount
?

Will it only take the finest level of
granularity into account and add subtotals for a group when a group boundary is part of that chunk?

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
18

of
30

Assumption:
client
-
driven paging is for “painting a screen”. For cross
-
tab reports this depen
ds on

the
page layout (axis arrangement) chosen by the client, so we could forbid $top, $skip and $inlinecount in
conjunction with $rollup and for
ce the client to add filters to the request, e.g. Country eq ‘USA’. The client
can get the list of values for const
ructing these filters with a “distinct” request up
-
front.

3.3

I
dentifying
Aggregated Entities

Aggregated
entities
have the same structure as the individual
entities

from which they have been
calculated
, so the shape of the results can still mirror the shape de
scribed by the service. However,
aggregated
entities

have different
id
s

and self links than unaggregated
entities. An aggregated entity
’s
self link must encode the necessary information to re
-
retrieve that particular aggregate value, for instance
the set o
f unique
groupable

property values that the aggregate represents.

Therefore, every
entity

must provide the canonical URI by which it can be identified. This URI can be
constructed
from

the original URI requesting the aggregate result whose
$filter

expressi
on is
extended by conditions for the
entity’s
restrictions for all groupable properties in
$select
. The same
restrictions can also be used for constructing an
entity
id

which must be provided for every
entity
.

Looking again to the sample request for gettin
g sales amounts per product and country presented in
section
3.1.2.1
Error! Reference source not found.

GET ~/S
ales?$aggregate=Amount

$groupby

Customer/Country,Product/Name

will return corresponding metadata as shown here for a single
entity
:

[


{
odata.id:

~
/
Sales(Customer
-
Country=‘Netherlands’,Product
-
Name=
‘Paper’)

,



odata.readLink
:

~/Sales?$aggregate=A
mount;$groupby=Customer/Country,Product/Name
&$filter=Cust
omer
/
Country eq ‘Netherlands’

and
Product
/
Name eq ‘Paper’
”,



Customer: { Country: “Netherlands”
},


Product: { Name: “Paper” },


Amount: 3


},


...

]

3.4

Processing
Sequenc
e

(Sprint 2)

Elaborate for next working draft version

The core system query options are processed in the following sequence:



$filter



$inlinecount



$orderby



$skiptoken



$ski
p



$top



$expand



$select



$format

The two new aggregation
-
relat ed system query options are integrated into this processing sequence:



$aggregate



$filter

(restricted to properties in
the
shape
defined by

$aggre
gate
)



$inlinecount



$orderby

(restricted to properties in
the
shape defined
by
$aggregate
)



$skiptoken



$skip

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
19

of
30



$top



$expand

(restricted to navigation properties
in
the
shape defined by

$
aggregate
)



$select

(restricted to propertie
s in
the shape defined
by
$aggregate
)



$rollup

(restricted to properties in
the shape defined
by
$aggregate
)



$format

Applying

aggregation first

covers the most prominent use cases like “find the five best
-
selling products
per country”. Yet it is insufficien
t to answer more sophisticated questions like “how much money do we
make with small sales”, which requires filtering the base set before applying the
aggregation. To enable
this type of question, and even more sophisticated ones, we introduce the concept o
f sequencing:
specify
several operations in
$aggregate

in the
order the
y

are to be applied
, separated by a colon
:

GET ~/Sales?$aggregate=
$filter=Amount le 1
:Amount

means “filter first, then aggregate”, and results in

[


{
Amount: 2 }

]

Operations can be aggregation expressions and the system
query option $filter, plus $expand with
clauses that specify a $filter for the related entites.

Using $filter within $aggregate does not preclude
using it as a normal system query option
:

GET ~
/Sales?
$aggrega
te=$filter=Amou
nt le 5.1
:Amount
$
groupby

Product/Name


&$filter=Amount ge 100000


More examples:

City
-
Country
-
Continent

Population per country

GET ~/Cities?$aggregate=
Population

$groupby

Country/Name,Continent/Name

All countries with megacities

and their continents

GET ~/Cities?$aggregate=
$filter=Population ge 1000000


:Population
$groupby

Country/Name,Continent/Name


&
$rollup=(Continent/Name,Country/Name)

All countries with millions of city dwellers an
d the

continents

only for these countries

GET ~/Cities?$aggregate
=Population

$groupby

Country/Name,Continent/Name
)


&
$filter=Population ge 1000000


&
$rollup=(Continent/Name,Country/Name)

All

countries with millions of city dwellers

and all continents with cit
ies

independent of th
eir size

GET ~/Cities?$aggregate=Population

$groupby

Country/Name,C
ontinent/Name


&$filter=Population ge 1000000


&$rollup=(Continent/Name,Country/Name)

unfiltered

The
unfiltered

suffix implies that
$rollup

is applied as if no
$filter

were specified.


Allow using new filtering on expanded items toget
her with sequencing

GET ~/SalesOrders?
$aggregate=
$filter=State eq 'incomplete'



:$expand=Items($filter=not Shipped)



:
Items/Amount


$
groupby
Items/Amount,Cus
tomer/Country


Express different functions in different directions with
aggregation
sequencing

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
20

of
30

GET ~/Sales?$aggregate=Amount

$groupby

Customer/Country,Time/Month,Time/Date


:
average(Amount)

$groupby

Customer/Country,Time/Month


GET ~/Sales?$aggregate=
Amount

$groupby

Customer/Country,Time/Month,Time/Date


:$filter=Amount gt 10000


:
average(Amount)

$groupby

Customer/Country,Time/Month


GET ~/Sales?$aggregate=
$filter=Amount gt 10000



:average(Amount)
$groupby

Customer/Country,Time/Month


Now enter $rollup

GET
~/Sales?
$aggregate=Amount


$
groupby

Customer/Country,Customer/Name,



Time/Year,Time/Month,Time/Date


:average(Amount)



$groupby
Customer/Country,Customer/Name,




Time/Year,
Time/Month



&
$rollup=(Customer/Country
-
sum
-
Customer/Name),


(
Time/Year
-
average
-
Time/Month)

3.5

Cross
-
Joins (
Sprint 3
)

Old

section on Queries Spanning Entity Sets

OData supports querying related entities through defining relationship and navigation properties in the
data model. These navigation paths help guide simple
consumers
in understanding and navigating
relationships.

I
n some cases, however, requests may span entity sets with no predefined associations. Such queries
could be facilitated by a general extension to OData that would allow requests to be rooted at the entity
container, rather than an individual entity set. Th
e entity container defines implicit navigation properties to
each entity set (and potentially each function) it contains, and queries across entity sets could be
supported by referring to properties qualified by entity set.

For example, if Customers and Co
untries were in separate entity sets with no defined relationship, to
query all Customers for a particular country based on a common country code one could pose the
following query:

GET ~SalesData?$select=
Customers/Name,Countries/Name


&
$expan
d=
Customers,Countries


&
$
filter=(Customers/CountryCode eq Countries/CountryCode)


and (Countries/Name eq 'USA')

would return:

[



{

Customers: [{Name: “Joe”}]
,

Countries
:

[
{
Name
:

“USA”
}
] },


{

Customers: [{Name: “Sue”}]
,

Co
untries
:

[
{
Name
:

“USA”
}
] }

]

Where useful navigations exist it is beneficial to expose those as explicit navigation properties in the
model, but the ability to pose queries that span entity sets not related by an association provides a
mechanism for advanc
ed
consumers
to pose queries across entity sets based on other join conditions,
such as relationships implied by a measure.

For example, the
consumer
could issue a query over the SalesData entity container:

GET ~SalesData?
$aggregate=Sales/Amount


$groupby
Products/Name,Time/Da
te
,Sales/Amount

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
21

of
30

Where the result would look like:

[



{

Sales:[{Amount:4}]
,

Products
:
[
{
Name
:
“Sugar”
}
]
,
Time
:
[
{
Date
:
1/2/12
}
] },


{

Sales:[{Amount:4}]
,

Products
:
[
{
Name
:
“Coffee”
}
]
,
Time
:
[
{
Date
:
1/1/12
}
] },


{

Sales:[{Amount:8}]
,

Products
:
[
{
Name
:
“Coffee”
}
]
,
Time
:
[
{
Date
:
1/2/12
}
] },


{

Sales:[{Amount:1}]
,

Products
:
[
{
Name
:
“Paper”
}
]
,
Time
:
[
{
Date
:
1/1/12
}
] },


{

Sale
s:[{Amount:1}]
,

Products
:
[
{
Name
:
“Paper”
}
]
,
Time
:
[
{
Date
:
1/2/12
}
] },


{

Sales:[{Amount:5}]
,

Products
:
[
{
Name
:
“Paper”
}
]
,
Time
:
[
{
Date
:
1/3/12
}
] },

]

The entity container may be annotated with
measures

that can be applied to aggregations from the entity
container.

Applying such a term to the SalesData entity container for an "ActualOverSales" aggregate would look
like:

<
EntityContainer

Name
=
"
SalesData
"

m:IsDefaultEntityContainer
=
"
true
"
>


<
...
>


<
TypeAnnotation

Term
=
"
Measures
"
>


<
Collection
>


<
Record
>



<
PropertyValue

Property
=
"
Name
"

String
=
"
ActualOverSales
"

/>


<
PropertyValue

Property
=
"
Type
"

String
=
"
Edm.Integer
"

/>


</
Record
>


</
Collection
>


</
TypeAnnotation
>

</
EntityContainer
>

The SalesData entity container would support the query:

G
ET ~SalesData?$select=Products/Name,Time/Month,


ActualOverSales



&$expand=Products,Time


&$aggregate=ActualOverSales

with the result:

[



{

ActualOverSales:
1
0,

Products
:
[
{
Name
:
“Sugar”
}
]
,
Time
:
[
{
Month
:
“20
12/1”
}
] },


{

ActualOverSales:
-
20,

Products
:
[
{
Name
:
“Coffee”
}
]
,
Time
:
[
{
Month
:
“2012/1”
}
] },


{

ActualOverSales:
25
,

Products
:
[
{
Name
:
“Paper”
}
]
,
Time
:
[
{
Month
:
“2012/1”
}
] },

]

3.6

ABNF for Extended URL Conventions

Formally define syntax for $aggregate,
$rollup, and
sequencing as delta to the core URL ABNF

aggregate = "$aggregate"


[ "=" ( "*"


/ aggregateItem *( COMMA aggregateItem )


)


]

aggregateItem = propertyWithPath


/ aggregationFunction "("
pro
pertyWithPath ")"


[ WSP "as" WSP
newDynamicPropertyWithSamePath ]


rollup = "$rollup=" rollupAxis *( COMMA rollupAxis )

rollupAxis =
annotatedHierarchyName


/
"(" ( "$all" / propertyWithPath ) *( COMMA propertyWithPath ) ")"


quer
yOptions = sequence
Option *(

"&" harmlessOption

)

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
22

of
30


/ queryOption *(

"&" queryOption

)


sequence
Option =
sequenc
ableOption 1*(":"
sequencab
leOption )


queryOption
= sequenc
ableOption / harmlessOption


harmlessOption = non
Sequencable
Option




/ aliasAndValue




/ parameterNameAndValue




/ customQueryOption


sequenc
ableOption = filter


/ orderby


/ aggregate


/ rollup


/ select


non
Sequencable
Option = define






/ skip



/ top




/ format




/ inlinecount




/ skiptoken


Unrelated

idea:
$orderby=Size:$filter=(Top(5) or Bottom(4)) and Color e
q 'green'


$select provides context for $aggregate, which provides context for $rollup

Are there more dependencies?


Is $orderby harmless?

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
23

of
30

4

Aggregation
-
Related
Annotations

The following annotations
allow
to describe
which data in

a

give
n entity model

can be

aggregated, and
how
.

Entity sets that support aggregation are annotated with the term

<
ValueTerm

Name
="SupportsAggregation"
Type
="Self.SupportsAggregationType">


<
Documentation
>


<
Summary
>


This entity set supports the $aggregate query option



</
Summary
>


</
Documentation
>


<
ValueAnnotation

Term
="Core.AppliesToEntitySet" />

</
ValueTerm
>

This term lists the properties that can be used in $aggregate and $select for aggregate queries:

<
ComplexType

Name
="SupportsAggregationType">


<
Documentation
>


<
Summary
>


AggregatableProperties contains the list of property names that can


be used in the $aggregate query option.


GroupableProperties contains the list of property names that can be


used in the $select query option in conju
nction with $aggregate.


</
Summary
>


</
Documentation
>


<
ValueAnnotation

Term
="Core.AppliesToEntitySet" />

<
Property

Name
="AggregatableProperties"


Type
="Collection(Self.AggregatableProperty)" />

<
Property

Name
="GroupableProperties"


Type
="Collection(Self.GroupableProperty)" />

</
ComplexType
>

4.1

Aggregatable

Properties

A property
that can be used in the
$aggregate

is described
with

<
ComplexType

Name
="Aggregatable
Property
">


<
Property

Name
="Name"
Type
="Edm.String">


<
ValueAnnotation

Te
rm
="Core.IsPropertyName" />


<
/
Property
>


<
Property

Name
="DefaultAggregrationFunction"


Type
="Edm.String"
Nullable
="true"/>


<
Property

Name
="AcceptedAggregrationFunctions"


Type
="Collection(Edm.String)"
Nullable
="true"/>

</
ComplexT
ype
>

Allowed values for
the
default aggregation function and the accepted aggregation functions are the
standard aggregation functions
sum
,
min
,
max
, and
average
, or a namespace
-
qualified name identifying
a producer
-
specific function.

Missing; dependency o
f number on unit

(here: Amount on Currency/Code)
; use/
define separate
CurrQuan vocabulary or have a more abstract aggregation
-
specific “dependency” term?

A

service SHOULD accept the shorthand
$aggregate=*

and aggregate all properties that have been
listed
in

Aggregatabl
eProperties

and have a declared default aggregation function. In our example
only the Amount property has been
listed
, and its default aggregation function is
sum
, so the request

GET
~/Sales?$select=
Customer/Country,Product/Name,Amount



&$expand=Customer,Product

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
24

of
30


&
$aggregate
=Amount

could also have been issued as

GET
~/Sales?$select=
Customer/Country,Product/Name,Amount


&$expand=Customer,Product


&
$aggregate
=*

This shorthand is useful if the model repres
ents a “cube” with strict separation of “measures”
(aggregatable properties) and “dimensions” (groupable properties), as it does what consumers of that
cube would expect. In this kind of model

measures


will

always

have a default aggregation function
,
whi
ch may be determined depending on the properties listed in
$select
. If a
property
listed in
$select

does not have a default aggregation function, it will not be considered part of the
$aggregate

list and
instead be used for grouping.

TODO: “floating
measur
e
s” are only defined for a subset of the “dimensions” of a “cube”
. If represented
as declared properties, they would not possess values for queries without $aggregate
, which would be
somewhat confusing. Alternatively t
hey
can

be represented as dynamic prop
erties

and

would be
d
eclared

in an additional collection property of the SupportsAgggregation annotation on entity set level:

<
ComplexType

Name
="FloatingMeasure"
Base
="Self.
AggregatableProperty
">


<
Property

Name
="Type"
Type
="Edm.String" />


<
Property

Nam
e
="InputProperties"
Type
="Collection(Edm.String)">


<
ValueAnnotation

Term
="Core.IsPropertyName" />


<
/
Property
>

</
ComplexType
>

4.2

Groupable

Properties

A property
that can be used to define the aggregation scope
is described

with

<
ComplexType

Name
="Groupab
l
eProperty
">


<
Property

Name
="Name"
Type
="Edm.String">


<
ValueAnnotation

Term
="Core.IsPropertyName" />


<
/
Property
>

<
Property

Name
="DependentProperties"
Type
="Collection(
Edm.
String)


Nullable
="true"
">


<
Value
Annotation

Term
="Core.IsPropert
yName"/>


</
Property
>

</
ComplexType
>

It MAY specify a li
s
t of properties that are functionally dependent on the grouping property and should not
be used on $select without the grouping property. In our example th
e customer name c
ould be listed as
dependen
t on the customer id. Producers
MUST

respond with
400 Bad Request

if a dependent
property is part of the
$select

list without its groupable property.

4.3

Hierarch
ies

(
Sprint 2
)

A hierarchy is an arrangement of groupable properties whose values are represented
as being “above”,
“below”, or “at the same level as” one another.

<
ComplexType

Name
="Hierarchy"
Abstract
="true">


<
ValueAnnotation

Term
="Core.AppliesToProperty"/>


<
Property

Name
="Name"
Type
="String"
Nullable
="false"/>

</
ComplexType
>


We distinguish betw
een two types of h
ierarchies: leveled hierarchies …


<
ComplexType

Name
="LeveledHierarchy"
BaseType
="Self.Hierarchy">


<!
--
Ordered list of properties in the hierarchy
--
>


<
Property

Name
="Levels"
Type
="Collection(Edm.String)"
Nullable
="false">


<
Value
An
notation

Term
="Core.IsPropertyName"/>


</
Property
>

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
25

of
30

</
ComplexType
>

Using them in $rollup

Drill via links

Functions operating on hierarchies for hierarchy selection

$filter=IsSiblingOf(’CostCenterHi erarchy’,’123’)

→ require to state path leading to cost cen
ter?

$filter=IsDescendantOf(‘EmployeeHi erarchy’.’SeBigBoss’)

Or use +/ path notation instead

Amount(sum,average over Time) with Time=Hierarchy(Year,Quarter,Month,Date)

From old examples section:

An alternative shorthand using the sever
-
defined leveled hier
archy from the annotation example in the
previous section would produce the same result


&$rollup=(Customer/Country,Customer/Name),


Product
Hierarchy

The hierarchy name is not enclosed in parentheses, so it can be distinguished f
rom a one
-
level ad
-
hoc
hierarchy using a property name that must be enclosed in parentheses.

From old Hierarchies section:

A group of properties can form a hierarchy:

<
ComplexType

Name
="LeveledHierarchy">


<
Property

Name
="Name"
Type
="String"
Nullable
="fal
se"/>


<!
--
Ordered list of properties in the hierarchy
--
>


<
Property

Name
="Levels"
Type
="Collection(Edm.String)"
Nullable
="false">


<
TypeAnnotation

Term
="

Core.IsPropertyName

"/>


</
Property
>

</
ComplexType
>


<
ComplexType

Name
="RecursiveHierarchy">


<
Property

Name
="HierarchyNodeIDProperty"
Type
="Edm.String"


Nullable
="false">


<
TypeAnnotation

Term
="

Core.IsPropertyName

"/>


</
Property
>


<
Property

Name
="HierarchyParentNodeIDProperty"
Type
="Edm.String"


Nullable
="false">


<
TypeAnnotation

Term
="

Core.IsPropertyName

"/>


</
Property
>


<
Property

Name
="HierarchyLevelProperty"
Type
="Edm.String">


<
TypeAnnotation

Term
="

Core.IsPropertyName

"/>


</
Property
>

</
ComplexType
>

These terms are applied to the Sales entity type, so th
at they can be used by
consumers
for requesting
additional aggregation levels, see section

3.2
:

<
EntityType

Name
="Sales">


<
Key
>


<
PropertyRef

Name
="OrderID"

/>


</
Key
>


<
Property

Name
="OrderID"
Type
="Edm.Int32"
Nullable
="false" />


<
Property

Name
="Amount"
Type
="Edm.Decimal"
Nullable
="false"


Precision
="5"
Scale
="2">


<
TypeAnnotation

Term
="DataAggregation.DependentMeasure">


<
PropertyVal
ue

Property
="DefaultAggregationFunction"
String
="sum">


</
TypeAnnotation
>


</
Property
>


<
NavigationProperty

Name
="Product"
Relationship
="Model1.ProductSales"


ToRole
="Product"
FromRole
="Sales" />


<
NavigationProperty

Name
="Custo
mer"
Relationship
="Model1.CustomerSales"

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
26

of
3
0


ToRole
="Customer"
FromRole
="Sales" />


<
NavigationProperty

Name
="Time"
Relationship
="Model1.SalesTime"


ToRole
="Time"
FromRole
="Sales" />

</
EntityType
>


<
EntityType

Name
=
"Product">


<
Key
>


<
PropertyRef

Name
="ProductID" />


</
Key
>


<
Property

Name
="ProductID"
Type
="Edm.String"
Nullable
="false" />


<
Property

Name
="Name"
Type
="Edm.String"
Nullable
="false" />


<
Property

Name
="Color"
Type
="Edm.String"
Nullable
="false" />


<
NavigationProperty

Name
="ProductGroup"


Relationship
="Model1.ProductGroupProduct"


ToRole
="ProductGroup"
FromRole
="Product" />


<
NavigationProperty

Name
="Sales"
Relationship
="Model1.ProductSales"



ToRole
="Sales"
FromRole
="Product" />


<
TypeAnnotation

Term
="DataAggregation.LeveledHierarchy">


<
PropertyValue

Property
="Name"
String
="ProductHierarchy"/>


<
PropertyValue

Property
="Levels">


<
Collection
>


<
String
="ProductGroup/
Name"/>


<
String
="Name"/>


</
Collection
>


</
PropertyValue
>


</
TypeAnnotation
>

</
EntityType
>

4.4

Functions and Actions on Aggregated Entities

(Sprint 2)

Functions and actions with a binding parameter may or may not be applicable to an aggregated

entity.
By
default we assume such bindings are not applicable to aggregated entities, and define a term

to annotate
those functions/actions that are also applicable to (a subset of the) aggregated entities. The applicability
most likely will depend on the

aggregation level, so these functions/actions must not be “always
bindable”. Assume the product is an implicit input for a function bindable to Sales, then aggregating away
the product makes this function inapplicable.

<
ComplexType

Name
="AvailableOnAggreg
ates"
BaseType
="
C
ore.Tag">


<
Property

Name
="DependsOnProperties"
Type
="Collection(String)"


Nullable
="true">



<
TypeAnnotation

Term
="

Core.IsPropertyName

"/>


</
Property
>

</
ComplexType
>

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
27

of
30

5

Conformance

The last numbered section in the specificat
ion must be the Conformance section. Conformance
Statements/Clauses go here.

[Remove # marker]

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
28

of
30

Appendix A.

Acknowl
e
dg
ments

The following individuals have participated in the creation of this specification and are gratefully
acknowledged:

Participants:

[Participant Name, Affiliation | Individual Member]

[Participant Name, Affiliation | Individual Member]


odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
29

of
30

Appendix B.

No
n
-
Normative Text

text

B.1

Subsidiary section

text

B.1.1

Sub
-
subsidiary section

text

odata
-
data
-
aggregation
-
ext
-
v1.0
-
wd01

Working Draft 01

1
6 October

2012

Standards Track
Draft

Copyright
©

O
ASIS Open 201
2
. All Rights Reserved.

Page
30

of
30

Appendix C.

Revision History

Revision

Date

Editor

Changes Made

01

2012
-
mm
-
dd

Ralf Hand
l

Translated contribution into OASIS format