Infology.Ru

Автор: Mosha Pasumansky
Дата публикации оригинала: 2006-11-05
Перевод: Андрей Бабуров
Источник: Блог Mosha Pasumansky

Самое главное при построении OLAP систем – это быстродействие. Это подтверждают разные сообщения в новостных группах и форумах. Можно сказать, что наиболее частый вопрос, который задают люди – «Как оптимизировать следующий MDX…». Оптимизация MDX – это и наука и искусство. Она требует смесь знаний конструкций MDX, знакомство с концепцией UDM и некоторое базовое понимание того, как работает оптимизатор запросов. Но самым сложным я нахожу понимание реальной проблемы, скрывающейся за вопросом. Большинство из них требуют глубокого погружения в конкретную модель и хорошие знания бизнес логики и требований. Часто эти детали делают настолько неясными технические вопросы, что никто сразу не предложит правильного решения (конечно, если я не понимаю вопрос после прочтения, то вряд ли включусь в обсуждение) или начнется длительное обсуждение, которое обрастет дополнительными деталями и уточнениями. И в конце, даже если ответ правильный и улучшает производительность, то все равно не просто уловить суть идеи оптимизации и обобщить ее для использования в других случаях.

Поэтому я решил продемонстрировать некоторые техники оптимизаций MDX с использованием простейших примеров. Поговорим сегодня об отклонении бюджета. Отклонение бюджета – это просто разница между реальными данными и забюджетированными. Выражение MDX для такого расчета не сложнее, чем просто Реальные данные – Бюджет. Уже после этого мы рассмотрим все связанные вопросы, увидим, как достичь значительного прироста производительности. Техники, использованные ниже очень просты и, я надеюсь, могут быть применены для решения разных задач. Я также постараюсь объяснить, что происходит «за кулисами» по ходу статьи.

Описание проблемы «отклонения бюджета»

Если отвлечься от бухгалтерии, то «отклонение бюджета» это не всегда вычисление Актуальные значения – Бюджет. Для расходных счетов считается обычно как Бюджет – Актуальные значения. Это правило настолько общее, что в Essbase есть специальная встроенная функция @VAR, которая делает то же самое. @VAR(Актуальные значения, Бюджет) вернет Актуальные значения – Бюджет для доходных счетов и Бюджет – Актуальные значения для расходных (про функцию @VAR можно почитать тут). Нам нужно сделать тоже самое на MDX. Дополнительное требование для расчета отклонений бюджета – это взять в счет тот факт, что бюджет не введен в каждую ячейку в кубе. Есть области, где бюджет неопределен, даже когда актуальные значения определены везде. В этих ячейках нам нужно оставить пустым отклонение бюджета. Если посмотреть, например, на куб Adventure Works, то мы увидим, что бюджет введен только на первый день каждого месяца. Это необычное допущение (хотя корректное). Обычно, бюджеты определены в отдельной группе мер, у которой гранулярность отличная от гранулярности группы мер актуальных значений. Для Даты измерение бюджет может иметь гранулярность по месяцам реже, чем по датам. В любом случае, даже такие пробелы в проектировании бюджета нормальны.

Теперь, если мы откроем куб Adventure Works, мы обнаружим, что там уже есть вычисление для отклонения бюджета, которое удовлетворяет всем нашим требованиям. Ниже приведен фрагмент MDX скрипта:

Create Member CurrentCube.[Scenario].[Scenario].[Budget Variance]
As Case
When IsEmpty
(
(
[Measures].[Amount],
[Scenario].[Scenario].[Budget]
)
)

Format_String = "Currency",
Non_Empty_Behavior = { [Measures].[Amount] }

Перед тем, как спроцессировать куб, нам нужно внести небольшие изменения в это выражение

1. Заменим «Не забюджетировано» (”Not Budgeted”) на NULL. Присваивание строк в значения мер – плохая идея и с точки зрения производительности и с точки зрения корректности. В части корректности – в основном, потому что если мы когда-нибудь будем делать вычисления, которые явно или неявно используют [Budget Variance] – есть шансы, что это будет работать неправильно. Если важно показать пользователям строку «Не забюджетировано», то можно использовать свойство FORMAT_STRING.
2. Проверки по атрибуту [Account Type] (тип счета) полагаются на переписанные правила атрибута – декодировать тип счета, когда текущая координата установлена на Счет. К сожалению, это декодирование не получится в измерениях parent-child между ключевым или родительским и другими атрибутами. Следовательно, проверка на тип счета будет работать неправильно. Нам нужно поменять ее на явную проверку свойства «Тип счета». Это не идеальное решение (только подумайте, что случится, если счета не имеют гранулярность, а типы счетов имеют), но, к сожалению это единственный способ преодолеть упомянутое выше ограничение parent-child.
3. И наконец, свойство Non_Empty_Behavior определено совсем неправильно – [Budget Variance] не ведет себя как мера Amount – с уважением к NULL. К счастью, поскольку она заключена в {}, AS проигнорирует ее (как минимум до SP2) на всем кроме вычисляемых мер. Ничего страшного, но мы уберем это из-за некорректного определения.

После этих изменений, исправленная версия будет выглядеть так:

Create Member CurrentCube.[Scenario].[Scenario].[Budget Variance]
AS Case
When IsEmpty
(
(
[Measures].[Amount],
[Scenario].[Scenario].[Budget]
)
)

Else ( [Measures].[Amount],[Scenario].[Scenario].[Actual] )
-
( [Measures].[Amount],[Scenario].[Scenario].[Budget] )
End
,Format_String = "Currency";

Правило здесь такое – типы счетов Expenditures and Liabilities (расходы и пассивы) считаются как расходные счета. Выражение здесь использует оператор case вместо традиционных вложенных IIF. Это выглядит немного красивее. Оператор case всегда был частью OLEDB для спецификации OLAP с самой первой версии, но только первый раз реализован в AS 2005.

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

WITH MEMBER Measures.PerfTest AS
SUM(
[Date].[Date].[Date]
* [Department].[Department].[Department]
* [Organization].[Organization].[Organization]
* [Destination Currency].[Destination Currency].[Destination Currency]
, Measures.Amount)
SELECT
[Scenario].[Scenario].[Budget Variance] ON 0
,[Account].[Accounts].[Conferences] ON 1
FROM [Adventure Works]
WHERE Measures.PerfTest

Там 1158 дат, 14 организаций и валют и 7 департаментов – объединение даст 1 558 776 ячеек. На моем лаптопе профайлер показывает, что запрос выполняется 27.172 секунды, что соответствует скорости примерно 54 871 ячеек в секунду. Думаю, каждый согласится, что для такого простого вычисления это медленно. Теперь, просто ради интереса, перепишем вычисление с использованием функции IIF:

Create Member CurrentCube.[Scenario].[Scenario].[Budget Variance Iif] AS
IIF(IsEmpty(
(
[Measures].[Amount],
[Scenario].[Scenario].[Budget]
)
)
, NULL
, IIF([Account].[Accounts].CurrentMember.Properties("Account Type") = "Expenditures"
Or
[Account].[Accounts].CurrentMember.Properties("Account Type") = "Liabilities"
,( [Measures].[Amount],[Scenario].[Scenario].[Budget] )
-
( [Measures].[Amount],[Scenario].[Scenario].[Actual] )

,( [Measures].[Amount],[Scenario].[Scenario].[Actual] )
-
( [Measures].[Amount],[Scenario].[Scenario].[Budget] )
)
)
, FORMAT_STRING = "Currency";

Выполнив этот же запрос, мы получим время 1.484 секунды. Это около 20 раз быстрее, чем с оператором case. Очевидно, что оптимизатор запросов использует разный план выполнения. Чтобы понять, что происходит, нам нужно поговорить немного о том, как работает алгоритм вычисления и о разных режимах оценки, которые могут быть использованы для построения плана выполнения запроса.

Режимы вычисления

После того, как FE проанализирует запрос и вычисления в кубе, которые необходимо применить, он подготовит список подмножеств куба и деревья выражений MDX, которые должны быть посчитаны для каждого подмножества. Эти подмножества – просто суб-кубы, с единичной гранулярностью – т.е. точно такие же кубы с SE используются, чтобы читать данные из партиций, такие же как описаны в событии «Запрос к Суб-кубу» в профайлере (см. здесь). Теперь, FE нужно применить выражение ко всему суб-кубу. Есть разные алгоритмы для этого – режимы вычисления. В AS есть несколько режимов вычисления. Их можно разделить на две группы:

• Ячейка-за-ячейкой
• Режим массового вычисления.

Режим «ячейка-за-ячейкой» - это простой режим. Код выполняется на всех ячейках в суб-кубе, обновляет текущую координату в контексте и применяет MDX выражение к текущей ячейке из выборки, точно также как если бы выражение не использовало предыдущие ячейки. Это означает, что для выражения требуется подготовить свою структуру данных, необходимую для вычислений каждой отдельной ячейки в суб-кубе. Очевидно, что это наименее эффективный режим вычисления, но самый простой с точки зрения кода и он служит «запасным» на тот случай, если другие режимы неприменимы.

Режимы массовых вычислений пытаются быть «умнее». Они тщательно анализируют суб-кубы и деревья выражений и пытаются разработать факт, который одним и тем же выражением должен быть посчитан по всем ячейкам в суб-кубе. Например, он может обнаружить, что выражение на самом деле зависит только от нескольких атрибутов и эти атрибуты меняются медленно или даже остаются константами в рамках данного суб-куба. Например, в функциях PrevMember или полу-аддитивной агрегации – LastChild. Обе эти функции зависят только от атрибутов измерения Time, и если в запросе есть атрибуты Time только в срезах и не в осях, тогда PrevMember и/или LastChild могут быть выполнены только единожды для целого суб-куба! Есть много других примеров, но уже понятно, что разные функции MDX могут оптимизироваться по-разному и поэтому выбор режима вычисления сильно зависит от функций MDX в дереве выражений.

Очевидно, мы предпочтем план выполнения, который использует массовые режимы вычисления. Лучший способ обнаружить, какой способ был использован – посмотреть в счетчик PerfMon MSAS 2005:MDX\Total cells calculated. Он наглядно показывает количество ячеек, которые были вычислены с использованием режима вычисления «ячейка-за-ячейкой». Если мы запустим запрос с использованием вычисляемого элемента [Budget Variance], мы увидим, что значение этого счетчика увеличится на 1 589 209. С другой стороны, если мы запустим запрос с использованием [Budget Variance Iif], то счетчик увеличится на 1. Давайте поясним, откуда взялось число 1. В основном, это для единственной ячейки, определенной посредством запроса, перед тем как функция SUM будет разворачивать выборку crossjoin. Оптимизатор запросов использует аргумент «стоимость против выгоды», который говорит, что если мы вычисляем маленькое количество ячеек (например, 1), то оптимизатор не будет использовать режим массового вычисления, поскольку стоимость для построения «массового вычислителя» несоизмерима и вычисление этой ячейки в режиме «ячейка-за-ячейкой» будет быстрее.

Теперь мы можем объяснить, почему IIF намного быстрее, чем CASE. Просто потому что IIF содержит код, поддерживающий массовые вычисления, а CASE не поддерживает. Это было из-за приоритетов. Написать хороший модуль массовых вычислений непросто, и нам пришлось выбирать приоритеты – что делать первым. Функция IIF – очень популярная MDX функция (здесь описано, что это вторая по популярности функция после CurrentMember). Также IIF проще чем CASE, потому что имеет дело только с двумя ветвлениями, в то время как CASE подразумевает неопределенное количество ветвлений. Надеюсь, что по мере роста популярности CASE, она получит собственный механизм массовых вычислений в следующей версии AS (не нужно полностью отказываться от функции CASE, потому что хоть она и менее эффективна, чем IIF, но это не скажется на запросах, обрабатывающих небольшое количество ячеек). Вдобавок, функция CASE выглядит более читабельно.

Но мы еще не закончили! 1.5 секунды для 1.5 миллиона записей это хорошо, но можно сделать еще лучше. Давайте посмотрим как.

Избегайте проверок во время выполнения

Читатели моего блога знают, что я всегда пытаюсь писать свои вычисления так, чтобы избегать IIF и пытаться использовать вместо этого SCOPE (например, см. обсуждения здесь и здесь). Есть хорошее объяснение для этого: IIF реализована таким образом, что проверяет условия во время выполнения (так называемые динамические проверки), в противовес SCOPE, которая проверяет только раз, когда выполняется MDX скрипт (так называемые статические проверки). Есть еще более важная причина для этого, которую мы обсудим далее в это статье. Итак, давайте попытаемся переписать наше выражение без IIF. Часть выражения по проверки типа счета на «расходные» и «нерасходные» будет выглядеть так:

Create [Scenario].[Scenario].[Budget Variance Ex];
SCOPE ([Measures].[Amount], [Scenario].[Scenario].[Budget Variance Ex]);
this = [Scenario].[Scenario].[Actual] - [Scenario].[Scenario].[Budget];
{[Account].[Account Type].[Expenditures], [Account].[Account Type].[Liabilities]} = [Scenario].[Scenario].[Budget] - [Scenario].[Scenario].[Actual];
Format_String(this) = "Currency";
END SCOPE;

Пара замечаний. Во первых, мы создали вычисляемый элемент [Budget Variance Ex] без формулы вычисления. Это означает, что он всегда будет NULL. Затем мы написали выражения SCOPE Actual-Budget, которые упрощают формулу и улучшают понимание. Исходное выражение [Budget Variance] жестко равно мере Measures.Amount везде, независимо от текущей меры. Это означает, что если текущая мера была [Internet Sales Amount], то [Budget Variance] будет работать с мерой Amount. С другой стороны, выражение выше возвращает NULL для [Budget Variance Ex], если текущая мера не Amount. Это более понятно для пользователя. В любом случае, логика первой установки значения всегда быть Actual – Budget, и в следующей строке перепишется, чтобыбыть Budget – Actual.

К несчастью, нам нужно снова вспомнить, что правила перезаписи атрибута не работают в измерениях parent-child для декодирования обычных атрибутов от ключевых или родительских атрибутов. Если бы измерение Account не было бы parent-child, приведенное выше решение работало бы отлично, но поскольку это измерение parent-child, то решение не будет работать корректно. Можно попытаться сделать что-то вроде этого:

Filter(Account.Account.Account.MEMBERS, Account.Account.CurrentMember.Properties("Account Type") = "Expenditures") =
[Scenario].[Scenario].[Budget] - [Scenario].[Scenario].[Actual];

Это тоже не сработает, потому что фильтр, установленный на ключевой или родительский атрибут измерения parent-child, не может быть использован в функции SCOPE (выдается ошибка «arbitrary shape»). Таким образом, мы остались без выбора, и нам придется использовать IIF, чтобы проверить тип счета. Но есть еще один IIF – когда мы проверяем – заполнена мера Budget или нет. Во первых, кажется, что будет невозможно избавится от этого IIF или после всех проверок данных как можно заменить ее на SCOPE? Это правда – мы не можем заменить на SCOPE, но мы можем пойти на небольшую хитрость. Заметим, что IIF используется в следующем виде: IIF(IsEmpty(Budget), NULL, expr) – т.е. когда Budget is NULL, чтоб результат тоже был NULL. Это может быть достигнуто посредством умножения Budget на выражение (expr) – вместо IIF просто написать Budget*expr. Есть небольшая проблема. Конечно, результат будет теперь некорректный, потому что умножается на Budget. Чтобы сделать результат корректным, мы разделим обратно на Budget: Budget*expr/Budget. Это сработает корректно, поскольку в MDX NULL/NULL = NULL.

Итак, окончательный скрипт будет выглядеть так:

Create [Scenario].[Scenario].[Budget Variance Trick];
SCOPE ([Measures].[Amount], [Scenario].[Scenario].[Budget Variance Trick]);
this = [Scenario].[Scenario].[Budget]*
IIF(
[Account].[Account].CurrentMember.Properties("Account Type") = "Expenditures"
Or
[Account].[Account].CurrentMember.Properties("Account Type") = "Liabilities"
,[Scenario].[Scenario].[Budget] - [Scenario].[Scenario].[Actual]
,[Scenario].[Scenario].[Actual] - [Scenario].[Scenario].[Budget])/[Scenario].[Scenario].[Budget];
Format_String(this) = "Currency";
END SCOPE;

Похоже на то, что мы сделали больше работы – помимо всего прочего добавили дополнительные операции умножения и деления, которые тоже занимают время. Давайте выполним наш запрос, используя посчитанную меру [Budget Variance Trick] и определим производительность. Проверяем профайлер, запрос выполнился за…. ВАУ!!! 31 миллисекунду. Это 0.031 секунды. На самом деле я ожидал, что запрос выполнится быстрее, но мы запускали его под профайлером, а профайлер не может считать длительность с точностью до миллисекунды. Но даже 31 миллисекунда – это отличный результат. Настолько отличный, что возникает вопрос – как можно обработать 1.5 миллиона ячеек так быстро? Объяснение в следующей главе.

Режим массового вычисления и NON_EMPTY_BEHAVIOR

Как же возможно обработать 1.5 миллиона ячеек за 31 миллисекунду? Ответ – на самом деле, обрабатывалось гораздо меньше, чем 1.5 миллиона ячеек! Я писал о планах выполнения для оператора умножения в этой статье. Вкратце, массовые вычисления реализуются так: если первый множитель NULL, то результат умножения становится NULL. Поэтому умножение фактически проводится для ячеек, где первый множитель не NULL. Первый множитель – это Бюджет и мы уже упоминали раньше, что бюджет вводится только для первого дня месяца. Таким образом, 97% данных, которые мы обрабатываем, пусты и нужно посчитать только 1/30 часть данных. 1.5 секунды/30 дает нам 50 миллисекунд – вот и весь секрет.

Теперь давайте посмотрим на решение. Работает быстро, но не очень красиво. Нам пришлось сделать избыточное умножение и деление для того, чтобы «обмануть» оптимизатор запросов. Не очень хорошо. И что делать, если бюджет не NULL, а 0? Тогда у нас получится деление на нуль и в итоге #1.INF. Конечно, возможно и от этого защититься, но мы и так зашли далеко. Должен быть способ, чтобы перехватывать это. Есть способ лучше. Можно дать оптимизатору запросов подсказку – описать, какие ячейки не подходят для расчета, поскольку результат вычисления все равно будет NULL. Эта подсказка называется свойство вычисления - NON_EMPTY_BEHAVIOR. Сейчас те, кто работал с AS 2000, слышал о NON_EMPTY_BEHAVIOR. Это было свойство только вычисляемых мер и это могло быть только одно физической мерой и быть использовано только как оптимизация для NON EMPTY. Похоже, что у нас нет ничего из этого – ни вычисляемой меры, ни реальной меры для ссылки, ни NON EMPTY. Добро пожаловать в AS 2005, где полностью переопределено и расширено понятие NON_EMPTY_BEHAVIOR. Это свойство вычисления (как FORMAT_STRING или BACK_COLOR), которое может быть установлено на любое подмножество ячеек в кубе и это значение – другое MDX выражение. Т.е. более общий синтаксис для этого:

NON_EMPTY_BEHAVIOR(<subcube>) = <expression>;

Смысл в том, что для всех ячеек этого подмножества (<subcube>) значения гарантировано будут пустыми, если <expression> вернет NULL. Учтите, что там нет требования «если и только если» - ячейки могут стать NULL даже если <expression> вернет не NULL, но если <expression> вернет NULL, то ячейка точно станет NULL. Это очень сильная подсказка и очень важно использовать ее корректно, потому что оптимизатор запросов доверяет автору MDX скрипта. Выражения для NON_EMPTY_BEHAVIOR должны быть определено правильно. Если они неправильны, то значения ячеек будут получать непредсказуемые результаты, в зависимости от плана выполнения, выбранного оптимизатором.

Теперь, давайте посмотрим – как мы можем применить это в нашем случае. Мы можем доказать математически, что нижеследующее- это правда. Если у нас есть следующее MDX выражение:

<subcube> = IIF( IsEmpty(<empty-expression>), NULL, <else-expression> );

То всегда будет правильно определяться

NON_EMPTY_BEHAVOIR(<subcube>) = <empty-expression>;

И в соответствии с гарантиями, которые предоставляет FE, лучше написать так:

SCOPE (<subcube>);
this = IIF( IsEmpty(<empty-expression>), NULL, <else-expression> );
NON_EMPTY_BEHAVOIR(this) = <empty-expression>;
END SCOPE;

Определенно, конструкция IIF(IsEmpty(…), NULL, …) очень популярна. И, конечно, что истина для функции IIF, то истина для CASE. Итак, вернемся к нашей формуле [Budget Variance] и добавим NON_EMPTY_BEHAVIOR. Вот что получится:

Create Member CurrentCube.[Scenario].[Scenario].[Budget Variance]
AS Case
When IsEmpty
(
(
[Measures].[Amount],
[Scenario].[Scenario].[Budget]
)
)

Else ( [Measures].[Amount],[Scenario].[Scenario].[Actual] )
-
( [Measures].[Amount],[Scenario].[Scenario].[Budget] )
End
,Format_String = "Currency"
,Non_Empty_Behavior = ([Measures].[Amount],[Scenario].[Scenario].[Budget]);

Или можно написать, как я обычно делаю:

Create [Scenario].[Scenario].[Budget Variance];
SCOPE ([Measures].[Amount], [Scenario].[Scenario].[Budget Variance]);
this =
Case
When IsEmpty([Scenario].[Scenario].[Budget])
Then NULL

Then [Scenario].[Scenario].[Budget] - [Scenario].[Scenario].[Actual]
Else [Scenario].[Scenario].[Actual] - [Scenario].[Scenario].[Budget]
End;
Format_String(this) = "Currency";
Non_Empty_Behavior(this) = [Scenario].[Scenario].[Budget];
END SCOPE;

Выполним запрос. Профайлер покажет время 47 миллисекунд. Можно заключить, что это хороший результат и дальше не оптимизировать.

В заключение отмечу, что в этой статье рассмотрена простая проблема вычисления отклонения бюджета, но с разными способами вычисления, которые может выбрать оптимизатор. Самая важная настройка – это подсказка NON_EMPTY_BEHAVIOR, но ей нужно пользоваться очень осторожно, чтобы обеспечить корректность результатов.

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

• Средние величины, отношения, деление на ноль и NON_EMPTY_BEHAVIOR
• Анализ активных событий в MDX
• Как получить текущую дату в MDX
• Drillthrough на вычисляемых мерах
• Подсчёт отличающихся значений в языке MDX