 |
|
| 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:
[IMAGE]
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 AmountGrowth in reseller sales amount Growth in reseller sales amount IIF
Jul-01$489,328.58(null)(null)
Aug-01$1,538,408.31214.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.80175.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:
[IMAGE]
The ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
output would look like this:
Reseller Sales AmountGrowth in reseller sales amount Growth in reseller sales amount IIF
Jul-01$115,360.90 (null)(null)
Aug-01$316,981.07174.77%174.77%
Sep-01$205,640.87-35.13%-35.13%
Oct-01$234,907.2514.23%14.23%
Nov-01$381,806.4862.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
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
