Log in  ::  Register  ::  Not logged in

# MDX Puzzle #3 Solution

Download Script

So, here is what I started with.  This query satisfies these requirements:

1.  Internet Order Quantity

2.  Product

3.  Return only the TOP 10 Products based on the Internet Order Quantity

One thing that is new is the use of TOPCOUNT in the Rows section of the query.  TOPCOUNT behaves similar to TSQLs TOP(), but it requires three arguments instead of one.  It requires a Set Expression, which is a valid MDX expression that returns a set.  In this example, the set is all the Children of the product dimension.  The next argument is the Count, which is the number that specifies how many tuples will be returned.  Finally, it expects a Numeric Expression, which is the value that will be used to determine which set is returned.  This value is typically an MDX expression that returns a value.  The TOPCOUNT sorts the set in descending order and returns the specified number of elements (the Count argument) with the highest values (the Numeric Expression).

Now let’s create the calculated member.  Here is the query that was used:

You will notice the use of the WITH keyword that was explained earlier and the MEMBER clause.  Two calculated members are created in the above query.  The first uses the AGGREGATE() and ROOT() functions to calculate the total of all Internet Orders.  The second performs simple division, dividing Internet Order Quantity by the calculated total from the first member to obtain the Percentage of Total Orders.

The AGGREGATE and ROOT() are two new functions in this series.  The AGGREGATE function accepts two arguments.  The first is a Set_Expression, which in this example is ROOT().  The second argument, which is optional, is a Numeric_Expression.  The Numeric_Expression is typically an MDX expression that returns a numbers.  For this example, the Internet Order Quantity measure was used.  the ROOT() function was for the Set_Expression because it returns ALL member from each attribute hierarchy in the cube.  As a result, the total Order Quantity for the entire cube will be returned.  One thing to note about the ROOT() function is that you can limit its results by passing either a Dimension or Tuple Expression as an argument.

Now to complete the puzzle, take the calculated members MDX query and paste it directly above the SELECT statement.  Then add the Percentage of Total calculation to the ON COLUMNS section of the SELECT statement.  Finally, you will have the solution.  See the query below:

One thing that I have realized is that, just like T-SQL, there are several ways to solve a query with MDX.  Once I have finished the journey of mastering the art of writing MDX, I will begin down the path of performance tuning and writing efficient MDX queries.

Download Script

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company

## SQLDownSouth

#### Comments

##### Posted by ThomasLL on 16 July 2010

Great series patrick.

What is the point of [Product].[Product].Children in the TOPCOUNT?

Why do you have to have [Product].[Product]?

Thomas

##### Posted by Patrick LeBlanc on 16 July 2010

Thanks ThomasLL.  The Product is the set that will be returned in the TOPCOUNT.  If you wanted to return customers you could replace product with customer.

Product.Product is specified because I want to return the children for the Product Attribute in the Product Dimension.

Hence, Product.Product.Children.  The first product is the Dimension and the second Product is the Attribute of the Product Dimension.  Children is a function that returns the set for the specified memebers.  In this example, Children returns a list of products.

Got it...

##### Posted by kasisriharsharao on 5 September 2010

Another way out, instead of using Aggregate and Root, i think we can use (ALL) level or [ALL] member; because either way we are heading to same solution;

Patrick, is there any performance gain in using Root() ???

##### Leave a Comment

Please register or log in to leave a comment.

 Copyright © 2002-2018 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.