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

Как добиться хороших результатов при расчёте средних величин и других величин, основанных на отношениях, в MDX? Я думал, что детально раскрыл эту тему ранее, но, вероятно, я не был достаточно конкретен. На прошлой неделе меня попросили помочь с выполнением работ для очень крупного и важного клиента (который должен был остаться неназванным). Когда я прибыл на место, то увидел, что персонал, занятый в данном проекте, хорошо осведомлён об Analysis Services и MDX. Модель была разработана в соответствии с лучшими традициями, они изучали Руководство по эксплуатации и внедряли все соответствующие изменения. Тем не менее, они не могли добиться того, чтобы очень простой расчёт средней величины в MDX выполнялся с хорошей производительностью в отчётах Excel среднего размера. Они также изучили все предыдущие записи по этой теме в моём блоге: (”How to check if cell is empty“, “Budget Variance and NEB” и “Производительность MDX-функции IIF“), однако это им не помогало. Вместе мы нашли решение, которое удовлетворяло потребности бизнеса и демонстрировало высокие результаты, поэтому я посчитал, что должен поделиться им, так как это является частым сценарием в подобных случаях.

Указанная проблема в большой степени относится к любым видам отношений, однако мы возьмем пример средней величины, для того чтобы быть более конкретными. Давайте предположим, что в хранилище данных у нас есть две меры, относящиеся к продажам продукта: SalesAmount и SalesCount. Теперь мы хотим подсчитать SalesAverage, и наиболее прямым способом для осуществления этого в скрипте MDX будет следующий способ:
Create SalesAverage = Measures.SalesAmount / Measures.SalesCount;

Однако внимательный разработчик обеспокоится делением на ноль, и, таким образом, следуя самым лучшим практикам, наиболее ясным выражением станет следующее:
Create SalesAverage = IIF( Measures.SalesCount = 0, NULL, Measures.SalesAmount / Measures.SalesCount );

Итак, теперь все кажется хорошо. Но, если мы вставим меру SalesAverage в отчёт, который делает cross join нескольких атрибутов с использованием NON EMPTY, то обнаружим, что, хотя результат отчёта небольшой, его вычисление занимает слишком большой промежуток времени. Это, конечно, хорошо известная ситуация, и обычно для её разрешения используется хинт NON_EMPTY_BEHAVIOR. Итак, первым желанием каждого является определение NON_EMPTY_BEHAVIOR при помощи Measures.SalesAmount или Measures.SalesCount. Но правильно ли то?

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

Ответом на этот вопрос станет «это зависит». Это зависит от того, как построено SalesCount и на чём оно основывается. Но вначале давайте рассмотрим, что произойдет, если SalesCount имеет значение 0. Тогда, в соответствии с нашим выражением, SalesAverage будет равно NULL. Это нарушает положение второй таксономии NON_EMPTY_BEHAVIOR, в которой утверждается, что рассчитываемая мера равняется NULL тогда и только тогда, когда мера, определяемая как NON_EMPTY_BEHAVIOR, равна NULL. В этом случае мы нарушаем следующую часть данного положения: «и только тогда». На самом деле мы получили, что SalesAverage равно NULL, в то время как базовая мера не равна NULL, она равна 0, что отличается от NULL. Если мы будем использовать здесь NON_EMPTY_BEHAVIOR, то в итоге получим недетерминированные неправильные результаты! Ирония здесь состоит в том, что другая таксономия NON_EMPTY_BEHAVIOR не является ограничительной, и требует только, чтобы результат расчетов был равен NULL, если выражение NON_EMPTY_BEHAVIOR равно NULL. Я, конечно, хочу, чтобы обе таксономии были объединены и чтобы обе не требовали части «и только тогда». Так как изменить это не очень тяжело, я надеюсь, что это улучшение будет реализовано в следующей версии Analysis Services. Я бы, определенно, голосовал за этот подход. В любом случае, вернемся к нашей проблеме, которую мы должны решить в текущей версии. Если SalesCount никогда не может быть равно 0, а только NULL, то проблемы не возникнет. Если SalesCount определяется как мера с AggregationFunction=Count с подсчетом транзакций в таблице фактов – это является безопасным. Даже если это определяется как AggregationFunction=Sum вверху столбца в таблице фактов, которая никогда не получает значения 0, это опять-таки является безопасным. К сожалению, в нашем случае мера SalesCount может иметь значения 0 в таблице фактов.

Одно из предлагаемых решений состоит в том, чтобы переписать MDX следующим образом:
Create SalesAverage = IIF( NOT IsEmpty(Measures.SalesCount) AND Measures.SalesCount = 0,
0,
Measures.SalesAmount / Measures.SalesCount );

Этот трюк гарантирует, что когда SalesCount равняется 0 (но не NULL), отношение переводит его в 0, поэтому можно безопасно определить, что NON_EMPTY_BEHAVIOR = Measures.SalesCount. Однако представители бизнеса не приняли такое изменение. Они хотят, чтобы среднее значение было равно NULL и отфильтровывалось с помощью NON EMPTY всякий раз, когда SalesCount равнялось 0 или NULL.

Учитывая это требование, я предложил другое решение.

  1. Перейдите к DSV и вставьте названный расчёт, который преобразует 0 в таблице фактов для столбца SalesCount в NULL.
  2. Поменяйте источник меры SalesCount на названный расчёт, созданный на шаге 1.
  3. Поменяйте свойство NullProcessing SalesCount в значение ‘Preserve’.

Сделав эти изменения, мы обеспечим то, что мера SalesCount никогда не будет нулем в данном кубе, так как все значения 0 были переведены в NULL и мера был установлен для сохранения этих NULL. Теперь, наконец-то, можно определить NON_EMPTY_BEHAVIOR как Measures.SalesCount.

Но это еще не конец. Теперь мы видим, что, так как SalesCount никогда не является нулем, у нас никогда не возникнет деления на ноль. Однако SalesCount может быть NULL, поэтому мы столкнемся с делением на NULL. Но хотя NULL ведет себя подобно нулю в арифметических расчетах, эти ситуации не являются идентичными. Самым важным различием является то, что при делении 0/0 получается неопределенное значение #1.IND, а при делении NULL/NULL получается NULL. Поскольку SalesAmount и SalesCount исходят из одной и той же группы показателей, то в случае, если транзакция пропадает из таблицы фактов, оба показателя вместе равны NULL, таким образом, получается ситуация NULL/NULL. Другие варианты остаются, если SalesCount равно 0 в таблице фактов. Но если SalesCount равняется 0, то SalesAmount также равняется 0, поскольку мы можем получить ноль денег только в том случае, если мы продали ноль единиц продукции. Поэтому, проделав тот же трюк с названным расчетом в DSV и NullProcessing=Preserve, мы получим, что SalesAmount всегда равно NULL, когда SalesCount равно NULL.

Поэтому нам не нужно проверять деление на ноль, так как это всегда безопасно. Окончательный скрипт MDX будет следующим:
CREATE MEMBER CurrentCube.Measures.SalesAverage AS
Measures.SalesAmount / Measures.SalesCount
, NON_EMPTY_BEHAVIOR = Measures.SalesCount;


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

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