Сила вычисляемых ячеек

Рас Уитни, SQL Server Online/RE #3/2001

Вычисляемые ячейки, как и вычисляемые члены размерностей позволяют применять формулы, включающие условия.

Недавно один администратор баз данных спросил у меня, зачем обращаться к системе управления многомерными базами данных, такой как SQL Server 2000 Analysis Services, если можно просто создать собственный набор реляционных таблиц OLAP (ROLAP) для хранения всей иерархической структуры размерностей куба и ассоциированных с ними агрегаций. Я не знаю точно, что заставило его задуматься об этом: то ли мнение об Analysis Services как о еще недостаточно зрелом продукте, то ли желание отчасти повторить путь эволюции OLAP. Тот, кому еще не довелось поработать с Analysis Services, мог бы подумать, что данный продукт пока недостаточно <обкатан>, так как появился на рынке лишь в январе 1999 года в составе SQL Server 7.0. Тогда он назывался OLAP Services. Однако Analysis Services не является OLAP-продуктом первого поколения. Корпорация Microsoft приобрела этот продукт у компании Panorama Software и для его развития создала команду разработчиков, состоящую из опытных и высококвалифицированных специалистов. Прежде чем выпустить первую версию Analysis Services (OLAP Services) на рынок, разработчики провели сравнительный анализ конкурирующих продуктов. И сравнение оказалось в пользу Analysis Services.

Одним из основных качеств, отличающих этот продукт от базового ядра OLAP, является возможность использования вычисляемых ячеек. Вычисляемые ячейки позволяют применять к заданному подмножеству ячеек куба формулы, включающие условия. Формулы с условиями могут изменять значения заданных ячеек и свойств ячеек. Не путайте свойства ячеек со свойствами членов. Ячейки позволяют сформировать решетку, в которую будут помещены результаты обработки запроса. Примерами свойств ячеек могут служить формат строки, цвет фона и цвет шрифта. Члены размерностей представляют сущности предметной области, которые встречаются в результатах обработки запроса в виде заголовков строк и столбцов.

Когда участники команды развития Analysis Services впервые рассказали мне о вычисляемых ячейках, я не сразу оценил их возможности. Мне казалось, что они относятся к тому, что принято называть "всякой всячиной", то есть к числу почти бесполезных или избыточных свойств, сопутствующих выпуску новой версии продукта. Ведь вычисляемые члены могут делать практически все, на что способны вычисляемые ячейки. Однако разработчики были полны энтузиазма, и я решил, что в этом, наверное, что-то есть, и надо будет разобраться позднее. Теперь время настало.

Зачем нужны вычисляемые ячейки?

Я приведу несколько доводов в пользу применения вычисляемых ячеек. Во-первых, их способность изменять свойства ячеек в соответствии с формулой с условиями является уникальной в MDX. Впервые в MDX появилась возможность настраивать представление результатов обработки запроса, например, выделять цветом исключения из общего правила. Для того чтобы иметь возможность выделить цветом исключительные значения, клиентские OLAP-приложения должны уметь обрабатывать свойства ячеек при демонстрации результатов. Хотя в настоящее время у большинства приложений Analysis Services такой возможности нет, тем не менее, в наиболее популярных клиентских приложениях, относящихся к сфере Analysis Services (таких как ProClarity Analytical Platform компании Knosys или OLAP@Work компании Business Objects) эти возможности появятся в ближайшем будущем. Обработка исключений позволяет при помощи атрибутов, отвечающих за шрифт и цвет, выделять значения в определенных ячейках на основании числовых условий. К примеру, можно выделить красным цветом те значения объемов продаж, которые оказались ниже, чем в прошлом году.

Во-вторых, ограничение области применения формулы для вычисляемых ячеек определенным подмножеством многомерного куба (то есть подмножеством, образованным заданными членами размерностей куба и ячейками) оказывается значительно легче задать при помощи вычисляемых ячеек, чем при помощи вычисляемых членов размерностей. Чтобы ввести ограничение для вычисляемых членов, придется вложить несколько условных операторов IIF, в результате чего текст кода будет трудно читать. В качестве примера приведена формула вычисляемого члена, в соответствии с которой всем ячейкам подмножества куба (1998, Drink) присваивается значение 500, а остальным ячейкам куба - значение Mexico:

CREATE MEMBER [Sales].[Measures].[Five
Hundred] as `iif(
Ancestor(Time.CurrentMember,
[Year]).Name = "1998", iif(
Ancestor(Product.CurrentMember, [Product
Family]).Name = "Drink", 500, [Unit
Sales]), [Unit Sales])`

Следующая формула вычисляемых ячеек приводит к аналогичному результату, но выглядит намного проще и срабатывает каждый раз, когда происходит выборка существующего показателя количества проданных товаров Unit Sales:

CREATE CELL CALCULATION [Sales].[Five Hundred]
FOR `(Descendants([1998]),
Descendants([Product].[All Products].[Drink]),
{[Unit Sales]})`
AS `500`

В-третьих, вычисляемые ячейки работают независимо от размерностей. Ограничение, присущее вычисляемым членам, заключается в том, что вычисляемый член обязан входить в состав какой-либо существующей размерности. В приведенном выше примере вычисляемого члена я воспользовался размерностью показателей, Measures. Это означает, что для создания работоспособной формулы необходимо использовать данный вычисляемый показатель. Подобное ограничение не позволяет применять формулу к произвольно выбранному подмножеству куба. Вычисляемые ячейки свободны от такого ограничения.

Четвертый довод заключается в том, что вычисляемые ячейки позволяют изменять кубы без введения новых элементов. Это свойство особенно ценно тем, что предоставляет возможность заменить способ вывода количественных показателей без внесения изменений в существующие приложения или отчеты OLAP. Пользователи OLAP могут получать новые значения, не меняя способ навигации в существующем кубе. При работе с вычисляемыми членами пользователь или клиентское приложение должны выбрать вычисляемый член в каком-либо запросе MDX, чтобы формула была применена к полученным результатам.

Наконец, пятый довод в пользу применения вычисляемых ячеек состоит в том, что они могут выполняться в нескольких проходах. Можно создать формулу для вычисляемой ячейки, которая применяется более чем к одному проходу вычисления запроса MDX. Многопроходное исполнение запросов MDX представляет собой, возможно, наиболее ценное качество Analysis Services. Я не буду подробно останавливаться на нем в этой статье. Достаточно сказать, что многопроходные вычисления позволяют производить, например, многоэтапный расчет бюджета. Если в некотором кубе хранятся реальные расходы и расходы, предусмотренные бюджетом, то можно создать формулу для многопроходного расчета показателей бюджета наподобие следующей:

1. Если значение показателя бюджета равно нулю, то использовать увеличенное на 10% значение соответствующего показателя прошлого года.

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

3. Если значение показателя бюджета все еще равно нулю, то взять прошлогоднее значение для родительской категории, увеличить его на 10% и разделить результат на число составляющих его пунктов.

Многопроходные вычисления представляют собой мощное средство расчетов, но они отличаются от составных (вложенных) операторов IF. В приведенном выше примере шаг 1 применяется ко всем предусмотренным в бюджете значениям до того, как будет выполняться шаг 2. Поэтому при расчете среднего значения бюджетных показателей, которое используется на шаге 2, берутся значения, полученные на шаге 1. В случае составного оператора IF все три шага применяются к одному и тому же бюджетному показателю, и лишь затем происходит переход к следующему показателю.

Создание вычисляемых ячеек

Как и в случае вычисляемых членов и именованных наборов, существует три способа создания вычисляемых ячеек. Можно воспользоваться Analysis Manager или же программным интерфейсом для приложений Decision Support Objects (DSO), чтобы создать вычисляемые ячейки на сервере. Помимо этого можно прибегнуть к оператору CREATE CELL CALCULATION или же включить в запрос MDX определение вычисляемых ячеек при помощи оборота WITH.

Первый и самый простой способ определить вычисляемые ячейки - воспользоваться услугами мастера Calculated Cells Wizard, входящего в состав Analysis Manager. Найти этот мастер можно в редакторе куба, Cube Editor, выбрав меню пункта Insert и щелкнув на пункте Calculated Cells. Альтернативный способ - щелкнуть на значке Insert Calculated Cells на панели инструментов редактора куба. Этот значок изображает маленький куб внутри куба большего размера.

Начиная свою работу, мастер создания вычисляемых ячеек, Calculated Cells Wizard, просит задать подмножество куба, как показано на рисунке 1.

После того как будет выбран тип набора членов, появится дополнительная возможность выбора, так что формулу можно будет расширить. В рассматриваемом в данной статье примере мне хотелось, чтобы при каждом обращении к показателю количества проданных товаров, Unit Sales, формула применялась к подмножеству куба, относящемуся к 1998 году и типу товаров - <Напитки>, Drink Products. Чтобы добиться этого, я сначала из списка слева выбрал размерность времени, Time, а затем определил тип набора членов - потомки заданного члена, Descendants of a member. После того как я выбрал этот тип набора членов, мастер предоставил мне возможность выбрать конкретный год (1998) в дереве членов размерности времени. Аналогичным образом я определил подмножество размерности товаров, Product, как потомков семейства напитков, Drink. Из размерности показателей, Measures, я выбрал показатель количества проданных товаров, Unit Sales, а в качестве типа набора членов - единичный показатель, A single measure. Завершив конфигурирование этих трех размерностей и оставив для остальных размерностей принятые по умолчанию установки, я нажал кнопку Next.

Как показано на рисунке 2, на следующем шаге работы мастера создания вычисляемых ячеек определяется условие, при котором Analysis Services будет применять формулу к заданному подмножеству куба. В нашем примере я предпочел не определять никакого условия, но вообще здесь можно задать любое выражение MDX, которое в результате выполнения дает значение <истина> или <ложь>. К примеру, если бы я захотел, чтобы формула применялась только к последнему члену в группе потомков каждого члена размерности времени, я мог бы написать следующее условие:

Time.CurrentMember.Name =
Time.CurrentMember.LastSibling.Name

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

Завершающий шаг работы мастера позволяет дать название новой формуле для вычисляемых ячеек, я назвал ее <Пять сотен>, Five Hundred. Нажав кнопку Finish, сохраните куб (выберите из меню File пункт Save), а затем щелкните по закладке страницы данных в нижней части редактора куба. Эта закладка предоставляет возможность просмотреть куб и убедиться, что результат достигнут. Для нашего примера следует вытянуть размерность времени в столбцы таблицы для просмотра куба, а размерность товаров - в строки. После этого раскройте иерархии обеих размерностей, и убедитесь, что у всех членов размерности товаров, относящихся к категории напитков, начиная с 1998 года значения равны 500.

Второй способ сформировать вычисляемые ячейки предполагает использование оператора CREATE CELL CALCULATION. Пример синтаксиса для этого метода приведен выше - там, где я сравнивал формулы для вычисляемых ячеек с формулами для вычисляемых членов.

Третий способ создания формулы для вычисляемых ячеек заключается в использовании оборота WITH в запросе MDX. Ниже приведен пример синтаксиса для такого запроса:

WITH CELL CALCULATION [Five Hundred]
FOR `(Descendants([1998]),
Descendants([Product].[All Products].[Drink]),
{[Unit Sales]})`
AS `500`
SELECT Time.Year.Members ON columns,
   [Product].[Product Department].Members ON rows
FROM Sales

Вычисляемые ячейки представляют собой достаточно мощное средство. Более того, я убежден, что фактически они превосходят по своим возможностям вычисляемые члены. У вычисляемых членов имеется своя четко обозначенная ниша, определяемая, к примеру, возможностью задавать способы группировки других членов размерности по установленным пользователями правилам, или же создавать для отделения от ядра куба формулы по принципу "минус один". Вычисляемые ячейки в состоянии справиться со многими задачами, для выполнения которых применяют вычисляемые члены, причем более успешно. Более того, не следует вносить изменения в клиентские приложения OLAP, чтобы использовать в них возможности вычисляемых ячеек. Провести соответствующее конфигурирование можно средствами Analysis Manager на сервере. Тем клиентским приложениям, которые используют возможности вычисляемых ячеек, не требуется никакой специальной поддержки. Я настоятельно рекомендую читателям задуматься о применении вычисляемых ячеек в создаваемых OLAP-приложениях, кубах или методах анализа.

Об авторе

Рас Уитни rwhitney@knosysinc.com возглавляет исследовательское отделение в компании Knosys, руководит разработкой клиентского инструментария OLAP. Входит в совет директоров компаний Knosys и Distributed Database Consulting (DDBC).