MDX Essentials: Basic Set Functions: The Order() Function

Monday Oct 13th 2003 by William Pearson
Share:

Provide the sorts you need for reports and applications using MDX. In this lesson, we will explore using the versatile Order() function, for both hierarchical and nonhierarchical sorts within MDX.

About the Series ...

This is the twelfth article of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements needed for getting the most out of the lessons included, please see the first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

What We Accomplished in our Last Article

In the last article of the series, MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions, we concluded our three-article group of lessons on the time series functions, a specialized family of functions that MDX provides to perform analysis within the context of time. After discussing in general the common business need to analyze data over time, we first overviewed the LastPeriods() and ParallelPeriod() functions. For each function, we then illustrated the syntax that is appropriate for its effective use. Finally, we tested our understanding of how to leverage the function by undertaking a relevant practice exercise, discussing the results we obtained and performing additional proof exercises to confirm their accuracy.

Introduction

In this lesson, we will focus our attention on a basic function that, however humble, finds itself in use in expressions and queries that rank from the simplest, perhaps, to the most advanced. The Order() function provides the sorting capabilities we need within MDX, and is thus an important part of our analysis toolsets.

As simple as mere ordering might appear to be, the support of hierarchies in MDX makes the processes a bit more involved. Two general types of order, hierarchized and nonhierarchized, can be anticipated. Ordering in a hierarchized manner arranges members within a given hierarchy, and then arranges the hierarchical levels. Nonhierarchized ordering, in contrast, ignores hierarchy completely in the arrangement of all members within the affected set.

Along with an introduction to the Order() function, this lesson will include:

  • an examination of the syntax surrounding the function;
  • illustrative examples of the uses of the function in practice exercises;
  • a brief discussion of the MDX results we obtain in the practice examples.

The Order() Function

The Order() function, according to the Analysis Services Books Online, "arranges members of a specified set, optionally preserving or breaking the hierarchy." As we have already intimated, we can use the function to arrange sets in hierarchies, or we can order members of a set without regard to hierarchies. Whichever way we choose to order the results returned by our queries, it is important to keep in mind that we should not rely on defaults to provide sort orders for us, as we may not obtain the results we expect.

We will examine the syntax for the Order() function in general, then we will look at the two types of order that we can enforce in separate examples. This will give us a richer understanding of the options we have within the Order() function, together with a feel for the similarities, as well as for the differences, of the results those options afford.

Discussion

Order() allows us to impose sort order on a standard MDX set in a manner that allows us to list results based upon any of our measures. The only difference in two Order() functions that provide different types of sorts is the optional order specification at the end of the function, as we shall see.

Order() can accept either a string expression or a numeric expression as criterion for ranking. Needless to say, proper selection, as well as filtering, of dimensions and levels is critical to prevent MDX from returning unexpected results.

The Order() function allows us to meet myriad common and uncommon business needs, including the obvious need to order lists of members within a set, be they employees, products, accounts, customers, months, or others. We might also wish to organize a set of members by specific attributes, such as statuses, degree of completion or readiness, and locations, to name a very few.

Let's look at some illustrations to further clarify the operation of Order().

Syntax

Syntactically, the sort criteria (String Expression or Numeric Expression) and the order specification (ASC, DESC, BASC, or BDESC) are placed within the parentheses to the right of Order(), as shown in the following illustration:

Order (<<Set>>, {<<String Expression>> | <<Numeric Expression>>} 
	[, ASC | DESC | BASC | BDESC])

Remarks

The Order() function returns hierarchized data when the ASC or DESC order specifications are appended to the function, and nonhierarchized data when BASC or BDESC are used. (The "B" serves as an instruction to "break," or "ignore" the hierarchy.) ASC is the default order specification when none is specified in the function.

In the case of the hierarchized order option, members are first ranked according to position within the hierarchy, then according to each level involved, based upon a string or numeric expression.

The following example expression illustrates a use of the Order() function with the hierarchized order option (inherent with the use of ASC):

{ORDER({[Warehouse].[USA].[WA].Children, [Warehouse].[USA].[OR].Children},  
	([Measures].[Warehouse Cost]) , ASC)}  ON ROWS 

This expression in a proper query would result in the return of the set depicted in Table 1.

Warehouse

Cost

Portland

11,509.54

Salem

13.530.31

Bellingham

921.39

Walla Walla

981.81

Spokane

2,294.52

Bremerton

3,249.29

Yakima

4,454.60

Seattle

12,335.21

Tacoma

13,672.34


Table 1: Ordering of the Set, Hierarchized Ascending, by Warehouse Cost

In the expression above, we use the Order() function to enumerate all Washington and Oregon warehouses, in Ascending (ASC) order, with respect to the total Warehouse Cost for each. Because we use ASC (and therefore the hierarchized option), we order with respect to the existing hierarchy: We obtain the two Oregon children first, sorted by Warehouse Cost in ascending order, followed by the children of Washington, sorted in ascending order. We can order the result dataset based upon any relevant measure in just this fashion.

By contrast, the choice of the alternative nonhierarchized option (in keeping with an example identical, otherwise, to the above, except for its use of the BASC order type) again ranks the members of the set, based once more upon the Warehouse Cost measure, but without regard to hierarchy. Let's look at the result when we simply change the order type in our first expression to BASC, and, in effect, direct that the hierarchy be ignored:

{ORDER({[Warehouse].[USA].[WA].Children, [Warehouse].[USA].[OR].Children},  
	([Measures].[Warehouse Cost]) , BASC)}  ON ROWS 

This expression in a proper query would result in the return of the set depicted in Table 2.

Warehouse

Cost

Bellingham

921.39

Walla Walla

981.81

Spokane

2,294.52

Bremerton

3,249.29

Yakima

4,454.60

Portland

11,509.54

Seattle

12,335.21

Salem

13.530.31

Tacoma

13,672.34


Table 2: Ordering of the Set, Nonhierarchized Ascending, by Warehouse Cost

In the expression above, we use the Order() function, again, to enumerate all Washington and Oregon warehouses, in Ascending order, with respect to the total Warehouse Cost for each. This time, however, because we use BASC (and therefore the nonhierarchized option), we order without respect to the existing hierarchy: We obtain the complete group of children sorted by Warehouse Cost in ascending order, plain and simple. No attention is paid to hierarchy whatsoever.

Practice - Returning Hierarchized Data

Let's reinforce our understanding of the basics we have covered so far, and by using the Order() function in a manner that illustrates its operation in returning hierarchized data, one of the two general options that we have in order types. We will call upon the MDX Sample Application again, as our tool for constructing and executing the MDX we examine, and for viewing the result datasets we obtain.

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants that might appear.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

We will compose a simple query to gain an understanding of the use of the Order() function to return hierarchized data, or data sorted within hierarchical considerations. Our query will focus on Warehouse Cost, a value that is captured monthly within the FoodMart organization and which is stored in the Warehouse cube.

5.  Type the following query into the Query pane:

-- MDX12-1:  Tutorial Query No. 1
SELECT 
{[Measures].[Warehouse Cost]} ON COLUMNS,
{ ORDER({[Warehouse].[USA].[WA].Children, [Warehouse].[USA].[OR].Children},  
   ([Measures].[Warehouse Cost]) , DESC)}  ON ROWS
FROM Warehouse
WHERE ([Time].[Year].[1998])

Notice that the query we have input contains an Order() expression similar to the one we used in the previous section. The query also accomplishes a complementary objective: By selecting two separate state levels in the Warehouse dimension to populate the row axis in the query, we have constructed a scenario whereby we know that we have two separate hierarchical levels; we thus have a basis for confirming the real effects of using each of the hierarchical and nonhierarchical sort types.

6.  Execute the query by clicking the Run Query button in the toolbar.

Analysis Services populates the Results pane, presenting the dataset shown in Illustration 1.


Illustration 1: Result Dataset - Order() Expression in Place, Hierarchized, Descending

We see the total Warehouse Cost for the specified states returned for year 1998. The Order() function sorts our states (Washington and Oregon are specified in the query to populate the row axis) from "highest to lowest," (and thus "from Washington to Oregon"), respecting state groupings in those sorts. We obtain the seven Washington city-children, sorted highest to lowest, with respect to Warehouse Cost, followed by the two Oregon city-children, also sorted highest to lowest based upon Warehouse Cost in descending order.

7.  Select File -> Save As, and give the file a meaningful name and location, if it is desirable to save the query.

Practice - Returning Nonhierarchized Data

Let's take a look at the same operation with a "break hierarchy" sort type, BDESC, within an otherwise identical query. We will return to the MDX Sample Application, and perform the following steps:

1.  Clear the top area (the Query pane) of any queries or remnants that might appear.

2.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

3.  Select the Warehouse cube in the Cube drop-down list box.

4.  Type the following query into the Query pane:

 -- MDX12-2:  Tutorial Query No. 2
 SELECT 
 {[Measures].[Warehouse Cost]
 } ON COLUMNS,
 { ORDER({[Warehouse].[USA].[WA].Children, [Warehouse].[USA].[OR].Children},  
 ([Measures].[Warehouse Cost]) , BDESC)}  ON ROWS
 FROM Warehouse
 WHERE ([Time].[Year].[1998])

Again, notice that the query we have input is identical to the last, with the exception of its use of the nonhierarchical BDESC sort type.

5.  Execute the query by clicking the Run Query button in the toolbar.

Analysis Services populates the Results pane, presenting the dataset shown in Illustration 2.


Illustration 2: Result Dataset - Order() Expression in Place, Nonhierarchized, Descending

We see the total Warehouse Cost for the specified states returned, once more, for year 1998. The Order() function sorts our Washington and Oregon warehouse city locations from highest to lowest, in terms of total Warehouse Cost, but this time the hierarchical level groupings are completely ignored. The warehouses cities are sorted solely with respect to Warehouse Cost. The Warehouse dimension's hierarchy, for purposes of our query, is "broken."

6.  Save the query as desired.

In conclusion, we can see that the Order() function provides flexible ways to arrange the display of our data, whether we wish to take hierarchies into consideration in our presentation or not.

Summary...

In this lesson, we explored the humble, yet versatile, Order() function. Order() finds itself an actor in many queries - queries that rank from the simplest to the most advanced. The Order() function provides the sorting capabilities we need within MDX, and is thus an important part of our analysis toolsets.

Through its support of hierarchies, MDX complicates sorting to a degree, as two general types of order, hierarchized and nonhierarchized, are possible. We explored how the Order() function stands ready to handle these scenarios, allowing us the options of arranging members within a given hierarchy, or of ignoring hierarchy completely in the arrangement of all members within the set we specify, and treating all to a simple sort. In addition to discussing the purpose of the Order() function, we examined of the syntax surrounding its use, and illustrated its application in practice exercises. Finally, we discussed the results we obtained in each exercise, remarking on the distinguishing characteristics of each.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved