Создание OLAP-клиентов с помощью Excel и Microsoft PivotTable Services
PivotTable Services с точки зрения пользователя
Объекты, применяемые при работе с PivotTable Services
Применение PivotTable Services в приложениях
Создание сводной таблицы на основе запроса к реляционной базе данных
Создание и просмотр локальных OLAP-кубов
Чтение локальных и серверных OLAP-кубов
Создание и сохранение сводных диаграмм
В двух статьях, посвященных применению Web-компонентов Microsoft Office (см. компакт-диски к № 11 и 12’2000), мы рассмотрели использование компонента PivotTable List для анализа, фильтрации и суммирования данных, получаемых из различных источников, а также для представления их в удобном для интерпретации виде. Этот компонент позволил нам реализовать простейшую OLAP-функциональность в различных бизнес-приложениях.
В настоящей статье мы продолжим рассмотрение реализации OLAP в бизнес-приложениях. В качестве OLAP-инструмента будем использовать Microsoft Excel, а в качестве средства разработки приложения, которое им управляет, — Borland Delphi (для этой цели подойдет любая версия этого продукта начиная с 3.0). Однако для решения подобных задач подойдет и любое другое средство, поддерживающее COM и механизмы Automation.
Мы начнем с обзора некоторых особенностей PivotTable Services — библиотек, применяемых как самим Excel, так и другими приложениями при создании многомерных баз данных. PivotTable Services представляет собой клиентский механизм кэширования и вычислений, позволяющий улучшить производительность и снизить трафик в сети, а также выполнять анализ после отключения от корпоративной сети. Библиотеки PivotTable Services доступны, в частности, с помощью автоматизации Microsoft Excel.
PivotTable Services с точки зрения пользователя
Для начала напомним, что сводная таблица Excel (pivot table) представляет собой интерактивную таблицу, применяемую для суммирования или статистического анализа большого количества исходных данных, обычно содержащихся в одном из диапазонов ячеек Excel либо являющихся результатом запроса к какой-либо базе данных. Строки и столбцы подобной интерактивной таблицы базируются на данных из нескольких столбцов исходной таблицы. Ячейки сводной таблицы представляют собой суммы значений одного из числовых полей исходной таблицы. При этом суммирование производится для тех строк исходной таблицы, в которых значения столбцов, образовавших столбцы и строки сводной таблицы, совпадают с именами строки и столбца сводной таблицы, на пересечении которых находится данная ячейка. В некоторых публикациях сводные таблицы иногда называют перекрестными таблицами, или кросс-таблицами (crosstabs).
Каким образом можно получить в Excel сводную таблицу? Для этого следует выбрать пункт меню Data | PivotTable and PivotChart Report (Данные | Сводная таблица). После этого управление будет передано мастеру PivotTable and PivotChart Wizard (Мастер сводных таблиц). Используя этот мастер, можно указать источник данных (который иногда называется таблицей фактов — fact table). Им может быть как диапазон ячеек самого Excel, так и результат запроса к внешней базе данных, доступной через интерфейсы OLE DB или ODBC. При этом можно выбрать поля таблицы, участвующие в формировании результата запроса, или создать многотабличный запрос с помощью Microsoft Query.
Далее пользователь должен определить, какие из полей будут участвовать в формировании строк, столбцов и страниц и какие из них будут использованы для вычисления суммарных значений, при этом в случае применения нечисловых полей в качестве суммируемых можно вычислить количество соответствующих записей в исходной таблице. Осуществить это можно путем перетаскивания мышью соответствующих наименований полей с панели инструментов PivotTable на сводную таблицу (рис. 1).
Отметим, что список полей исходной таблицы появляется на панели инструментов PivotTable в тот момент, когда фокус ввода перенесен на сводную таблицу, для чего достаточно щелкнуть по ней мышью (при условии, что эта панель инструментов не закреплена), и исчезает после завершения ее редактирования.
В отличие от компонента PivotTable List данные из исходной таблицы или запроса в самой сводной таблице показать нельзя. Тем не менее фактические данные из исходной таблицы получить все-таки возможно. Делается это с помощью пункта контекстного меню Group and Online | Show Detail (Группа и структура | Отобразить детали) конкретной ячейки, при этом фактические данные помещаются на вновь созданный лист рабочей книги.
Как мы уже отмечали выше, Excel PivotTable Services позволяет создавать сводные таблицы на основе диапазонов Excel и запросов к базам данных. Помимо этого сводные таблицы можно создавать также на основе OLAP-кубов, полученных с помощью Microsoft SQL Server 7.0 или 2000 либо с помощью других OLAP-серверов, доступных через OLE DB или ADO. В случае когда основой для создания сводной таблицы является OLAP-куб, PivotTable Wizard не позволит переносить любое из полей в любую область — в этом случае поля, которые можно использовать для формирования строк, столбцов и фильтров, и поля, предназначенные для суммирования, уже определены заранее в соответствии с тем, к какому типу OLAP-данных они принадлежат. Если сводная таблица основана на OLAP-кубе, отображение исходных данных также недоступно, то в OLAP-кубах этих данных просто нет.
Следует отметить, что Excel и сам может создавать локальные OLAP-кубы, сохраняемые в файлах *.cub, либо описания кубов, сохраняемые в файлах *.oqy и используемые при пересчете куба на основе исходной базы данных (такое описание содержит сведения об источнике данных, запрос, использовавшийся при создании куба, а также сведения о его структуре).
В общем случае OLAP-куб представляет собой структуру, содержащую многомерные OLAP-данные, то есть измерения (dimensions) — описательные данные, составляющие оси многомерного куба, и меры (measures) — суммируемые количественные величины. Измерения могут содержать многоуровневые иерархии значений, а меры являются агрегатными данными (суммы, средние, максимальные или минимальные значения, количество записей) на основе полей реляционной СУБД, представляющих интерес с точки зрения статистического анализа. Создавая OLAP-куб на основе базы данных или запроса, мы преобразуем «плоские» наборы данных в многомерные.
В большинстве случаев OLAP-кубы создаются в многомерных базах данных, управляемых OLAP-серверами (такими как Microsoft SQL Server). Однако иногда бывает удобно создать локальное многомерное хранилище данных в виде файла, в котором данные организованы подобным способом. Это можно сделать с помощью все тех же Microsoft PivotTable Services, например непосредственно из Excel либо из другого приложения, применяющего Microsoft PivotTable Services. Отметим, что OLAP-сервер для создания или использования такого OLAP-куба не требуется.
Вернемся на несколько шагов назад, к процессу создания локального куба с помощью Excel. После создания запроса к базе данных пользователь может вместо перехода к редактированию сводной таблицы выбрать опцию создания локального OLAP-куба. После этого управление будет передано мастеру создания локальных кубов — OLAP Cube Wizard. Этот эксперт позволяет создать локальный OLAP-куб, отвечая на вопросы последовательно появляющихся диалогов.
Однако, несмотря на удобство и простоту использования мастеров создания сводных таблиц и локальных кубов, использование PivotTable Services по-прежнему остается процедурой, сложной для многих пользователей Excel, поскольку в этом случае от них требуется набор весьма нетривиальных знаний о структуре исходной базы данных, именах таблиц и полей, типах данных, а также представлений о том, какими должны быть измерения будущего куба, что представляет собой их иерархическая структура и какие поля следует выбрать в качестве его мер. Поэтому во многих случаях наилучший способ предоставить пользователям возможность анализа данных и при этом уберечь их от всех этих сложностей — это создавать приложения, применяющие автоматизацию Excel (краткое введение в автоматизацию Excel можно найти в статье Наталии Елмановой «Автоматизация приложений Microsoft Office в примерах, часть 1», см. компакт-диск № 11’2000). Ниже мы покажем, как это сделать. В частности, мы рассмотрим, как создавать в приложениях сводные таблицы и локальные OLAP-кубы, как читать готовые локальные и серверные OLAP-кубы, как создавать диаграммы, основанные на сводных таблицах и OLAP-кубах, и как сохранять эти диаграммы в виде графических файлов.
 |
|
Объекты, применяемые при работе с PivotTable Services
Перед тем как создавать приложения с использованием PivotTable Services, мы должны понять, какие объекты Excel следует для этого применять. Небольшая часть объектной модели Excel, применяемая для управления PivotTable Services, показана на рис. 2.
Главный объект, применяемый при создании сводных таблиц, — объект PivotCache, предоставляющий интерфейс к кэшу в оперативной памяти, создаваемому для данных конкретной сводной таблицы (обратите внимание на то, что в объектной модели компонента PivotTable List такого или подобного ему объекта нет). Этот объект доступен как один из членов коллекции PivotCaches, получаемой при вызове метода PivotCaches объекта Excel Workbook. Для создания нового объекта PivotCache нам следует применить метод Add коллекции PivotCaches, имеющий два параметра. Первый из них указывает на тип источника данных (листинг 1).
Второй параметр этого метода необязателен. Он может представлять собой диапазон ячеек Excel или комбинацию из так называемой строки связи ADO (ADO connection string) и имени источника данных. Этот параметр не требуется, если первый параметр равен xlExternal и, следовательно, не важен для наших будущих примеров — в них мы будем использовать только внешние источники данных.
Наиболее важным из методов объекта PivotCache является метод CreatePivotTable, который создает сводную таблицу (и добавляет новый объект PivotTable в коллекцию PivotTables объекта Worksheet), основанную на источнике данных, определенном в следующих свойствах объекта PivotCache (табл. 1).
Метод CreatePivotTable имеет два параметра. Первый из них указывает на левую верхнюю ячейку будущей сводной таблицы, второй определяет имя объекта PivotTable, например:
PC.CreatePivotTable (WB.Worksheets[1].Cells[3,1],’PivotTable1');
Способ создания сводных таблиц или чтения OLAP-кубов следующий: сначала мы создаем объект PivotCache, определяем его свойства Connection, CommandType и CommandText, а затем вызываем его метод CreatePivotTable. Ниже мы более подробно расскажем, как это сделать.
|
|
Применение PivotTable Services в приложениях
Во всех последующих примерах (которые можно найти на нашем CD-ROM) мы будем использовать компонент Delphi TOleContainer со страницы System палитры компонентов Delphi, применяемый обычно в качестве контейнера OLE-документов (подавляющее большинство средств разработки, поддерживающих COM, содержит подобные компоненты или объекты). В этом случае мы можем отобразить окно Excel непосредственно на форме Delphi, а также решить, хотим ли мы предоставить пользователю доступ к меню и инструментальной панели Excel (в последнем случае необходимо просто поместить на форму компоненты TPanel и TMainMenu).
Первое, что следует сделать, — это создать экземпляр приложения Excel внутри OLE-контейнера:
var WB : Variant; //объект Workbook PC : Variant; //объект PivotCache PT : Variant; //объект PivotTable ... // Создаем объект в OLE-контейнере OleContainer1.CreateObject(‘Excel.Sheet’,False); WB := OleContainer1.OleObject;
Следующим шагом будет создание нового объекта PivotCache:
// Добавляем новый объект PivotCache PC := WB.PivotCaches.Add(xlExternal);
Затем нам нужно установить значения свойств Connection, CommandType и CommandText объекта PivotCache и создать сводную таблицу. Эти свойства зависят от типа источника данных. В следующем разделе данной статьи мы расскажем более подробно, как это делается в случае различных источников данных.
|
|
Создание сводной таблицы на основе запроса к реляционной базе данных
Для создания сводной таблицы, основанной на запросе к базе данных, мы будем использовать ODBC-источник, указывающий на базу данных NORTHWIND из комплекта поставки Microsoft Access или Microsoft Office Professional (в нашем случае источник данных называется NW):
const //ODBC-источник для создания сводных таблиц ODBC_DSN = ‘NW’; … PC.Connection := ‘ODBC;DSN=’+ODBC_DSN;
Поскольку сводная таблица будет основана на запросе, его текст и будет значением свойства CommandText объекта PivotCache (листинг 2). Затем мы создаем сводную таблицу (листинг 3).
После этого у нас есть два способа продолжить работу. Мы можем позволить пользователям переносить мышью поля на сводную таблицу или можем сделать это программно, избавив пользователя от ручного формирования сводной таблицы. Давайте посмотрим, как можно реализовать второй вариант.
Для любого поля в запросе к базе данных, на котором будет основана сводная таблица, мы можем определить свойство Orientation соответствующего объекта PivotField, доступного с помощью коллекции PivotFields (листинг 4). Возможные значения свойства Orientation приведены в листинге 5.
Если в области строк, столбцов, фильтров или суммируемых данных сводной таблицы более одного поля, мы можем расположить их в удобном для нас порядке (например, в соответствии с уровнями той или иной иерархии данного измерения), используя свойство Position объекта PivotField (листинг 6).
В результате выполнения приведенных выше фрагментов кода внутри OLE-контейнера появится сводная таблица с соответствующими строками, столбцами и страницами (рис. 3).
В этом примере мы создали сводную таблицу, основанную на запросе к базе данных. В следующем примере мы покажем, как создать локальный OLAP-куб, основанный на подобном запросе.
|
|
Создание и просмотр локальных OLAP-кубов
Для создания и просмотра локального OLAP-куба, основанного на запросе к базе данных, необходимо создать объект PivotCache (с помощью метода Add коллекции PivotCaches):
PC:=WB.PivotCaches.Add(xlExternal);
Этот объект будет содержать данные OLAP-куба, который мы собираемся создать.
Далее следует сформировать строку, содержащую параметры, необходимые для создания куба и доступа к нему:
- Определение параметров соединения, указывающих на будущий куб и источник
фактических данных для него, например:
OLEDB;Provider=MSOLAP; Initial Catalog=[OCWCube]; Data Source=c:\data\northwind.mdbОстальные части этой строки отвечают за создание куба с помощью PivotTable Services.
- Предложение CREATE CUBE, в котором описываются измерения куба и уровни их иерархии, а также меры куба. В общем случае оно имеет вид, показанный в листинге 7.
- Предложение INSERT INTO, имеющее в общем случае следующий вид:
- Запрос SQL SELECT, возвращающий набор данных, используемых для создания куба.
InsertInto=INSERT INTO OCWCube
(
<Dimension Name>.<Level Name>,
[<Dimension Name>.<Level Name>,…]
<Measure Name>,
[<Measure Name>,… ]
)
OPTIONS ATTEMPT_ANALYSIS
Пример кода для создания локального куба представлен в листинге 8.
Подробное описание синтаксиса предложений CREATE CUBE и INSERT INTO можно найти в SQL Server 2000 Books Online, в разделе, посвященном программированию Analysis Services.
Отметим, что файл *.oqy, формируемый с помощью Microsoft Query, содержит строку, которую мы только что сформировали.
Теперь мы можем установить свойства Connection, CommandType и CommandText вновь созданного объекта PivotCache:
//Определим свойства объекта PivotCache PC.Connection := Connstr; PC.CommandType := xlCmdCube; PC.CommandText := ‘OCWCube’;
И наконец, нам следует определить, какие поля нужно использовать для формирования строк, столбцов, страниц и какие меры необходимо отобразить в области данных (листинг 9).
В данном случае мы воспользовались коллекцией CubeFields объекта PivotTable. Обратите внимание на то, что при создании приложения с помощью Delphi обратиться к членам коллекции CubeFields по имени нельзя, поэтому приходится использовать их порядковые номера в коллекции (отметим, что при использовании Visual Basic такой проблемы нет).
Сводная таблица, основанная на вновь созданном кубе, показана на рис. 4.
Обратите внимание: в данном случае в качестве полей для формирования столбцов, строк и фильтров (страниц) мы можем использовать только измерения куба, а в качестве полей для формирования области данных — меры куба (в данном случае это поле SUM of ExtendedPrice). Эти ограничения, как уже было сказано выше, являются следствием отсутствия фактических данных в OLAP-кубах.
|
|
Чтение локальных и серверных OLAP-кубов
Чтение уже созданных OLAP-кубов представляет собой гораздо более простую задачу, нежели их создание программным путем, — в этом случае нет необходимости в описании измерений, уровней иерархии, суммируемых данных, а также источников данных, на основе которых создается куб.
Для открытия локального куба нам нужно определить параметры соединения для доступа к кубу. В число этих параметров входят имя OLE DB-провайдера (в данном случае это OLE DB Provider for OLAP Services) и местоположение *.cub-файла (листинг 10).
Для открытия OLAP-куба, созданного с помощью Microsoft SQL Server 7.0 OLAP Services, нам нужно определить строку связи для доступа к OLAP-серверу и имя OLAP-куба:
PC.Connection := ‘OLEDB; Provider=MSOLAP; ‘+ ‘Data Source=maindesk;Initial Catalog=FoodMart; ‘ PC.CommandType := xlCmdCube; PC.CommandText := ‘Sales’;
Если куб создан с помощью Microsoft SQL Server 2000 Analysis Services, строка связи выглядит так:
PC.Connection := ‘OLEDB;Provider=MSOLAP.2;’+ ‘Data Source=maindesk;Initial Catalog=FoodMart 2000; ‘ PC.CommandType := xlCmdCube; PC.CommandText := ‘OCWCube’;
Все остальные действия аналогичны тем, что мы делали в предыдущем случае; мы можем позволить пользователю сформировать строки, столбцы, фильтры и отображаемые суммируемые данные вручную, а можем сделать это программно (листинг 11).
Как и в случае локальных кубов, отображение исходных фактических данных в такой сводной таблице невозможно: серверные кубы, как и локальные, фактических данных не содержат.
|
|
Создание и сохранение сводных диаграмм
И последнее, что нам хотелось бы рассмотреть в данной статье, это создание диаграмм на основе сводных таблиц. Это несложная задача — требуется только добавить элемент в коллекцию Charts объекта Workbook и определить тип диаграммы, например:
const xlColumnClustered = $00000033; ... WB.Charts.Add; WB.Charts[1].ChartType := xlColumnClustered;
Полученная диаграмма изображена на рис. 5.
Может возникнуть вполне закономерный вопрос: почему при создании диаграммы мы не указали диапазон ячеек, из которого для нее берутся данные? Дело в том, что если сводная таблица Excel в данный момент активна (то есть имеет фокус ввода), она по умолчанию становится источником данных для будущей диаграммы. Однако в общем случае при создании диаграмм диапазон, безусловно, должен быть выбран.
Приведенный ниже фрагмент кода иллюстрирует, как сохранить диаграмму в графическом файле формата BMP:
var BMP: TBitmap; ... if SaveDialog2.Execute then begin //Копируем в буфер обмена область диаграммы WB.Charts[1].ChartArea.Copy; //Создаем объект TBitmap BMP:=TBitmap.Create; //Помещаем в него содержимое буфера обмена BMP.LoadFromClipboardFormat(cf_BitMap, ClipBoard.GetAsHandle(cf_Bitmap),0); //Сохраняем объект TBitmap в файле BMP.SaveToFile(SaveDialog2.FileName); //Освобождаем ресурсы BMP.Free; end;
В этом примере мы копируем график в буфер обмена, используя метод Copy объекта ChartArea. Затем мы создаем объект TBitmap, помещаем в него содержимое буфера обмена и сохраняем этот объект в файле.
|
|
Заключение
В данной статье мы рассмотрели, как можно применять PivotTable Services в приложениях с помощью автоматизации Excel. Теперь мы знаем, как создать сводную таблицу, основанную на запросе к базе данных, создать и прочесть локальный OLAP-куб и сформировать строки, столбцы, фильтры и суммируемые данные в сводной таблице. Помимо этого мы также выяснили, как получить сводную таблицу, основанную на OLAP-кубах, созданных с помощью Microsoft SQL Server OLAP Services или Microsoft SQL Server 2000 Analysis Services. Наконец, мы рассмотрели, как создать сводную диаграмму и сохранить ее в графическом файле.
В дальнейшем мы продолжим рассмотрение OLAP. Нашей следующей темой будет применение объектов Decision Support Objects (DSO), доступных пользователям Microsoft SQL Server и применяемых для создания OLAP- и Data Mining-решений.
КомпьютерПресс 12'2000
