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

Мы обычно рассматриваем систему кэширования Analysis Services как чёрный ящик, считая, что она сделает как раз то, что нужно для оптимизации выполнения. И в идеальном мире система кэширования должна быть абсолютно прозрачной для конечных пользователей. И, хотя оптимизатор запросов MDX и система кэширования очень продвинуты, они не безукоризненны. В этой статье мы увидим, как с помощью очень простых изменений в MDX мы можем лучше воспользоваться преимуществами кэширования и значительно увеличить производительность расчётов.

рисунок.Распределение Гаусса
(Для выполнения примеров, представленных в данной статье, вам необходим MDX Studio.)

В качестве примера мы будем использовать очень простой статистический анализ данных, при котором мы применим “Правило трех сигм” для определения аномальных значений в комбинации атрибутов. Это основывается на том факте, что для данных, которые удовлетворяют нормальному распределению (распределению Гаусса), почти все значения (99,7% из них) находятся в пределах 3 среднеквадратических отклонений от среднего значения. Таким образом, значения, которые находятся от среднего значения дальше, чем в 3 среднеквадратических отклонениях, могут считаться выбросами. (Существуют, конечно, более прогрессивные методы определения выбросов значений в данных, но для целей данной статьи указанный метод достаточно репрезентативен).

Мы начнем с анализа продаж через Интернет (Internet Sales) по дням внутри куба Adventure Works. Найти в MDX дни “за пределами трёх сигм” кажется простым.

with
member sales_avg as Avg ([Date].[Date].[Date], [Internet Sales Amount]), format_string = 'currency'
member sales_stdev as StDev([Date].[Date].[Date], [Internet Sales Amount]), format_string = 'currency'
select { [Internet Sales Amount] } on 0
, filter([Date].[Date].[Date], abs([Internet Sales Amount]-sales_avg) > 3*sales_stdev) on 1
from [Adventure Works]

Если мы выполним данный запрос, то получим список из 10 дат со значениями-выбросами. Но для выполнения запроса требуется 7,5 секунды. Это ужасно! 7,5 секунды для проверки 1158 членов кажутся совершенно неприемлемым. Для получения лучшего представления о том, что происходит, давайте взглянем на статистику выполнения запроса, собранную MDX Studio:

Time : 7 sec 417 ms
Calc covers : 1164
Cells calculated : 1344448
Sonar subcubes : 3
SE queries : 1159
Cache hits : 1159
Cache misses : 2
Cache inserts : 2
Cache lookups : 1161

Теперь понятно, что происходит. Для каждого из 1158 членов машина заново рассчитывает как среднее, так и квадратическое отклонение, несмотря на то, что они были точно теми же самыми в каждой точке (это легко проверить через отладчик в MDX Studio). Проблема состоит в том, что движок не понимает, что средние и квадратические отклонения были одними и теми же для всех дат, поэтому здесь мы должны ему немного помочь. Мы можем переопределить sales_avg как расчёт, который обращается ко всем членам в атрибуте Дата. При этом способе, независимо от того, какую дату мы рассматриваем прямо сейчас, координата ячейки сместится к члену All и будет рассчитываться только один раз, потому что в любое другое время, когда к ней обращаются, ответ может быть получен посредством обращения к FE-кэшу. Переписанный с помощью этого трюка запрос будет выглядеть следующим образом:

with
member sales_avg_ as Avg ([Date].[Date].[Date], [Internet Sales Amount]), format_string = 'currency'
member sales_stdev_ as StDev([Date].[Date].[Date], [Internet Sales Amount]), format_string = 'currency'
member sales_avg as ([Date].[Date].[All Periods],sales_avg_), format_string = 'currency'
member sales_stdev as ([Date].[Date].[All Periods],sales_stdev_), format_string = 'currency'
select { [Internet Sales Amount] } on 0
, filter([Date].[Date].[Date], abs([Internet Sales Amount]-sales_avg) > 3*sales_stdev) on 1
from [Adventure Works]

Если мы проверим его сейчас, то выяснится, что он был выполнен всего лишь за 0,1 доли секунды. Статистика MDX Studio выглядит следующим образом:

Time : 109 ms
Calc covers : 9
Cells calculated : 4642
Sonar subcubes : 3
SE queries : 2
Cache hits : 4
Cache misses : 2
Cache inserts : 2
Cache lookups : 6

Это намного лучше. Вместо SE-запроса для каждой ячейки, мы делаем только 2 SE-запроса (один для главной оси и один для расчета avg/stdev). В этом случае подсчитывается также значительно меньшее количество ячеек, так как теперь средние и квадратические отклонения подсчитываются только раз и обслуживаются из кэша.

Давайте теперь сделаем проблему немного более сложной. Мы подсчитывали статистику все 4 года и обнаружили, что все даты с выбросами были в 2004 году. Это, возможно, объясняется ростом с течением времени. Итак, давайте подсчитаем статистику по-другому, вместо подсчета статистики для всех дат за 4 года, давайте подсчитаем ее в каждом месяце и найдем выбросы для каждого месяца. Прямой способ в MDX может выглядеть следующим образом.


with
member sales_avg as Avg ([Date].[Calendar].Parent.children, [Internet Sales Amount]), format_string = 'currency'
member sales_stdev as StDev([Date].[Calendar].Parent.children, [Internet Sales Amount]), format_string = 'currency'
select { [Internet Sales Amount], sales_avg, sales_stdev } on 0
, filter([Date].[Date].[Date], abs([Internet Sales Amount]-sales_avg) > 3*sales_stdev) on 1
from [Adventure Works]

Выполняя этот запрос, теперь найдем два выброса:

  	Internet Sales Amount  	sales_avg  	sales_stdev
July 22, 2001 	$38,241.29    	$15,270.59    	$6,747.86
December 8, 2001 	$55,454.04    	$24,371.87    	$10,146.40

И статистика MDX Studio будет следующей:

Time : 704 ms
Calc covers : 1206
Cells calculated : 38792
Sonar subcubes : 41
SE queries : 1198
Cache hits : 1198
Cache misses : 2
Cache inserts : 2
Cache lookups : 1200

И снова, статистика не выглядит хорошо. Очень много SE-запросов, подсчитывается очень много ячеек. Как мы можем применить тот же подход, что и раньше? Текущая координата для All dates теперь будет неправильной, потому что средние и квадратические отклонения теперь разные для каждого месяца. Поэтому мы должны сместить координаты на уровень месяцев. Самый простой способ сделать это – просто переместить вызов к функции Parent изнутри вызовов Avg/Stdev в преобразование сдвига координаты кортежа. Ниже представлено, как выглядит итоговый запрос:

with
member sales_avg_ as Avg ([Date].[Calendar].children, [Internet Sales Amount]), format_string = 'currency'
member sales_stdev_ as StDev([Date].[Calendar].children, [Internet Sales Amount]), format_string = 'currency'
member sales_avg as ([Date].[Calendar].Parent,sales_avg_), format_string = 'currency'
member sales_stdev as ([Date].[Calendar].Parent,sales_stdev_), format_string = 'currency'
select { [Internet Sales Amount], sales_avg, sales_stdev } on 0
, filter([Date].[Date].[Date], abs([Internet Sales Amount]-sales_avg) > 3*sales_stdev) on 1
from [Adventure Works]

Статистика после этого редактирования будет следующей

Time : 203 ms
Calc covers : 162
Cells calculated : 6954
Sonar subcubes : 117
SE queries : 78
Cache hits : 78
Cache misses : 2
Cache inserts : 2
Cache lookups : 80

Что является гораздо более приемлемым результатом.


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

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