Exam Ref 70-768 Developing SQL Data Models by Varga Stacia

Author:Varga, Stacia [Varga, Stacia]
Language: eng
Format: azw3
Publisher: Pearson Education
Published: 2017-05-05T04:00:00+00:00

* * *

SELECT

[Measures].[Sales Amount Without Tax] ON COLUMNS,

HEAD(

/* First argument – set of stock items sorted by profit

in descending order */

ORDER(

[Stock Item].[Stock Item].[Stock Item].Members,

[Measures].[Profit],

DESC),

/* Second argument – specify the number of members of set

in first argument to return */

5)

ON ROWS

FROM

[Wide World Importers DW];

* * *

FIGURE 3-19 Results of ORDER and HEAD functions applied to Stock Item members

This example is also good for reviewing what happens when the SSAS formula engine processes the query. Remember that the first steps are to resolve the members to place on columns and rows. There is only one measure on columns, Sales Amount Without Tax, which is explicitly stated and does not require any further processing by the formula engine.

On the other hand, the set to place on rows does require processing. The following steps must occur before the tuples are constructed to return the values that you see in the measure column:

1. Specifically, the formula engine starts by resolving the ORDER function. It constructs a tuple for the measure in the second argument, Profit, and each member in the ORDER function’s first argument, the set of members on the Stock Item level of the Stock Item attribute hierarchy in the Stock Item dimension. That is, all members except the All member. The tuple also includes the WHERE clause in the query, if one exists.

2. The formula engine then retrieves the value for each of those tuples and sorts the members in descending order.

3. Next, the formula engine applies the HEAD function to reduce the set of all sorted members to the first five members, which are placed on rows.

4. The final step is to compute the tuples for each cell resulting from the intersections of rows and columns and return the results.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.