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

Часть 5

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

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

Отображение пользовательских свойств в таблицах Microsoft Excel

В части 4 данной статьи мы завершили обзор основных функций семейства КУБ(). С помощью базовых операторов КУБЭЛЕМЕНТ() и КУБЗНАЧЕНИЕ() в книге Microsoft Excel можно составить отчет по результатам работы практически любого типового MDX-запроса.

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

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

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

Как известно, базовой категорией OLAP от Microsoft Microsoft является измерение (Dimension), которое состоит из набора связанных между собой атрибутов и, как правило, ассоциируется с отдельной таблицей измерения (dimension table) в базе данных. Таблица измерения связана с таблицей фактов, в которой хранятся значения мер аналитического куба. Каждый атрибут измерения представляет собой отдельную иерархию, а кроме того, в измерении возможно создание дополнительных иерархий путем объединения по определенным правилам нескольких атрибутов в единую логическую структуру.

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

Перечисленные принципы работы с иерархиями проще всего пояснить на наглядном примере. Допустим, что в OLAP-кубе присутствует измерение [Дата], построенное на основе таб­лицы Dim_Time (рис. 1).

Рис. 1. Базовая таблица измерения Дата

Таблица Dim_Time имеет очень простую структуру и состоит всего из четырех атрибутов:

1. Month_N — номер месяца;
2. Месяц — название месяца;
3. Квартал — название квартала;
4. SF — сезонный фактор, вспомогательный атрибут, роль и назначение которого будут обсуждаться позже.

Самый важный среди атрибутов измерения — ключевой атрибут, посредством которого таблица измерения связывается с таблицей фактов. Будем считать, что в этой почетной роли выступает столбец Month_N. Заметим сразу, что все неключевые атрибуты таблицы напрямую зависят от данного столбца — по номеру месяца однозначно определяются названия месяца и квартала, а также параметр SF. Поэтому на базе любого столбца таблицы Dim_Time получится организовать самостоятельную иерархию. Создадим две иерархии: [Дата].[Квартал] и [Дата].[Месяц], каждая из которых будет состоять лишь из одного атрибута — названия квартала и месяца соответственно.

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

Рис. 2. Объединение в наборе двух иерархий одного измерения

Но если в следующем выражении попытаться дважды использовать одну и ту же иерархию, то оно уже выполняться не будет:

select {[Дата].[Квартал].[All].Children*[Дата].[Квартал].[All].Children} on 1,

{} on 0

from [PF]

При запуске этого выражения выдается ошибка В функции Crossjoin иерархия Квартал используется более одного раза.

Вернемся теперь к анализу рис. 1. Из представленной на нем таблицы видно, что атрибуты Месяц и Квартал связаны между собой отношением «один ко многим» (любой месяц относится к одному кварталу, а название квартала, в свою очередь, однозначно определяется указанием соответствующего месяца). Данный тривиальный факт допускает различные интерпретации.

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

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

Отметим, что связь между атрибутами Месяц и Квартал обладает еще одной существенной особенностью — она остается неизменной с течением времени. Январь будет относиться к I кварталу как в 2011 году, так и во все последующие годы. Такие связи называют жесткими — rigid relations. Ясно, что в общем случае такое условие может и не выполняться. К примеру, работник со временем может переходить из отдела в отдел, но при этом в каждый отдельный промежуток времени он работает только в одном подразделении. Иными словами, иерархия «сотрудник — отдел» всегда остается естественной, но при этом может динамически меняться ее структура. Подобные связи называют гибкими — flexible relations.

Наконец, атрибуты могут быть связаны между собой отношением «многие ко многим». Типичным примером здесь являются характеристики «возраст» и «пол». С позиций OLAP данный тип отношения означает отсутствие связей. Но отсутствие связей вовсе не означает, что атрибуты невозможно организовать в -иерархию. При необходимости поля «Возраст» и «Пол» можно свести в иерархии «пол — возраст» и «возраст — пол». Такие иерархии называются нерегламентированными или иерархиями отчетов. Для бизнес-пользователей они совершенно не отличаются от естественных, хотя и обладают гораздо худшей производительностью.

Если логические связи в исходных данных присутствуют, они определяются на вкладке Attribute relationships конструктора измерений среды Business Intelligence Development Studio. На рис. 3 показаны возможные связи для атрибутов таблицы, представленной на рис. 1.

Рис. 3. Установление связей между атрибутами измерения

Жесткий тип связей отображается на диаграмме стрелками черного цвета, а гибкий — белого. По умолчанию каждой связи присваивается тип flexible, но разработчик OLAP-куба имеет возможность изменить его на rigid, если уверен, что связь действительно таковой является. Несмотря на то что все связи между атрибутами таблицы жесткие, для примера они разделены на схеме на два типа.

Каждая стрелка представляет собой отдельную связь. Из рис. 2 видно, что атрибуты Квартал и SF связаны с ключевым полем Month_N двумя связями — напрямую, а также транзитивно через атрибут Месяц.

Аналитический сервер осознает, что одна из связей каждого атрибута является избыточной и поэтому помечает связи Month_N-SF и Month_N-Квартал признаком redundant. На схеме они отображаются стрелками синего цвета. Избыточные связи не являются ошибкой дизайна и не блокируют процессинг куба.

Что же нам дают связи между атрибутами? Как уже отмечалось, они позволяют создавать естественные иерархии. В нашем случае из атрибутов Квартал и Месяц можно собрать двухуровневую иерархию Дата (рис. 4).

Рис. 4. Двухуровневая иерархия Квартал — Месяц

Данная иерархия позволяет производить навигацию между временными периодами, используя элементы сразу из двух столбцов табл. 1. В следующем выражении, к примеру, выбирается квартал, соответствующий указанному месяцу:

select {ancestor([Дата].[Дата].[Месяц].[Янв],[Дата].[Дата].[Квартал])} on 0

from [PF]

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

Связь «один ко многим» между атрибутами Месяц и Квартал можно интерпретировать по-разному. Если не обращать внимания на совпадение кварталов для различных месяцев, мы можем считать ее связью «один к одному», в которой каждому месяцу однозначно соответствует квартал. Иными словами, если не рассматривать связь между атрибутами с позиций создания иерархий, атрибут Квартал является обычным свойством атрибута Месяц.

У всех атрибутов измерения есть целый набор системных свойств. В MDX-запросах к ним можно обращаться посредством функции Properties(). Следующее выражение выводит в отчете имя элемента, его идентификатор и значение.

with member Measures.[Name] AS [Дата].[Месяц].[Фев].properties(‘Name’)

            member Measures.[ID] AS [Дата].[Месяц].[Фев].properties(‘ID’)

            member Measures.[Member_Value] AS [Дата].[Месяц].[Фев].properties(‘Member_Value’)

            member Measures.[Квартал] AS [Дата].[Месяц].[Фев].properties(‘Квартал’)

select {Measures.[Name]

            , Measures.[ID]

            , Measures.[Member_Value]

            , Measures.[Квартал]} on 0

from [PF]

Как мы уже поняли, задание связей между атрибутами позволяет определить дополнительные свойства атрибута измерения. Свойства, которые создаются на этапе проектирования OLAP-куба, называются user defined. Связь Месяц ® Квартал на рис. 2 создает новое свойство Квартал для атрибута Месяц. По своей функциональности оно ничем не отличается от системного свойства. К нему, в частности, тоже можно обратиться посредством функции Properties(), что наглядно показывает рис. 5.

Рис. 5. Различные свойства элемента
«Фев» из иерархии Месяц

Пользовательские свойства позволяют обращаться к атрибутам, используемым при составлении иерархий. На самом деле реальная ценность подобных действий весьма ограниченна: если есть потребность совместного применения атрибутов, их следует сводить в одну иерархию. Мы использовали связь Месяц ® Квартал только для наглядной демонстрации самой идеи пользовательских свойств.

Основная область применения пользовательских свойств лежит в ином классе задач. Они являются тем базисом, на котором в OLAP-кубах держатся процедуры бизнес-логики. Дело в том, что OLAP-кубы используются не только для хранения многомерных данных, но и для моделирования бизнес-процессов организации. Практически это означает необходимость расчета внутри системы различных показателей. Атрибутов, которые применяются для составления иерархий, как правило, не хватает для реализации полноценных вычислений.

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

• усилиями специалистов по продаже;
• сезонного фактора.

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

Будем считать, что для продаж выполняется следующая формула:

Продажи_Фактические = Продажи_Номинальные*Сезонный_Коэффициент.

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

Продажи_Номинальные= Продажи_Фактические/Сезонный_Коэффициент.

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

При проектировании измерений на базе каждого атрибута по умолчанию создается отдельная иерархия. Для атрибутов, в которых хранятся пользовательские свойства, она не несет какого-либо содержательного смысла. Чтобы скрыть наличие такого атрибута от пользовательских приложений, его свойству AttributeHierarchyVisible следует присвоить значение False. В качестве дополнительной меры безопасности можно исключить использование атрибута в многоуровневых иерархиях, для этого значение False следует присвоить свойству AttributeHierarchyEnabled.

После выполнения указанных операций атрибут маркируется серым цветом в конструкторе измерений среды Business Intelligence Development Studio (рис. 6). Подобное цветовое выделение технических полей значительно облегчает работу с измерениями, содержащими большое количество атрибутов.

Рис. 6. Атрибуты измерения Дата

После того как определена связь между атрибутами Месяц и SF, можно приступать к расчету величины номинальных продаж согласно преведенному ранее выражению.

В конструкторе кубов на вкладке Calculations добавим новый вычисляемый элемент (Calculated member) [Сумма_Норм], который разместим на оси Measures (рис. 7).

Рис. 7. Создание вычисляемого элемента Сумма_Норм

В качестве формулы, вычисляющей его значение, напишем следующее MDX-выражение:

[Measures].[Сумма]/[Дата].[Месяц].CurrentMember.Properties(‘SF’)

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

Созданные вычисляемые элементы в дальнейшем могут использоваться в пользовательских отчетах. В случае размещения на оси Measures в бизнес-приложениях они становятся неотличимы от обычных мер аналитического куба. В частности, элемент [Сумма_Норм] можно разместить в поле значений макета сводного отчета Microsoft Excel (рис. 8).

Рис. 8. Использование меры Сумма_Норм в сводном отчете

Из рис. 8 видно, что показатель [Сумма_Норм] рассчитывается для всех строк сводного отчета. Другими словами, приведенное выше выражение вычисляется для всех типов кортежей, представленных на оси Y:

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

([Дирекция].[Дирекция].[Дир_1],[Сервис].[Сервис].[All]),

([Дирекция].[Дирекция].[All],[Сервис].[Сервис].[All])

Заметим также, что свойство SF было определено для атрибута Месяц. Однако при составлении MDX-выражений нельзя напрямую обращаться к атрибутам измерений. Аргументами в формуле могут быть только иерархии измерения, поэтому в Выражении 6 для получения нужного значения свойства SF использовалась иерархия Месяц, созданная на базе одноименного атрибута.

В отчете на рис. 8 в области столбцов для отображения временных интервалов применяется уже другая иерархия измерения Дата — двухуровневая иерархия Дата, показанная на рис. 4.

Отображение пользовательских свойств в таблицах Microsoft Excel

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

Организовать доступ к пользовательским свойствам, хранящим численные значения, в среде Microsoft Excel можно двумя путями:

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

Рассмотрим сначала классический подход. В очередной раз воспользуемся конструктором кубов и через вкладку Calculations добавим в куб новый вычисляемый элемент, который назовем SF (рис. 9).

Рис. 9. Создание вычисляемого элемента для показа пользовательского свойства SF

Элемент SF описывается выражением [Дата].[Месяц].CurrentMember.Properties(‘SF’), смысл которого заключается в трансляции значений свойства SF на ось Measures. Для пользователя элемент SF выглядит как полноценная мера, которую, в частности, можно просматривать через сводные таблицы Microsoft Excel — достаточно разместить ее в области значений макета сводного отчета (рис. 10).

Рис. 10. Вывод значений свойства SF через сводный отчет

Рис. 10 подтверждает, что показатель SF не аддитивен — для него не рассчитываются промежуточные итоги.

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

КУБСВОЙСТВОЭЛЕМЕНТА( “Подключение”, “Выражение элемента”, “Свойство”).

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

Аргументы функции удобно задавать в режиме автозавершения формул, который включен по умолчанию для всех операторов семейства КУБ(). В данном режиме после ввода первых символов выражения Microsoft Excel отображает под ячейкой динамически раскрывающийся список возможных функций, имен и текстовых строк, соответствующих данным символам (рис. 11).

Рис. 11. Использование режима автозавершения формул

Список автозавершения показывается сразу после ввода в строку формулы одного из перечисленных ниже символов:

1. «”» (открывающие кавычки) в начале каждого аргумента.

Например:

• КУБСВОЙСТВОЭЛЕМЕНТА(« ;
• КУБСВОЙСТВОЭЛЕМЕНТА(«OLAP_Connection”;”.

2. «.» (точка) после закрывающей прямоугольной скобки.

Например:

• КУБСВОЙСТВОЭЛЕМЕНТА(“OLAP_Connection”; “[Дата].
• КУБСВОЙСТВОЭЛЕМЕНТА(«OLAP_Connection»;»[Дата].[Month_N].[All].

(ситуация воспроизведена на рис. 10).

3. «(» (открывающая круглая скобка) после открывающих кавычек в текстовой строке с многомерными выражениями, показывающая его начало.
4. «;» (точка с запятой) после закрывающей прямоугольной скобки в текстовой строке с многомерными выражениями, показывающая его вторую часть.
5. «{» (открывающая фигурная скобка) после открывающих кавычек в текстовой строке с многомерными выражениями, обозначающая начало набора.

Режим автозавершения можно включить самостоятельно в любой момент, например в процессе редактирования ранее составленных формул. Делается это следующим образом. Сначала на листе книги выбирается нужная ячейка и нажатием клавиши F2 переводится в режим отображения формул. Затем курсором мыши выделяется требуемый фрагмент функции и нажимается комбинация клавиш Alt + стрелка вниз. В результате пользователю будет показан динамический список, состав которого определяется исходя из выбранного фрагмента в строке формулы. На рис. 12 курсор указывает на слово Month_N.

Рис. 12. Включение режима автозавершения

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

В заключение статьи нам осталось рассмотреть несколько существенных моментов, возникающих при работе с функцией КУБСВОЙСТВОЭЛЕМЕНТА().

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

Рис. 13. Вывод нечисловых свойств

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

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

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