Tip

Sub-queries supported in Analysis Services MDX

Baya Pavliashvili, Contributor

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

    Requires Free Membership to View

-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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.