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

Альтернативные интерфейсы доступа к многомерным данным

Читайте также:
  1. E. интерпретирование аналитических результатов по конкретно заданным вопросам правоохранительных органов или суда.
  2. Альтернативные действия
  3. АЛЬТЕРНАТИВНЫЕ НАИМЕНОВАНИЯ ПЕРВИЧНЫХ ПАР
  4. АЛЬТЕРНАТИВНЫЕ ОБЪЯСНЕНИЯ ЭФФЕКТА МЕТОДА СКРЫТЫХ ВОПРОСОВ
  5. Альтернативные технологии
  6. АЛЬТЕРНАТИВНЫЕ ЦЕЛИ ФИРМЫ
  7. Аудит доступа к объектам
  8. Базовые средства манипулирования данными в языке SQL.
  9. Было ли нападение гитлеровцев действительно неожиданным для Сталина и его ближнего окружения?
  10. В3. В случае несоответствия показателей нормативным значениям с ухудшения финансовая устойчивость организации по данным может быть определена как «неудовлетворительно».
  11. Внутренние и внешние интерфейсы ПК

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

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

Покажем, как с помощью методов КУБ() можно составить интересующий нас отчет. Начнем с создания обычного сводного отчета на базе OLAP-куба (рис. 3).

 

Рис. 3. Сводный отчет на базе OLAP-куба

Для удобства дальнейшего восприятия при помощи цветового форматирования разделим столбцы отчета, относящиеся к фактическим и плановым значениям. Далее на вкладке Параметры в группе Сервис выберем подгруппу Средства OLAP и нажмем на кнопку Преобразовать в формулы. Поскольку в макете нашего отчета присутствует область фильтров, откроется диалоговое окно Преобразование в формулы (рис. 4).

 

Рис. 4. Диалоговое окно «Преобразование в формулы»

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

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

 

Рис. 5. Результат преобразования в формулы

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

Оставшаяся часть таблицы состоит из набора формул двух типов. Элементы из полей строк и столбцов сводной таблицы (измерения OLAP-куба) были преобразованы в формулы КУБЭЛЕМЕНТ(), а поля из области значений (мера OLAP-куба) — в формулы КУБЗНАЧЕНИЕ().

Каждая из формул имеет весьма простую и ясную структуру. Рассмотрим для начала синтаксис формулы КУБЗНАЧЕНИЕ():

КУБЗНАЧЕНИЕ (подключение, выражение_элемента1, выражение_элемента2…)

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

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

На рис. 5 представлена формула КУБЗНАЧЕНИЕ(), рассчитывающая объем выручки по услуге «Интернет — Доступ» за февраль для дирекции № 1. Видно, что выражениями элементов являются ссылки на обычные ячейки листа Microsoft Excel. Примечательно, что текстовые строки в этих ячейках можно заполнять по-разному. Базовый вариант заключается в применении функций КУБЭЛЕМЕНТ(), но при необходимости можно использовать и любой другой способ.

Для примера заменим формулы КУБЭЛЕМЕНТ() элементов измерения «План_Факт» строчными значениями — для этого в первых трех колонках наберем «[Факт]», а последних трех — «[План]». Как видно из рис. 5, подобная операция сразу приводит отчет к асимметричному виду — формату, который был целью данной статьи.

Повторимся еще раз, аргументами функции КУБЗНАЧЕНИЕ() являются ячейки листа, что означает возможность выполнения над ними любых стандартных операций Microsoft Excel. Например, работу с измерением «План_Факт» можно значительно упростить при помощи опции проверки данных. Ограничим множество допустимых значений ячейки перечнем элементов (вкладка Данные → меню Работа с даннымиПроверка данных). При помощи такой несложной операции в отчете будет достигнут эффект поля с раскрывающимся списком, а сам отчет при этом приблизится по функциональности к привычным сводным таблицам (рис. 6).

 

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

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

Рис. 6 демонстрирует, как данная идея может быть воплощена на практике. Сначала фильтры исходного отчета были размещены рядом с областью данных отчета, затем весь полученный диапазон был скопирован и повторно помещен на лист книги Microsoft Excel. Формулы нижнего раздела были сориентированы на использование соответствующих фильтров.

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

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

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

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

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

КУБЭЛЕМЕНТ(подключение, выражение_элемента, подпись)

Первый аргумент данного оператора, так же как и в случае с КУБЗНАЧЕНИЕ(), устанавливает параметры подключения к аналитическому кубу. Затем следует текстовая строка Выражение_элемента, описывающая многомерное выражение, которое задает координаты элемента в кубе. Завершает запись аргумент Подпись, в котором, при необходимости, можно определить строку для показа ее в отчете вместо имени текущего элемента измерения.

Такая возможность может быть весьма полезна на практике. В первую очередь она позволяет гибко настраивать отчет под нужды конкретного пользователя. Обратимся еще раз к нашему примеру, представленному на рис. 6. Допустим, нам не нравится, как названы услуги в аналитическом кубе, и мы хотим для сервиса «Абонентская плата» использовать более сжатое написание — «Абон. плата». Для этого достаточно дополнить исходную формулу текстовой строкой КУБЭЛЕМЕНТ(«OLAP_Connection»;»[Сервис].[Сервис].&[1]»; «Абон. плата»). Как видно из рисунка, проделанная операция никак не повлияла на расчет численных значений отчета. Дело в том, что значением функции КУБЭЛЕМЕНТ в случае ее вызова другими функциями КУБ() является элемент, определяемый многомерным выражением (второй аргумент КУБЭЛЕМЕНТ), а не то значение, которое показывается в ячейке листа.

Истинная мощь формулы проявляется, когда выражение элемента представляет собой не единственную координату, а кортеж — набор из нескольких координат на различных осях куба. Предположим, что в аналитическом отчете вместо элемента «Абон. плата», задаваемого формулой «[Сервис].[Сервис].&[1]», требуется разместить элемент «Абон. плата — план», определяющий плановое значение объема продаж для услуги «Абонентская плата» по текущему ракурсу. Понятно, что требуемый элемент задается кортежем ([Сервис].[Сервис].&[1], [План_Факт].[План_Факт].&[1]).

В Microsoft Excel для записи такого кортежа придется использовать довольно экзотический синтаксис:

{“[Сервис].[Сервис].&[1]”;”[План_Факт].[План_Факт].&[1]”}.

При этом функция КУБЭЛЕМЕНТ() в целом примет следующий вид:

=КУБЭЛЕМЕНТ(“OLAP_Connection”;{“[Сервис].[Сервис].&[1]”;”[План_Факт].[План_Факт].&[1]”}; “Абон. плата - план”).

Условно говоря, последний вызов эквивалентен созданию внутри программы Microsoft Excel вычисляемого элемента «Абон. плата — план», то есть конструкции вида:

with member [Сервис].[Сервис].[Абон. плата - план] as ([Сервис].[Сервис].&[1],[План_Факт].[План_Факт].&[1])

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

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

Создадим на листе книги именованный диапазон «Кортеж» и построчно заполним его формулами, задающими координаты элемента на отдельных измерениях (рис. 7).

 

Рис. 7. Заполнение именованного
диапазона формулами, задающими координаты
элемента на отдельных измерениях

В совокупности данные выражения определяют кортеж, вычисляющий плановую выручку в январе по услуге «Абонентская плата». Подстановка имени диапазона в формулу КУБЭЛЕМЕНТ(«OLAP_Connection»; Кортеж; «Абон. плата (план)») дает возможность сослаться на нее в отчете Microsoft Excel. При этом, когда в формуле явно указан параметр Подпись, он отображается в ячейке на листе книги Microsoft Excel. Если же такого параметра нет, в ячейке показывается последний элемент кортежа. Таким образом, если формулу КУБЭЛЕМЕНТ() написать в сокращенном виде: КУБЭЛЕМЕНТ(«OLAP_Connection»; Кортеж), то в ячейке отчета будет показываться «Янв», что соответствует последнему элементу из списка «Кортеж» — [Дата].[Месяц].&[1].

Следует отметить, что каждая строка в диапазоне «Кортеж» может быть произвольным MDX-выражением. Например, для формул на рис. 7 существует эквивалентное написание:

[Сервис].[Сервис].[All].FirstChild

[План_Факт].[План_Факт].&[1]

[Дата].[Дата].[Месяц].members.Item(0)

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

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

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

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


1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 |

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



Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Студалл.Орг (0.005 сек.)