Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 197

Sales Amount Freight Net Sales Profit% Profit%City

France restauration $920.10 $30.34 $889.76 96.70% 97.40%

Princesa Isabel Vinhos $1,409.20 $86.85 $1,322.35 93.84% 95.93%

Around the Horn $6,406.90 $305.59 $6,101.31 95.23% 95.58%

North/South $604.00 $33.46 $570.54 94.46% 95.58%

Seven Seas Imports $9,021.24 $425.03 $8,596.21 95.29% 95.58%

··· ··· ··· ··· ··· ···

Here, Profit% computes the profit percentage of the current member, and
Profit%City applies Profit% to the parent of the current member, that is, the
profit of the state to which the city belongs.

6.1.11 Sorting

In cube queries, all the members in a dimension have a hierarchical order.
Forexample,considerthequerybelow:

SELECT Measures.MEMBERS ON COLUMNS,
Customer.Geography.Country.MEMBERS ON ROWS
FROM Sales

The answer of this query is given next.

Unit Price Quantity Discount Sales Amount Freight Sales Count

Austria $84.77 4,644 21.71% $115,328.31 $6,827.10 114

Belgium $64.65 1,242 9.72% $30,505.06 $1,179.53 49

Denmark $70.28 1,156 17.94% $32,428.94 $1,377.75 45

Finland $54.41 848 9.09% $17,530.05 $827.45 51

France $64.51 3,052 11.76% $77,056.01 $3,991.42 172

··· ··· ··· ··· ··· ··· ···

The countries are displayed in the hierarchical order determined by the
Continent level (the topmost level of the Geography hierarchy), that is, first
the European countries, then the North American countries, and so on. If we
wanted the countries sorted by their name, we can use the ORDER function,
whose syntax is given next:

ORDER(Set, Expression [, ASC | DESC | BASC | BDESC])

The expression can be a numeric or string expression. The default sort order
is ASC .The' B ' prefix indicates that the hierarchical order can be broken.
The hierarchical order first sorts members according to their position in
the hierarchy, and then it sorts each level. The nonhierarchical order sorts
members in the set independently of the hierarchy. In the previous query, the