MDX Concepts - Microsoft SQL Server 2008 Analysis Services - page 154

In this statement, we specified that we want our set to be sorted in descending order. And
we ignored the hierarchical order of the set; that is, we broke the hierarchy.

Here's an example where it helps to use the hierarchical order. Suppose we need to analyze
store performance, but we want to do this in context of the country where the store is
located. Therefore, we won't just sort the stores by comparing them to other stores; first,
we order the values for the countries where the stores located. Then, we order the states,
and then the cities, and after all that, the stores. Now we can compare the value of the
sales in one store to the sales in other stores in the same city.

Here's a simpler example that demonstrates how the Order function can be used on a
smaller set that contains countries and states of the Store dimension. In this example, we
use a user-defined hierarchy Stores shown in Figure 10.11. By passing the keyword DESC
to the Order function, we tell the system to keep the hierarchical order defined by the
user-defined hierarchy when it orders the members of the set, as shown in Figure 10.19.

FIGURE 10.19 When you pass the DESC keyword to the Order function, it takes into account
the hierarchical order of the set and return a set of members sorted in descending order.

SELECT Order({[Store].[Stores].[Store Country].members,
[Store].[Stores].[Store State].members},
[Measures].[Store Sales], DESC) ON COLUMNS
FROM [Warehouse and Sales]

The results of this query show that the most sales occurred in the United States, and
among the states in the United States, Washington had the biggest sales, followed by the
stores in California and then in Oregon.

Referencing Objects in MDX and Using Unique Names

You can choose from various ways to specify Dimension , Hierarchy , Level , and Member
names in an MDX statement:

. By name

. By qualified name

. By unique name

By Name

Examples in earlier sections have referenced members and other MDX objects by their
names. Although this is the simplest syntax for writing an MDX statement by hand
(without involving software that generates MDX), it has its drawbacks.

To resolve a member that is referenced by its name, Analysis Services has to iterate over all
the dimensions and all their hierarchies, looking for the member with that name. This