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

Измерение Время в OLAP – особенное. Многие функции в MDX обычно приобретают смысл только при их применении к измерению Время (PrevMember, Lag, ParallelPeriod, PeriodsToDate, ClosingPeriod и т.д.); полуаддитивные меры по-другому работают со временем и т.д. Однако сегодня мы поговорим на более простую тему – сосчитаем количество дней в выбранном на настоящий момент периоде времени. Имеются различные способы использования данного показателя, например, вычисление среднего значения по времени (это обычно представляет интерес для инвентарных приложений для получения среднего уровня запасов). Для целей этой статьи мы будем использовать куб Adventure Works и рассчитаем среднее значение [Internet Sales Amount] по измерению [Ship Date]. Мы можем сказать, что для расчёта среднего значения во времени необходимо использовать полуаддитивную меру AverageOfChildren. Это верно с двумя оговорками. Во-первых, семантика полуаддитивного агрегирования AverageOfChildren в отношении интерпретации NULL та же самая, что и в функции Avg – а именно, она не считает дни, в которых не было продаж, и если мы хотим посчитать эти дни, то в этом случае функция AverageOfChildren не работает. Во-вторых, функция AverageOfChildren доступна только в версии Enterprise Edition. Наконец, зная количество дней (или других периодов времени) в настоящем времени, мы можем использовать это также и в других расчётах, где применяются средние значения, потому что это довольно просто и наглядно отражает наши задачи.

Наиболее простым и понятным способом подсчета количества дней на текущую дату является следующий:

Count(Descendants([Ship Date].[Calendar], [Ship Date].[Calendar].[Date]))

Теперь, используя данные расчеты, мы можем также высчитать другие показатели, такие как Ежедневные продажи (Sales per Day) и т.д. Посмотрите его работу в следующем запросе:

WITH
MEMBER Measures.[Number Of Days] AS Count(Descendants([Ship Date].[Calendar], [Ship Date].[Calendar].[Date]))
MEMBER Measures.SalesPerDay AS [Measures].[Internet Sales Amount] / Measures.[Number Of Days]
SELECT
{[Measures].[Internet Sales Amount], Measures.[Number Of Days], Measures.SalesPerDay} ON 0
, DrillDownMember(
DrillDownMember(
DrillDownMember(
DrillDownMember([Ship Date].[Calendar].[All Periods], [Ship Date].[Calendar].[All Periods])
,[Ship Date].[Calendar].[Calendar Year].&[2003])
,[Ship Date].[Calendar].[Calendar Semester].&[2003]&[1])
,[Ship Date].[Calendar].[Calendar Quarter].&[2003]&[2])
ON 1
FROM [Adventure Works]

Этот запрос показывает продажи, количество дней и ежедневные продажи для отчёта, в котором пользователь детализировал 2003 год, первую половину года, второй квартал, и указывает месяцы апрель, май, июнь. Работает отлично. Но, когда пользователь хочет посмотреть значение ежедневных продаж не для всего запроса и не для отдельных месяцев, а, например, для комбинации месяцев - апрель и май, в этом случае начинаются проблемы. Данная операция широко известна как множественный выбор (multiselect).


WITH
MEMBER Measures.[Number Of Days] AS Count(Descendants([Ship Date].[Calendar], [Ship Date].[Calendar].[Date]))
MEMBER Measures.SalesPerDay AS [Measures].[Internet Sales Amount] / Measures.[Number Of Days]
SELECT
{[Measures].[Internet Sales Amount], Measures.[Number Of Days], Measures.SalesPerDay} ON 0
FROM [Adventure Works]
WHERE {[Ship Date].[Calendar].[Month].&[2003]&[4],[Ship Date].[Calendar].[Month].&[2003]&[5]}

Как количество дней, так и ежедневные продажи показывают схожую ошибку ‘ Query (2, 45) The MDX function DESCENDANTS failed because the coordinate for the ‘Month Name’ attribute contains a set’. Фрагмент ‘Query (2, 45)’ сообщает нам о том, что проблема возникла в запросе в строке 2, столбец 45. Это означает, что проблема возникла в определении рассчитываемого элемента [Number Of Days] при обращении к функции Descendants (мы узнали это уже из текста ошибки, но в том случае, если бы здесь было более одной функции Descendats, мы смогли бы определить, где произошла ошибка. О частных проблемах я писал раньше в статье “Написание расчетов в MDX, подходящих для множественного выбора”. Используя подходы, описанные в указанной статье, мы можем переписать определение вычисляемого члена для замены функции Descendants оператором EXISTING. Результат будет следующим:

WITH
MEMBER Measures.[Number Of Days] AS Count(EXISTING [Ship Date].[Calendar].[Date_])
MEMBER Measures.SalesPerDay AS [Measures].[Internet Sales Amount] / Measures.[Number Of Days]
SELECT
{[Measures].[Internet Sales Amount], Measures.[Number Of Days], Measures.SalesPerDay} ON 0
FROM [Adventure Works]
WHERE {[Ship Date].[Calendar].[Month].&[2003]&[4],[Ship Date].[Calendar].[Month].&[2003]&[5]}

Теперь ошибки не происходит, и снова получаются правильные результаты, как для количества дней, так и для ежедневных продаж. Теперь мы довольны? Нет, мы недовольны, потому что решены еще не все проблемы. При этом, я сейчас даже не говорю о потерях в производительности, которые происходят, если мы заменяем функцию Descendants оператором EXISTING. Учитывая, что измерение Время обычно очень небольшое (например, 10 лет требуют не более 3660 дней, что является мизером, если дело касается Analysis Services), разница в производительности будет не очень значительной. Я говорю о том, что базовые сценарии, которые требуют или визуальных итогов, или пользовательской группировки, могут быть разбиты более фундаментальным образом, чем множественный выбор.

Давайте вернемся к нашему первому запросу, в котором мы детализировали 2003 год, 3 квартал. Теперь, скажем, мы хотим спрятать месяц июнь, как будто он не существует, и посмотреть, как изменится результат для запроса для 3 квартала, и как изменится весь год. Это так называемый режим визуальных итогов, и Excel всегда по умолчанию включает его. Другие инструменты, такие как OWC, также позволяют делать это. Есть много различных способов, с помощью которых визуальные итоги могут быть реализованы – посредством использования свойства строки соединения ‘Default Visual Mode’, функции MDX VisualTotals, использования подзапросов или оператора CREATE SUBCUBE – не имеет значения. В конце концов все они достигают одного и того же. Давайте посмотрим, как наш запрос будет выглядеть при этом сценарии:

WITH
MEMBER Measures.[Number Of Days] AS Count(Descendants([Ship Date].[Calendar], [Ship Date].[Calendar].[Date]))
MEMBER Measures.SalesPerDay AS [Measures].[Internet Sales Amount] / Measures.[Number Of Days]
SELECT
{[Measures].[Internet Sales Amount], Measures.[Number Of Days], Measures.SalesPerDay} ON 0
, VisualTotals(
Except(
DrillDownMember(
DrillDownMember(
DrillDownMember(
DrillDownMember([Ship Date].[Calendar].[All Periods], [Ship Date].[Calendar].[All Periods])
,[Ship Date].[Calendar].[Calendar Year].&[2003])
,[Ship Date].[Calendar].[Calendar Semester].&[2003]&[1])
,[Ship Date].[Calendar].[Calendar Quarter].&[2003]&[2])
,[Ship Date].[Calendar].[Month].&[2003]&[6])
)
ON 1
FROM [Adventure Works]

Здесь мы прячем июнь с помощью функции Except, а затем помещаем всё в вызов VisualTotals для получения визуальных итогов. Результаты будут довольно плохие. Тогда как физическая мера [Internet Sales Amount] работает абсолютно правильно в присутствии визуальных итогов и показывает только продажи в апреле и мае, вычисленная мера полностью игнорирует это. Например, мера [Number of Days] показывает 91 день, хотя должна показывать 61. Что еще хуже, показатель SalesPerDay в результате этого подвергается полному хаосу. В конце концов – это деление [Internet Sales Amount] на [Number of Days]. Таким образом, мы делим то, что использует визуальные итоги, на то, что не использует. В результате не получается ничего. Число просто не имеет никакого смысла. По меньшей мере, с множественным выбором мы допустили ошибку – пользователь не получает тот результат, который он хочет, но также не получает и неправильный результат. Если бы у него выскочила ошибка, то пользователь знал бы – что-то неправильно. Здесь же у него получается неправильный результат без какого-либо предупреждения. Как он может узнать, что результат неправильный? И изменение выражения при помощи использования функции EXISTING не помогает – результат остается тем же. Здесь наблюдается фундаментальная проблема. При множественном выборе текущая координата отражает его, поэтому выражения MDX могут обнаружить наличие множественного выбора. Но визуальные итоги остаются скрытыми. Невозможно обнаружить их напрямую из MDX. Можно только наблюдать за их побочными эффектами. В общем, это сложная проблема, но некоторые люди думают над решением, и вы можете помочь в этом!

К счастью, в этом частном случае возможно что-то сделать. Это решение для подсчета количества дней в определенном временном периоде. И в отличие от некоторых других проблем, это решение действительно очень элегантное. Оно также решает некоторые другие проблемы. И имеет наилучшую производительность. И оно не требует MDX! Это может звучать как сказка, но это правда. Решение действительно довольно простое, оно было найдено многими людьми независимо друг от друга. В частности Владимир Штепа известен его популяризацией на Интернет-форумах. Трюк состоит в том, чтобы создать новую группу показателей, которая будет иметь только одно измерение – Время, и меру с типом агрегации ‘Count’, связанную с атрибутом День в измерении Время (или другим атрибутом, если мы должны посчитать что-то другое, а не дни). Самым важным для этой новой группы показателей является то, что необходимо оставить IgnoreUnrelatedDimensions=true – это позволит использовать эту меру в других измерениях в кубе. Давайте назовём эту меру [Number Of Days] и посмотрим, как наши запросы работают с ней.

Оба

WITH
MEMBER Measures.SalesPerDay AS [Measures].[Internet Sales Amount] / Measures.[Number Of Days]
SELECT
{[Measures].[Internet Sales Amount], Measures.[Number Of Days], Measures.SalesPerDay} ON 0
FROM [Adventure Works]
WHERE {[Ship Date].[Calendar].[Month].&[2003]&[4],[Ship Date].[Calendar].[Month].&[2003]&[5]}

И

WITH
MEMBER Measures.SalesPerDay AS [Measures].[Internet Sales Amount] / Measures.[Number Of Days]
SELECT
{[Measures].[Internet Sales Amount], Measures.[Number Of Days], Measures.SalesPerDay} ON 0
, VisualTotals(
Except(
DrillDownMember(
DrillDownMember(
DrillDownMember(
DrillDownMember([Ship Date].[Calendar].[All Periods], [Ship Date].[Calendar].[All Periods])
, [Ship Date].[Calendar].[Calendar Year].&[2003])
, [Ship Date].[Calendar].[Calendar Semester].&[2003]&[1])
, [Ship Date].[Calendar].[Calendar Quarter].&[2003]&[2])
, [Ship Date].[Calendar].[Month].&[2003]&[6])
)
ON 1
FROM [Adventure Works]

теперь работают правильно и возвращают нам правильный результат. Но, в этом подходе есть ещё дополнительные преимущества.

Если нам необходимо подсчитать не просто количество дней, но что-то более сложное, как, например, количество рабочих дней, это может быть легко сделано. Просто добавьте другую меру в группу показателей с помощью AggregationType Sum и поставьте значение ‘1′ для всех дней, которые считаются рабочими днями и NULL для тех дней, которые являются выходными и праздничными днями.

* Это хорошо работает, если измерений Время несколько, а также с ролевыми измерениями. На самом деле, в Adventure Works мы имеем 3 выполняющие разные роли измерения Время. Используя их все в группе показателей, мы получим единую меру [Number Of Days] для корректного отражения количества дней, вне зависимости от того, какое измерение Время режет пользователь. При подходе с вычислимыми мерами, для каждого измерения Время приходится создавать свою вычислимую меру.

* Производительность в этом случае наилучшая, какая только может быть, так как сейчас [Number Of Days] является действительной мерой, которая предварительно рассчитывается во время процессинга.

* В дополнение к множественному выбору и визуальным итогам допустимы другие сценарии, такие как пользовательская группировка и нарезание локальных кубов – все они работают правильно.


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

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