Вычисляемые элементы в подзапросах выборки и вложенных кубах
Применимо к:
SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium
Вычисляемый элемент — это элемент измерения, значение которого вычисляется из выражения во время выполнения и может использоваться в подзапросах выборки и вложенных кубах, чтобы более точно определить пространство куба запроса.
Включение вычисляемых элементов в подпространстве
Свойство SubQueries строка подключения в ConnectionString или свойство DBPROPMSMDSUBQUERIES в разделе Поддерживаемые свойства XMLA (XMLA) определяет поведение или допустимость вычисляемых элементов или вычисляемых наборов для вложенных выборок или вложенных кубов. В контексте настоящего документа, если не указано иное, термин «подзапросы выборки» означает подзапросы выборки и вложенные кубы.
Свойство SubQueries может принимать следующие значения.
| Значение | Описание |
|---|---|
| 0 | Вычисляемые элементы не допускаются в подзапросах выборки и вложенных кубах. Если при вычислении подзапроса выборки или вложенного куба присутствует ссылка на вычисляемый элемент, возникает ошибка. |
| 1 | Вычисляемые элементы допускаются в подзапросах выборки и вложенных кубах, однако в возвращаемое подпространство восходящие элементы не вводятся. |
| 2 | Вычисляемые элементы допускаются в подзапросах выборки и вложенных кубах, и в возвращаемое подпространство вводятся восходящие элементы. Кроме того, в выборке вычисляемых элементов допускается смешанная гранулярность. |
Использование значений 1 или 2 свойства SubQueries позволяет использовать вычисляемые элементы для фильтрации возвращаемого подпространства подзапросов выборки.
Для пояснения концепции приведем пример: вначале создадим вычисляемый элемент, а затем подзапрос выборки, отражающий указанное выше поведение.
В следующем примере будет создан вычисляемый элемент, добавляющий [Seattle Metro] как город к иерархии [Geography].[Geography] в штате Washington.
Для правильного выполнения примера строка подключения должна содержать свойство SubQueries со значением 1, а все инструкции многомерных выражений должны выполняться в одном сеансе.
Важно!
Если вы тестируете запросы с помощью Management Studio, нажмите кнопку "Параметры" в диспетчере подключений, чтобы открыть панель дополнительных свойств строки подключения. Введите subqueries=1 или 2, чтобы разрешить вычисляемые элементы в подпространстве.
Сначала задается многомерное выражение:
CREATE MEMBER [Adventure Works].[Geography].[Geography].[State-Province].&[WA]&[US].[Seattle Metro Agg]
AS AGGREGATE(
{
[Geography].[Geography].[City].&[Bellevue]&[WA]
, [Geography].[Geography].[City].&[Issaquah]&[WA]
, [Geography].[Geography].[City].&[Redmond]&[WA]
, [Geography].[Geography].[City].&[Seattle]&[WA]
}
)
Затем, чтобы увидеть вычисляемые элементы, допустимые в подзапросах выборки, создается следующий запрос многомерных выражений:
Select [Date].[Calendar Year].members on 0,
[Geography].[Geography].allmembers on 1
from (Select {[Geography].[Geography].[State-Province].&[WA]&[US].[Seattle Metro Agg]} on 0 from [Adventure Works])
Where [Measures].[Reseller Sales Amount]
Мы получим следующие результаты.
| All Periods | CY 2011 | CY 2012 | CY 2013 | CY 2014 | |
|---|---|---|---|---|---|
| Seattle Metro Agg | 2 383 545,69 $ | 291 248,93 $ | 763 557,02 $ | 915 832,36 $ | 412 907,37 $ |
Как было указано выше, если свойство SubQueries имеет значение 1, родительские элементы [Seattle Metro] в возвращаемом подпространстве отсутствуют, поэтому [Geography].[Geography].allmembers содержит только вычисляемый элемент.
Если тот же пример выполнить со значением свойства SubQueries в строке подключения, равным 2, то будут получены следующие результаты.
| All Periods | CY 2001 | CY 2002 | CY 2003 | CY 2004 | |
|---|---|---|---|---|---|
| All Geographies | (null) | (null) | (null) | (null) | (null) |
| США | (null) | (null) | (null) | (null) | (null) |
| Вашингтон | (null) | (null) | (null) | (null) | (null) |
| Seattle Metro Agg | 2 383 545,69 $ | 291 248,93 $ | 763 557,02 $ | 915 832,36 $ | 412 907,37 $ |
Как отмечалось ранее, если значение свойств SubQueries равно 2, родители элемента [Seattle Metro] присутствуют в возвращаемом подпространстве, однако значения для этих элементов не существуют, поскольку для агрегатов отсутствуют регулярные элементы. Поэтому в этом примере для всех родителей вычисляемого элемента задаются значения NULL.
Чтобы лучше понять описанное выше поведение, стоит понимать, что вычисляемые элементы не включаются в агрегаты своих родителей, в отличие от регулярных элементов (см. раздел ); другими словами, фильтрация только по вычисляемым элементам приведет к тому, что родители получат пустые значения вследствие отсутствия регулярных элементов для пополнения агрегированных значений результирующего подпространства. Если к выражению фильтрации добавить регулярные элементы, то агрегированные значения будут формироваться из этих регулярных элементов. Продолжим рассмотрение примера. Добавим к той же оси, на которой отображается вычисляемый элемент, город Portland, шт. Oregon и город Spokane, шт. Washington. Многомерное выражение в этом случае будет таким:
Select [Date].[Calendar Year].members on 0,
[Geography].[Geography].allmembers on 1
from (Select {
[Seattle Metro Agg]
, [Geography].[Geography].[City].&[Portland]&[OR]
, [Geography].[Geography].[City].&[Spokane]&[WA]
} on 0 from [Adventure Works]
)
Where [Measures].[Reseller Sales Amount]
Будут получены следующие результаты.
| All Periods | CY 2001 | CY 2002 | CY 2003 | CY 2004 | |
|---|---|---|---|---|---|
| All Geographies | 235 171,62 $ | 419,46 $ | 4996,25 $ | 131 788,82 $ | 97 967,09 $ |
| США | 235 171,62 $ | 419,46 $ | 4996,25 $ | 131 788,82 $ | 97 967,09 $ |
| Орегон | 30 968,25 $ | 419,46 $ | 4996,25 $ | 17 442,97 $ | 8109,56 $ |
| Портленд | 30 968,25 $ | 419,46 $ | 4996,25 $ | 17 442,97 $ | 8109,56 $ |
| 97205 | 30 968,25 $ | 419,46 $ | 4996,25 $ | 17 442,97 $ | 8109,56 $ |
| Вашингтон | 204 203,37 $ | (null) | (null) | 114 345,85 $ | 89 857,52 $ |
| Spokane | 204 203,37 $ | (null) | (null) | 114 345,85 $ | 89 857,52 $ |
| 99202 | 204 203,37 $ | (null) | (null) | 114 345,85 $ | 89 857,52 $ |
| Seattle Metro Agg | 2 383 545,69 $ | 291 248,93 $ | 763 557,02 $ | 915 832,36 $ | 412 907,37 $ |
В приведенных результатах агрегированные значения для [All Geographies], [United States], [Oregon] и [Washington] получаются на основе статической обработки родителей &[Portland]&[OR] и &[Spokane]&[WA]. Из вычисляемого элемента не берется ничего.
Комментарии
В выражениях подзапроса выборки или вложенного куба допускается использование только элементов, вычисляемых в сеансе, или элементов, вычисляемых глобально. Наличие в многомерном запросе элементов, вычисляемых в запросе, приведет к ошибке выполнения выражения подзапроса выборки или вложенного куба.
См. также:
ConnectionString
Подзапросы выборки в запросах
Поддерживаемые свойства XML для аналитики (XMLA)