Вычисляемые элементы в подзапросах выборки и вложенных кубах
В предыдущих версиях вычисляемые элементы не допускались в подзапросах выборки и вложенных кубах. Тем не менее, начиная с версии SQL Server 2008, они разрешены и включаются свойством соединения. Кроме того, в SQL Server 2008 R2 определяется новое поведение вычисляемых элементов в подзапросах выборки и вложенных кубах.
Вычисляемые элементы в подзапросах выборки и вложенных кубах
Свойство строки подключения SubQueries в свойстве ConnectionString или свойство DBPROPMSMDSUBQUERIES в свойствах Поддерживаемые свойства XML для аналитики (XMLA) определяет поведение или разрешаемое использование вычисляемых элементов или вычисляемых наборов в подзапросах выборки и вложенных кубах. В контексте настоящего документа, если не указано иное, термин «подзапросы выборки» означает подзапросы выборки и вложенные кубы.
Свойство SubQueries может принимать следующие значения.
Значение |
Описание |
0 |
Вычисляемые элементы не допускаются в подзапросах выборки и вложенных кубах. Если при вычислении подзапроса выборки или вложенного куба присутствует ссылка на вычисляемый элемент, возникает ошибка. |
1 |
Вычисляемые элементы допускаются в подзапросах выборки и вложенных кубах, однако в возвращаемое подпространство восходящие элементы не вводятся. |
2 |
Вычисляемые элементы допускаются в подзапросах выборки и вложенных кубах, и в возвращаемое подпространство вводятся восходящие элементы. Кроме того, в выборке вычисляемых элементов допускается смешанная гранулярность. |
Использование значений 1 или 2 свойства SubQueries позволяет использовать вычисляемые элементы для фильтрации возвращаемого подпространства подзапросов выборки.
Для пояснения концепции приведем пример: вначале создадим вычисляемый элемент, а затем подзапрос выборки, отражающий указанное выше поведение.
В следующем образце будет создан вычисляемый элемент, добавляющий [Seattle Metro] как город к иерархии [Geography].[Geography] в штате Washington.
Для правильного выполнения примера строка подключения должна содержать свойство SubQueries со значением 1, а все инструкции многомерных выражений должны выполняться в одном сеансе.
Сначала задается многомерное выражение:
//Remember to set Subqueries=1 in the connection string prior
//to issue these commands
//--> AS2008 behavior
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 2001 |
CY 2002 |
CY 2003 |
CY 2004 |
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) |
United States |
(null) |
(null) |
(null) |
(null) |
(null) |
Washington |
(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.
Чтобы лучше понять описанное выше поведение, необходимо помнить, что вычисляемые элементы не включаются в агрегаты своих родителей, в отличие от регулярных элементов (см. раздел Calculated Members and Named Sets); другими словами, фильтрация только по вычисляемым элементам приведет к тому, что родители получат пустые значения вследствие отсутствия регулярных элементов для пополнения агрегированных значений результирующего подпространства. Если к выражению фильтрации добавить регулярные элементы, то агрегированные значения будут формироваться из этих регулярных элементов. Продолжим рассмотрение примера. Добавим к той же оси, на которой отображается вычисляемый элемент, город 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 |
$4,996.25 |
$131,788.82 |
$97,967.09 |
United States |
$235,171.62 |
$419.46 |
$4,996.25 |
$131,788.82 |
$97,967.09 |
Oregon |
$30,968.25 |
$419.46 |
$4,996.25 |
$17,442.97 |
$8,109.56 |
Portland |
$30,968.25 |
$419.46 |
$4,996.25 |
$17,442.97 |
$8,109.56 |
97205 |
$30,968.25 |
$419.46 |
$4,996.25 |
$17,442.97 |
$8,109.56 |
Washington |
$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]. Из вычисляемого элемента не берется ничего.
Примечания
В выражениях подзапроса выборки или вложенного куба допускается использование только элементов, вычисляемых в сеансе, или элементов, вычисляемых глобально. Наличие в многомерном запросе элементов, вычисляемых в запросе, приведет к ошибке выполнения выражения подзапроса выборки или вложенного куба.
См. также