Построение куба из многомерной базы данных

Боб Пфайф, SQL Server Magazine OnLine, #04/2001

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

Хотите получить решение, достаточно гибкое и отвечающее потребностям бизнеса? Спроектируйте, постройте и наполните информацией из реляционной базы данных специальную многомерную базу данных или витрину данных. Она поможет быстро формировать разнообразные отчеты. В статье "От реляционного к многомерному" (опубликованной в 1 номере SQL Server Magazine Online за 2001 год) я на примере показал, как построить многомерную базу данных Northwind_MD. При этом за основу была взята учебная база данных Northwind, поставляемая в составе SQL Server. Для наполнения многомерной базы информацией был разработан модуль Data Transformation Services (DTS). Хотя к многомерной базе данных можно обращаться напрямую, в Northwind_MD факты хранятся на уровне транзакций. Это означает, что при работе в реальном времени процессору запросов приходится выполнять агрегирование данных транзакций, что замедляет обработку запроса и требует дополнительных ресурсов. Можно увеличить производительность этой системы и сделать многомерные данные доступными для пользователей, которые получат возможность самостоятельно анализировать информацию и формировать отчеты. Для этого следует воспользоваться средствами Microsoft OLAP Services и Microsoft Excel. Аналитические службы OLAP Services применяют размещаемый на сервере компонент OLAP Server, а также клиентский компонент PivotTable Services. Применение Excel в роли интерфейса при доступе к кубу облегчает пользователям работу с многомерными данными.

Новые возможности SQL Server 2000

Новые возможности аналитических служб Analysis Services, входящих в состав SQL Server 2000, позволят усовершенствовать проект лежащей в основе рассматриваемого решения многомерной базы данных. На рисунке 1 показан проект исходной многомерной базы данных.

Расшифровка принятых на этом рисунке обозначений приведена в листинге 1.

Employee - Служащий
	EmployeeID - Идентификатор служащего
	EmpLastName -- Фамилия служащего
	MgrLastName - Фамилия менеджера (начальника)

Product - Товар
	ProductID - Идентификатор товара
	ProductName - Название товара
	ProductCategory - Категория товара

Supplier -
	SupplierID - Идентификатор поставщика
	CompanyName - Название компании
	Country - Страна

Customer - Заказчик
	CustomerID - Идентификатор заказчика
	CompanyName - Название компании
	City - Город
	Country - Страна

Time - Время
	TimeID - Идентификатор времени
	Date - Дата
	Year - Год
	Quarter - Квартал
	Month - Месяц
	Day - День

Orders_Fact - Заказы
	ProductID - Идентификатор товара
	CustomerID - Идентификатор заказчика
	EmployeeID - Идентификатор служащего
	TimeID - Идентификатор времени
	Price - Цена
	Quantity - Количество

В приведенной на рисунке 1 таблице размерности служащих Employee показано отношение прямой иерархической подчиненности менеджеров и служащих. Но в таблице служащих оперативной системы между ними установлено рекурсивное отношение. Для этого в столбце ReportsTo каждой строки указывается идентификатор служащего, принадлежащий тому менеджеру, которому данный сотрудник направляет свои отчеты. В пакете DTS, который я использовал для наполнения многомерной базы данных, отражена иерархия служащих, как показывают результаты обработки запроса, приведенные на рисунке 2.

Обратите внимание на то, что служащий по фамилии Fuller является руководителем верхнего звена (его идентификационный номер равен 1). Несколько человек направляют свои отчеты непосредственно ему. Служащий по фамилии Buchanan также имеет несколько прямых подчиненных. Кроме того, и Fuller, и Buchanan отвечают за свои объемы продаж.

Как показано на рисунке 3, существует только один способ отразить эту иерархию в структуре размерности Employee. Размерность показывает служащего Fuller и его подчиненных, служащего Buchanan и его подчиненных, а также тот факт, что Buchanan подчиняется директору Fuller. Однако эта иерархия содержит и более глубокую информацию. Дело в том, что Buchanan отвечает не только за те продажи, которые произвел он сам, но и за все продажи, выполненные сотрудниками его группы. Поэтому при вычислении значения объема продаж необходимо суммировать его персональный показатель с показателями продаж всех его сотрудников. А при расчете объема продаж для директора Fuller объем продаж менеджера Buchanan, в свою очередь, суммируется с аналогичными показателями других руководителей групп.

Чтобы отразить в многомерной базе данных полное разбиение всех уровней иерархии размерности Employee, необходимо вычленить встроенное отношение родитель-дитя между ее членами. Пользуясь только средствами SQL, разобрать рекурсивное взаимоотношение отдельных строк, присутствующее в таблице, затруднительно. До появления Server 2000 при обработке многоуровневых иерархий администраторы сталкивались с двумя проблемами. Во-первых, могло потребоваться больше информации об иерархии, чем содержалось в исходной системе Northwind (в книге Джо Селко "SQL for Smarties: Advanced SQL Programming", выпущенной издательством Morgan Kaufmann, рассматриваются различные способы обработки иерархий средствами SQL). Вторая проблема состоит в том, что может понадобиться расширенный набор операторов T-SQL (пример того, как при помощи операторов T-SQL можно обрабатывать иерархические структуры в таблицах с рекурсивными зависимостями, приведен в книге Кэлен Дилани "Inside Microsoft SQL Server 7.0", опубликованной издательством Microsoft Press).

Однако с появлением Analysis Services в рамках SQL Server 2000 можно более полно отразить иерархические отношения, как показано на рисунке 4.

Я разбил иерархию служащих компании Northwind Traders Company на несколько уровней при помощи функции Dimension Designer, предназначенной для проектирования размерностей в Analysis Server. Эта функция позволяет построить иерархическую размерность на базе одной таблицы, для строк которой задано отношение «родитель-дитя». Обратите внимание на то, что на самом верхнем уровне находится единственный сотрудник, Fuller, а Buchanan занимает подчиненное положение. Те сотрудники, которые направляют свои отчеты непосредственно директору Fuller (в их число входит и Buchanan), располагаются на один уровень ниже. А подчиненные менеджера Buchanan и его собственный показатель объема продаж (пункт Buchanan data) размещаются под ним. Это представление описывает иерархическую структуру подчиненности служащих компании Northwind Traders Company, включая и ту схему, в соответствии с которой производится агрегирование данных. При помощи этой схемы пользователи смогут получать все более и более детальную информацию. Более подробные сведения об особенностях аналитической службы Analysis Services, входящей в состав SQL Server 2000, можно найти в статьях Раса Уитни "Применение Analysis Services 8.0" (опубликована в 4 номере SQL Server Magazine Online за 2000 год), "Действия в Analysis Services" (вошла в первый номер SQL Server Magazine Online за 2001 год), а также в статье "Практическое применение средств DM из Analysis Services" (опубликована во втором номере SQL Server Magazine Online за 2001 год).

Построение кубов, обеспечивающих быстрый отклик

Появление в составе SQL Server 2000 аналитической службы Analysis Services избавило меня от необходимости переделывать пакет DTS, чтобы он смог правильно обрабатывать иерархические отношения между служащими. Мне не пришлось писать дополнительный код или использовать вспомогательные промежуточные таблицы, чтобы полностью раскрыть всю иерархическую структуру. За меня это сделали программы Analysis Services. Несмотря на то, что служба Analysis Services может подсоединиться непосредственно к оперативным данным для построения размерностей и фактографической таблицы куба, я предпочел сохранить базовые данные транзакций в своей многомерной БД на тот случай, если придется обратиться к ним напрямую. Поэтому я скорректировал проект размерности служащих Employee в многомерной базе данных таким образом, чтобы он отражал рекурсивные отношения, существующие внутри таблицы служащих в исходной базе данных.

На рисунке 5 показана пересмотренная модель размерностей многомерной базы данных.

В пакет DTS, который был описан в статье "От реляционного к многомерному", потребовалось внести лишь минимальные изменения. Эти изменения коснулись загрузки модифицированной таблицы размерности служащих Employee. В листинге 1 приведен новый сценарий создания многомерной базы данных. Для доступа к этому листингу откройте статью с идентификационным номером InstantDoc ID 9709 на сайте по адресу http://www.sqlmag.com. Там же находится пакет DTS, позволяющий наполнить эту базу данных информацией. Пакет DTS помещен в архив 9709.zip.

После создания многомерной базы данных следует подсоединиться к SQL Server 2000 Analysis Server, чтобы произвести агрегирование данных куба заранее. Это обеспечит заметное сокращение времени отклика при последующей обработке запросов. Прежде всего, необходимо создать новую базу данных в Analysis Server, воспользовавшись программой Analysis Manager, которую можно запустить из консоли Microsoft Management Console (MMC). Затем следует построить многомерный куб. Ниже перечислены все шаги, которые необходимо выполнить для завершения процесса. Эти шаги одни и те же для SQL Server 2000 Analysis Server и для SQL Server 7.0 OLAP Server за единственным исключением. Во входящем в состав OLAP Server мастере построения размерностей Dimension Wizard отсутствует функция задания иерархии Parent-child hierarchy. Поэтому при использовании OLAP Server читателям придется примириться с менее сложной структурой иерархии в таблице размерности служащих Employee многомерной базы данных.

Шаг 1: Создание базы данных в Analysis Server. В Analysis Manager существует список всех аналитических серверов Analysis Server, включая и локальный. Чтобы создать новую базу данных на сервере, щелкните правой кнопкой мыши на названии выбранного сервера и в появившемся меню введите название для новой базы данных. Новую многомерную базу данных я назвал Northwind_Star. После того как база данных будет создана, следует раскрыть ее, чтобы увидеть вложенные папки для первичных источников данных Data Sources, кубов Cubes, общих размерностей Shared Dimensions, моделей исследования данных Mining Models, и ролей, санкционирующих проведение определенных видов работ в базе данных, Database Roles. В процессе создания базы данных и входящих в нее компонентов Analysis Server формирует соответствующие метаданные и помещает их в файл.mdb. Впоследствии эти данные можно экспортировать в репозитарий SQL Server Repository. Репозитарий представляет собой хранилище метаданных, предназначенных для использования приложениями, работающими с хранилищами данных. Это обеспечивает возможность более гибкого, расширяемого и сложного управления компонентами хранилища данных.

Шаг 2: Добавление источника данных. Чтобы создать источник данных, который обеспечит доступ к многомерной базе данных Northwind_Star для Analysis Services, щелкните правой кнопкой мыши на папке источников данных Data Sources, расположенной под новой базой данных Northwind_Star. После этого выберите из появившегося контекстного меню пункт New Datasource, соответствующий новому источнику данных. В диалоговом окне Microsoft Data Link выберите пункт поставщика OLE DB Provider for SQL Server и введите имя сервера, информацию для входа в систему, а также название базы данных (Northwind_Star) для соединения с Analysis Server.

Шаг 3: Создание общих размерностей. Следующим шагом является построение общих размерностей, которые будут использоваться в кубе. Общие размерности доступны для любого куба в данной БД. К примеру, для того чтобы избежать появления дубликатов размерностей и предоставить единые метрики для показателей деловой активности, используемые в различных кубах, размерность времени Time часто делают общей для всех кубов. В качестве альтернативы можно было бы создать уникальные частные размерности, которые будут использоваться только в данном конкретном кубе. Однако при внесении изменений в частные размерности придется заново строить куб, к которому эти размерности относятся. Чтобы создать общую размерность, содержащую сведения о служащих компании для базы данных Northwind_Star, выберите из выпадающего меню пункты новой размерности New Dimension, а затем мастера Wizard. Мастер поможет проделать одни и те же шаги как при использовании SQL Server 2000, так и SQL Server 7.0. Однако при работе с SQL Server 7.0 количество шагов будет меньше. Нажмите на первом экране кнопку Next, а на втором выберите вариант «Parent-Child: Two related columns in a single dimension table», что приведет к созданию в одной таблице размерности двух связанных столбцов для отражения взаимоотношений «родитель-дитя». Нажмите Next, после чего выберите на экране таблицу со сведениями о служащих. Еще раз нажмите Next и выберите столбцы, которые будут определять отношения «родитель-дитя», как показано на рисунке 6.

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

На следующем экране выберите вариант наличия данных у членов размерности, Members with data, поскольку члены всех уровней иерархии имеют показатели объемов продаж. На следующем экране выберите вариант наличия ассоциированных данных у членов, не являющихся листьями, Nonleaf members have associated data, а также параметр, позволяющий данным членов размерности оставаться на экране, Data members are visible. Торговые представители и их непосредственные руководители имеют свои показатели объемов продаж, а наличие данных у «нелистовых» членов размерности позволяет иметь такие данные и сотрудникам, находящимся на более высоких уровнях иерархии. Второй параметр показывает цифры продаж для руководящих служащих и проводит агрегирование этих данных с показателями подчиненных им сотрудников. На последнем экране введите для размерности название Employee и нажмите кнопку Finish. Откроется редактор размерностей Dimension Editor; его следует закрыть. На этом процесс создания размерности закончен. Чтобы просмотреть данные размерности из Analysis Manager, просто щелкните правой кнопкой мыши на названии размерности.

Для создания еще одной общей размерности, которая использует таблицу времени Time из многомерной базы данных Northwind_Star, можно воспользоваться указаниями мастера. В качестве типа размерности выберите схему звезды, Star Schema, затем параметр размерности времени и столбец дат Date. В качестве уровней иерархии размерности времени выберите год, квартал, месяц и день. Во всех остальных диалоговых окнах примите предлагаемые по умолчанию значения. Присвойте размерности имя Time. Теперь можно перейти к созданию еще одной общей размерности для базы данных Northwind_Star на основе таблицы товаров Product. Для нее также выберите в качестве типа размерности схему звезды, Star Schema. Столбцами выберите столбцы, содержащие категорию товара Product Category и название товара Product Name. Присвойте размерности имя Product. Шаг 4: Построение куба. Теперь, когда все размерности определены, можно приступить к созданию куба на основе построенных размерностей и таблицы фактов продаж. Сначала щелкните правой кнопкой мыши на папке кубов Cubes, выберите пункт построения нового куба, New Cube. Запустите программу-мастер, и в качестве фактографической таблицы возьмите таблицу Order_Fact. На следующем экране, где проводится определение показателей, выберите столбцы количества Quantity и цены Price. На следующем экране выберите все три размерности, нажмите Yes в окне сообщения счетчика числа строк в фактографической таблице, Fact Table Row Count. Назовите куб Product Sales и нажмите Finish. При этом откроется редактор куба Cube Editor и покажет взаимоотношения размерностей и фактографической таблицы.

Теперь закройте редактор кубов и нажмите кнопку Yes в ответ на вопрос мастера о том, желаете ли вы сохранить созданный куб и спроектировать выделяемую для него память. В качестве варианта хранения данных куба выберите MOLAP (Multidimensional OLAP). Это обеспечит хранение информации в собственном сжатом формате файла на сервере Analysis Server. На следующем экране потребуется определить способ агрегирования. Выберите вариант остановки по нажатию кнопки стоп, Until I click Stop, и запустите утилиту проектирования агрегаций Aggregation Designer. На правой панели под графом появятся приблизительно 17 типов различных агрегаций. На последнем экране выберите немедленную обработку, Process Now, и нажмите кнопку Finish. При этом откроется диалоговое окно, в котором будут отображаться те предложения SQL, которые в данный момент выполняет Analysis Server для наполнения куба в соответствии с проектом агрегирования данных. Чтобы просмотреть данные куба, щелкните правой кнопкой мыши по названию куба и выберите пункт просмотра данных, Browse Data. Теперь куб готов к использованию.

Наведите красоту

Следующий шаг процесса представления содержимого многомерной базы данных в удобном для пользователей виде состоит в прикреплении пользовательского интерфейса к созданному и наполненному кубу. Компонент клиентской части Analysis Services под названием PivotTable Services обеспечивает возможности подключения, клиентского кэширования и выполнения разнообразных вычислений. И в Excel 2000, и в продуктах OLAP производства независимых компаний используется утилита вращения таблиц PivotTable Services, которая входит в поставку Microsoft Office 2000.

Доступ к кубу можно осуществлять через Excel 2000, который уже установлен на подавляющем большинстве компьютеров. Чтобы подсоединиться к Analysis Server из Excel, откройте новую книгу Excel и выберите пункт PivotTable и отчет PivotChart из меню данных Data. Затем выберите вариант Get External Data дл получения внешних данных из PivotTable и перейдите на первый экран мастера PivotChart Wizard. На втором экране нажмите кнопку получения данных Get Data, а затем перейдите на закладку OLAP Cubes. Чтобы создать новый источник данных, выделите пункт New Data Source и нажмите OK. Назовите источник данных, выберите поставщика OLE DB Provider for OLAP Services (при работе с SQL Server 2000 следует выбрать Microsoft OLE DB Provider for OLAP Services 8.0), после чего нажмите кнопку подключения Connect. В следующем диалоговом окне вам будет предложено ввести имя для работы в Analysis Server; для данного примера необязательно вводить идентификатор и пароль пользователя. Заметим, что здесь также можно подсоединиться к кубу, который был сохранен в виде файла. Нажмите Next и выберите базу данных Northwind_Star в Analysis Server. Во всех следующих диалоговых окнах нажимайте ОК, а в конце щелкните Finish.

На рисунке 7 показана полученная в результате пустая электронная таблица Excel. Обратите внимание на области, предназначенные для ввода данных размерностей и показателей для представления куба в виде таблицы. Диалоговое окно PivotTable содержит размерности и показатели, пригодные для просмотра. Можно перенести размерности и показатели в электронную таблицу. К примеру, можно поместить размерность служащих Employee в поле строк, а размерность товаров Product в поле столбцов; размерность времени Time можно расположить в поле страницы, а показатели - в расположенный в середине раздел данных data items.

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

"Оставайтесь с нами"

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

Когда вам понадобится решение, позволяющее быстро донести информацию до пользователей, вспомните о том, что аналитическая служба SQL Server 2000 Analysis Services предлагает широкий спектр возможностей. С помощью этого продукта можно создавать гибкие высокопроизводительные программные средства формирования отчетов и анализа систем обработки транзакций. Служба PivotTable Services обеспечивает быстрый доступ к данным. Многие компании-партнеры Microsoft предлагают разнообразные средства с полным набором функций, которые отличаются ясным графическим интерфейсом и возможностью проводить анализ в реальном времени. Кроме того, новая функция мастера создания размерностей Analysis Services Dimension Wizards, задающая отношения «родитель-дитя», позволяет создавать более сложные иерархические структуры.

Об авторе

Боб Пфайф (robertp@crosstier.com является одним из руководителей CrossTier.com. Специалист по базам данных, имеет богатый опыт в области создания хранилищ данных, разработки сетевых приложений и приложений для электронной коммерции между предприятиями. Сертифицирован как MCDBA, MCSD, MCSE, MCT, имеет статус SQL Server MVP.