 |
|
| SQL Server Tips: |
|
|
|
DATA WAREHOUSING AND BUSINESS INTELLIGENCE
Sub-queries supported in Analysis Services MDX
By Baya Pavliashvili
03.13.2007
Rating: --- (out of 5)
|
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.
| 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.
Copyright 2007 TechTarget
|
|
More on this topic:
Use cube partitions to improve Analysis Services performance
Analysis Services 2005 in SQL Server has improved security
SQL Server Business Intelligence toolbox
|
|
Rate this Tip
|
To rate tips, you must be a member of SearchSQLServer.com.
Register now
to start rating these tips. Log in if you are already a member.
|
Submit a Tip
|
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.
|
|
|
|
|
|
|
|
|
|
| TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
Home: 
