MDX Data Manipulation - SELECT
Retrieves data from a specified cube.
Syntax
[ WITH <SELECT WITH clause>
[ , <SELECT WITH clause>...n ]
]
SELECT
[ *
| ( <SELECT query axis clause>
[ , <SELECT query axis clause>,...n ]
)
]
FROM
<SELECT subcube clause>
[ <SELECT slicer axis clause> ]
[ <SELECT cell property list clause> ]
<SELECT WITH clause> ::=
( CELL CALCULATION <CREATE CELL CALCULATION body clause> )
| ( [ CALCULATED ] MEMBER <CREATE MEMBER body clause>)
| ( SET <CREATE SET body clause>)
| ( MEASURE = <measure body clause> )
<SELECT query axis clause> ::=
[ NON EMPTY ] Set_Expression
[ <SELECT dimension property list clause> ]
ON
Integer_Expression
| AXIS(Integer)
| COLUMNS
| ROWS
| PAGES
| SECTIONS
| CHAPTERS
<SELECT subcube clause> ::=
Cube_Name
| [NON VISUAL] (SELECT
[ *
| ( <SELECT query axis clause> [ ,
<SELECT query axis clause>,...n ] )
]
FROM
<SELECT subcube clause>
<SELECT slicer axis clause> )
<SELECT slicer axis clause> ::=
WHERE Tuple_Expression
<SELECT cell property list clause> ::=
[ CELL ] PROPERTIES CellProperty_Name
[ , CellProperty_Name,...n ]
<SELECT dimension property list clause> ::=
[DIMENSION] PROPERTIES
(DimensionProperty_Name
[,DimensionProperty_Name,...n ] )
| (LevelProperty_Name
[, LevelProperty_Name,...n ] )
| (MemberProperty_Name
[, MemberProperty_Name,...n ] )
Arguments
Set_Expression
A valid Multidimensional Expressions (MDX) expression that returns a set.
Integer
An integer between 0 and 127.
Cube_Name
A valid string that provides a cube name.
Tuple_Expression
A valid Multidimensional Expressions (MDX) expression that returns a tuple.
CellProperty_Name
A valid string that represents a cell property.
DimensionProperty_Name
A valid string that represents a dimension property.
LevelProperty_Name
A valid string that represents a level property.
MemberProperty_Name
A valid string that represents a member property.
Remarks
The <SELECT slicer axis clause> expression must contain members in dimensions and hierarchies other than those referenced in the specified <SELECT query axis clause> expressions.
If an attribute in the cube is omitted from the specified <SELECT query axis clause> expressions and the <SELECT slicer axis clause> value, the attribute's default member is implicitly added to the slicer axis.
The NON VISUAL option in the subselect statement enables you to filter out members while keeping the true totals instead of filtered totals. This enables you to query for the top ten sales (persons/products/regions) and obtain the true total of sales for all queried members, instead of the total value of sales for the top ten returned. See the examples below for more information.
Calculated members can be included in <SELECT query axis clause> whenever the connection was opened using the connection string parameter subqueries=1; see Supported XMLA Properties (XMLA) and ConnectionString for parameter usage. An example is provided on calculated members in subselects.
Autoexists
When two or more attributes of the dimension are used in a SELECT statement, Analysis Services evaluates the attributes' expressions to make sure that the members of those attributes are properly confined to meet the criteria of all other attributes. For example, suppose you are working with attributes from the Geography dimension. If you have one expression that returns all members from the City attribute, and another expression that confines members from the Country attribute to all countries/regions in Europe, then this will result in the City members being confined to only those cities that belong to countries/regions in Europe. This characteristic of Analysis Services is called Autoexists and applies only to attributes in the same dimension. Autoexists only applies to attributes from the same dimension because it tries to prevent the dimension records excluded in one attribute expression from being included by the other attribute expressions. Autoexists can also be understood as the resulting intersection of the different attributes expressions over the dimension records. See the following examples below:
//Obtain the Top 10 best reseller selling products by Name
with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'
set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'
set Preferred10Products as '
{[Product].[Model Name].&[Mountain-200],
[Product].[Model Name].&[Road-250],
[Product].[Model Name].&[Mountain-100],
[Product].[Model Name].&[Road-650],
[Product].[Model Name].&[Touring-1000],
[Product].[Model Name].&[Road-550-W],
[Product].[Model Name].&[Road-350-W],
[Product].[Model Name].&[HL Mountain Frame],
[Product].[Model Name].&[Road-150],
[Product].[Model Name].&[Touring-3000]
}'
select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,
Top10SellingProducts on 1
from [Adventure Works]
The obtained result set is:
| Model Name + Measures | Reseller Sales Amount | Discount Amount | PCT Discount |
|---|---|---|---|
| Mountain-200 | $14,356,699.36 | $19,012.71 | 0.13% |
| Road-250 | $9,377,457.68 | $4,032.47 | 0.04% |
| Mountain-100 | $8,568,958.27 | $139,393.27 | 1.63% |
| Road-650 | $7,442,141.81 | $39,698.30 | 0.53% |
| Touring-1000 | $6,723,794.29 | $166,144.17 | 2.47% |
| Road-550-W | $3,668,383.88 | $1,901.97 | 0.05% |
| Road-350-W | $3,665,932.31 | $20,946.50 | 0.57% |
| HL Mountain Frame | $3,365,069.27 | $174.11 | 0.01% |
| Road-150 | $2,363,805.16 | $0.00 | 0.00% |
| Touring-3000 | $2,046,508.26 | $79,582.15 | 3.89% |
The obtained set of products seems to be the same as Preferred10Products; so, verifying the Preferred10Products set:
with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'
set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'
set Preferred10Products as '
{[Product].[Model Name].&[Mountain-200],
[Product].[Model Name].&[Road-250],
[Product].[Model Name].&[Mountain-100],
[Product].[Model Name].&[Road-650],
[Product].[Model Name].&[Touring-1000],
[Product].[Model Name].&[Road-550-W],
[Product].[Model Name].&[Road-350-W],
[Product].[Model Name].&[HL Mountain Frame],
[Product].[Model Name].&[Road-150],
[Product].[Model Name].&[Touring-3000]
}'
select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,
Preferred10Products on 1
from [Adventure Works]
As per the following results, both sets (Top10SellingProducts, Preferred10Products) are the same
| Model Name + Measures | Reseller Sales Amount | Discount Amount | PCT Discount |
|---|---|---|---|
| Mountain-200 | $14,356,699.36 | $19,012.71 | 0.13% |
| Road-250 | $9,377,457.68 | $4,032.47 | 0.04% |
| Mountain-100 | $8,568,958.27 | $139,393.27 | 1.63% |
| Road-650 | $7,442,141.81 | $39,698.30 | 0.53% |
| Touring-1000 | $6,723,794.29 | $166,144.17 | 2.47% |
| Road-550-W | $3,668,383.88 | $1,901.97 | 0.05% |
| Road-350-W | $3,665,932.31 | $20,946.50 | 0.57% |
| HL Mountain Frame | $3,365,069.27 | $174.11 | 0.01% |
| Road-150 | $2,363,805.16 | $0.00 | 0.00% |
| Touring-3000 | $2,046,508.26 | $79,582.15 | 3.89% |
In the previous examples we created two sets: one as a calculated expression and the other as a constant expression. These examples illustrate the different flavors of Autoexists.
Autoexists can be applied deep or shallow to the expressions. The default setting is deep. The following example will illustrate the concept of deep Autoexists. In the example we are filtering Top10SellingProducts by [Product].[Product Line] attribute for those in [Mountain] group. Note that both attributes (slicer and axis) belong to the same dimension, [Product].
with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'
set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'
// Preferred10Products set removed for clarity
select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,
Top10SellingProducts on 1
from [Adventure Works]
where [Product].[Product Line].[Mountain]
Produces the following result set:
| Model Name + Measures | Reseller Sales Amount | Discount Amount | PCT Discount |
|---|---|---|---|
| Mountain-200 | $14,356,699.36 | $19,012.71 | 0.13% |
| Mountain-100 | $8,568,958.27 | $139,393.27 | 1.63% |
| HL Mountain Frame | $3,365,069.27 | $174.11 | 0.01% |
| Mountain-300 | $1,907,249.38 | $876.95 | 0.05% |
| Mountain-500 | $1,067,327.31 | $17,266.09 | 1.62% |
| Mountain-400-W | $592,450.05 | $303.49 | 0.05% |
| LL Mountain Frame | $521,864.42 | $252.41 | 0.05% |
| ML Mountain Frame-W | $482,953.16 | $206.95 | 0.04% |
| ML Mountain Frame | $343,785.29 | $161.82 | 0.05% |
| Women's Mountain Shorts | $260,304.09 | $6,675.56 | 2.56% |
In the previous result set we have seven newcomers to the list of Top10SellingProducts and Mountain-200, Mountain-100 and HL Mountain Frame have moved to the top of the list. In the previous result set those three values were interspersed
This is called Deep Autoexists, because the Top10SellingProducts set is evaluated to meet the slicing conditions of the query. Deep Autoexists means that all expressions will be evaluated to meet the deepest possible space after applying the slicer expressions, the sub select expressions in the axis, and so on.
However, one might want to be able to do the analysis over the Top10SellingProducts as equivalent to Preferred10Products, as in the following example:
with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'
set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'
set Preferred10Products as '
{[Product].[Model Name].&[Mountain-200],
[Product].[Model Name].&[Road-250],
[Product].[Model Name].&[Mountain-100],
[Product].[Model Name].&[Road-650],
[Product].[Model Name].&[Touring-1000],
[Product].[Model Name].&[Road-550-W],
[Product].[Model Name].&[Road-350-W],
[Product].[Model Name].&[HL Mountain Frame],
[Product].[Model Name].&[Road-150],
[Product].[Model Name].&[Touring-3000]
}'
select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,
Preferred10Products on 1
from [Adventure Works]
where [Product].[Product Line].[Mountain]
Produces the following result set:
| Model Name + Measures | Reseller Sales Amount | Discount Amount | PCT Discount |
|---|---|---|---|
| Mountain-200 | $14,356,699.36 | $19,012.71 | 0.13% |
| Mountain-100 | $8,568,958.27 | $139,393.27 | 1.63% |
| HL Mountain Frame | $3,365,069.27 | $174.11 | 0.01% |
In the above results, the slicing gives a result that contains only those products from Preferred10Products that are part of the [Mountain] group in [Product].[Product Line]; as expected, because Preferred10Products is a constant expression.
This result set is also understood as shallow Autoexists. This is because the expression is evaluated before the slicing clause. In the previous example, the expression was a constant expression for illustration purposes in order to introduce the concept.
Autoexists behavior can be modified at the session level using the Autoexists connection string property. The following example begins by opening a new session and adding the Autoexists=3 property to the connection string. You must open a new connection in order to do the example. Once the connection is established with the Autoexist setting it will remain in effect until that connection is finished.
with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'
set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'
//Preferred10Products set removed for clarity
select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,
Top10SellingProducts on 1
from [Adventure Works]
where [Product].[Product Line].[Mountain]
The following result set now shows the shallow behavior of Autoexists.
| Model Name + Measures | Reseller Sales Amount | Discount Amount | PCT Discount |
|---|---|---|---|
| Mountain-200 | $14,356,699.36 | $19,012.71 | 0.13% |
| Mountain-100 | $8,568,958.27 | $139,393.27 | 1.63% |
| HL Mountain Frame | $3,365,069.27 | $174.11 | 0.01% |
Autoexists behavior can be modified by using the AUTOEXISTS=[1|2|3] parameter in the connection string; see Supported XMLA Properties (XMLA) and ConnectionString for parameter usage.
Examples
The following example returns the sum of the Measures.[Order Quantity] member, aggregated over the first eight months of calendar year 2003 that are contained in the Date dimension, from the Adventure Works cube.
WITH MEMBER [Date].[Calendar].[First8Months2003] AS
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Month].[August 2003]
)
)
SELECT
[Date].[Calendar].[First8Months2003] ON COLUMNS,
[Product].[Category].Children ON ROWS
FROM
[Adventure Works]
WHERE
[Measures].[Order Quantity]
To understand NON VISUAL, the following example is a query of [Adventure Works] to obtain [Reseller Sales Amount] figures in a table where the product categories are the columns and the reseller business types are the rows. Note that totals are given for both products and resellers.
The following SELECT statement:
select [Category].members on 0,
[Business Type].members on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]
Produces the following results:
| Business Type + Category | All Products | Accessories | Bikes | Clothing | Components |
|---|---|---|---|---|---|
| All Resellers | $80,450,596.98 | $571,297.93 | $66,302,381.56 | $1,777,840.84 | $11,799,076.66 |
| Specialty Bike Shop | $6,756,166.18 | $65,125.48 | $6,080,117.73 | $252,933.91 | $357,989.07 |
| Value Added Reseller | $34,967,517.33 | $175,002.81 | $30,892,354.33 | $592,385.71 | $3,307,774.48 |
| Warehouse | $38,726,913.48 | $331,169.64 | $29,329,909.50 | $932,521.23 | $8,133,313.11 |
To produce a table with data only for theAccessories and Clothing products, the Value Added Reseller and Warehouse resellers, yet keeping the overall totals could be written as follows using NON VISUAL:
select [Category].members on 0,
[Business Type].members on 1
from NON VISUAL (Select {[Category].Accessories, [Category].Clothing} on 0,
{[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} on 1
from [Adventure Works])
where [Measures].[Reseller Sales Amount]
Produces the following results:
| Business Type + Category | All Products | Accessories | Clothing |
|---|---|---|---|
| All Resellers | $80,450,596.98 | $571,297.93 | $1,777,840.84 |
| Value Added Reseller | $34,967,517.33 | $175,002.81 | $592,385.71 |
| Warehouse | $38,726,913.48 | $331,169.64 | $932,521.23 |
To produce a table that visually totals the columns but for row totals brings the true total of all [Category], the following query should be issued:
select [Category].members on 0,
[Business Type].members on 1
from NON VISUAL (Select {[Category].Accessories, [Category].Clothing} on 0
from ( Select {[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} on 0
from [Adventure Works])
)
where [Measures].[Reseller Sales Amount]
Note how NON VISUAL is only applied to [Category].
The above query produces the following results:
| Business Type + Category | All Products | Accessories | Clothing |
|---|---|---|---|
| All Resellers | $73,694,430.80 | $506,172.45 | $1,524,906.93 |
| Value Added Reseller | $34,967,517.33 | $175,002.81 | $592,385.71 |
| Warehouse | $38,726,913.48 | $331,169.64 | $932,521.23 |
When compared with the previous results, you can observe that the [All Resellers] row now adds up to the displayed values for [Value Added Reseller] and [Warehouse] but that the [All Products] column shows the total value for all products, including those not displayed.
The following example demonstrates how to use calculated members in subselects to filter on them. To be able to reproduce this sample, the connection must be established using the connection string parameter subqueries=1.
select Measures.allmembers on 0
from (
Select { [Measures].[Reseller Sales Amount]
, [Measures].[Reseller Total Product Cost]
, [Measures].[Reseller Gross Profit]
, [Measures].[Reseller Gross Profit Margin]
} on 0
from [Adventure Works]
)
The above query produces the following results:
| Reseller Sales Amount | Reseller Total Product Cost | Reseller Gross Profit | Reseller Gross Profit Margin |
|---|---|---|---|
| $80,450,596.98 | $79,980,114.38 | $470,482.60 | 0.58% |
See Also
Key Concepts in MDX (Analysis Services)
MDX Data Manipulation Statements (MDX)
Restricting the Query with Query and Slicer Axes (MDX)