Альтернативные интерфейсы доступа к многомерным данным

В программе Microsoft Excel версии 2007 был добавлен альтернативный интерфейс доступа к многомерным данным — семейство функций КУБ(). Изначально при помощи методов КУБ() можно было обратиться только к аналитическому серверу от компании Microsoft, что существенно сужало круг их потенциальных пользователей.

С выходом Microsoft Excel 2010 ситуация изменилась в лучшую сторону. Последняя версия Microsoft Excel комплектуется сервисом PowerPivot, в котором реализована поддержка двух основных методов КУБ(): КУБЭЛЕМЕНТ() и КУБЗНАЧЕНИЕ(). Благодаря сервису PowerPivot функции КУБ() наконец стали общедоступными. По своим возможностям они прекрасно дополняют базовую функциональность сводных таблиц.

Покажем, как с помощью методов КУБ() можно составить интересующий нас отчет. Начнем с создания обычного сводного отчета на базе OLAP-куба (рис. 3).

Рис. 3. Сводный отчет на базе OLAP-куба

Для удобства дальнейшего восприятия при помощи цветового форматирования разделим столбцы отчета, относящиеся к фактическим и плановым значениям. Далее на вкладке Параметры в группе Сервис выберем подгруппу Средства OLAP и нажмем на кнопку Преобразовать в формулы. Поскольку в макете нашего отчета присутствует область фильтров, откроется диалоговое окно Преобразование в формулы (рис. 4).

Рис. 4. Диалоговое окно «Преобразование в формулы»

Нам необходимо в новом отчете сохранить фильтры, поэтому флажок Преобразовать фильтры отчета следует оставить снятым (он снят по умолчанию).

В результате проделанной операции все заголовки строк, столбцов и ячейки области значений будут преобразованы в формулы листа, а первоначальный макет отчета сводной таблицы будет сохранен, но в нем останутся только фильтры — с их помощью в дальнейшем можно будет производить отбор нужных данных (рис. 5).

Рис. 5. Результат преобразования в формулы

После выполнения операции Преобразовать в формулы сводная таблица становится обычным диапазоном ячеек, с которым можно выполнять любые стандартные операции Microsoft Excel. Поэтому из отчета можно удалить все столбцы, относящиеся к разделу плановых значений. В случае классической сводной таблицы такая операция невозможна — ведь сводная таблица представляет собой связанный диапазон.

Оставшаяся часть таблицы состоит из набора формул двух типов. Элементы из полей строк и столбцов сводной таблицы (измерения OLAP-куба) были преобразованы в формулы КУБЭЛЕМЕНТ(), а поля из области значений (мера OLAP-куба) — в формулы КУБЗНАЧЕНИЕ().

Каждая из формул имеет весьма простую и ясную структуру. Рассмотрим для начала синтаксис формулы КУБЗНАЧЕНИЕ():

КУБЗНАЧЕНИЕ (подключение, выражение_элемента1, выражение_элемента2…)

Первый аргумент функции — текстовая строка подключение, в которой задается имя подключения к аналитическому кубу. При создании отчета на основе сводной таблицы данное имя наследуется из ее параметров подключения.

Следующими аргументами функции являются текстовые строки Выражение_элемента, которые определяют координаты отдельных элементов в кубе. Все вместе они составляют кортеж, задающий координаты объекта в многомерном пространстве.

На рис. 5 представлена формула КУБЗНАЧЕНИЕ(), рассчитывающая объем выручки по услуге «Интернет — Доступ» за февраль для дирекции № 1. Видно, что выражениями элементов являются ссылки на обычные ячейки листа Microsoft Excel. Примечательно, что текстовые строки в этих ячейках можно заполнять по-разному. Базовый вариант заключается в применении функций КУБЭЛЕМЕНТ(), но при необходимости можно использовать и любой другой способ.

Для примера заменим формулы КУБЭЛЕМЕНТ() элементов измерения «План_Факт» строчными значениями — для этого в первых трех колонках наберем «[Факт]», а последних трех — «[План]». Как видно из рис. 5, подобная операция сразу приводит отчет к асимметричному виду — формату, который был целью данной статьи.

Повторимся еще раз, аргументами функции КУБЗНАЧЕНИЕ() являются ячейки листа, что означает возможность выполнения над ними любых стандартных операций Microsoft Excel. Например, работу с измерением «План_Факт» можно значительно упростить при помощи опции проверки данных. Ограничим множество допустимых значений ячейки перечнем элементов (вкладка Данные → меню Работа с данными → Проверка данных). При помощи такой несложной операции в отчете будет достигнут эффект поля с раскрывающимся списком, а сам отчет при этом приблизится по функциональности к привычным сводным таблицам (рис. 6).

Рис. 6. Ограничение множества допустимых значений ячейки перечнем элементов

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

Рис. 6 демонстрирует, как данная идея может быть воплощена на практике. Сначала фильтры исходного отчета были размещены рядом с областью данных отчета, затем весь полученный диапазон был скопирован и повторно помещен на лист книги Microsoft Excel. Формулы нижнего раздела были сориентированы на использование соответствующих фильтров.

Итогом такой работы стал отчет, содержащий сразу две проекции одного многомерного пространства. Причем у этих проекций есть как общие измерения — «План_Факт», «Дата», так и независимые — «Сервис». Кроме того, каждая проекция располагает собственным набором фильтров.

Предложенный подход можно развивать дальше — ничто не мешает объединить в одном отчете данные из разных кубов. Понятно, что совместно используемые измерения в обоих кубах должны либо совпадать, либо хотя бы обладать иерархиями одинакового уровня гранулярности.

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

Как мы убедились, для простых задач типа просмотра нескольких показателей из гетерогенной среды в общем пространстве измерений единое хранилище данных не требуется. Гораздо проще и быстрее привести все показатели к единому знаменателю при помощи функций КУБ() на этапе формирования выходного пользовательского отчета. Учитывая, что такая работа не требует каких­либо специальных знаний в программировании, она может быть выполнена бизнес-пользователем самостоятельно, без привлечения ИТ-специалистов. Современная парадигма бизнес-анализа во многом базируется именно на этой идее.

Чтобы завершить рассказ о главных представителях семейства КУБ(), нам осталось разобраться с работой функции КУБЭЛЕМЕНТ(), имеющей следующий синтаксис:

КУБЭЛЕМЕНТ(подключение, выражение_элемента, подпись)

Первый аргумент данного оператора, так же как и в случае с КУБЗНАЧЕНИЕ(), устанавливает параметры подключения к аналитическому кубу. Затем следует текстовая строка Выражение_элемента, описывающая многомерное выражение, которое задает координаты элемента в кубе. Завершает запись аргумент Подпись, в котором, при необходимости, можно определить строку для показа ее в отчете вместо имени текущего элемента измерения.

Такая возможность может быть весьма полезна на практике. В первую очередь она позволяет гибко настраивать отчет под нужды конкретного пользователя. Обратимся еще раз к нашему примеру, представленному на рис. 6. Допустим, нам не нравится, как названы услуги в аналитическом кубе, и мы хотим для сервиса «Абонентская плата» использовать более сжатое написание — «Абон. плата». Для этого достаточно дополнить исходную формулу текстовой строкой КУБЭЛЕМЕНТ(«OLAP_Connection»;»[Сервис].[Сервис].&[1]»; «Абон. плата»). Как видно из рисунка, проделанная операция никак не повлияла на расчет численных значений отчета. Дело в том, что значением функции КУБЭЛЕМЕНТ в случае ее вызова другими функциями КУБ() является элемент, определяемый многомерным выражением (второй аргумент КУБЭЛЕМЕНТ), а не то значение, которое показывается в ячейке листа.

Истинная мощь формулы проявляется, когда выражение элемента представляет собой не единственную координату, а кортеж — набор из нескольких координат на различных осях куба. Предположим, что в аналитическом отчете вместо элемента «Абон. плата», задаваемого формулой «[Сервис].[Сервис].&[1]», требуется разместить элемент «Абон. плата — план», определяющий плановое значение объема продаж для услуги «Абонентская плата» по текущему ракурсу. Понятно, что требуемый элемент задается кортежем ([Сервис].[Сервис].&[1], [План_Факт].[План_Факт].&[1]).

В Microsoft Excel для записи такого кортежа придется использовать довольно экзотический синтаксис:

{“[Сервис].[Сервис].&[1]”;”[План_Факт].[План_Факт].&[1]”}.

При этом функция КУБЭЛЕМЕНТ() в целом примет следующий вид:

=КУБЭЛЕМЕНТ(“OLAP_Connection”;{“[Сервис].[Сервис].&[1]”;”[План_Факт].[План_Факт].&[1]”}; “Абон. плата - план”).

Условно говоря, последний вызов эквивалентен созданию внутри программы Microsoft Excel вычисляемого элемента «Абон. плата — план», то есть конструкции вида:

with member [Сервис].[Сервис].[Абон. плата - план] as ([Сервис].[Сервис].&[1],[План_Факт].[План_Факт].&[1])

Как видите, функция КУБЭЛЕМЕНТ() позволяет определять элементы куба при помощи сложных условий отбора, но на практике выполнять такие действия не очень удобно. Главный сдерживающий фактор здесь заключается в трудоемкости составления многомерных выражений, удовлетворяющих требованиям Microsoft Excel, Действительно, приведенная выше формула наглядно показывает, что даже для простого кортежа из двух элементов требуется довольно громоздкая запись.

К счастью, в функции КУБЭЛЕМЕНТ() предусмотрен альтернативный подход к заданию аргумента Выражение_элемента — вместо текстовой строки допускается указывать ссылку на диапазон ячеек.

Создадим на листе книги именованный диапазон «Кортеж» и построчно заполним его формулами, задающими координаты элемента на отдельных измерениях (рис. 7).

Рис. 7. Заполнение именованного
диапазона формулами, задающими координаты
элемента на отдельных измерениях

В совокупности данные выражения определяют кортеж, вычисляющий плановую выручку в январе по услуге «Абонентская плата». Подстановка имени диапазона в формулу КУБЭЛЕМЕНТ(«OLAP_Connection»; Кортеж; «Абон. плата (план)») дает возможность сослаться на нее в отчете Microsoft Excel. При этом, когда в формуле явно указан параметр Подпись, он отображается в ячейке на листе книги Microsoft Excel. Если же такого параметра нет, в ячейке показывается последний элемент кортежа. Таким образом, если формулу КУБЭЛЕМЕНТ() написать в сокращенном виде: КУБЭЛЕМЕНТ(«OLAP_Connection»; Кортеж), то в ячейке отчета будет показываться «Янв», что соответствует последнему элементу из списка «Кортеж» — [Дата].[Месяц].&[1].

Следует отметить, что каждая строка в диапазоне «Кортеж» может быть произвольным MDX-выражением. Например, для формул на рис. 7 существует эквивалентное написание:

[Сервис].[Сервис].[All].FirstChild

[План_Факт].[План_Факт].&[1]

[Дата].[Дата].[Месяц].members.Item(0)

Все формулы, в свою очередь, являются текстовыми строками, которые, при необходимости, можно составлять динамически при помощи обычных текстовых функций Microsoft Excel.

Приведенные примеры доказывают, что функции КУБЭЛЕМЕНТ() и КУБЗНАЧЕНИЕ() обладают широким потенциалом для работы с многомерными данными, однако на них налагается ряд существенных ограничений. Во­первых, при разработке отчетов с использованием функций семейства КУБ() следует руководствоваться логикой составления MDX-выражений. Один из базовых постулатов MDX говорит, что любая иерархия может присутствовать на осях отчета (Axis0, Axis1, Where) только один раз. Поэтому если элемент куба в функции КУБЭЛЕМЕНТ() определялся с помощью кортежа, то присутствующие в нем измерения уже не могут напрямую применяться в инструкции КУБЗНАЧЕНИЕ().

Во­вторых, базовые функции КУБ() являются статическими по своей природе. Оператор КУБЭЛЕМЕНТ() оптимально подходит для создания отчетов с фиксированной структурой, но малопригоден для работы с динамическими наборами.

С первым из перечисленных недостатков следует просто смириться, а вот проблема создания отчетов с изменяющимися размерами успешно решается при помощи других функций КУБ(). Именно им и будет посвящена следующая часть статьи.

Поиск по сайту: