Важные правила обращения с наборами элементов при составлении многомерных отчетов

После того как мы разобрались с созданием динамических наборов на базе одного измерения в среде Microsoft Excel, нам осталось решить последнюю проблему — научиться размещать на одной оси отчета элементы сразу из нескольких измерений. Начать придется с небольшого теоретического вступления.

Как уже неоднократно говорилось, многомерный отчет, создаваемый при помощи инструкции Select, располагает измерения OLAP-куба на трех осях:

Axis1 — ось X таблицы;

Axis2 — ось Y таблицы;

Where — невидимая в отчете ось, которая соответствует разделу фильтров сводной таблицы.

В простейшем варианте на каждой из осей размещаются наборы (Sets), состоящие из элементов одного измерения. Например, в выражении № 1 представлен запрос, который выводит на ось X (горизонтальная ось при экономичной записи обозначается символом «0») все элементы измерения «Дата», а на ось Y (обозначается символом «1») — элементы измерения «Дирекция». На оси «Where» размещается элемент [План_Факт].[План_Факт].[Факт], что означает фильтрацию данных только по фактическим значениям.

Выражение 1

select {nonempty([Дата].[Дата].members)} on 0,

{[Дирекция].[Дирекция].members} on 1

from [PF]

where [План_Факт].[План_Факт].[Факт]

Итогом работы запроса станет отчет, представленный на рис. 4.

Рис. 4. Создание простого MDX-запроса

Из рисунка видно, что на вертикальной оси («1») выводится набор, состоящий всего из трех элементов и при этом полностью совпадающий с измерением [Дирекция]:

Набор 7

{

([All]),

([Дир_1]),

([Дир_2])

}

В общем случае набор может состоять из любой комбинации элементов. Главное, чтобы все они относились к одному измерению. Увеличим для примера размер набора, для этого повторно добавим в выражение № 1 элементы измерения [Дирекция], а также еще один вычисляемый элемент [Дир_2 - Дир_1], считающий отклонение результатов Дир_2 от результатов Дир_1 по текущему контексту.

Выражение 2

with member [Дирекция].[Дирекция].[Дир_2 - Дир_1] as ([Дирекция].[Дирекция].[Дир_2]-[Дирекция].[Дирекция].[Дир_1])

select {nonempty([Дата].[Дата].members)} on 0,

{[Дирекция].[Дирекция].members, [Дирекция].[Дирекция].members, [Дирекция].[Дирекция].[Дир_2 - Дир_1]} on 1

from [PF]

where [План_Факт].[План_Факт].[Факт].

В результате работы Выражения № 2 структура выходного отчета немного изменится (рис. 5).

Рис. 5. Отчет на базе производного набора элементов
из измерения «Дирекция»

Основное отличие нового отчета от исходного варианта заключается в увеличении количества строк таблицы. Во втором случае их уже семь, но заголовками строк остаются всё те же представители измерения [Дирекция].[Дирекция]:

Набор 8

{

([All]),

([Дир_1]),

([Дир_2]),

([All]),

([Дир_1]),

([Дир_2]),

([Дир_2 – Дир_1])

}.

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

В случае MDX подобная операция выполняется посредством оператора Item(). Перепишем еще раз наше MDX-выражение:

Выражение 3

with member [Дирекция].[Дирекция].[Дир_2 - Дир_1] as ([Дирекция].[Дирекция].[Дир_2]-[Дирекция].[Дирекция].[Дир_1])

set [Дирекция_Расширенная] as {[Дирекция].[Дирекция].members, [Дирекция].[Дирекция].members, [Дирекция].[Дирекция].[Дир_2 - Дир_1]}

select {nonempty([Дата].[Дата].members)} on 0,

{[Дирекция_Расширенная].Item(3), [Дирекция_Расширенная].Item(6)} on 1

from [PF]

where [План_Факт].[План_Факт].[Факт]

В последнем варианте на вертикальной оси показываются только 4-й и 7-й элементы (нумерация элементов в наборе ведется с нуля) набора, определенного на предыдущем этапе (рис. 6).

Рис. 6. Вывод элементов с определенными порядковыми номерами

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

Мы подошли к одному из принципиальных отличий языка MDX от SQL. Запросы к реляционным данным, написанные на SQL, не гарантируют порядок возврата записей. Базовой технологией хранения данных в СУБД является «куча» (heap), поэтому запрос, запущенный в разное время, может показывать одни и те же записи в разной последовательности. Чтобы получить в результирующем наборе гарантированный порядок следования записей, требуется воспользоваться инструкцией ORDER. Однако команда ORDER только сортирует кортежи, но не нумерует их. Для получения упорядоченного и нумерованного набора следует применить одну из специальных ранжирующих оконных функций: RANK(), DENSE_RANK() или ROW_NUMBER(). Ранг записи при этом становится ее отдельным атрибутом и хранится в дополнительной колонке таблицы. Аналитические данные, напротив, упорядочены по своей природе. Поэтому результатом работы MDX-выражения всегда будет отсортированный набор, в котором каждый элемент по умолчанию обладает рангом.

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

Выражение 4

with set [Дирекции * Услуги] as [Дирекция].[Дирекция].members*[Сервис].[Сервис].members

select {nonempty([Дата].[Дата].members)} on 0,

{ [Дирекции * Услуги]} on 1

from [PF]

where [План_Факт].[План_Факт].[Факт]

В результате работы запроса получим отчет, показанный на рис. 7.

Рис. 7. Детализация доходов по дирекциям и видам услуг

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

Набор 9

{

([All],[All]),

([All],[Абонентская плата]),

([All],[Интернет­Доступ]),

([All],[МГ/МН связь]),

([All],[Местная связь]),

([All],[Установочная плата]),

([Дир_1],[All]),

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

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

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

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

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

([Дир_2],[All]),

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

([Дир_2],[Интернет­Доступ]),

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

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

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

}

Каждый элемент теперь представлен массивом из двух элементов. Но, как и в случае с одномерным набором, все элементы новой последовательности также имеют ранги, позволяющие обращаться к ним. Выделим для примера кортеж ([Дир_1],[Абонентская плата]), он расположен в результирующем наборе на 8-м месте. Для этого в очередной раз воспользуемся функцией Item().

Выражение 5

With set [Дирекции * Услуги] as [Дирекция].[Дирекция].members*[Сервис].[Сервис].members

select {nonempty([Дата].[Дата].members)} on 0,

{[Дирекции * Услуги].Item(7)} on 1

from [PF]

where [План_Факт].[План_Факт].[Факт]

Ожидаемым результатом работы Выражения 5 станет отчет, состоящий всего из одной строки (рис. 8).

Рис. 8. Вывод элемента многомерного набора
с определенным порядковым номером

Зададимся вопросом: что произойдет, если изменить порядок следования атрибутов в кортеже? Каким будет отчет из Выражения 5, в котором на первом месте будет стоять измерение [Сервис], а не [Дирекция]?

Выражение 6

With set [Услуги*Дирекции] as

[Сервис].[Сервис].members*[Дирекция].[Дирекция].members

select {nonempty([Дата].[Дата].members)} on 0,

{ [Услуги*Дирекции]} on 1

from [PF]

where [План_Факт].[План_Факт].[Факт]

Новый результирующий набор (рис. 9) несколько отличается от отчета на рис. 7.

Рис. 9. Изменение порядка следования измерений в наборе

Заметим, что выражения 4 и 6 эквиваленты в том плане, что определяют одно подмножество OLAP-куба. Из рисунков видно, что количество и состав записей в обоих отчетах совпадают, но при этом существенно различается порядок их следования. Последовательность, в которой записи выводятся в результирующем наборе, зависит от того, как перечислялись измерения в операторе перекрестного умножения. Иными словами, в зависимости от синтаксиса MDX-выражений записи в отчетах упорядочиваются по-разному.

Место кортежа в наборе определяет его ранг. Поэтому ранг кортежа также меняется в зависимости от вида записи MDX-выражения. В частности, кортеж ([Абонентская плата], [Дир_1]) в выражении 6 определяет тот же адрес на осях измерений [Дирекция] и [Сервис], что и кортеж ([Дир_1],[Абонентская плата]). Но его ранг равен 5 (строка с кортежем идет пятой по счету в отчете на рис. 7).

Для успешной работы с функцией КУБПОРЭЛЕМЕНТ() критически важно уметь на базе исходного набора составлять производные наборы, в которых используется иной порядок следования измерений в кортеже, но при этом сохраняется ранг самого кортежа. В нашем случае необходимо уметь составлять следующий набор.

Набор 10

{

([All],[All]),

([Абонентская плата],[All]),

([Интернет­Доступ],[All]),

([МГ/МН связь],[All]),

([Местная связь],[All]),

([Установочная плата],[All]),

([All],[Дир_1]),

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

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

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

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

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

([All],[Дир_2]),

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

([Интернет­Доступ],[Дир_2]),

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

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

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

}

В Наборе 10 кортеж ([Абонентская плата],[Дир_1]) стоит на 8-м месте, а значит имеет тот же ранг, что и кортеж ([Дир_1],[Абонентская плата]) в Наборе 9. Проблема заключается в том, что получить набор из Набора 9 методом «грубой силы» — путем изменения порядка измерений в операторе CROSSJOIN() — не удастся. Такую операцию придется выполнять посредством функции EXTRACT().

Функция EXTRACT() в определенном смысле является обратной для оператора CROSSJOIN(). Если оператор перекрестного умножения создает прямое произведение наборов­аргументов, то оператор EXTRACT(), напротив, из итогового набора извлекает элементы одного из базовых измерений. Извлечение элементов производится в том же порядке, в каком они следуют в исходном наборе, — оператор сохраняет ранги. Но если в ходе такой операции возникают дубликаты, то они не показываются в итоговом результате.

Рассмотрим, как работает функция EXTRACT() на нашем примере. Набор 9 из начала статьи представляет собой набор кортежей из двух элементов: ([Дирекция], [Сервис]), задаваемого операцией прямого произведения измерений [Дирекция], и [Сервис]: [Дирекция].[Дирекция].members*[Сервис].[Сервис].members.

Теоретически на базе набора ([Дирекция], [Сервис]) можно составить набор, состоящий только из элементов одного измерения ([Дирекция]).

Набор 11

{

([All]),

([All]),

([All]),

([All]),

([All]),

([All]),

([Дир_1]),

([Дир_1]),

([Дир_1]),

([Дир_1]),

([Дир_1]),

([Дир_1]),

([Дир_2]),

([Дир_2]),

([Дир_2]),

([Дир_2]),

([Дир_2]),

([Дир_2])

}

Если теперь из Набора 11 удалить все дубликаты, не нарушая при этом порядка следования элементов, то он выродится в Набор 12, совпадающий с измерением [Дирекция].

Набор 12

{

([All]),

([Дир_1]),

([Дир_2])

}

Именно эти действия и совершает функция EXTRACT() — рис. 10.

Рис. 10. Работа функции Extract()

Посредством функции EXTRACT() можно определять кортежи, состоящие из любой комбинации измерений исходного набора. В частности, на базе набора элементов {[Дирекция], [Сервис]} допустимо составить набор из кортежей {[Сервис], [Дирекция]}. При этом функция сохранит порядок следования элементов и удалит возникающие дубликаты из результирующего набора. Понятно, что в нашем случае повторяющихся элементов не будет, а оператор EXTRACT() просто переставит элементы в кортежах во всем наборе.

Из рис. 11 следует, что в отчете на оси «1» кортеж ([Абонентская плата],[Дир_1]) находится на 8-м месте и соответственно имеет такой же ранг, что и кортеж ([Дир_1],[Абонентская плата]) из отчета на рис. 7.

Рис. 11. Изменение порядка следования измерений посредством функции EXTRACT()

Заключение

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

«Блеск и нищета» сводных таблиц Часть 9. Создание отчета со сложной структурой

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

Настоящий материал подводит итог всего цикла статей, посвященного основным функциям КУБ(): КУБЭЛЕМЕНТ(), КУБСВОЙСТВОЭЛЕМЕНТА(), КУБПОРЭЛЕМЕНТ(), КУБМНОЖ(), КУБЧИСЛОЭЛМНОЖ(). Нам останется изучить только последнего представителя этого семейства — КУБЭЛЕМЕНТКИП(), который стоит особняком и предназначен для работы с ключевыми индикаторами производительности (КИПами).

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

• на одной оси в нем размещаются сразу два измерения;
• для каждого из этих измерений имеется возможность менять состав, включенных в форму элементов.

Перечисленные требования на первый взгляд не кажутся слишком сложными для реализации. Если бы речь шла о формировании отчета с помощью инструмента сводных таблиц, это бы соответствовало действительности. Чтобы получить желаемый макет сводной таблицы, нужно всего лишь перенести по принципу drag and drop выбранные поля в область строк в форме настройки. Но на самом деле размещение двух или более измерений на одной оси означает формирование набора из многомерных кортежей. В случае сводных таблиц такая работа скрыта от пользователя — ее делает в фоновом режиме сервис Pivot Service. А в случае применения функций КУБ() ее уже придется выполнять вручную. Поэтому создание, казалось бы, простого отчета на базе класса функций КУБ() представляет определенную проблему, которая серьезно усугубляется внутренними ограничениями данного интерфейса. Функции КУБ() ориентированы на отображение одномерных данных. Поэтому полностью показать с их помощью кортежи из нескольких элементов не получится.

Тем не менее данная задача успешно решается. В предыдущей статье мы обсудили идею, которая вкратце сводилась к размещению в одном отчете нескольких экземпляров функции КУБПОРЭЛЕМЕНТ(). При этом используемые в функциях наборы должны отличаться лишь порядком следования измерений в кортеже. В MDX нет оператора, позволяющего напрямую переставлять элементы в кортежах. Однако желаемую операцию все­таки можно выполнить посредством «обходного маневра» — с помощью оператора извлечения множеств EXTRACT(). Покажем, как эти знания можно применить на практике.

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

На рис. 1 исходные элементы измерений представлены двумя группами пользовательских элементов формы типа «флажок». Каждая из групп замкнута на собственный диапазон ячеек, в котором определяющим является столбец G. В нем в зависимости от выбора пользователя, стоит наименование соответствующего элемента измерения либо заглушка “ Null ”. Обращаем внимание, что если в группе измерений содержится несколько иерархий, то наименования элементов следует указывать полностью. В противном случае MDX-выражения, создаваемые на следующих этапах, могут отрабатывать некорректно. Поэтому на рис. 1 названия месяцев приводятся с префиксом “ [Месяц]. ”.

Рис. 1. Исходный отчет

Затем названия элементов, хранящиеся в отдельных ячейках, при помощи функции Сцепить() объединяются в одно выражение набора. Например, набор, состоящий из выбранных пользователем месяцев, можно сформировать при помощи выражения 1.

Поиск по сайту: