 |
АвтоАвтоматизацияАрхитектураАстрономияАудитБиологияБухгалтерияВоенное делоГенетикаГеографияГеологияГосударствоДомДругоеЖурналистика и СМИИзобретательствоИностранные языкиИнформатикаИскусствоИсторияКомпьютерыКулинарияКультураЛексикологияЛитератураЛогикаМаркетингМатематикаМашиностроениеМедицинаМенеджментМеталлы и СваркаМеханикаМузыкаНаселениеОбразованиеОхрана безопасности жизниОхрана ТрудаПедагогикаПолитикаПравоПриборостроениеПрограммированиеПроизводствоПромышленностьПсихологияРадиоРегилияСвязьСоциологияСпортСтандартизацияСтроительствоТехнологииТорговляТуризмФизикаФизиологияФилософияФинансыХимияХозяйствоЦеннообразованиеЧерчениеЭкологияЭконометрикаЭкономикаЭлектроникаЮриспунденкция
Набор 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 решается другими методами и средствами. О них и пойдет речь в следующих статьях цикла.
Поиск по сайту: