Автор: Mosha Pasumansky
Дата публикации оригинала: 2007-12-18
Источник: Блог Mosha Pasumansky

Секционирование является встроенным свойством Analysis Services с первой версии. Естественно, все подсистемы движка, включая оптимизатор запросов MDX, хорошо работают с секционированием. Однако иногда бывают случаи, когда сверхагрессивная предварительная выборка может создать план запроса, который не является оптимальным по отношению к схеме секционирования. Сегодня мы рассмотрим один из таких сценариев и посмотрим, как переписать запрос MDX, чтобы он лучше подходил для схемы секционирования.

Давайте рассмотрим сценарий, когда мы должны определить последнюю дату, на которую есть данные в определенной группе показателей. Пример будет строиться в базе данных Adventure Works с использованием измерения Дата и Internet Sales Amount в качестве меры.

Еще необходимо отметить следующее: при написании этой статьи я использую MDX Studio, что упрощает многие операции, поэтому, когда я говорю «смотреть в множестве», я имею в виду просмотр в окне Watch в MDX Studio, когда я говорю «очистить кэш», это означает использование кнопки очистки кэша в MDX Studio и так далее. Единственным дополнительным инструментом, который нужно запустить, является Profiler, так как трассировки AS еще не интегрированы в MDX Studio, но группа разработчиков MDX Studio работает над этим вопросом. Также для того, чтобы можно было запускать операторы MDX из MDX Studio, я добавил функцию “Cube=Adventure Works” в поле Connection Properties диалогового окна Connect.

Наиболее эффективно подсчитать последнюю дату можно используя следующую формулу:

CREATE SET CurrentCube.LastDate as Tail(NonEmpty([Date].[Date].[Date], [Measures].[Internet Sales Amount]), 1)

Мы можем видеть, что результирующее множество будет иметь в нем единственный член – 31 июля 2004 года. Но если мы исполним этот оператор при чистом кэше, то можем видеть следующее:

Query Begin 0 - MDXQuery MDX Studio v0.2.6.0
Progress Report Begin 14 - Query Started reading data from the ‘Internet_Sales_2001′ partition.
Progress Report Begin 14 - Query Started reading data from the ‘Internet_Sales_2002′ partition.
Progress Report Begin 14 - Query Started reading data from the ‘Internet_Sales_2003′ partition.
Progress Report End 14 - Query Finished reading data from the ‘Internet_Sales_2001′ partition.
Progress Report Begin 14 - Query Started reading data from the ‘Internet_Sales_2004′ partition.
Progress Report End 14 - Query Finished reading data from the ‘Internet_Sales_2002′ partition.
Progress Report End 14 - Query Finished reading data from the ‘Internet_Sales_2003′ partition.
Progress Report End 14 - Query Finished reading data from the ‘Internet_Sales_2004′ partition.
Query End 0 - MDXQuery

Мы видим, что были запрошены все секции, хотя последней непустой датой является 2004, и поэтому достаточно запросить секцию Internet_Sales_2004. К сожалению, в этом примере оптимизатор запросов уходит в сторону и решает игнорировать внешнюю часть (…., 1) и выполняет функцию NonEmpty буквально для всех дат. Поэтому здесь необходим другой подход. Второй очевидной стратегией для определения последней непустой даты является рекурсивный способ – начинаем с последней даты в измерении и идём обратно до того момента, пока не дойдем до даты, которая имеет данные. В этом случае формула следующая:

CREATE
MEMBER CurrentCube.LastDateIndex AS
Iif(
IsEmpty([Measures].[Internet Sales Amount]),
[Date].[Calendar].PrevMember,
Rank([Date].[Calendar].CurrentMember, [Date].[Calendar].[Date])
)
SET LastDate AS [Date].[Calendar].[Date].Item((LastDateIndex, Tail([Date].[Calendar].[Date],1).Item(0))-1)

Здесь в вычислимом члене LastDateIndex мы применяем рекурсивную логику, а затем позиционируем его по последней дате (используя для этого Tail(1) над уровнем дат). Результат будет тем же, что и представленный выше, но запись выглядит по-другому:

Query Begin 0 - MDXQuery MDX Studio v0.2.6.0
Query Subcube 2 - Non-cache data August 31, 2004
Query Subcube 2 - Non-cache data August 30, 2004
Query Subcube 2 - Non-cache data August 29, 2004
Query Subcube 2 - Non-cache data August 28, 2004
Query Subcube 2 - Non-cache data August 27, 2004
Query Subcube 2 - Non-cache data August 26, 2004
Query Subcube 2 - Non-cache data August 25, 2004
Query Subcube 2 - Non-cache data August 24, 2004
Query Subcube 2 - Non-cache data August 23, 2004
Query Subcube 2 - Non-cache data August 22, 2004
Query Subcube 2 - Non-cache data August 21, 2004
Query Subcube 2 - Non-cache data August 20, 2004
Query Subcube 2 - Non-cache data August 19, 2004
Query Subcube 2 - Non-cache data August 18, 2004
Query Subcube 2 - Non-cache data August 17, 2004
Query Subcube 2 - Non-cache data August 16, 2004
Query Subcube 2 - Non-cache data August 15, 2004
Query Subcube 2 - Non-cache data August 14, 2004
Query Subcube 2 - Non-cache data August 13, 2004
Query Subcube 2 - Non-cache data August 12, 2004
Query Subcube 2 - Non-cache data August 11, 2004
Query Subcube 2 - Non-cache data August 10, 2004
Query Subcube 2 - Non-cache data August 9, 2004
Query Subcube 2 - Non-cache data August 8, 2004
Query Subcube 2 - Non-cache data August 7, 2004
Query Subcube 2 - Non-cache data August 6, 2004
Query Subcube 2 - Non-cache data August 5, 2004
Query Subcube 2 - Non-cache data August 4, 2004
Query Subcube 2 - Non-cache data August 3, 2004
Query Subcube 2 - Non-cache data August 2, 2004
Query Subcube 2 - Non-cache data August 1, 2004
Progress Report Begin 14 - Query Started reading data from the ‘Internet_Sales_2004′ partition.
Progress Report End 14 - Query Finished reading data from the ‘Internet_Sales_2004′ partition.
Query Subcube 2 - Non-cache data July 31, 2004
Query End 0 - MDXQuery

Теперь мы видим много не кэшированных запросов Query Subcube для дат, начиная с 31 августа 2004 года до 31 июля 2004 года. Интересно, что только последняя дата при этом инициирует чтение секции, тогда как для других при автоматическом секционировании фрагментов обнаруживается, что они не относятся к какой-либо секции. Таким образом, в каком-то смысле это хороший результат, так как мы имеем дело только с одной секцией, но в другом смысле план запроса с большим количеством запросов Query Subcube не выглядит хорошо. Нам повезло, что в Adventure Works был только 31 день в конце измерения Дата, поэтому рекурсия составляла только 31 шаг. Но довольно часто встречаются измерения Дата на несколько лет вперед, при которых рекурсия может составлять тысячи шагов, и накладные расходы на запросы Query Subcube могут стать существенными, не говоря уже о накладных расходах на глубину рекурсии.

Это заставляет нас использовать другой способ. Вместо итераций по дням, мы можем делать итерации по секциям. Так как в Adventure Works применяется схема секционирования по годам, мы делаем итерации по годам, а затем находим последнюю непустую дату в году. В MDX это реализуется следующим способом:

CREATE
MEMBER CurrentCube.LastYearIndex AS
Iif(
IsEmpty([Measures].[Internet Sales Amount]),
[Date].[Calendar Year].PrevMember,
Rank([Date].[Calendar Year].CurrentMember, [Date].[Calendar Year].[Calendar Year])
)
SET LastYear AS [Date].[Calendar Year].[Calendar Year].Item((LastYearIndex, [Date].[Calendar Year].LastChild)-1)
SET LastDate AS Tail(NonEmpty([Date].[Date ].[Date].MEMBERS, (LastYear, [Measures].[Internet Sales Amount])), 1)

Это дает нам желаемый результат, и теперь трассировка выглядит следующим образом:

Query Begin 0 - MDXQuery MDX Studio v0.2.6.0
Progress Report Begin 14 - Query Started reading data from the ‘Internet_Sales_2004′ partition.
Progress Report End 14 - Query Finished reading data from the ‘Internet_Sales_2004′ partition.
Query Subcube 2 - Non-cache data
00000000,000,00000,00,000000000000100000,000000000000000000,000000000000000000,0000000000000000000000,000000000000000000000,00,10
Progress Report Begin 14 - Query Started reading data from the ‘Internet_Sales_2004′ partition.
Progress Report End 14 - Query Finished reading data from the ‘Internet_Sales_2004′ partition.
Query Subcube 2 - Non-cache data
00000000,000,00000,00,010000000000100000,000000000000000000,000000000000000000,0000000000000000000000,000000000000000000000,00,10
Query End 0 - MDXQuery

Мы избегаем появления большого количества запросов Query Subcube, но у нас остается два, и теперь каждый из них инициирует запрос секции. Почему их два? На этот вопрос легко ответить, если проверить гранулированные битовые маски запросов Query Subcube. Мы видим, что различие между ними состоит в измерении Дата. Первый запрос Query Subcube не имеет структурирования по атрибуту Дата, тогда как второй – имеет. Теперь ясно, что первый запрос Query Subcube запускается посредством проверки IsEmpty([Measures].[Internet Sales Amount]) внутри функции Iif – потому что это происходит на уровне Год, а второй обусловлен NonEmpty по датам с фильтром по определенным годам.

Нам не нравится тот факт, что мы возвращаемся к секции дважды, фактически за одними и теми же данными, но с разной детализацией. Поэтому в MDX мы можем сделать небольшой трюк. Так как мы в любом случае должны идти на уровень года, давайте лучше сделаем это раньше, чем позже, и расширим степень детализации уже в функции Iif. В итоге в MDX это будет выглядеть следующим образом:

CREATE
MEMBER CurrentCube.LastYearIndex AS
Iif(
IsEmpty(Aggregate(Descendants([Date].[Calendar], [Date].[Calendar].[Date]), [Measures].[Internet Sales Amount])),
[Date].[Calendar Year].PrevMember,
Rank([Date].[Calendar Year].CurrentMember, [Date].[Calendar Year].[Calendar Year])
)
SET LastYear AS [Date].[Calendar Year].[Calendar Year].Item((LastYearIndex, [Date].[Calendar Year].LastChild)-1)
SET LastDate AS Tail(NonEmpty([Date].[Date].[Date].MEMBERS, (LastYear, [Measures].[Internet Sales Amount])), 1)

Посмотрим, что мы теперь имеем при трассировке:

Query Begin 0 - MDXQuery MDX Studio v0.2.6.0
Progress Report Begin 14 - Query Started reading data from the ‘Internet_Sales_2004′ partition.
Progress Report End 14 - Query Finished reading data from the ‘Internet_Sales_2004′ partition.
Query Subcube 2 - Non-cache data
00000000,000,00000,00,011010000001100000,000000000000000000,000000000000000000,0000000000000000000000,000000000000000000000,00,10
Query Subcube 1 - Cache data
00000000,000,00000,00,010000000000100000,000000000000000000,000000000000000000,0000000000000000000000,000000000000000000000,00,10
Query End 0 - MDXQuery

Это замечательно – как раз то, что мы хотели. Второй запрос Query Subcube, который обращался к некэшированным данным, теперь становится кэшированным, и в этом случае для одной секции выполняется только один запрос.

Эта статья была написана по результатам дискуссии на форуме sql.ru


Для удобства отслеживания новых публикаций рекомендуем подписаться на рассылку или на канал RSS.

Читайте также: