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
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.
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
