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

Часть 6

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

Вспомогательные функции КУБМНОЖ() и КУБЧИСЛОЭЛМНОЖ()

Что скрывается за строкой «Выражение_множества»

Сортировка в функции КУБМНОЖ()

Несколько слов о контексте выражения

Сортировка в функции КУБМНОЖ(): продолжение

Функция КУБЧИСЛОЭЛМНОЖ()

Заключение

Вспомогательные функции КУБМНОЖ() и КУБЧИСЛОЭЛМНОЖ()

В наборе операторов КУБ() есть специальные сервисные функции, помогающие организовать и значительно упростить работу с аналитическими отчетами. К их числу относятся КУБМНОЖ() и КУБЧИСЛОЭЛМНОЖ().

Оператор КУБМНОЖ() предоставляет пользователям удобный способ создания наборов для их последующего использования в аналитических отчетах. Фактически данный оператор является функцией массива — его работа состоит в вычислении MDX-выражения в кубе на OLAP-сервере и возврате результатов в ячейку Microsoft Excel. Функция возвращает массив элементов, поэтому ее нельзя использовать в расчетах напрямую. Она может быть может лишь аргументом для других функций семейства КУБ(), для ее записи применяется следующий синтаксис:

КУБМНОЖ(подключение, выражение_множества, подпись, порядок_сортировки, сорт_по)

Первый аргумент стандартен для всех операторов КУБ() — он представляет собой текстовую строку «подключение», в которой задается имя подключения к аналитическому кубу.

Как отмечалось выше, функция КУБМНОЖ() является формулой массива и возвращает сразу множество элементов, поэтому у нее нет отдельного значения для представления в ячейке листа. Поэтому, когда программа Microsoft Excel находится в режиме отображения результатов, ячейка с введенной формулой КУБМНОЖ() остается пустой. Внешне она ничем не отличается от соседних пустых ячеек. Чтобы не потерять ячейку с функцией на листе, можно воспользоваться аргументом «подпись» — прописать в нем текстовую строку, которая должна отображаться в ячейке. Образно говоря, строка «подпись» является своеобразной «заглушкой» для формулы. Она никак не связана с именем диапазона. Присвоение имени ячейки выполняется обычным способом — через диспетчер имен Microsoft Excel.

Что скрывается за строкой «Выражение_множества»

Строка «Выражение_множества» определяет некоторое MDX-выражение, которое должно быть рассчитано в кубе. Обычно «Выражение_множества» — это текстовая строка, но в функции поддерживаются и другие способы его задания. Эти дополнительные режимы делают функцию КУБМНОЖ() очень мощным инструментом по работе с наборами непосредственно в среде Microsoft Excel.

Каждый набор объединяет несколько кортежей. Допустим, в книге Microsoft Excel в ячейках B1 и B2 заданы выражения, определяющие отдельные элементы многомерного пространства, например ([Дирекция].[Дирекция].&[2], [Сервис].[Сервис].&[1]) и ([Дирекция].[Дирекция].&[1], [Сервис].[Сервис].&[3]) соответственно. Каждое из этих выражений в отдельности можно использовать в функциях КУБЭЛЕМЕНТ() и КУБЗНАЧЕНИЕ(). О том, как это делается, рассказывалось в прошлых частях статьи. Но как быть, если нам требуется объединить эти разрозненные элементы в единое целое — набор?

Явный путь заключается в написании соответствующего MDX-выражения средствами Microsoft Excel. К примеру, можно составить формулу СЦЕПИТЬ(«{«;B1;», «;B2;»}»), которая на выходе даст длинную текстовую строку «{([Дирекция].[Дирекция].&[2], [Сервис].[Сервис].&[1]), ([Дирекция].[Дирекция].&[1], [Сервис].[Сервис].&[3])}». Такая строка определяет уже выражение множества из двух элементов, поэтому ее можно использовать в качестве аргумента для функции КУБМНОЖ(). Недостатки предложенного решения очевидны.

Во­первых, составление синтаксически корректного выражения набора при помощи функции конкатенации строк является весьма трудоемким занятием. Представьте, сколько усилий придется потратить, чтобы объединить в одну строку данные из 20 ячеек. А ведь их может быть и 100, и даже 200!

Во­вторых, последовательность объединения строк определяет очередность их попадания в набор и соответственно порядок их отображения на осях аналитического отчета. Для прикладных задач важно обеспечить упорядоченность размещения элементов в наборе: сначала должны идти кортежи, относящиеся к «Дирекции_1», а затем к «Дирекции_2». В противном случае пользователю будет сложно ориентироваться в отчетной форме. При малом числе измерений в кортежах (одно или два) и небольшом количестве самих элементов такую задачу вполне реально выполнить вручную — достаточно аккуратно перечислять ячейки в формуле СЦЕПИТЬ(). Но при значительных объемах данных одной внимательности будет недостаточно. Ситуация значительно усугубляется, когда в ходе работы требуется динамически менять состав набора — добавлять новые или удалять существующие элементы. Такая операция требует постоянного переписывания громоздкой формулы СЦЕПИТЬ(), что не очень удобно, учитывая необходимость вставки промежуточных служебных строк типа «,».

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

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

1. Определить непрерывный диапазон стандартным способом — путем указания координат его начала и конца (заданием пары «верхняя левая ячейка — правая нижняя ячейка»).
2. Из нескольких непрерывных диапазонов ячеек составить именованный диапазон в диспетчере имен Microsoft Excel.
3. Объединить несколько именованных или непрерывных диапазонов в один внутри самой формулы — для этого при записи выражения их требуется выделить круглыми скобками.

На рис. 1 приведен пример вызова функции КУБМНОЖ(), в которой используется каждый из перечисленных вариантов.

Рис. 1. Возможные способы определения диапазонов ячеек

На этом возможности формулы не исчерпываются. Одну функцию КУБМНОЖ() можно использовать в качестве аргумента при составлении другой функции КУБМНОЖ(). Иными словами, ссылки на ячейки с формулами КУБМНОЖ() можно указывать в разделе «Выражение_множества», причем как в отдельности, так и объединяя их в собственные диапазоны ячеек. В свою очередь, вновь полученное значение функции КУБМНОЖ() допустимо применять для организации рекурсивного вызова в следующей функции и т.д.

Столь широкий потенциал открывает перед пользователями практически неограниченные возможности манипулирования многомерными данными. Единственное существенное ограничение, о котором всегда нужно помнить, — при составлении наборов из нескольких элементов следует ориентироваться на синтаксис MDX, который запрещает объединение в единое целое разнородных объектов. Поэтому, к примеру, попытка составить набор из кортежей (Дирекция, Сервис) и (Сервис, Дирекция) обречена на провал.

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

Сортировка в функции КУБМНОЖ()

Для решения данной задачи в функции КУБМНОЖ() предназначены два последних аргумента — «Порядок_сортировки» и «Сорт_по». Они позволяют произвести дополнительную сортировку набора без написания какого-либо MDX-кода.

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

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

Значением по умолчанию аргумента «Порядок_сортировки» является 0. В этом случае элементы набора возвращаются в ячейку в порядке, определенном исходным MDX-выражением. Режим по умолчанию оптимально подходит для промежуточных вычислений КУБМНОЖ().

Режимы 1 и 2 предназначены для сортировки полученного набора по мере куба, которая указывается в аргументе «Сорт_по». Сразу отметим, что аргумент «Сорт_по» применяется только в этих режимах, о чем, собственно, и говорят значения в последнем столбце таблицы.

В справочной системе Microsoft Excel приведен наглядный пример, как пользоваться режимом 1. Чтобы получить город с наибольшим объемом продаж, значение «Выражение_множества» должно быть набором городов, а «Сорт_по» — мерой объема продаж. Чтобы получить город с наибольшим населением, значение «Выражение_множества» должно быть набором городов, а «Сорт_по» — мерой количества населения. От себя добавим, что режимы «сортировка по возрастанию» и «сортировка по убыванию» работают для наборов произвольной размерности, а не только для состоящих из представителей единственного измерения.

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

Набор 1

{

([Факт], [Янв]),

([Факт], [Фев]),

([Факт], [Мар]),

([Факт], [Апр]),

([Факт], [Май]),

([Факт], [Июн])

}.

Но только первые три из них имеют численные значения по мере «Сумма» в конце I квартала, а вот для месяцев апрель, май и июнь соответствующие значения равны Empty. В такой ситуации функция КУБМНОЖ() сортирует набор в два этапа. Если выбран режим «по возрастанию», то «пустые» элементы набора располагаются в начале итогового списка в том же порядке, как они появляются в наборе. Затем следуют «непустые» элементы, отсортированные по выбранной мере. В режиме «по убыванию» всё происходит наоборот — пустые элементы перемещаются в конец списка.

В функции КУБМНОЖ() реализован режим неиерархической (nonhierarchical) сортировки. Принцип работы неиерархической сор­тировки заключается в том, что кортежи набора упорядочиваются без оглядки на взаимное размещение элементов в иерархических измерениях. Допустим, месяцы объединены в двухуровневую иерархию «Дата», в которой на верхнем уровне расположены кварталы, а на листовом — месяцы. На базе такой иерархии можно составить набор для извлечения фактических значений по месяцам:

Набор 2

{

([Факт], [Дата].[Месяц].[Янв]),

([Факт], [Дата].[Месяц].[Фев]),

([Факт], [Дата].[Месяц].[Мар]),

([Факт], [Дата].[Месяц].[Апр]),

([Факт], [Дата].[Месяц].[Май]),

([Факт], [Дата].[Месяц].[Июн])

}

В наборе 2 месяцы являются элементами нижнего уровня «Месяц». Если отсортировать такой набор средствами MDX, например при помощи оператора Order() — Order([Набор_2], [Measures].[Сумма]), то месяцы в нем будут упорядочиваться по возрастанию значений меры «Сумма», однако только в пределах своих кварталов. Иными словами, даже если сумма для марта окажется больше суммы апреля, кортеж ([Факт], [Дата].[Месяц].[Мар]) всё равно будет находиться выше кортежа ([Факт], [Дата].[Месяц].[Апр]) в итоговом наборе прос-то потому, что март находится в «младшем» I квартале, а апрель — в «старшем» II квартале.

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

Чтобы включить «подавление» иерархий (break hierarchy), оператор Order(), упорядочивающий элементы набора, должен исполняться с ключом BASC или BDESC (B означает Break). Работа функции КУБМНОЖ() в режиме сортировки по мере куба аналогична действию оператора Order() с указанными ключами (BASC — режим 1 «Сортировка по возрастанию») или (BDESC — режим 2 «Сортировка по убыванию»).

К сожалению, функцию КУБМНОЖ() не получится перевести в режим иерархической сортировки, но для среды Microsoft Excel такое ограничение не выглядит критично. Дело в том, что функции КУБ() ориентированы на работу с одноуровневыми измерениями. Когда они используются для отображения многоуровневых иерархий, в электронных таблицах показывается только нижний уровень (Leaf level). Здесь иерархическая сортировка только запутает пользователя. Совершенно непонятно, почему месяц с меньшим объемом продаж должен следовать за месяцем с большим объемом, если не знать, что они относятся к разным кварталам. А кварталы, которые находятся на верхнем уровне измерения, не показываются в отчете.

При организации сортировки по мере важно уметь правильно подбирать аргумент «Сорт_По». Обычно в качестве его значения выбирается одна из мер куба —– «Объем продаж» или «Население города». Однако в общем случае таким параметром может быть любой кортеж, определяющий координаты ячеек в многомерном пространстве. На практике существует ряд ситуаций, когда бывает полезно воспользоваться данным обстоятельством.

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

Несколько слов о контексте выражения

В очередной раз обратимся к примерам из практики.

Рассмотрим простой запрос, который разделяет выручку по месяцам (рис. 2):

Рис. 2. Распределение выручки по календарным периодам

Выражение 1

select {[Measures].[Сумма]} on 0,

non empty {[Дата].[Месяц].[Месяц].members} on 1

from [PF].

Предположим, что нам требуется в этом отчете упорядочить месяцы по возрастанию плановых показателей. Вообще говоря, результат исполнения выражения 1 можно воспринимать по-разному. С одной стороны — это отчет, с другой — его можно считать многомерным пространством, являющимся подпространством исходного куба. Результат выражения 1 — подкуб куба [PF].

Вы видите, количество атрибутов, спроецированных на оси отчета, меньше общего числа атрибутов в исходном кубе. Для недостающих атрибутов Microsoft Analysis создает специальную ось — Slicer Axis (наиболее точный перевод — ось среза). Она содержит по одному элементу каждой из осей, не представленных в отчете. Такие элементы называются Default member (элементы по умолчанию).

Элементы по умолчанию выбираются для каждого измерения на этапе проектирования многомерной модели. Для агрегируемых атрибутов (в иерархии присутствует элемент [All]) в качестве такого элемента обычно назначается [All]. В частности, элемент [All] выбирается элементом по умолчанию для измерения [План_Факт], которого нет в выражении 1. Поэтому в показателе «Сумма» стоит результат ([План]+[Факт]).

Когда требуется детализировать срез данных, добавленных в подкуб, используют предложение WHERE. Если мы хотим оставить в подкубе только плановые значения, в предложении WHERE нужно указать соответствующий элемент измерения [План_Факт] (рис. 3).

Рис. 3. Задание глобального контекста

Из рис. 3 видно, что теперь в операторе Select используется уточненная координата по измерению [План_Факт], в нем элемент [All] заменили на элемент [План]. Поэтому об операторе Select говорят, что он выполняется в контексте предложения WHERE.

Важно отметить, что добавленное в запрос ограничение WHERE будет действовать для любого пользователя, подключенного к аналитическому серверу, в рамках каждой его открытой сессии. Поэтому про инструкцию WHERE еще говорят, что она определяет глобальный контекст куба (Global scope).

После того как мы сузили исходное пространство и оставили в нем только плановые значения, мы можем попытаться отсортировать набор {[Дата].[Месяц].members}, который откладывается на оси Y отчета, по возрастанию меры «Сумма».

Как видно из рис. 4, в созданном отчете месяцы упорядочены по возрастанию плановых показателей. Это ожидаемый результат: в подкубе нет ничего, кроме плановых значений. Поэтому вполне естественно, что сортировка набора по мере «Сумма» автоматически ранжирует периоды и по возрастанию плана. Но вот можно ли признать полученный результат удачным? Скорее всего, нет. У нас не получится использовать упорядоченный набор из дат для отображения фактических результатов продаж — ведь информации о факте нет в созданном срезе данных.

Рис. 4. Сортировка календарных периодов по мере «Сумма»

Из сказанного следует, что глобальный контекст не подходит для задания ограничений при выполнении сортировок наборов в отчете.

По аналогичным причинам для этих целей не подходит и контекст сессии. Напомним читателям, что в рамках открытой сессии также можно определить новое подпространство куба посредством оператора Create SubCube (рис. 5).

Рис. 5. Задание контекста сессии

Инструкция Create SubCube переопределяет пространство заданного куба на вложенный куб. Фактически она изменяет видимое пространство куба для всех последующих операций в рамках одной сессии. Поэтому о команде Create SubCube говорят, что она определяет контекст сессии (Session scope). Рис. 5 демонстрирует, как она работает на практике. Операция Create SubCube определяет срез данных, как и команда WHERE (в нашем примере мы опять оставили в подпространстве только плановые значения). После чего всем последующим операторам исходный куб представляется уже в новом, усеченном виде.

В частности, оператор Select в выражении 2 выполнится в контексте подкуба и, как показывает рис. 5, выдает тот же результат, что и команда, запущенная в глобальном контексте.

Выражение 2

select non empty order({[Дата].[Месяц].[Месяц].members},[Сумма]) on 1,

{[Сумма]} on 0

from [PF]

Выходом из этой ситуации является использование контекста выражения (Query scope). Ряд операторов, посредством которых формируются наборы в отчете, может быть запущен в собственном контексте. Принципиальным здесь является тот факт, что контекст выражения заново переопределяет «старшие» области видимости, но только в пределах действия самого выражения. Как написано в справке Microsoft: «Если координаты ячейки для отдельного атрибута указаны и по оси среза, и по другой оси, координаты, указанные в функции, могут иметь преимущество при определении элементов набора по этой оси. Примерами таких функций являются Filter (многомерные выражения) и Order (многомерные выражения), результат можно отфильтровать или упорядочить по элементам атрибута, которые исключены из контекста вычисления предложением WHERE или инструкцией SELECT в предложении FROM».

Простым языком данную идею можно изложить следующим образом: для определения контекста выражения можно использовать даже те ячейки, которых нет в текущем срезе куба. Или еще проще: контекст выражения определяется независимо от прочих контекстов.

Посмотрим на выражение (рис. 6). В операторе Select в предложении WHERE определяется глобальный контекст куба — в подкубе оставлены только плановые показатели. Но в операторе Order() при помощи кортежа ([Сумма], [Факт]) задается собственный контекст выражения. В итоге набор {[Дата].[Месяц].[Месяц].members} упорядочивается по фактическим значениям продаж, которые были исключены из текущего среза данных!

Рис. 6. Задание контекста выражения

Сортировка в функции КУБМНОЖ(): продолжение

После того как мы в общем разобрались с вычислением координат в запросе, вернемся к обсуждению работы функции КУБМНОЖ(). В режиме сортировки по мере она работает аналогично оператору Order() языка MDX. Роль параметра «Сорт_По» состоит в определении контекста текущего выражения со всеми вытекающими из этого возможностями и ограничениями.

Об имеющихся возможностях мы уже говорили. Контекст выражения позволяет задать для отдельной формулы область видимости ячеек, отличную от действующего среза данных. Но при этом он не меняет глобальный контекст либо контекст сессии, то есть не меняет само подпространство, на базе которого формируется отчет. Данное объяснение может показаться запутанным, что, впрочем, типично для большинства тем многомерного анализа. Но на бытовом уровне всё оказывается не так страшно, главное — сразу не пугаться итоговых результатов действия запросов.

Рассмотрим задачу, представленную на рис. 7. Допустим, что в нашем распоряжении имеются данные о фактических и плановых продажах с разбивкой по месяцам, а цель заключается в сортировке двух наборов: набора 2 и набора 3.

Рис. 7. Варианты сортировок по мере куба

Набор 3

{

([Дата].[Месяц].[Янв]),

([Дата].[Месяц].[Фев]),

([Дата].[Месяц].[Мар]),

([Дата].[Месяц].[Апр]),

([Дата].[Месяц].[Май]),

([Дата].[Месяц].[Июн])

}

Разберем возможные варианты сортировок. Что произойдет, если отсор­тировать по убыванию параметра «Сумма» набор 3 (вариант 1 на рис. 7)?

Формула КУБМНОЖ() при этом примет вид: КУБМНОЖ(«OLAP_Connection»;H2:H7;»Множество: [Сумма]»;2;»[Сумма]»).

Параметр «Сорт_По» определяет контекст оператора Order(). Для всех измерений, не указанных в наборе 3, в качестве текущего значения будет выбираться значение по умолчанию, а для оси Measures — элемент «Сумма». Элемент [All] выбирается и для измерения [План_Факт], поэтому сортировка набора 3 в первом случае ведется по убыванию месячных значений показателя ([План]+[Факт]). Как видно из рис. 2, в июне сумма плановых и фактических продаж максимальна и равна 2322. Соответственно данный месяц располагается на первом месте итогового отсортированного набора. Затем идет март с показателем 2083, за ним апрель — 1990 и т.д.

Набор, составленный из упорядоченных таким образом месяцев, откладывается на оси Y отчета, но в самом отчете показываются данные по фактическим продажам (на оси Х отчета размещено значение [Факт]). Поэтому в отчетной форме (отмечена индексом 1 на рис. 2) для июня значение показателя «Сумма» равно 919, а для второго месяца, марта, — 1399.

Изменим условия сортировки и зададим в качестве параметра «Сорт_По» кортеж ([План], [Сумма]) (вариант 2 на рис. 7). Для этого перепишем функцию КУБМНОЖ():

КУБМНОЖ(«OLAP_Connec-tion»;H2:H7;»Множество: ([План], [Сумма])»;2;»([План],[Сумма])»)

В новой редакции параметр «Сорт_По» определяет срез пространства не только по мере Measures, но и по измерению [План_Факт]. Элемент [All] из указанного измерения заменен на элемент [План]. Результат проведенной замены виден в отчетной форме № 2. В форме, как и раньше, показываются данные о фактических продажах, но при этом месяцы упорядочены по убыванию плановых значений. Внимание читателей хочется обратить на то, что контекст выражения не изменил глобальный контекст отчета — значения денежных показателей остались такими же, как и в форме № 1. Изменился лишь порядок их следования.

Перейдем к более сложному случаю и посмотрим, как будет выполняться сортировка набора 2. Указанный набор состоит из элементов не одного измерения (Месяц), а двух — кортежей (Факт, Месяц).

По сути в отчете № 3 определен такой же срез многомерного пространства, как и в отчете № 1. В обоих случаях показываются одни и те же данные по фактическим продажам, сгруппированные по месяцам. Однако, сходные по «содержанию», они различаются по «форме»: в отчете № 1 измерение [План_Факт] размещено на оси Х, а в отчете № 3 — на оси Y.

Как же теперь будет работать формула КУБМНОЖ(«OLAP_Connection»;H2:H7;»Множество: [Сумма]»;2;»[Сумма]»)?

Параметр «Сорт_По», равный одному элементу [Сумма], определяет контекст выражения. Поскольку контекст выражения не детализирует срез данных по оси [План_Факт], он вычисляется на основании глобального контекста, который для набора 3 равен элементу [Факт]. Поэтому в данном случае сортировка в функции КУБМНОЖ() будет выполняться по убыванию фактических месячных значений. Форма № 3 на рис. 7 служит тому наглядным доказательством.

В завершение рассмотрим самый нетривиальный случай. У нас есть набор 3, в котором определена координата [Факт] по измерению [План_Факт], но мы, как и прежде, хотим отсортировать набор по убыванию плановых значений. Последняя редакция формулы КУБМНОЖ() по написанию совпадает с вариантом 2:

КУБМНОЖ(«OLAP_Connection»;H2:H7;»Множество: ([План], [Сумма])»;2;»([План],[Сумма])»)

Для набора 3 формула работает следующим образом. На основании параметра «Сорт_По» определяется контекст выражения для оператора Order(), сортирующего набор. Как говорилось выше, контекст выражения переопределяет глобальный контекст в пределах одной формулы, поэтому сортировка набора 3 будет осуществляться в разрезе (План, Месяц), хотя в наборе до этого был явно указан элемент [Факт]. Отсор-тированный набор используется далее для составления отчета. Отчет же исполняется в рамках глобального контекста, который никак не зависит от контекста выражения команды Order(). Для оси [План_Факт] глобальный контекст задан в виде элемента [Факт]. Поэтому в отчетной форме № 4 мы видим весьма интересную конструкцию. Набор 2 отсор­тирован по убыванию плановых значений. В ячейках на листе книги Microsoft Excel показываются только его крайние правые элементы. Поэтому с точки зрения пользователя он идентичен набору 3. Но набор 2 сразу отбирает только фактические значения из многомерного пространства, которые мы и видим в итоговом отчете № 4.

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

Режимы 3 и 4 позволяют упорядочивать набор в лексикографическом порядке по последнему элементу в каждом кортеже.

Режимы 5 и 6 сортируют набор по ключевым значениям элементов, входящих в кортеж. Натуральное возрастание в данном случае означает, что набор последовательно упорядочивается сначала по ключевым значениям первых элементов из кортежа, затем — вторых и т.д.

Для демонстрации данного принципа в последний раз в этой статье обратимся к наглядным примерам.

Допустим, у нас есть два множества:

Множество 1

{([Дир_1],[Абонентская плата])}

{([Дир_1],[Интернет­доступ])}

{([Дир_1],[МГ/МН связь])}

{([Дир_1],[Местная связь])}

{([Дир_1],[Установочная плата])}

Множество 2

{([Дир_2],[Абонентская плата])}

{([Дир_2],[МГ/МН связь])}

{([Дир_2],[Местная связь])}

{([Дир_2],[Установочная плата])}

На рис. 8 они выделены зеленым и фиолетовым цветами соответственно. На осях «Дирекция» и «Сервис» аналитического куба элементы размещаются следующим образом:

Рис. 8. Режим сортировки по натуральному возрастанию

• «Дирекция»:

  1) [Дир_1],

  2) [Дир_2];

• «Сервис»:

  1) абонентская плата,

  2) интернет-доступ,

  3) МГ/МН связь,

  4) местная связь,

  5) установочная плата.

Выберем по два элемента из каждого множества и создадим при помощи функции КУБМНОЖ() такой набор, что элементы в нем на всех уровнях будут нарушать естественный порядок следования. Из рисунка видно, что в наборе кортеж, относящийся к «младшей» дирекции № 2, размещен перед кортежами «старшей» дирекции № 1. Кроме того, в кортежах дирекции № 1 «старшая» услуга [Абонентская плата] следует за «младшей» — [МГ/МН связь].

Режим сортировки по натуральному возрастанию расставляет в один проход все элементы набора по правильным местам. В итоговом множестве сначала перечисляются все кортежи дирекции № 1, причем внутри данного списка они сортируются по естественному возрастанию услуг, затем следуют кортежи дирекции № 2, которые также отсортированы по услугам.

Обсуждение функции КУБМНОЖ() хочется закончить демонстрацией рис. 9, иллюстрирующего различные режимы сортировки одного и того же набора.

Рис. 9. Сравнение различных режимов сортировки функции КУБМНОЖ()

Функция КУБЧИСЛОЭЛМНОЖ()

Функция КУБЧИСЛОЭЛМНОЖ() является самой простой и понятной из всего семейства операторов КУБ(). Как видно из ее названия, она позволяет узнать число элементов в выбранном множестве. У функции есть единственная переменная — «Множество». Данный параметр может быть либо функцией КУБМНОЖ(), либо ссылкой на ячейку, содержащей функцию КУБМНОЖ().

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

Во­вторых, аргументом может быть только множество, сформированное функцией КУБМНОЖ(). Поэтому, если в ячейке Microsoft Excel содержится строка, описывающая некоторое MDX-выражение, ее не получится подать на вход КУБЧИСЛОЭЛМНОЖ() напрямую. Для того чтобы узнать, сколько в множестве элементов, сначала придется вычислить выражение в кубе на сервере посредством функции КУБМНОЖ() и только затем подсчитать количество элементов в возращенном наборе. Понятно, что такая задача легко решается вложенной формулой КУБЧИСЛОЭЛМНОЖ(КУБМНОЖ(«MDX-выражение»)).

Заключение

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

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