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