Введение в OLAP, часть 10. Применение PivotTable Service для создания локальных OLAP-кубов

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

Microsoft PivotTable Service
Расширения DDL и DML для создания локальных кубов
Предложение CREATE CUBE
Предложение INSERT INTO
Свойства Source_DSN, Data Source и Provider
Пример создания локального OLAP-куба
Поставка приложений, использующих PivotTable Service

Предыдущая статья данного цикла была посвящена созданию собственных OLAP-клиентов с помощью Visual Basic 6 и других средств разработки. В ней мы обсуждали два способа чтения данных из OLAP-кубов Microsoft SQL Server 2000 Analysis Services: способ, основанный на применении ADO (ActiveX Data Objects) и Microsoft OLE DB Provider for OLAP Services 8.0, и способ, основанный на применении ADO MD (ADO Multidimensional).

Отметим, что помимо непосредственного доступа к OLAP-данным из приложений можно создавать контроллеры автоматизации Excel или применять в приложениях компоненты PivotTable List и ChartSpace (об этом мы уже упоминали в предыдущих статьях данного цикла; см. КомпьютерПресс № 9, 10’2001). Подобные решения нередко оказываются весьма выгодными с точки зрения трудозатрат на их создание, если, конечно, пользователи созданных приложений обладают лицензиями Microsoft Office и установленными Office Web Components или Excel, — ведь значительная часть кода, требующегося для реализации манипуляций с отображением OLAP-данных, в Microsoft Office уже реализована.

Тем не менее просмотр OLAP-данных — не единственная задача, которую можно реализовать в клиентских приложениях. Нередко в них требуется также возможность создания или обновления OLAP-кубов. Этой теме будут посвящены настоящая и следующая статьи данного цикла. В этот раз мы рассмотрим программное создание OLAP-кубов с помощью Microsoft PivotTable Service, а в следующей статье — программное создание серверных OLAP-кубов с помощью Decision Support Objects.

Microsoft PivotTable Service

Мicrosoft PivotTable Service представляет собой OLE DB-провайдер, поддерживающий расширения OLE DB for OLAP, выполняющий роль интерфейса к Analysis Services, а также осуществляющий кэширование данных Analysis Services в клиентском приложении.

Помимо этого PivotTable Service является и OLAP-сервером, реализованным в виде клиентских библиотек. Этот сервер используется для локального анализа данных, находящихся в кэше клиентского приложения, манипуляции ими и создания локальных OLAP-кубов.

PivotTable Service поддерживает язык Multidimensional Expressions (MDX) и подмножество языка SQL, а также некоторые дополнительные расширения подмножеств языка SQL — DDL (Data Definition Language) и DML (Data Manipulation Language), необходимые для описания структуры локальных OLAP-кубов. О них мы поговорим ниже.

С помощью PivotTable Service можно создавать локальные кубы на основе данных из реляционных СУБД и серверных OLAP-кубов и затем формировать запросы к таким кубам, не обращаясь непосредственно к Analysis Services, что во многих случаях позволяет снизить сетевой трафик и повысить производительность приложений, а также, используя возможность записи в ячейки таких кубов, проводить анализ возможных вариантов. Отметим, что соединение с Analysis Services можно осуществить с помощью протоколов TCP/IP или HTTP.

Microsoft PivotTable Service используется в клиентах Microsoft SQL Server 2000 Analysis Services. Он используется, в частности, приложениями Microsoft Office (например, при создании сводных таблиц и диаграмм Microsoft Excel), равно как и другими клиентскими приложениями. PivotTable Service входит в состав как Analysis Services, так и Microsoft Excel и может быть включен в состав других клиентских приложений.

Microsoft PivotTable Service можно использовать в различных средствах разработки, поддерживающих создание COM-клиентов, в частности Visual Basic, Microsoft Visual C++, Borland Delphi, Borland C++Builder.

Расширения DDL и DML для создания локальных кубов

Прежде чем приступить к программному созданию локальных OLAP-кубов с помощью Microsoft PivotTable Service, нам следует обсудить, какие расширения DDL и DML для этого требуются. Как минимум, нам понадобится описать структуру будущего куба (этой цели служит свойство PivotTable Service CreateCube, содержащее предложение CREATE CUBE), описать, какие данные следует поместить в этот куб (этой цели служит свойство PivotTable Service InsertInto, содержащее предложение INSERT INTO), а также указать, откуда взять эти данные и в каком файле сохранить локальный куб.

Предложение CREATE CUBE

Это предложение представляет собой расширение DDL, предназначенное для описания структуры будущего локального куба. Полный синтаксис этого предложения можно найти в SQL Server Books Online, мы же ограничимся наиболее часто встречающимся видом этого предложения:

CREATE CUBE <Cube Name>
(
  DIMENSION <Dimension Name> [TYPE <Dimension Type>]
  LEVEL <Level Name>[TYPE <Level Type>],
  [LEVEL <Level Name> [TYPE <Level Type>]…],
  [[DIMENSION <Dimension Name> [TYPE <Dimension Type>]
  LEVEL <Level Name>[TYPE <Level Type>,
  [LEVEL <Level Name> >[TYPE <Level Type>…],…],
  MEASURE <Measure Name> FUNCTION <Function Name>,
  [MEASURE <Measure Name> FUNCTION <Function Name>,…]
)  

Например, создавая локальный куб на основе базы данных NorthWind, входящей в комплект поставки Microsoft SQL Server, мы можем написать следующее предложение CREATE CUBE:

CREATE CUBE Sales(
 DIMENSION [Country], LEVEL [All] TYPE ALL, LEVEL [Country],
                      LEVEL [City], LEVEL [CustomerID],
 DIMENSION [Salesperson], LEVEL [All] TYPE ALL, LEVEL [Salesperson],
 DIMENSION [ShipperName], LEVEL [All] TYPE ALL, LEVEL [ShipperName],
 DIMENSION [CategoryName],LEVEL [All] TYPE ALL, LEVEL [CategoryName],  
                          LEVEL [ProductName],
 DIMENSION [OrderDate] TYPE TIME, LEVEL [All] TYPE ALL,
                                  LEVEL [Year] TYPE YEAR, 
                                  LEVEL [Quarter] TYPE QUARTER, 
                                  LEVEL [Month] TYPE MONTH,   
                                  LEVEL [Day] TYPE DAY,
MEASURE [Sum Of ExtendedPrice] FUNCTION SUM,  
MEASURE [Sum Of Quantity]      FUNCTION SUM   

Предложение INSERT INTO

Это предложение представляет собой расширение DML, предназначенное для заполнения локального куба данными. Полный синтаксис этого предложения можно найти в SQL Server Books Online. Рассмотрим наиболее часто встречающийся вид этого предложения:

InsertInto=INSERT INTO <Cube name>
    (
     <Dimension Name>.<Level Name>,
     [<Dimension Name>.<Level Name>,…]
     <Measure Name>,
     [<Measure Name>,… ]
    )
    OPTIONS <option list>
SELECT <Column list>
FROM <table list>
WHERE <joins list>  

Например, в случае заполнения данными локального куба, описанного выше, это предложение может выглядеть так:

InsertInto=INSERT INTO Sales
 [Country].[Country], [City], [CustomerID],
 [Sum Of ExtendedPrice], [Sum Of Quantity],
 [ShipperName].[ShipperName], [Salesperson].[Salesperson],
 [CategoryName].[CategoryName],
 [ProductName], [OrderDate])
OPTIONS ATTEMPT_ANALYSIS
SELECT Invoices.Country, Invoices.City, Invoices.CustomerID,  
       Invoices.ExtendedPrice, Invoices.Quantity, Invoices.ShipperName,
       Invoices.Salesperson, Categories.CategoryName,
       Products.ProductName, Invoices.OrderDate
FROM Northwind.dbo.Categories Categories,
     Northwind.dbo.Invoices Invoices,
     Northwind.dbo.Products Products 
WHERE Categories.CategoryID = Products.CategoryID
AND Invoices.ProductID = Products.ProductID  

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

Свойства Source_DSN, Data Source и Provider

Эти свойства описывают OLEDB-источник данных для создания локального куба, в каком файле его сохранить и с помощью какого OLEDB-провайдера к нему потом обращаться. В случае создания локального куба на основе базы данных Northwind свойство Source_DSN может выглядеть, например, так:

SOURCE_DSN="DRIVER=SQL Server;SERVER=MAINDESK;UID=sa;DATABASE=Northwind"  

Cвойство Data Source может принять следующий вид:

DATA SOURCE=d:\nw.cub  

Cвойство Provider может выглядеть так:

Provider = MSOLAP.2

Таким образом, мы перечислили минимальный набор свойств, необходимых для программного создания локального куба. Полный список свойств можно найти в Microsoft SQL Server Books Online.

Пример создания локального OLAP-куба

Локальный OLAP-куб, созданный программно с помощью PivotTable Service

Описав минимальный набор свойств локального куба, попробуем создать его програмным способом. Воспользуемся для этой цели Visual Basic 6. Учитывая все сказанное выше, создать соответствующий код будет несложно, см. листинг 1.

Private Sub Command1_Click()
Dim cnCube As ADODB.Connection
Dim s As String
Dim Prov As String
Dim DS As String
Dim SourceDSN As String
Dim SourceDSNSuffix As String
Dim CreateCube As String
Dim InsertInto As String
Dim Sel As String

 Prov = "PROVIDER=MSOLAP"
 DS = "DATA SOURCE=d:\nw.cub"
 SourceDSN = "SOURCE_DSN=""DRIVER=SQL Server;SERVER=MAINDESK;UID=sa;DATABASE=Northwind"""
 CreateCube = "CREATECUBE=CREATE CUBE Sales( "
 CreateCube = CreateCube & " DIMENSION [Country], LEVEL [All] TYPE ALL, LEVEL [Country], LEVEL [City], LEVEL [CustomerID],"
 CreateCube = CreateCube & " DIMENSION [Salesperson], LEVEL [All] TYPE ALL, LEVEL [Salesperson],"
 CreateCube = CreateCube & " DIMENSION [ShipperName], LEVEL [All] TYPE ALL, LEVEL [ShipperName],"
 CreateCube = CreateCube & " DIMENSION [CategoryName],LEVEL [All] TYPE ALL, LEVEL [CategoryName], LEVEL [ProductName],"
 CreateCube = CreateCube & " DIMENSION [OrderDate] TYPE TIME, LEVEL [All] TYPE ALL, LEVEL [Year] TYPE YEAR, LEVEL [Quarter] TYPE QUARTER,  LEVEL [Month] TYPE MONTH,    LEVEL [Day] TYPE DAY,"
 CreateCube = CreateCube & " MEASURE [Sum Of ExtendedPrice]    FUNCTION SUM,   MEASURE [Sum Of Quantity]    FUNCTION SUM  )"

InsertInto = InsertInto & "InsertInto=INSERT INTO Sales ("
InsertInto = InsertInto & "[Country].[Country], [City], [CustomerID], [Sum Of ExtendedPrice], [Sum Of Quantity], [ShipperName].[ShipperName], [Salesperson].[Salesperson],"
InsertInto = InsertInto & "[CategoryName].[CategoryName], [ProductName], [OrderDate]) OPTIONS ATTEMPT_ANALYSIS "

Sel = " SELECT Invoices.Country, Invoices.City, Invoices.CustomerID, Invoices.ExtendedPrice, Invoices.Quantity, Invoices.ShipperName,"
Sel = Sel + "Invoices.Salesperson, Categories.CategoryName, Products.ProductName, Invoices.OrderDate "
Sel = Sel + "FROM Northwind.dbo.Categories Categories, Northwind.dbo.Invoices Invoices, Northwind.dbo.Products Products  "
Sel = Sel + "WHERE Categories.CategoryID = Products.CategoryID AND Invoices.ProductID = Products.ProductID"

InsertInto = InsertInto & Sel

Set cnCube = New ADODB.Connection
s = Prov & ";" & DS & ";" & SourceDSN & ";" & CreateCube & ";" & InsertInto & ";"
cnCube.Open s

End Sub

В этом примере кода создание куба происходит в тот момент, когда становится активным объект ADO Connection, свойство ConnectionString которого содержит перечисленные выше пять свойств PivotTable Service, разделенные точкой с запятой.

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

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

Поставка приложений, использующих PivotTable Service

Как было сказано выше, PivotTable Service может быть включен в состав других клиентских приложений; для этой цели в комплект поставки Microsoft SQL Server 2000 входят соответствующие дистрибутивы:

• Ptslite.exe — приложение для установки только файлов PivotTable Service;
• Ptsfull.exe — приложение для установки файлов PivotTable Service и Microsoft Data Access Components.

Оба эти приложения находятся в каталоге \Msolap\Install\Pts дистрибутива SQL Server 2000.

Итак, мы обсудили создание локальных OLAP-кубов с помощью Microsoft PivotTable Service, а также основные возможности PivotTable Service и основные свойства, необходимые для программного создания локального куба. Следующая статья данного цикла будет посвящена программному созданию серверных OLAP-кубов с помощью Decision Support Objects.