Sub-queries supported in Analysis Services MDX

MDX in SQL Server 2005 Analysis Services brings exciting improvements including query support and expression/calculation language.

MDX in SQL Server 2005 Analysis Services brings exciting improvements including query support and expression/calculation language. Baya Pavliashvili highlights CASE, statements, sub-queries and SCOPE functions, which make MDX more powerful and flexible for your intricate business intelligence solutions.

MDX now supports sub-queries, allowing you to change the criteria of the outer query dynamically, depending on the context returned by the sub-query. Each MDX query is executed with a context of a multi-dimensional space (sometimes referred to as a sub-cube). If a query doesn't include a sub-query, then the context is the entire cube. For example, let's suppose we wanted to reduce the previous query to only return the calculations related to the first quarter of fiscal year 2001. We can alter the query as follows:


Note that the sub-query is included in the FROM clause, as opposed to Transact-SQL where a sub-query would typically be part of the WHERE clause. As expected, the results will be limited to the first quarter of fiscal year 2001:

  Reseller Sales Amount Growth in reseller sales amount Growth in reseller sales amount IIF
Jul-01 $489,328.58 (null) (null)
Aug-01 $1,538,408.31 214.39% 214.39%
Sep-01 $1,165,897.08 -24.21% -24.21%
Oct-01 $844,721.00 -27.55% -27.55%
Nov-01 $2,324,135.80 175.14% 175.14%
Dec-01 $1,702,944.54 -26.73% -26.73%

This example is simplistic. Of course we could just as easily include the DESCENDANTS function in the original query to limit the result set. However, sub-queries can come in extremely handy if you have a complicated WHERE clause that contains multiple members. This is important because, although MDX allows sets in the WHERE clause (another welcomed improvement in Analysis Services 2005), it only allows one member from each hierarchy. For example, we could modify the following query to limit the output to only the sales occurring in Australia and Canada:


The output would look like this:

  Reseller Sales Amount Growth in reseller sales amount Growth in reseller sales amount IIF
Jul-01 $115,360.90 (null) (null)
Aug-01 $316,981.07 174.77% 174.77%
Sep-01 $205,640.87 -35.13% -35.13%
Oct-01 $234,907.25 14.23% 14.23%
Nov-01 $381,806.48 62.53% 62.53%
Dec-01 $258,662.88 -32.25% -32.25%

In previous versions of Analysis Services, the only way to accomplish similar functionality would be to create a named set and use AGGREGATE function. Unfortunately, AGGREGATE function had many limitations, not the least of which was its performance.


More powerful Analysis Services MDX in SQL Server 2005
 Home: Introduction
 Part 1: CASE statements now supported by MDX script
 Part 2: Sub-queries supported in Analysis Services MDX
 Part 3: MDX functions: SCOPE, THIS and FREEZE

ABOUT THE AUTHOR
Baya Pavliashvili is a DBA manager overseeing database operations that support more than one million users. Pavliashvili's primary areas of expertise are performance tuning, replication and data warehousing.

This was first published in March 2007

Dig deeper on Microsoft SQL Server 2005

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close