Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

MDX Puzzle #3 Solution

Download Script

Sorry that it’s taken me so long to write this post, but I have been a little busy with the SQL Lunch.  I am back now, so let’s solve this puzzle.  This puzzle introduced a new keyword, WITH, that can be used to create a calculated member that is only available for a single MDX query.  Note that, after the query is finished executing the calculated member no longer exists.  Also, introduced are three functions ROOT, AGGREGATE, and TOPCOUNT, which will all be explain later in this posting. 

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

image

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:

image

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:

image

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

 

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.

Posted by ThomasLL on 20 July 2010

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.