Инструкция SELECT (многомерные выражения)
Извлекает данные из заданного куба.
Синтаксис
[ 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>)
<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 ] )
Аргументы
Set_Expression
Допустимое многомерное выражение, которое возвращает набор.Integer
Целое число между 0 и 127.Cube_Name
Допустимая строка, представляющая имя куба.Tuple_Expression
Допустимое многомерное выражение, возвращающее кортеж.CellProperty_Name
Допустимая строка, представляющая свойство ячейки.DimensionProperty_Name
Допустимая строка, представляющая свойство измерения.LevelProperty_Name
Допустимая строка, представляющая свойство уровня.MemberProperty_Name
Допустимая строка, представляющая свойство элемента.
Замечания
В выражении <SELECT slicer axis clause> должны содержаться элементы измерений и иерархий, отличающиеся от указанных в заданных выражениях <SELECT query axis clause>.
Если в заданных выражениях <SELECT query axis clause> и значении <SELECT slicer axis clause> атрибут куба пропущен, то к оси среза неявно добавляется элемент атрибута по умолчанию.
Параметр NON VISUAL в инструкции подзапроса выборки позволяет отфильтровать элементы во время хранения верных итогов вместо отфильтрованных. Это позволяет выполнить запрос для первых десяти продаж (люди/продукты/регионы) и получить истинный итог продаж для всех запрашиваемых элементов, а не общий итог для первых десяти возвращаемых. Дополнительные сведения см. в примерах ниже.
Вычисляемые элементы могут быть включены в <предложение осей запроса SELECT>, когда соединение открыто с параметром строки соединения subqueries=1. Сведения об использовании параметра см. в разделах Поддерживаемые свойства XML для аналитики (XMLA) и ConnectionString. Пример приведен для вычисляемых элементов в подзапросах выборки.
Автоматическая проверка существования
Если два или более атрибута измерения используются в инструкции SELECT, службы Analysis Services оценивают эти атрибуты, чтобы обеспечить, что элементы этих атрибутов правильно ограничены и соответствуют критериям остальных атрибутов. Например, предположим, что идет работа с атрибутами измерения «География». Если существует выражение, возвращающее все элементы атрибута «Город», и другое выражение, ограничивающее элементы атрибута «Страна» всеми странами Европы, то элементы атрибута «Город» будут ограничены только городами, расположенным в странах Европы. Эта характеристика служб Analysis Services называется автоматической проверкой существования и применима только к атрибутам, находящимся в одном измерении. Автоматическая проверка существования применима только к атрибутам, находящимся в одном измерении, потому что пытается предотвратить включение записей измерения, не включенных в одно выражение с атрибутом, в другое выражение с атрибутом. Автоматическую проверку существования можно также воспринимать как результат пересечения различных выражений с атрибутом по записям измерения. См. следующий пример:
//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]
Полученный результирующий набор выглядит следующим образом:
|
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% |
Полученный набор продуктов выглядит так же, как и Preferred10Products; проверяем набор Preferred10Products:
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]
Согласно полученным результатам наборы Top10SellingProducts и Preferred10Products совпадают:
|
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% |
В предыдущих примерах мы создали два набора: один — как вычисляемое выражение, другой — как константное выражение. В этих примерах демонстрируются другие особенности автоматической проверки существования.
Автоматическую проверку существования можно применить к выражениям глубоко или поверхностно. Значение по умолчанию — глубоко. Следующий пример демонстрирует концепцию глубокой автоматической проверки существования. В примере набор Top10SellingProducts фильтруется по атрибуту [Product].[Product Line] для попадающих в группу [Mountain]. Обратите внимание, что оба атрибута (срез и ось) принадлежат одному измерению ([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]
Создает следующий результирующий набор:
|
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% |
Предыдущий результирующий набор содержит в списке Top10SellingProducts семь новых элементов, а Mountain-200, Mountain-100 и HL Mountain Frame переместились в начало списка. В предыдущем результирующем наборе эти три значения перемешивались с другими элементами.
Это называется глубокой автоматической проверкой существования, так как набор Top10SellingProducts оценивается на соответствие условиям срезов в запросе. Глубокая автоматическая проверка существования означает, что все выражения будут оцениваться для обнаружения наибольшего пространства из возможных после применения выражений выделения среза, выражений подзапроса по оси и т. д.
Однако может возникнуть необходимость провести анализ Top10SellingProducts как эквивалента Preferred10Products, как в следующем примере:
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]
Создает следующий результирующий набор:
|
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% |
В результатах, приведенных выше, срез дает результат, содержащий только те продукты из набора Preferred10Products, которые являются частью группы [Mountain] атрибута [Product].[Product Line] — потому что Preferred10Products является константным выражением, как уже было сказано.
Результирующий набор можно воспринимать как поверхностную автоматическую проверку существования. Это связано с тем, что выражение оценивается перед предложением среза. В предыдущем примере выражение было константным для демонстрации особенностей в целях представления о концепции.
Поведение автоматической проверки существования может изменяться на уровне сеанса при помощи свойства Autoexists строки соединения. В следующих примерах открывается новый сеанс и добавляется свойство Autoexists=3 в строку соединения. Для выполнения примера необходимо создать новое соединение. После установки соединения с параметром автоматической проверки существования (Autoexist) эта проверка будет выполняться до самого закрытия соединения.
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]
Следующий результирующий набор отображает поведение поверхностной автоматической проверки существования.
|
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=[1|2|3] в строке соединения; сведения об использовании этого параметра см. в разделах Поддерживаемые свойства XML для аналитики (XMLA) и ConnectionString.
Примеры
В следующем примере возвращается сумма элемента Measures.[Order Quantity], вычисленная за первые восемь месяцев календарного 2003 г., содержащихся в измерении Date куба Adventure Works.
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]
Для иллюстрации применения NON VISUAL следующий пример содержит запрос [Adventure Works] для получения значений[Reseller Sales Amount] в таблице, где категории продуктов являются столбцами, а типы торговых посредников являются строками. Обратите внимание, что итоги даны и для продуктов, и для торговых посредников.
Рассмотрим следующую инструкцию SELECT.
select [Category].members on 0,
[Business Type].members on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]
Она выдает следующие результаты:
|
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 |
Чтобы создать таблицу с данными только для продуктов Accessories и Clothing и торговых посредников Value Added Reseller и Warehouse, хранение полных итогов должно быть записано при помощи 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]
Выдаются следующие результаты:
|
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 |
Чтобы создать таблицу, которая визуально рассчитывает итоги столбцов, а для строк приводится истинный итог всех [Category], должен быть выполнен следующий запрос.
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]
Обратите внимание, что NON VISUAL применяется только к [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 |
При сравнении с предыдущими результатами можно заметить, что в строке [All Resellers] складываются отображаемые значения [Value Added Reseller] и [Warehouse], однако столбец [All Products] отображает общее значение для всех продуктов, включая те, которые не отображаются.
В следующем примере показано применение вычисляемых элементов в подзапросах выборки для фильтрации по ним. Для выполнения этого образца соединение должно быть установлено с помощью параметра строки соединения 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]
)
Этот запрос выдаст следующий результат.
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% |