>Введение в OLAP: часть 6. Microsoft Excel как OLAP-клиент

Алексей Федоров,
Наталия Елманова, преподаватель УКЦ "Interface Ltd",
КомпьютерПресс 9'2001

Средства чтения OLAP-данных в Microsoft Office
Манипуляция OLAP-данными в Microsoft Excel
   Создание сводной таблицы с данными OLAP-кубов
   Манипуляция отображением данных в сводной таблице
   Создание сводных диаграмм с данными OLAP-кубов
   Создание локальных OLAP-кубов
Заключение

В предыдущей части данной статьи (Введение в OLAP: часть 5. Создание многомерных баз данных) мы рассмотрели процесс создания многомерных баз данных для Microsoft Analysis Services и содержащихся в них объектов, а также ознакомились с простейшим средством просмотра сечений кубов, встроенным в Analysis Manager. Этот способ работы с OLAP-данными - не единственный (и далеко не самый удобный, по крайней мере для конечного пользователя) из возможных на сегодняшний день. Помимо него существует немало других средств просмотра этих данных - от приложений Microsoft Office и входящих в его состав компонентов до многочисленных средств просмотра OLAP-данных, предлагаемых сторонними производителями. Разработчики могут создавать собственные приложения для работы с OLAP-данными - как с применением компонентов Microsoft Office, так и без них.

Мы начнем с рассмотрения одного из самых простейших способов работы с OLAP-данными - использования Microsoft Excel. Остальные способы работы с OLAP-данными будут рассмотрены в следующих номерах журнала.

Средства чтения OLAP-данных в Microsoft Office

Прежде чем обсуждать возможности Microsoft Excel как OLAP-клиента, кратко остановимся на компонентах Microsoft Office, используемых для работы с OLAP-данными, - это позволит нам в дальнейшем избежать терминологической путаницы. Тем более что все эти компоненты содержат в своем названии словосочетание PivotTable.

Первым из компонентов Microsoft Office, предназначенных для создания OLAP-клиентов, является набор библиотек PivotTable Service. С одной стороны, он является составной частью Analysis Services и выполняет роль связующего звена между Analysis Services и их клиентами (не обязательно имеющими отношение к Microsoft Office). PivotTable Service может быть установлен отдельно на компьютер, на котором эксплуатируются какие-либо клиенты Analysis Services; для его установки в состав Analysis Services входит отдельный дистрибутив. С другой стороны, PivotTable Service входит и в состав Microsoft Office 2000/XP и при этом может быть использован не только для работы с данными Analysis Services, но и для создания и чтения локальных OLAP-кубов, не имеющих отношения к Analysis Services, как с помощью Microsoft Excel, так и без него.

Вторым компонентом, который может быть использован для просмотра OLAP-кубов, является служба, называемая PivotTable Reports, - средство создания сводных таблиц Microsoft Excel. Это средство позволяет получать, сохранять в кэше в оперативной памяти и отображать на листах рабочих книг двухмерные и трехмерные наборы агрегатных данных на основе данных из реляционных СУБД и рабочих книг Excel. PivotTable Reports входит в Excel начиная с версии 5.0, но возможность считывать с помощью него данные из OLAP-кубов Analysis Services, равно как и создавать локальные OLAP-кубы, впервые появилась в Excel 2000. Отметим, что средство создания сводных таблиц Excel использует библиотеки PivotTable Services.

И наконец, третьим компонентом, применяемым при создании OLAP-клиентов, является PivotTable List - элемент управления ActiveX, входящий в состав Microsoft Office Web Components и предназначенный для просмотра сечений OLAP-кубов. Применяется он главным образом на Web-страницах, а иногда и в обычных Windows-приложениях (о применении его в Delphi-приложениях см. приложение CD-ROM к КомпьютерПресс © 12'2000).

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

Манипуляция OLAP-данными в Microsoft Excel

Как было отмечено выше, средства создания сводных таблиц Microsoft Excel хранят в кэше агрегатные данные, вычисленные на основе данных из реляционных СУБД или полученные от OLAP-серверов. Манипулируя сводной таблицей, пользователь может управлять отображением данных из этого кэша.

Прежде чем приступить к созданию примера, заметим, что посредством Microsoft Excel 2000 можно корректно отображать данные из OLAP-кубов, созданных с помощью Microsoft SQL Server 7.0 OLAP Services. Что касается OLAP-кубов, созданных с помощью Microsoft SQL Server 2000 Analysis Services, по большей части посредством Microsoft Excel 2000, то они также отображаются корректно, однако имеются и некоторые ограничения. Например, при создании локальных кубов OLAP или при сохранении сводной таблицы в виде Web-страницы с помощью соответствующих мастеров автоматически выбирается OLE DB-провайдер предыдущей версии (версии 7.0), не поддерживающий несбалансированные измерения. Это приводит к сообщениям об ошибках и к игнорированию таких измерений или даже всего источника данных.

При использовании же Microsoft Excel 2002 эти проблемы не возникают.

Создание сводной таблицы с данными OLAP-кубов

В качестве примера создадим сводную таблицу, содержащую данные OLAP-куба, созданного ранее (см. часть 5 этой статьи в КомпьютерПресс © 8'2001). Для этого запустим Microsoft Excel и из меню Data выберем PivotTable and PivotChart Report. После этого управление будет передано мастеру PivotTable and PivotChart Wizard. В первой диалоговой панели этого мастера укажем, что для построения сводной таблицы выбирается внешний источник данных, для чего выберем опцию External data source. Затем укажем, что это за источник, нажав кнопку Get Data в следующей диалоговой панели, что приведет к запуску приложения Microsoft Query. Далее выберем закладку OLAP Cubes и, если в операционной системе еще нет описания соответствующего источника данных, создадим его (рис. 1).

Рис. 1. Описание источника данных

В процессе создания источника данных укажем его имя, выберем OLE DB-провайдер (в нашем случае - Microsoft OLE DB Provider for OLAP Services 8.0, поскольку мы используем Microsoft SQL Server 2000 Analysis Services) и нажмем на кнопку Connect (рис. 2).

Рис. 2. Выбор провайдера данных

В диалоговой панели Multidimensional Connection укажем имя компьютера (если это локальный компьютер, можно использовать имя localhost), на котором расположен OLAP-сервер, а также данные для аутентификации пользователя, которые понадобятся только в том случае, если для связи с OLAP-сервером мы используем HTTP-протокол (рис. 3).

Рис. 3. Выбор OLAP-сервера

И наконец, выберем имя многомерной базы данных, в которой хранится OLAP-куб (рис. 4).

Рис. 4. Выбор многомерной базы данных

Определив источник данных, выберем куб, который мы будем отображать в сводной таблице (рис. 5).

Рис. 5. Выбор куба для отображения в сводной таблице

После этого можно нажать кнопку OK. В результате мы получим пустую сводную таблицу, вид которой в Excel 2000 показан на рис. 6.

Рис. 6. Сводная таблица в Excel 2000

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

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

При смещении фокуса ввода в другое место листа эти кнопки исчезают.

В Excel 2002 диалоговая панель PivotTable выглядит иначе - она не содержит кнопок с именами измерений и мер. Их список предоставляется в отдельной панели PivotTable Field List (рис. 7).

Рис. 7. Сводная таблица в Excel 2002

Теперь нам необходимо определить, какие из мер мы хотим отобразить в сводной таблице. Для этого достаточно перенести мышью кнопку (в случае Excel 2002 - соответствующий элемент из списка) с наименованием нужной меры в область данных (Data Area; на рис. 7 она обозначена надписью Drop Data Items Here). Результат этой манипуляции представлен на рис. 8.

Рис. 8. Выбор меры для отображения в сводной таблице

Теперь требуется определить, какие из полей будут участвовать в формировании строк, столбцов и страниц (иногда последние называются фильтрами). В общем случае сводная таблица является трехмерной, и можно считать, что третье измерение расположено перпендикулярно экрану, а мы наблюдаем сечения, параллельные плоскости экрана и определяемые тем, какая <страница> выбрана для отображения. Осуществить фильтрацию можно путем перетаскивания мышью соответствующих кнопок с панели инструментов PivotTable (в случае Excel 2002 - соответствующих элементов с панели PivotTable Field List) на области строк, столбцов и страниц сводной таблицы - Row Area, Column Area и Page Area. Результат этой манипуляции показан на рис. 9.

Рис. 9. Готовая сводная таблица

Итак, мы отобразили в сводной таблице Excel содержимое OLAP-куба. Теперь этим отображением можно манипулировать.

Манипуляция отображением данных в сводной таблице

Если нас интересуют более подробные данные, связанные с одним из членов одного из отображаемых измерений, можно дважды щелкнуть по ячейке с этим значением и отобразить члены следующего уровня данного измерения (эта операция называется drill-down). То, что получится, если дважды щелкнуть на ячейке A5, показано на рис. 10.

Рис. 10. Результат операции drill-down

Если же нас интересуют более подробные данные, нежели представленные в данный момент в сводной таблице, следует выбрать ячейку с именем соответствующего измерения (например, ячейку A4) и нажать на панели инструментов PivotTable кнопку Show Detail (рис. 11).

Рис. 11. Отображение следующего уровня иерархии измерения

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

Рис. 12. Выбор отображаемых членов измерения

Если в сводной таблице отображается несколько мер, они формируют отдельное дополнительное измерение Data. По умолчанию оно располагается на оси строк, но может быть перенесено и на ось столбцов (рис. 13).

Рис. 13. Отображение нескольких мер в сводной таблице

Если в сводной таблице оставить только одну меру, перенеся оставшиеся обратно на панель инструментов PivotTables, измерение Data исчезнет.

Отметим, что с помощью одного из доступных в Excel шаблонов оформления можно изменить оформление сводной таблицы. Кроме того, можно выбрать на панели инструментов PivotTables пункты меню PivotTable | Table Options или PivotTable | Field Settings и изменить другие параметры отображения данных в сводной таблице.

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

Создание сводных диаграмм с данными OLAP-кубов

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

Рис. 14. Сводная диаграмма с данными OLAP-куба

Отметим, что с помощью панелей инструментов PivotTable и PivotTable FieldList, а также выпадающих списков на осях и легенде можно управлять отображением данных на сводной диаграмме, например выполнять операцию drill-down; при этом сводная таблица будет меняться синхронно с диаграммой.

Создание локальных OLAP-кубов

Как уже было отмечено выше, Microsoft Excel позволяет создавать локальные OLAP-кубы, представляющие собой подмножества данных серверных OLAP-кубов. Локальные кубы хранятся в файлах с расширением *.cub. Напомним, что для корректного создания локального куба на основе серверного куба, содержащего несбалансированные измерения, рекомендуется применять Microsoft Excel 2002. Поэтому все последующие примеры выполнены в этой версии Microsoft Excel.

Чтобы создать локальный OLAP-куб на основе серверного куба, следует на панели инструментов PivotTables выбрать пункт меню PivotTable | Offline OLAP в Excel 2002 (в Excel 2000 ему соответствовал пункт меню PivotTable | Client-Server Settings) и нажать кнопку Create offline data file (рис. 15; в Excel 2000 - Create Local Cube).

Рис. 15. Диалоговая панель Offline OLAP Settings

Далее следует выбрать измерения и их уровни, а также меры, которые будут присутствовать в локальном кубе (рис. 16).

Рис. 16. Выбор измерений и мер для локального куба

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

Рис. 17. Выбор членов измерений для локального куба

Теперь осталось только сохранить локальный куб в файле с расширением *.cub. Отметим, что этот файл является отчуждаемым: его можно просматривать на любом компьютере, оснащенном как Microsoft Excel 2002, так и Microsoft Excel 2000, независимо от наличия на нем Microsoft SQL Server Analysis Services или их клиентской части.

Разработчики приложений, использующих описанную выше функциональность Microsoft Excel, могут обратиться к нашим ранее опубликованным статьям, посвященным этому вопросу (например, <Создание OLAP-клиентов с помощью Excel и Microsoft PivotTable Services>).

Заключение

Итак, мы рассмотрели один из простейших способов работы с OLAP-данными - применение в качестве OLAP-клиента приложений и компонентов Microsoft Office. Мы научились отображать OLAP-кубы c помощью средств создания сводных таблиц Microsoft Excel, манипулировать отображением данных в сводных таблицах, строить сводные диаграммы на основе OLAP-кубов, а также ознакомились с созданием локальных OLAP-кубов, содержащих подмножества данных серверного OLAP-куба.

* * *

Другие способы работы с OLAP-данными будут рассмотрены в следующих номерах журнала.