Database Reference
In-Depth Information
A usual need when applying a roll-up operation is to count the number of
members in one of the dimensions removed from the cube. For example, the
following query obtains the number of distinct products sold by quarter:
ROLLUP*(Sales, Time
→
Quarter, COUNT(Product) AS ProdCount)
In this case, a new measure
ProdCount
will be added to the cube. We will see
below other ways to add measures to a cube.
In many real-world situations, hierarchies are
recursive
,thatis,they
contain a level that rolls up to itself. A typical example is a supervision
hierarchy over employees. Such hierarchies are discussed in detail in Chap.
4
.
The particularity of such hierarchies is that the number of levels of the
hierarchy is not fixed at the schema level, but it depends on its members.
The
RECROLLUP
operation is used to aggregate measures over recursive
hierarchies by iteratively performing roll-ups over the hierarchy until the top
level is reached. The syntax of this operation is as follows:
RECROLLUP(CubeName, Dimension
→
Level, Hierarchy, AggFct(Measure)*)
We will show an example of such an operation in Sect.
4.4
.
The
drill-down
operation performs the inverse of the roll-up operation,
that is, it goes from a more general level to a more detailed level in a hierarchy.
The syntax of the drill-down operation is as follows:
DRILLDOWN(CubeName, (Dimension
→
Level)*)
where
Dimension
Level
indicates to which level in a dimension we want to
drill down to. In our example given in Fig.
3.4
c, we applied the operation
→
DRILLDOWN(Sales, Time
→
Month)
The
sort
operation returns a cube where the members of a dimension have
been sorted. The syntax of the operation is as follows:
SORT(CubeName, Dimension, (Expression [
{
ASC
|
DESC
|
BASC
|
BDESC
}
])*)
where the members of
Dimension
are sorted according to the value of
Expression
either in ascending or descending order. In the case of
ASC
or
DESC
, members are sorted within their parent (i.e., respecting the
hierarchies), whereas in the case of
BASC
or
BDESC
, the sorting is performed
across all members (i.e., irrespective of the hierarchies). The
ASC
is the
default option. For example, the following expression
SORT(Sales, Product, ProductName)
sortsthemembersofthe
Product
dimension in ascending order of their name,
as shown in Fig.
3.4
d. Here,
ProductName
is supposed to be an attribute of
products. When the cube contains only one dimension, the members can
be sorted based on its measures. For example, if
SalesByQuarter
is obtained
from the original cube by aggregating sales by quarter for all cities and all
categories, the following expression
Search WWH ::
Custom Search