4. Create the following new query:
— SSP_007-6: Optimized Set Operation # 2
WITH
MEMBER [Measures].[Total Units Shipped] AS
‘SUM (
{[Warehouse].[All Warehouses].[Canada],
[Warehouse].[All Warehouses].[Mexico]},
SUM (
{[Store].[All Stores].[Canada],
[Store].[All Stores].[Mexico]},
SUM (
{[Product].[All Products].[Drink],
[Product].[All Products].[Food]},
[Measures].[Units Shipped]
)))’
MEMBER [Measures].[Total Warehouse Sales] AS
‘SUM (
{[Warehouse].[All Warehouses].[Canada],
[Warehouse].[All Warehouses].[Mexico]},
SUM (
{[Store].[All Stores].[Canada] ,[Store].[All Stores].[Mexico]},
SUM (
{[Product].[All Products].[Drink],[Product].[All Products].[Food]},
[Measures].[Warehouse Sales]
)))’
SELECT
{ [Measures].[Total Units Shipped],
[Measures].[Total Warehouse Sales] } ON COLUMNS,
{[Time].[1998]} ON ROWS
FROM Warehouse
Again, we will run the query and observe the results.
5. Execute the query using the Run Query button.
The results dataset appears as shown in Figure 8.
Figure 8: Results Dataset, Optimized Summation Query
6. Save the query as SSP_007-6.
We note that the results are identical for the two approaches to the query (with the exception of the measure names, which, like the rounding of the Warehouse Sales amounts, could be adjusted, were they significant concerns). The second approach, however, performs appreciably better, as would be evident if the query were extracting a much larger amount of data from a real-world data source. The following general rule summarizes the use of the commutative nature of the SUM function (and applies equally to the MAX, MIN, and TopCount() functions):
SUM (CrossJoin (X,Y), b) =
SUM(X, SUM(Y, b))
In both the examples of performance enhancements we have explored above, and in any other potential performance enhancement scenario we might contemplate, we would need to consider the cost / benefit tradeoffs, actual performance metrics against different sources, and whether the differences in performance are too negligible to devote much time to attempting to increase performance further. The point is that tuning of MDX expressions can be accomplished, both in the manner suggested above or through other approaches, to maximize performance, and that beginning with the MDX statements themselves is usually the best approach to any optimization effort. Once the base query is tuned to an optimal state, we can always consider client / server balancing, hardware upgrades, and perhaps further measures to increase performance.
Summary and Conclusion
In this lesson, we continued our focus on the use of control of location as a primary tuning interaction type within the realm of options available to us for MDX query optimization. We performed a practice exercise to reinforce the concepts exposed. We then extended our considerations of the available types of interaction to include the optimization of set operations and syntax arrangement considerations. Within our exploration of the importance of the optimization of set operations, we undertook practice examples that illustrated some ways we can rearrange queries to enhance performance, often significantly.
Copyright 2004 by the author.
]]>