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

Часть 1

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

Сводные отчеты: новые задачи

Несколько слов о шкалах измерений

Отчеты 3D

Заключение

В настоящее время различные виды задач количественного анализа данных присутствуют в деятельности любой организации. Один из эффективных способов их решения заключается в переводе структурированных данных из реляционных источников в многомерные базы данных и проведении всех последующих вычислений на уровне OLAP-хранилищ. Сегодня такие операции можно выполнить во многих прикладных программах различных производителей, среди которых центральное место, безусловно, занимают сводные таблицы (Pivot Table) Microsoft Excel. Своего исключительного положения сводные таблицы достигли благодаря совместному действию двух факторов — распространенности и универсальности. Первый из них не требует дополнительных пояснений. Достаточно вспомнить, что сводные таблицы являются стандартным компонентом программы Microsoft Excel из офисного пакета Microsoft Office. А вот универсальность сводных таблиц как рабочего инструмента бизнес-пользователя, напротив, заслуживает обсуждения. Во­первых, поставщиками данных для сводной таблицы могут выступать как книги Microsoft Excel, так и широкий спектр внешних источников, включая OLAP-кубы, а также различные OLEDB- и ODBC-источники.

Во­вторых, вся работа с аналитическими данными построена на активном использовании интерактивной среды формирования отчетов. Сводная таблица является типичным графическим пользовательским интерфейсом (GUI), в котором составление любого отчета превращается в последовательность операций drag-and-drop. MDX-запросы к многомерным данным при этом формируются автоматически. Такой подход является воплощением концепции zero coding, позволяющей пользователям настраивать функциональность прикладного ПО самостоятельно, без какого-либо участия со стороны ИТ-специалистов. Но за удобство и наглядность работы в визуальных средах приходится повсеместно расплачиваться ограничением потенциала исходного языка. И сводные таблицы здесь не стали исключением из общего правила — MDX-запросы скрыты от пользователей, их невозможно изменить либо как­то повлиять на их выполнение.

Невольно напрашивается вопрос: а велика ли она, эта плата за универсальность? Много ли мы теряем, перекладывая все технические проблемы на услужливые плечи Pivot Table? Есть ли в MDX возможности, полезные и нужные с позиций бизнес-практики, но недоступные напрямую из сводных таблиц? Стоит ли вообще пытаться их использовать? Каким образом можно получить к ним доступ из среды Microsoft Excel?

Сводные отчеты: новые задачи

Начнем с того, что MDX может применяться как средство «тюнинга» обычных сводных таблиц. Он позволяет адаптировать формат стандартного отчета под решение конкретных задач и добиться при этом весьма интересных результатов.

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

Рис. 1. Пример сводного отчета

В том случае, когда требуется представить результаты деятельности по одной из дирекций либо для всей компании в целом, сводная таблица прекрасно справляется со своей задачей — достаточно выбрать нужное значение в фильтре «Дирекция». Но реальные информационные потребности пользователей часто лежат в иной плоскости. Их, например, могут интересовать не абсолютные, а относительные значения показателей. Знание, что доход в сегменте SОНО для первой дирекции в мае составил 188 единиц, становится более содержательным и осмысленным, если к нему может быть добавлено: «и это лучше результатов другой дирекции».

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

Несколько слов о шкалах измерений

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

В рассматриваемом примере есть измерение «План_Факт», разделяющее значения отчета на прогнозные величины — «План» и полученные в ходе осуществления хозяйственной деятельности — «Факт». Группы «План» и «Факт» можно назвать как угодно: «1» и «2» или «Красные» и «Зеленые». Понятно, что наименования «План» и «Факт» являются наиболее подходящими, так как содержат в себе принципы классификации. Пользователю просто не нужно вспоминать, какому критерию удовлетворяют элементы, отнесенные к группе «Факт». Однако отчет будет успешно работать, если назвать элементы измерения «Красные» и «Зеленые».

Действительно, отчеты 1 и 2 эквиваленты.

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

Новый формат позволяет обойтись без названий элементов измерения в строках отчета. Таким образом, измерение было как бы перенесено внутрь самой таблицы. Можно сказать и по-другому — к отчету было добавлено новое измерение.

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

Отчеты 3D

Цветовое кодирование измерений является одним из перспективных направлений визуализации данных. Но его полноценное использование на практике ограничено двумя факторами.

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

Вторая причина имеет технологическую природу. Понятно, что описанного выше результата невозможно добиться встроенными средствами условного форматирования Microsoft Excel. Аргументами функций форматирования могут быть только значения ячеек из листа книги. Иными словами, ячейки удастся раскрасить в зависимости от типа дохода единственным способом — расположив само измерение «План_Факт» на оси отчета, то есть на листе. А именно этой ситуации и хотелось бы избежать.

Наверное, здесь будет уместно привести сравнение с 3D-изо­бражениями. Без специальных стереоочков не получится преобразовать в объемное изображение видеосигнал, посылаемый стандартным оборудованием, в частности телевизором. В мире сводных таблиц роль стереоочков выполняет MDX. Нужно только уметь его правильно использовать.

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

Рис. 2. Пример сводного отчета с цветовым кодированием измерений

Всё дальнейшее изложение материала будет вестись на примере применения аналитического сервера Microsoft Analysis версии 9.0 от компании Мicrosoft, входящего в состав стандартной комплектации SQL Server 2005. Однако предложенные идеи при желании можно адаптировать и для любого другого многомерного хранилища данных.

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

Сводные таблицы Microsoft Excel умеют использовать правила внешнего форматирования. Достаточно выбрать соответствующие опции в разделе «Форматирование сервера OLAP» при настройке источника данных (рис. 3).

Рис. 3. Настройка форматирования сводных таблиц

Правила форматирования, хранящиеся на OLAP-сервере, — это многомерные выражения, составленные на языке MDX (MultiDimensional eXpressions). Их очевидным достоинством является возможность использовать в качестве аргумента любое измерение куба.

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

iif(

([Дирекция].[Дир_1],[Measures].[Сумма])>

([Дирекция].[Дир_2],[Measures].[Сумма]),

255,65280).

В инструкции сравниваются между собой значения меры «Сумма» обеих дирекций, а выходным значением является код в цветовой модели RGB: 255 — «Красный» либо 65280 — «Зеленый». Поскольку значения элементов для остальных измерений не заданы явно при сравнении, они считаются равными CurrentMember, то есть определяются контекстом выражения, или, проще говоря, видом макета отчета.

В Microsoft Analysis Services 9.0 выражение примет несколько более сложный вид:

iif(

([Дирекция].[Дирекция].[Дир_1],[Measures].[Сумма])>

([Дирекция].[Дирекция].[Дир_2],[Measures].[Сумма]),

255,65280)

Такой синтаксис объясняется тем, что в Microsoft Analysis Services 9.0 измерение всегда определяется парой связанных значений: названием группы измерений и наименованием иерархии в этой группе. Поэтому даже простое одноуровневое измерение требует создания для себя одноименной группы измерений и заведения иерархии в ней.

Полученное выражение MDX следует назначить в качестве правила форматирования для меры «Сумма». В случае с Microsoft Analysis Services 9.0 такую операцию в явном виде выполнить нельзя — на сервере не реализована возможность по форматированию мер аналитического куба. Но данное ограничение с легкостью обходится посредством создания вычисляемых полей (Calculated Members). Добавим на ось Меаsures, которая содержит все меры куба, поле «Сумма+», дублирующее меру «Сумма» (рис. 4). Поскольку поле «Сумма+» добавлено на ось Меаsures, оно станет новой мерой куба.

Рис. 4. Создание вычисляемого поля «Сумма+» на сервере Microsoft Analysis Services 9.0

В разделе Color Expressions, в поле Fore color напишем нашу формулу. В завершение настроим макет сводного отчета Microsoft Excel — заменим в области значений меру «Сумма» на «Сумма+» (рис. 5).

Рис. 5. Настройка макета сводного отчета Microsoft Excel

Заключение

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

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

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