Блеск и нищета сводных таблиц
Часть 7
В прошлых статьях цикла были рассмотрены две базовые функции семейства КУБ() — КУБЗНАЧЕНИЕ() и КУБЭЛЕМЕНТ(). Потенциала, заложенного в этих функциях, достаточно для построения в среде Microsoft Excel сводных отчетов практически произвольного формата. Более того, логика исполнения функций предполагает использование в качестве аргументов значений из обычных ячеек листа книги, что открывает перед пользователем широкие возможности по созданию отчетов, объединяющих данные из разнородных источников.
В то же время указанные функции обладают рядом «врожденных» недостатков, самым существенным из которых можно считать статичность создаваемых на их базе отчетов. Такие отчеты в корне отличаются от привычных сводных таблиц. Линейные размеры сводной таблицы динамически меняются в зависимости от комбинации измерений, расположенных на осях отчета (в полях названий строк и столбцов). При использовании функций КУБ() хочется получить схожую функциональность, достичь ее можно с помощью оператора КУБПОРЭЛЕМЕНТ().
Концепция использования функции КУБПОРЭЛЕМЕНТ() довольна проста. В двух словах ее можно сформулировать следующим образом: «оператор позволяет извлечь определенный элемент из набора». Эта идея весьма привлекательно смотрится именно в прикладном смысле. Представьте, насколько расширяются возможности составления отчетов, когда вместо трудоемкого расчета координат ячейки в многомерном пространстве достаточно указать номер элемента в некотором наборе. Для диапазонов элементов подобная возможность приобретает особую ценность. Проще всего продемонстрировать ее преимущества перед «классическим» подходом на конкретном примере.
Допустим, перед нами стоит задача составления детализированного по месяцам отчета о продажах, совершенных за первое полугодие. Для такого отчета требуется, чтобы на одной из осей отчета (например Y) был отложен следующий набор (мы хотим, чтобы кроме месячных сумм в отчете показывались итоги за квартал):
{
[1КВ],
[Янв],
[Фев],
[Мар],
[2КВ],
[Апр],
[Май],
[Июн]
}
Набор 1
На листе книги Microsoft Excel подобный набор можно составить путем многократного последовательного вызова функции КУБЭЛЕМЕНТ(): в ячейку A1 ввести формулу КУБЭЛЕМЕНТ(“OLAP_Connection”;”[1КВ]”), в ячейку A2 — формулу КУБЭЛЕМЕНТ(«OLAP_Connection»;»[Янв]») и т.д.
Предложенный способ стабильно работает для небольших массивов, не подверженных частым изменениям. Но в общем случае его сложно признать изящным. Потребности в отображаемых данных с течением времени могут серьезно меняться. К примеру, пользователь может нуждаться в отчете, построенном на принципах скользящего итога. Напомним, что в подобном отчете показываются данные только за текущий и предыдущий отчетные периоды.
Адаптировать отчетную форму II квартала, собранную при помощи операторов КУБЭЛЕМЕНТ(), в пригодный для применения в III квартале вид будет непросто. Для этого придется заново переписать формулы всего диапазона. В ячейке A1 существующую формулу заменить на КУБЭЛЕМЕНТ(“OLAP_Connection”;”[2КВ]”), в ячейке A2 — на выражение КУБЭЛЕМЕНТ(«OLAP_Connection»;»[Апр]») и т.д. Понятно, что при переходе из III в IV квартал трудоемкую операцию по переделыванию макета отчетной формы придется повторить еще раз.
Каким образом можно выйти из сложившейся тупиковой ситуации? Для начала вспомним о богатых возможностях языка MDX. Набор 1 составляется двумя способами. Первый из них — прямое перечисление входящих в него элементов; на этом методе базируется работа функции КУБЭЛЕМЕНТ(). Альтернативный подход можно условно назвать «аналитическим». Набор 1 описывается посредством простого MDX-выражения (Выражения 1):
DrilldownLevel({[1КВ]:[2КВ]})
Выражение 1 определяет множество элементов многомерного пространства. В предыдущей статье мы кратко рассказали, как формировать такие множества на стороне клиентского приложения. Для этих целей в состав семейства КУБ() введена мощная функция КУБМНОЖ(). Вызов КУБМНОЖ(«OLAP_Connection»; «DrilldownLevel({[1КВ]:[2КВ]})») создает в книге Microsoft Excel массив элементов, соответствующий Набору 1. Для полноценной работы с ним требуется разместить его отдельные члены в ячейках электронной таблицы. Данная операция выполняется при помощи функции КУБПОРЭЛЕМЕНТ(), имеющей простой синтаксис:
КУБПОРЭЛЕМЕНТ(подключение, выражение_множества, номер, подпись).
Первый аргумент функции стандартен для всех операторов КУБ() и представляет собой текстовую строку «подключение», в которой задается имя подключения к аналитическому кубу. При создании отчета на основе сводной таблицы это имя наследуется из ее параметров подключения.
Следующим аргументом функции является строка «Выражение_множества», определяющая набор, из которого следует выбирать элементы. Набор может задаваться как текстовой строкой, так и при помощи функции КУБМНОЖ(). Множество допускается определять как внутри функции, так и в отдельной ячейке.
Аргумент функции «Номер», согласно справке Excel, «является целочисленным значением, определяющим наивысшее значение, которое будет возвращено…». За этим расплывчатым определением на самом деле скрывается понятие ранга элемента в наборе. Формальное определение ранга будет дано несколько позже. А сейчас, обсуждая одномерные наборы, под «Номером» мы будем понимать порядковый номер элемента в исходном наборе.
Так, «Номер» элемента [1КВ] в Наборе 1 равен «1», а элемента [Апр] — «6».
Последний аргумент «Подпись» является необязательным. В нем указывается строка, которая показывается на листе книги Microsoft Excel вместо возвращаемого значения. Такая опция бывает полезной, если пользователю нужно вывести в отчете чтонибудь отличное от ID или внутреннего имени элемента.
Рис. 1 представляет функцию КУБПОРЭЛЕМЕНТ() в действии. В качестве выражения множества мы указали Выражение 1, а номер элемента набора взяли из ячейки листа книги Microsoft Excel.
Рисунок демонстрирует также некоторые особенности практического применения оператора КУБПОРЭЛЕМЕНТ(). В частности, попытка показать девятый по счету элемент из набора, содержащего всего восемь членов, вполне ожидаемо приводит к ошибке исполнения. Состав набора, который подается в качестве входного аргумента функции, может меняться с течением времени. Для отображения данных за целый год понадобится набор, состоящий из 16 элементов. Поэтому, если мы хотим достичь гибкости и универсальности работы отчета, в макете отчетной формы нам придется зарезервировать места для 16 номеров, а также прописать функцию КУБПОРЭЛЕМЕНТ() в 16 ячейках листа Microsoft Excel.
Когда выражение множества описывает меньший, чем максимально возможный, набор, часть вызовов функции КУБПОРЭЛЕМЕНТ() будет выполняться с ошибками. К ошибкам такого рода следует относиться спокойно — их наличие говорит не о проблемах в бизнес-логике, а лишь об отсутствии данных в выбранном ракурсе многомерного пространства. Безусловно, отображение кодов, сигнализирующих об ошибках, снижает визуальную привлекательность и информативность отчета. Кроме того, руководствуясь принципом разумной достаточности, пользователя следует ограждать от любой избыточной информации. Поэтому если в процессе работы ему понадобился отчет только за первые два квартала года, отчетная форма должна заканчиваться на восьмой строке, то есть на месяце «Июнь». Проще всего этого добиться средствами условного форматирования, встроенными в Microsoft Excel. Более подробно данный вопрос мы разберем в следующих статьях цикла.
Код ошибки «#Н/Д» также может возвращаться, если синтаксис «выражения множества» составлен неверно или множество содержит хотя бы один элемент, измерение которого отличается от остальных. На последнем тезисе хочется акцентировать внимание. Еще раз повторим, что функция КУБПОРЭЛЕМЕНТ() работает с наборами из многомерного пространства. Такие множества могут состоять только из элементов одного измерения. А в случаях, когда набор создается на базе нескольких измерений, порядок их следования в кортежах должен быть одинаковым во всем наборе.
Функция КУБПОРЭЛЕМЕНТ() не будет работать для следующих наборов:
{
[Янв],
[Фев],
[Дир_1]
}
Набор 2
{
([Дир_1], [Янв]),
([Дир_1], [Фев]),
([Мар], [Дир_1])
}
Набор 3
В Наборе 2 предпринята попытка составить множество из элементов разных измерений — «Дата» и «Дирекция», а в Наборе 3 кортеж ([Мар], [Дир_1]) отличается по своей структуре от других элементов. При необходимости элементы из указанных наборов можно показать в одном отчете, но для этого придется воспользоваться функцией КУБЭЛЕМЕНТ(). Вспомним, что каждый экземпляр функции КУБЭЛЕМЕНТ(), помещенный на лист Microsoft Excel, организует отдельное прямое подключение к инфо-кубу OLAP и работает независимо от других. Соответственно у каждого вызова КУБЭЛЕМЕНТ() может быть собственное выражение элемента: у одного ([Дир_1], [Янв]), а другого — ([Мар], [Дир_1]).
Теперь рассмотрим еще один подход к формированию наборов, который можно условно назвать комбинированным. Он объединяет достоинства первых двух способов и в первую очередь ориентирован на обычных бизнес-пользователей. Предлагаемый подход основан на использовании редкой функции из арсенала Microsoft Excel — ДВССЫЛ(). Итак, в чем же его суть?
Вариант формирования наборов, основанный на использовании MDX-выражений, подкупает краткостью записи и легкостью внесения последующих изменений. Но что делать, если пользователю не нужна информация, сгруппированная стандартным способом?
Допустим, его интересует не весь Набор 1, а только некоторое подмножество — например данные за II квартал, а также последний месяц I квартала (Набор 4). Похожие задачи регулярно возникают, когда требуется получить не только результаты отчетного периода, но и некоторые «стартовые условия» для выполнения сравнительного анализа.
{
[Мар],
[2КВ],
[Апр],
[Май],
[Июн]
}
Набор 4
Выражение 2, описывающее Набор 4 в терминах MDX, немногим сложнее Выражения 1, при помощи которого формируется Набор 1:
{[Мар], DrilldownLevel([2КВ])}.
Проблема здесь только одна — такое выражение сложно собрать «на лету». А наша задача состоит в предоставлении пользователю сервиса, позволяющего динамически формировать наборы — например выбирая границы диапазона из списка.
При работе с элементами, принадлежащими к одному уровню иерархии (только месяцам или только кварталам), создать диапазон, зная его начало и конец, довольно просто. Достаточно написать формулу примерно такого вида:
{([Месяц_Начало]:[Месяц_Конец])}.
В свою очередь, текстовую строку, представляющую данную формулу, легко составить средствами Excel — в операторе конкатенации строк Сцепить(). Но даже такое тривиальное выражение подразумевает, что пользователь владеет языком MDX хотя бы на базовом уровне. Идея, заложенная в основу функций КУБ(), гораздо шире. Она сводит работу с многомерными пространствами до уровня манипуляций с отдельными ячейками Microsoft Excel. В прошлой статье мы уже рассказывали, как составить набор произвольной структуры для аналитического отчета, имея в своем распоряжении только текстовые списки и оператор КУБМНОЖ(). Так, создание экзотического набора (Набор 5) является делом нескольких минут.
{
[1КВ],
[2КВ],
[3КВ],
[Янв],
[Фев],
[Мар],
[1КВ],
[Мар],
[Фев],
[Янв]
[2КВ],
[Апр],
[Май],
[Июн]
}
Набор 5
Фактически всё, что при этом требуется от пользователя, — написать названия элементов измерения в соседних ячейках листа Microsoft Excel, а затем передать получившийся диапазон на вход функции КУБМНОЖ(). Пользователь может не знать, что на самом деле элементы [1КВ] и [Янв] принадлежат к разным уровням иерархии — функция КУБМНОЖ() сама сформирует набор, пригодный для дальнейшего применения. Такая легкость выглядит особенно привлекательно на фоне работы со стандартными сводными таблицами. Повторить Набор 5 внутри сводного отчета, мягко говоря, будет непросто. В отличие от функций КУБ(), позволяющих определять состав и структуру наборов на этапе проектирования пользовательской формы, в сводных таблицах элементы измерения могут выводиться только в порядке, заданном в момент дизайна куба.
Невольно возникает вопрос: а часто ли вообще требуется составлять наборы, отличающиеся от упорядоченных иерархий аналитического куба? Наверное, не очень часто. Но в тех редких случаях, когда такая потребность реально возникает, хочется, чтобы пользователь мог работать в программе Microsoft Excel привычным для себя способом, особо не задумываясь, как переложить свои запросы в термины языка MDX. На рис. 2 показано, как это можно сделать.
Рис. 2. Отчет, использующий только встроенные формулы
Microsoft Excel
Рис. 1. Работа функции КУБПОРЭЛЕМЕНТ()
Сначала вставим в форму поля, позволяющие задавать границы временного интервала для отчета. Для этого с помощью опции Microsoft Excel «Проверка данных» установим, что ячейки «Начало диапазона» и «Конец диапазона» должны выбираться из списка, выделенного на рисунке голубым цветом (колонка «Период»). Напротив каждого элемента из списка проставим относительный адрес ячейки, в котором будем хранить подходящий элемент аналитического пространства. Так, месяцу «Январь» соответствует элемент [Янв] измерения «Дата». Элемент [Янв] помещен в ячейку с адресом «I4», этот адрес мы и запишем справа от ячейки с именем месяца «Январь». Отметим, что все вместе элементы из колонки «Набор» образуют диапазон, пригодный для дальнейшего использования в функции КУБМНОЖ(). Для этого функция должна получить ссылку на интересующий интервал.
Составить текстовую строку, определяющую нужную ссылку, несложно — достаточно сначала воспользоваться хорошо знакомой функцией ВПР(), с помощью которой ведется поиск по вертикальным столбцам таблицы данных, а затем функцией объединения строк СЦЕПИТЬ(). На рис. 2 в ячейке с именем «Интервал» размещается итоговый результат последовательной работы этих функций — текстовая строка «I7:I11». Нам осталось сделать завершающий штрих — передать полученный диапазон на вход функции КУБМНОЖ(). Здесь как раз пригодится функция ДВССЫЛ(), которая возвращает ссылку, заданную текстовой строкой. Если в функции КУМНОЖ() в качестве параметра «выражение множества» написать ДВССЫЛ(Диапазон), то на месте немедленно будет вычислена ссылка, определяемая строкой «I7:I11».
Теперь любое изменение значений в ячейках «Начало диапазона» и «Конец диапазона» будет приводить к автоматическому изменению строки «Диапазон», пересчету набора, определенного функцией КУБМНОЖ(), и изменению списка элементов, попадающих в набор отчетной формы.
Предложенный подход просто и быстро решает все задачи, которые обсуждались в настоящей статье. В конечном счете все они сводятся к выбору нужных месяцев в полях «Начало диапазона» и «Конец диапазона». Но самое главное — он не требует от пользователя владения языком MDX, а также обладания какими-либо иными специальными знаниями в области аналитической обработки данных.
На этом рассказ о функции КУБПОРЭЛЕМЕНТ() можно было бы закончить, если бы не еще одна «родовая болезнь» сводных отчетов. В подавляющем большинстве случаев сводные отчеты являются проекцией многомерного пространства на весьма ограниченное число измерений. Но идеальная ситуация, когда на каждой из осей отчета размещается единственное измерение, на практике достигается очень редко. Как правило, OLAP-кубы имеют сложную структуру и по-настоящему информативные отчеты получаются, когда на оси X и, в особенности, Y размещаются элементы сразу из нескольких измерений.
Для сводных таблиц операция добавления нового измерения на ось отчета сводится к банальному перетаскиванию поля с именем измерения в область строк или столбцов макета таблицы. С точки зрения пользователя, такая операция ровным счетом ничего не стоит. Конечно, новое измерение, добавленное в область строк, может существенно поменять линейные размеры сводной таблицы или даже кардинально изменить восприятие показываемых данных. Но для нас сейчас важно другое: «движок» Pivot Service гарантирует, что при изменении макета отчета элементы нового измерения будут корректно отображаться на листе Microsoft Excel.
В случае функций КУБ() таких гарантий вам никто не предоставит. Как мы уже поняли, функция КУБПОРЭЛЕМЕНТ() позволяет разместить в ячейке листа кортеж произвольной размерности. Но возможности Microsoft Excel в части визуализации данных до сих пор весьма ограниченны, в частности он не может показывать в одной ячейке массив из нескольких элементов. Нелепость ситуации заключается в том, что о массиве, хранящемся в ячейке, знает только функция КУБЗНАЧЕНИЕ(). По большому счету этого достаточно, чтобы рассчитать правильное значение меры. Но как быть пользователю, которому вместо кортежей ([Дир_1], [Янв]) и ([Дир_2], [Янв]) показываются только их последние правые элементы [Янв] и [Янв]? Так ли уж важно, что с 1 января соотносится сумма 100 ед., а со 2-м — 200? Иными словами, имеет ли смысл вообще рассчитывать данный измеримый показатель? В глобальном смысле подобное ограничение серьезно снижает ценность всех операторов КУБ().
Повторимся еще раз: функции КУБ() относятся к категории пользовательских. Поэтому, выбирая между гибкостью и простотой использования, корпорация Microsoft остановилась именно на последней характеристике. В силу своей простоты функции КУБЭЛЕМЕНТ() и КУБПОРЭЛЕМЕНТ() оптимально подходят для отображения одномерных наборов. Но платой за удобство работы с простыми наборами стала ограниченность их возможностей в сложных случаях (задачу показа двумерных кортежей уже можно считать сложной). Сразу хочется оговориться, что упомянутая ограниченность не является принципиальной проблемой. Сейчас на рынке присутствуют аналитические решения, предоставляющие пользователю мощнейшие интерфейсы, посредством которых можно управлять выводом на лист Microsoft Excel разверток сразу нескольких измерений.
К счастью, ситуация с функциями КУБ() не так безнадежна, как может показаться на первый взгляд. Действительно, при вызове операторов КУБЭЛЕМЕНТ() и КУБПОРЭЛЕМЕНТ() на листе книги будут отображаться только последние элементы из кортежей.
Таким образом, при выводе посредством КУБПОРЭЛЕМЕНТ() элементов из Набора 6 пользователь увидит на листе столбец:
[Янв];
[Янв].
{
([Дир_1], [Янв]),
([Дир_2], [Янв])
}
Набор 6
Допустим, что на базе Набора 6 мы умеем быстро формировать Набор 7.
{
([Янв], [Дир_1]),
([Янв], [Дир_2])
}
Набор 7
Функция КУБПОРЭЛЕМЕНТ(), примененная к Набору 7, покажет пользователю уже другие элементы из кортежей:
[Дир_1];
[Дир_2].
Если же на листе книги завести два экземпляра функции КУБПОРЭЛЕМЕНТ(), которые ссылаются на одно и то же подключение и одинаковые порядковые номера элементов, но на разные наборы, то мы получим следующее множество значений:
[Дир_1], [Янв];
[Дир_2], [Янв].
Мы видим, что оно полностью соответствует структуре Набора 6. Предложенная манипуляция приводит к неизбежному снижению производительности — всетаки вместо расчета элементов из одного набора нам придется обсчитывать сразу два. Но в реальности такие потери несущественны.
Основная проблема кроется в другой области. В реляционных базах данных порядок следования атрибутов в отношении совершенно не важен. Поэтому с позиций реляционной теории можно считать, что Набор 6 и Набор 7 тождественны. Преобразование одного набора в другой выполняется тривиальным оператором Select — путем перечисления атрибутов в нужном порядке.
Но для аналитической среды порядок следования измерений в кортеже имеет определяющее значение. Поэтому к наборам 6 и 7 нужно относиться как к различным множествам (в действительности они таковыми и являются). К сожалению, в MDX нет оператора, позволяющего переставлять элементы в кортеже. Поэтому задача по формированию Набора 7 на базе Набора 6 решается другими методами и средствами. О них и пойдет речь в следующих статьях цикла.
 |
|
