You are using Multidimensional Expressions (MDX) to query a SQL Server Analysis Services (SSAS) cube. You need to compute the aggregate value of the 10 most-ordered produces in the Product Categories

Build an analysis services database

Question

You are using Multidimensional Expressions (MDX) to query a SQL Server Analysis Services (SSAS) cube. You need to compute the aggregate value of the 10 most-ordered produces in the Product Categories hierarchy. The Product level is the lowest in the hierarchy. Which functions should you use to complete the MDX query? (To answer, drag the appropriate functions from the list of functions to the correct locations in the answer area.)
WITH MEMBER [Measures].[SumOfTop10products]
AS
(

(

[Product].[Product Categories].[Product].MEMBERS
,
10
,
[Measures].[Order Quantity]
)
,

(

(

(
[Product].[Product Categories].[Product].MEMBERS
,
10
,
[Measures].[Order Quantity]
)
,

)
)
)
SELECT
{[Measures].[Order Quantity], [Measures].[SumOfTop10products]}
ON COLUMNS,
[Product].[Product Categories].[Category].&[4]
,[Product].[Product Categories].[Subcategory].&[31]
ON ROWS
FROM [Orders]

Exhibit

Explanation

Completed MDX Query: WITH MEMBER [Measures].[SumOfTop10products] AS ( TOPCOUNT ( MEMBERS [Product].[Product Categories].[Product].MEMBERS , 10 , [Measures].[Order Quantity] ) , AGGREGATE ( DESCENDANT ( TOPCOUNT ( [Product].[Product Categories].[Product].MEMBERS , 10 , [Measures].[Order Quantity] ) , LEAVES ) ) ) SELECT {[Measures].[Order Quantity], [Measures].[SumOfTop10products]} ON COLUMNS, [Product].[Product Categories].[Category].&[4] ,[Product].[Product Categories].[Subcategory].&[31] ON ROWS FROM [Orders]

Explanation of terms:

Aggregate: Returns a number that is calculated by aggregating over the cells returned by the set expression. If a numeric expression is not provided, this function aggregates each measure within the current query context by using the default aggregation operator that is specified for each measure. If a numeric expression is provided, this function first evaluates, and then sums, the numeric expression for each cell in the specified set.
TopCount: Sorts a set in descending order and returns the specified number of elements with the highest values.
Leaves: Returns a set composed of all attributes (optionally limited to those belonging to a specific dimension). For each attribute x in the return set, if x is the granularity attribute or is directly or indirectly related to the granularity attribute, the granularity is set on attribute x without affecting the slice. The Leaves function is designed for use inside a SCOPE statement or at the left side of an assignment.
DESCENDANTS: Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
Incorrect: TopSum: Returns, in order of decreasing rank, the top-most rows of a table whose cumulative total is at least a specified value.

Topics

#MDX#TOPCOUNT#AGGREGATE#Product Categories hierarchy

Community Discussion

No community discussion yet for this question.

Full 70-466 Practice