Блеск и нищета сводных таблиц

Часть 2

Павел Сухарев

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

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

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

К счастью, современное развитие информационных технологий позволяет формулировать данный вопрос в менее ультимативной форме. Дело в том, что в последнее время лозунг «BI — в массы!» получил ярко выраженное материальное подкрепление. Большинство вендоров предлагают программные решения, ориентированные на активное и самостоятельное использование конечными пользователями. Сегодня проектирование простых кубов вышло на уровень утилитарных задач и ненамного превосходит по трудоемкости создание привычных для всех сводных таблиц.

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

Пользователи получили в свое распоряжение мощные аналитические системы с наглядными интерфейсами, значительно превосходящие по возможностям стандартные сводные таблицы. Разработка несложных кубов в таких средах занимает мало времени и, что самое приятное, не требует серьезной теоретической подготовки. Иными словами, она доступна всем и каждому. И здесь критичной неожиданно становится другая проблема: как воспользоваться всей этой расширенной функциональностью? Общее направление движения нам уже известно — MDX-выражения. Осталось лишь понять, каким образом можно обойтись без программирования клиентских компонентов, используя только стандартные средства Microsoft Excel.

Попытаемся для начала выяснить, когда мы будем ограничены системными возможностями сводных таблиц Microsoft Excel.

Предположим, что перед нами стоит задача анализа результатов продаж компании за определенный период, представленных «плоской» таблицей (см. таблицу) с тремя атрибутами:

• дата;
• дирекция;
• услуга.

Допустим, что нашей целью является анализ продаж дирекции № 1. Как это часто бывает, интересует не положение дел в целом, а только ситуация с продажами в те месяцы, когда дирекция № 2 показала высокие результаты, например больше 400 единиц. Запросы типа «посмотреть, как ведет себя один показатель при задании каких­либо условий на другой» на практике встречаются повсеместно.

Типовой подход к решению задачи — составить сводный отчет, как показано на рис. 1, затем выбрать месяцы, в которых итоговые суммы продаж дирекции № 2 больше 400, — апрель и июнь. Остальные месяцы при этом скрыть, установив соответствующие фильтры для поля «Месяц» отчета.

Рис. 1. Анализ при помощи сводной таблицы

На первый взгляд всё довольно просто, быс­тро и удобно. К сожалению, при использовании такого метода придется столкнуться с рядом сложностей и неприятных ограничений.

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

Во­вторых, отбор нужных элементов измерения «Период» пришлось выполнять вручную. На малых интервалах (квартал, полгода) такая операция выглядит оправданной. Но как быть, если мы хотим проанализировать работу дирекций за 5 лет? Ведь в этом случае придется просматривать уже не 6, а 60 столбцов. Если же измерение содержит несколько сотен или, что еще хуже, много тысяч элементов, задача отбора нужных элементов становится практически невыполнимой.

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

Альтернативный вариант напрашивается сам собой — произвести необходимую настройку программным способом. Представьте, насколько удобнее и быстрее просто отдать команду «оставить в строке отчета “Месяцы” только те, в которых продажи Дирекции № 2 оказались больше 400 единиц». Понятно, что при таком подходе число месяцев (6, 60 или 600) уже совершенно несущественно, их количество будет влиять лишь на размер финальной таблицы.

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

Таблица с данными из нашего примера в OLAP-хранилище представляется трехмерным кубом (рис. 2), в котором атрибуты таблицы (столбцы) являются одноименными осями измерений (Dimension). Обычно хранилище проектируется таким образом, чтобы каждое измерение содержало все уникальные значения соответствующего атрибута, а также дополнительный элемент (ALL). Элемент (ALL) — агрегатное значение измерения, которое обычно рассчитывается как сумма всех значений элементов измерения.

Рис. 2. Хранение данных в OLAP

Любое измерение всегда представляет собой иерархию, на верхнем уровне которой располагается элемент (ALL), а на нижнем — значения атрибута. В OLAP возможно создание много­уровневых иерархий на базе нескольких атрибутов. Типичный пример — иерархия «Дата» All > Квартал > Месяц (рис. 3).

Рис. 3. Многоуровневая иерархия

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

Измерения и меры являются базовыми сущностями многомерных вычислений. MDX-запросы на основе этих объектов формируют Отчеты (Reports). Отчеты содержат «Оси» (Axes), на которых располагаются «Наборы» (Sets), являющиеся подмножествами куба. Наборы могут быть двух типов:

• набором элементов — экземпляры относятся к одному измерению:

{

([Дирекция].[Дир_1]),

([Дирекция].[Дир_2])

}

• набором кортежей — упорядоченных последовательностей элементов из нескольких измерений:

{

([Дирекция].[Дир_1], [Дата].[Янв]),

([Дирекция].[Дир_1], [Дата].[Фев]),

([Дирекция].[Дир_2], [Дата].[Янв]).

}

Как уже было сказано, все измерения являются иерархиями. Данное обстоятельство позволяет создавать наборы не только путем прямого перечисления входящих в него элементов, но и аналитическим способом. Например, набор {([Дирекция].[Дир_1]), ([Дирекция].[Дир_2])}, представляющий собой все дирекции компании, может быть задан гораздо проще — посредством использования функции Children. Функция Children показывает всех потомков выбранного элемента измерения. В случае ее применения для элемента (ALL) в одноуровневой иерархии будут показаны все элементы измерения: {[Дирекция].[All].Children}.

Функции MDX, формирующие наборы, можно вызывать последовательно, что очень помогает для задания различных ограничений на множество элементов. Допустим, нам требуется показать только те месяцы из измерения «Дата», в которых содержатся данные о фактических продажах. Если измерение «Дата» имеет структуру, как показано на рис. 3, то сначала придется отобрать всех потомков элемента (ALL), расположенных на уровне «Месяц». Делается это при помощи функции DESCENDANTS, которая выводит всех потомков элемента измерения для выбранного уровня иерархии — DESCENDANTS( [Дата].[All], [Дата].[Месяц]). Набор элементов, полученный в результате вызова функции DESCENDANTS, может выступать в роли входных аргументов для другой функции MDX, в частности NONEMPTY. Поэтому последовательный вызов функций NONEMPTY и DESCENDANTS в конструкции NONEMPTY({DESCENDANTS ([Дата].[All], [Дата].[Месяц])}) сначала отберет все месяцы измерения, а затем оставит лишь те из них, в которых содержатся фактические значения. Понятно, что полученный результат также является набором и может, в свою очередь, выступать входным аргументом для следующей функции MDX.

Подобным образом можно определить подмножество элементов базового измерения, удовлетворяющее сколь угодно сложному условию. В нашем случае необходимо получить месяцы с определенным уровнем продаж. Отбор элементов измерения по значениям меры куба осуществляется при помощи функции Filter(). Напишем следующую инструкцию MDX*:

FILTER({DESCENDANTS ([Дата].[Дата].[All], [Дата].[Дата].[Месяц])},

([Дирекция].[Дирекция].[Дир_2])>400)

В данной конструкции для каждого месяца из исходного набора проверяется уровень продаж по ракурсу «Дирекция_2». В результирующем множестве остаются только те месяцы, которые удовлетворяют условию «Продажи Дирекции № 2 больше 400 единиц».

Создадим теперь отчет, на оси строк Rows которого отложим полученный набор:

SELECT

{[Measures].[Сумма]} ON СOLUMNS,

FILTER ({DESCENDANTS ([Дата].[Дата].[All], [Дата].[Дата].[Месяц])},

([Дирекция].[Дирекция].[Дир_2])>400) ON ROWS

FROM [PF]

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

Рис. 4. Применение
функции FILTER

Чтобы получить детализацию доходов по дирекциям, на оси Rows нужно разместить сразу два измерения — «Дата» и «Дирекции». Такая операция выполняется при помощи функции CrossJoin (рис. 5):

Рис. 5. Применение
функции CrossJoin

SELECT

{[Measures].[Сумма]} ON СOLUMNS,

CROSSJOIN(

FILTER ({DESCENDANTS ([Дата].[Дата].[All], [Дата].[Дата].[Месяц])},

([Дирекция].[Дирекция].[Дир_2])>400)

,

[Дирекция].[Дирекция].[All].Children

)

ON ROWS

FROM [PF]

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

Итак, первая часть задачи благополучно решена — сформирована выборка из OLAP-базы, удовлетворяющая поставленным условиям и подходящая для создания сводной таблицы (рис. 6). Осталось получить такую же таб­лицу в Microsoft Excel. Идея основана на организации в среде Microsoft Excel OLE DB-подключения к аналитической базе данных с возможностью изменения текста команды на запрос данных.

Рис. 6. Выборка из OLAP-базы
для создания сводной таблицы

Для начала изучим параметры источника данных, которые задаются по умолчанию при подключении сводной таблицы к кубу OLAP. Для этого выделим сводную таблицу, затем на ленте быстрого доступа Microsoft Excel на вкладке Данные в разделе Подключения выберем элемент Свойства. Перейдя на закладку Определение, можно увидеть поле Текст команды. Данное поле содержит текст запроса, посылаемого на сервер. В случае подключения сводной таблицы к аналитической базе в поле указывается название одного из хранящихся в ней кубов. Тип возвращаемого объекта задается в поле Тип команды, которое имеет предопределенное значение Куб и недоступно для выбора.

Задача сводится к созданию такого источника данных, в котором поле Тип команды будет разблокировано. Делается это довольно просто. В параметрах сводной таблицы разрешаем выполнение операции DrillDown — на закладке Данные включаем опцию Разрешить отображение деталей. После этого дважды кликаем по любой ячейке с данными из сводного отчета. На отдельном листе книги должна появиться таблица, детализирующая выбранную нами сумму. Такая таблица является результатом MDX-запроса по текущему ракурсу сводного отчета и, что особенно ценно, имеет собственный источник данных.

Выберем таблицу, после чего нажмем уже знакомую нам кнопку Свойства на вкладке Данные. В появившейся форме еще раз нажимаем эту же кнопу — на этот раз справа от поля Имя.

Прорвавшись сквозь дебри интерфейса Microsoft Excel, попадаем на форму определения свойств подключения. Дадим подключению более удобное и понятное имя, например My_MDX (рис. 7). Отметим, что на вкладке Определение поле Тип команды имеет теперь значение По умолчанию.

Рис. 7. Настройка пользовательского подключения

На листе со сводным отчетом выберем одну из ячеек и присвоим ей имя — MDX_Command. Напишем в ней одной строкой составленный ранее MDX-запрос:

SELECT {[Measures].[Сумма]} ON СOLUMNS, CROSSJOIN(FILTER ({DESCENDANTS ([Дата].[Дата].[All], [Дата].[Дата].[Месяц])}, ([Дирекция].[Дирекция].[Дир_2])>400), [Дирекция].[Дирекция].[All].Children) ON ROWS FROM [PF]

Перейдем в редактор Visual Basic, добавим к проекту модуль и вставим в него новую процедуру:

Sub My_MDX_Query()

ActiveWorkbook.Connections(«My_MDX»).OLEDBConnection.CommandText = Range(«MDX_Command»).Value

ActiveWorkbook.Connections(«My_MDX»).Refresh

End Sub

Разместим на листе со сводным отчетом кнопку. Для этой кнопки в качестве макроса, выполняемого при нажатии, назначим нашу процедуру My_MDX_Query().

Нажмем на кнопку и убедимся, что таблица, в которой ранее хранились детальные данные, выводит теперь результаты нашего MDX-запроса (рис. 8).

Рис. 8. Результаты MDX-запроса

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

***

Предложенная в статье методика позволяет значительно повысить мощность стандартных сводных отчетов Microsoft Excel за счет составления собственных MDX-запросов к аналитическим кубам. Альтернативный подход заключается в использовании семейства функций КУБ(), обсуждению которых будут посвящены следующие статьи цикла.

КомпьютерПресс 03'2011