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

Тема множественного выбора в вычислениях популярна на форумах. Я писал об этом ранее здесь. В феврале 2008 года (данная статья написана в 2007 г – прим. ред.) я напишу в блоге как AS2008 будет с этим работать. Но в тоже время поступают вопросы по AS2005. Сейчас многие в работе используют EXISTING в выражении WHERE для определения условия на множество в запросе, однако такая конструкция порождает много подзапросов, при использовании ее в Excel 2003 и других приложениях. Excel 2007 для множественного выбора использует скрытые подзапросы. Их присутствие не обнаруживается ни с помощью EXISTING, ни с помощью другой функции MDX. Стандартный подход для обнаружения подзапросов всегда был таким: использовать именованные множества на уровне запросов, потому что они неявно существуют в подзапросах. Но, это утверждение не применимо к Excel 2007, потому что пользователь может только контролировать формулы для вычислений внутри MDX запроса.

Таким образом, можно сформулировать вопрос так: - “Возможно, ли в AS2005 обнаружить ограничения на подзапросы из MDX-вычислений?”. До сегодняшнего времени ответ был - “нет”. Я придумал, как сделать так, чтобы можно было ответить “да”. Должен предупредить, что решение некрасивое и неэффективное и, возможно, представляет только теоретический интерес. Я бы не рекомендовал его использовать в реальных задачах. Но, тем не менее, это интересно.

Давайте начнем с предположения, что самый нижний атрибут, на котором возможен множественный выбор, содержит несколько элементов. Более точно - он должен содержать не более 63 элементов. Позже мы увидим, как можно увеличить этот предел, но сейчас используем то, что есть. В нашем примере мы выберем атрибут [Ship Date].[Calendar Year], в котором только 4 элемента. Для вычисления возьмем пример подсчета максимальных продаж за год. Классически, формула выглядит так:

Max(Descendants([Ship Date].[Calendar].CurrentMember, [Ship Date].[Calendar].[Calendar Year]), [Measures].[Internet Sales Amount])

Действительно, если мы запустим следующий запрос

WITH
MEMBER Measures.[Max Yearly Sales] AS Max(Descendants([Ship Date].[Calendar].CurrentMember, [Ship Date].[Calendar].[Calendar Year]), [Measures].[Internet Sales Amount])
SELECT
{Measures.[Max Yearly Sales]} ON 0
FROM (
SELECT { [Ship Date].[Calendar].[Calendar Year].&[2001], [Ship Date].[Calendar].[Calendar Year].&[2003] } ON 0
FROM [Adventure Works])

то результат будет $10,158,562.38. Быстро проверим запрос:

SELECT {[Measures].[Internet Sales Amount]} ON 0
, [Ship Date].[Calendar].[Calendar Year] ON 1
FROM [Adventure Works]

Число, которое мы получили, было актуально для 2004 года. Но результат даже не содержит данные нашего ограничения в подзапросе, которое покрывает только 2001 и 2003 год. Так как же это работает? Во-первых, мы создадим дополнительно маленькую таблицу (или именованный запрос в Datasource view) который выглядит так:

Year Encoding
2001 1
2002 2
2003 4
2004 8

В общем случае мы хотим получить список всех элементов в атрибуте нижнего уровня, по которому ожидается множественный выбор, и присвоим значения степени числа 2. Идея в том, что каждый элемент получает бит в 64х битном представлении типа integer. Добавляя значения Encoding для разных элементов, мы получим эквивалент для операции OR в битовом представлении.

Далее мы сделаем группу метрик вне этой таблицы, путем присоединения колонки Year к атрибуту [Calendar Year] в размерности [Ship Date], и Encoding использована как метрика с функцией агрегации Sum. Но в соответствии с природой значений Encoding, функция агрегации здесь по-настоящему ведет себя как OR.

Это основа идеи. Когда есть ограничение с подзапросом, все физические метрики получают автоматически визуальные итоги (Visual Totals), примененные к ним, если координата [Calendar Year] не была перезаписана формулой. Применение Visual Totals к метрике Encoding даст в результате число, у которого биты установлены в 1 только в элементах, участвующих в подзапросе! Теперь нам нужно обратно декодировать из битовой маски во множество MDX. Самый простой способ сделать это - запустить фильтр по [Calendar Year] и отфильтровать по И. Но это не сработает, если использовать фильтр по измерению [Calendar Year], которое было переопределено – не создадутся Visual Totals. Поэтому нам нужно сделать кое-что более сложное – написать специальную хранимую процедуру, которая ведет себя также как фильтр, только без перезаписи координат. Такая хранимая процедура проста в написании, ниже представлен код для неё:

public Set DecodeSet(Set set, System.UInt64 Encoding)
{
SetBuilder sb = new SetBuilder();
System.UInt64 bit = 1;
foreach (Tuple t in set.Tuples)
{
if ((bit & Encoding) != 0)
sb.Add(t);

bit *= 2;
}

return sb.ToSet();
}

Теперь выражение для вычисляемого элемента будет выглядеть так:

Max(ASSP.ASStoredProcs.Util.DecodeSet([Ship Date].[Calendar].[Calendar Year], [Measures].[Encoding]), [Measures].[Internet Sales Amount])

И мы можем проверить, что это работает правильно, запустив следующий запрос:

WITH
MEMBER Measures.[Max Yearly Sales] AS Max(ASSP.ASStoredProcs.Util.DecodeSet([Ship Date].[Calendar].[Calendar Year], [Measures].[Encoding]), [Measures].[Internet Sales Amount])
SELECT
{Measures.[Max Yearly Sales]} ON 0
FROM (
SELECT { [Ship Date].[Calendar].[Calendar Year].&[2001], [Ship Date].[Calendar].[Calendar Year].&[2003] } ON 0
FROM [Adventure Works])

Это все хорошо, теперь вернемся к случаю, когда у нас более 63 элементов атрибута. Например, нам нужно охватить отдельные даты (не годы), и в кубе [Adventure Works] у нас есть данные почти за 4 года - за 1158 дней. 1158 > 63. Таким образом, единственное решение в этом случае – разбить атрибут на группы по 63 элемента и присвоить отдельную метрику каждой группе. В нашем случае получится 19 групп. Это не очень красиво, но, по крайней мере, автоматизировано. Хранимую процедуру можно изменить, чтобы она принимала второй параметр, где будет проходить формула MeasureGroupMeasures(”ShipDateMultiselect”), и хранимая процедура будет иметь вложенные циклы для того, чтобы поместить элемент в правильную метрику. Точный код процедуры оставлен для самостоятельных упражнений читателю, если еще остался интерес.


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

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