-- SMDX004-001 ORDER() Function, Sorting by Internet Sales Amount,
-- DESC Keyword, Two Cross-joined Dims (Hierarchies left intact)
SELECT
{[Measures].[Internet Sales Amount] ,
[Measures].[Internet Order Quantity]} ON AXIS(0),
NON EMPTY
ORDER(
[Product].[Category].CHILDREN *
[Customer].[Country].CHILDREN,
[Measures].[Internet Sales Amount], DESC) ON AXIS(1)
FROM
[Adventure Works]
The Query pane appears, with our input.
Illustration 4: Our Initial Query in the Query Pane …
The Results pane is populated by Analysis Services, and the dataset appears.
Illustration 5: Results Dataset – Hierarchized Ordering
In the returned dataset, we see that the data is ordered by the Product Category names. Within the positioning of the cross-joined Customer Country, we note that action of the hierarchized sorting is somewhat emphasized: while ordering is obviously by descending Internet Sales Amount, it’s easy to see that the sorts “restart” with each Product Category (and therefore the Customer Country children sort within the natural order of the Product Category members) , and that, while each member of the cross-joined Customer Countries is represented in each of the three sorts, these members change positions to match the ordering imposed by the Internet Sales Amount.
-
Type (or cut and paste) the following query into the Query pane:
-
Execute the query by clicking the Execute button in the toolbar...
-
Select File -- Save MDXQuery1.mdx As …, name the file SMDX004-001, and place it in a meaningful location.
To sort solely upon Internet Sales Amount, without regard to hierarchical structure as we see here, we will need to break hierarchies, as we shall see in the next section.
Ordering without Regard to Hierarchies
Let’s say that, after performing the sort we examined above for a client, we are next asked to do something a bit more elaborate: we are asked by our colleagues to order the rows of the data set we have produced solely upon the measure Internet Sales Amount. They wish to see everything sorted by the measure from greatest to least value, without any of the ordering “resets” we saw earlier. They are therefore asking us to ignore the Product Category hierarchy altogether and to “pool” all Customer Countries for purposes of the sort.
Let’s employ Order() once again, working, as before, with the members of the Product Category and Customer Country levels of their respective dimensions, this time sorting them in truly descending order by Internet Sales Amount.
A new tab, again connected to the Adventure Works cube, appears in the Query pane.
-- SMDX004-002 ORDER()Function, Sorting by Internet Sales Amount,
-- BDESC Keyword to break dimension / hierarchy and
-- sort solely upon [Measures].[Internet Sales Amount]
SELECT
{[Measures].[Internet Sales Amount] ,
[Measures].[Internet Order Quantity]} ON AXIS(0),
NON EMPTY
ORDER(
[Product].[Category].CHILDREN *
[Customer].[Country].CHILDREN,
[Measures].[Internet Sales Amount], BDESC) ON ROWS
FROM
[Adventure Works]
The query recreates the same scenario we established earlier: it generates two dimensions side-by-side, the examination of the behavior of whose members will allow us to confirm our understanding of the Order() function in its non-hierarchical mode. (Note that the only difference between this query and the one previous, where we ordered with respect to hierarchies, is that we use the “BDESC” keyword, versus the “DESC” keyword.)
The Query pane appears, with our input:
Illustration 6: Second Query: Order() Function (Descending), Breaking Hierarchy
The Results pane is populated, once more, by Analysis Services, and the dataset appears.
Illustration 7: Results Dataset – Order() Expression in Place, Non-Hierarchized, Descending
As expected, the Order() function sorts our Product Category and Customer Country combinations (thus we can consider the members of each dimension on a given row “pooled”) from “highest to lowest,” from the perspective of total Internet Sales Amount, irrespective of the hierarchy of either dimension in this sort.
-
Select File -- New from the main menu.
-
Select Query with Current Connection from the cascading menu that appears next.
-
Type (or cut and paste) the following query into the Query pane:
-
Execute the query by clicking the Execute button in the toolbar, once again.
-
Select File -- Save MDXQuery3.mdx As …, name the file SMDX004-002.
Summary Exercise: Respect Hierarchies, then Ignore Them
Finally, let’s take look at another use of Order() wherein we ignore hierarchies. First, we’ll order a data set where hierarchies are respected, the output of which we can then compare to an identical query where we break hierarchies within the sort. Our initial query / data set pair will again provide a framework which we can easily alter to demonstrate the differences that result from a non-hierarchized sort among identical elements. It will also offer us another opportunity to use Order() in hierarchized, and then non-hierarchized, modes with multiple dimension sets.
Another new tab, again connected to the Adventure Works cube, appears in the Query pane.
First, let’s perform another sort by a measure, using the DESC keyword (and thus keep intact, once again, the hierarchies of two cross-joined dimensions).
-- SMDX004-003 ORDER()Function, Sorting by Reseller Sales Amount,
-- DESC Keyword, Two Cross-joined Dims (respecting hierarchies)
SELECT
{[Measures].[Reseller Sales Amount]} ON AXIS(0),
{SUBSET
(ORDER
([Date].[Calendar].[Month].MEMBERS*
[Product].[Product Categories].[Category].MEMBERS
,[Measures].[Reseller Sales Amount]
,DESC
)
,0
,12
)} ON AXIS(1)
FROM
[Adventure Works]
The Query pane appears, with our input.
Illustration 8: Order() at Work, Again Respecting Hierarchies, in the Query Pane …
The Results pane is populated by Analysis Services, and the dataset appears.
Illustration 9: Results Dataset – Hierarchized Ordering
While this query is a little more elaborate than our earlier hierarchy-respecting query, we can still see similar results with regard to the sort itself. In effect, we are returning the Reseller Sales Amount for the top twelve selling categories of products, based upon the same measure. The MDX Subset() function, which we explore in an independent Stairway to MDX article, is employed to return only the first twelve tuples in the set after the result is ordered using the Order() function.
In the returned dataset, we see that the data is ordered by the Calendar Month names. Within the positioning of the cross-joined Product Category, we note that action of the hierarchized sorting is made plain, as in our earlier example: while ordering is obviously by descending Reseller Sales Amount, we can again easily ascertain that the sorts “restart” with each Calendar Month (and therefore the Product Category children sort within the natural order of the Calendar Month members) , and that, while each member of the cross-joined Product Categories is represented in each of the three sorts, these members change positions to match the ordering imposed by the Reseller Sales Amount.
Next, we will see again that, to sort solely upon specified measure Reseller Sales Amount, without the regard to THE hierarchical structure we see in evidence here, we will once again need to break hierarchies, as we shall see in the next example. We’ll employ Order() once again, working, as before, with the members of the Calendar Month and Product Category levels of their respective dimensions, sorting them in truly descending order by the measure, Reseller Sales Amount.
Another new tab, again connected to the Adventure Works cube, appears in the Query pane.
-- SMDX004-004 ORDER()Function, Sorting by Reseller Sales Amount,
-- BDESC Keyword, Two Cross-joined Dims (ignoring hierarchies)
SELECT
{[Measures].[Reseller Sales Amount]} ON AXIS(0),
{SUBSET
(ORDER
([Date].[Calendar].[Month].MEMBERS*
[Product].[Product Categories].[Category].MEMBERS
,[Measures].[Reseller Sales Amount]
,BDESC
)
,0
,12
)} ON AXIS(1)
FROM
[Adventure Works]
Note that the query is identical to the query we created in our last example, except for one small difference: the “DESC” keyword of the last query (which dictated that the Order() function respect hierarchies) has now become “BDESC.” Moreover, the query recreates the same scenario we established earlier: it generates two dimensions side-by-side, the examination of the behavior of whose members will allow us to confirm our understanding of the Order() function, only this time in its non-hierarchical mode.
The Query pane appears, with our input:
Illustration 10: Order() Function (Descending), Once Again Breaking Hierarchy …
The Results pane is populated, once more, by Analysis Services, and the dataset appears.
Illustration 11: Results Dataset – Order() Expression in Place, Non-Hierarchized, Descending
As expected, the Order() function sorts our Calendar Month and Product Category combinations (as if they were “pooled,” once again, from “highest to lowest,” from the perspective of total Reseller Sales Amount, completely without regard to the hierarchy of either dimension in this sort. (The Subset() function, once again, serves to deliver the Reseller Sales Amount for the top twelve selling categories of products, based upon the same measure.)
-
Select File -- New from the main menu.
-
Select Query with Current Connection from the cascading menu that appears next, as we did earlier.
-
Type (or cut and paste) the following query into the Query pane:
-
Execute the query by clicking the Execute button in the toolbar...
-
Select File -- Save MDXQuery1.mdx As …, name the file SMDX004-003, and place it in a meaningful location.
-
Select File -- New from the main menu, once again.
-
Select Query with Current Connection from the cascading menu that appears next, as we did before.
-
Type (or cut and paste) the following query into the Query pane:
-
Execute the query by clicking the Execute button in the toolbar, once again.
-
Select File -- Save MDXQuery3.mdx As …, naming the file SMDX004-004.
-
Select File -- Exit to leave the SQL Server Management Studio, when ready.
Summary …
In this article, we further explored the MDX Order() function, which, as we noted in The Order() Function: Maintaining Cube Hierarchies, finds itself within expressions and queries that rank from the simplest to the most advanced. We learned, in general, that Order() provides sorting capabilities that allow us to reach beyond the natural cube structure, and this function is thus an important member of our analysis and reporting toolsets.
We then expanded beyond what we had learned in The Order() Function: Maintaining Cube Hierarchies , and focused upon the use of the Order() function to return non-hierarchized data via the appended BASC or BDESC order specifications, comparing the results we obtained in each of a couple of practice exercises to a those of identical queries, in each case, that respected hierarchies. Throughout the article, we examined the syntax involved with Order(), and showed some business uses for the function by generating queries that capitalized upon its capabilities.
