Введение в OLAP: часть 5. Создание многомерных баз данных

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

Создание коллективных измерений
   Создание измерения типа <дата/время>
   Создание регулярного измерения
   Создание измерения с несбалансированной иерархией
   Создание измерения типа <родитель-потомок>
Создание OLAP-кубов
   Создание описания куба
   Создание вычисляемых выражений
   Создание многомерного хранилища данных

Заключение

В предыдущей статье данного цикла "Создание и заполнение хранилищ данных с помощью Data Transformation Services" мы обсудили вопросы заполнения хранилищ данных и синхронизации их с содержимым оперативной базы данных. На этот раз мы рассмотрим, как на основании хранилищ данных можно создавать многомерные базы данных и OLAP-кубы с помощью Microsoft Analysis Services - аналитических сервисов, с архитектурой которых мы уже знакомы ("Архитектура Microsoft Analysis Services").

Создание многомерных баз данных и описание источников данных

Рассмотрим создание многомерного OLAP-куба на основании хранилища данных Northwind_Mart, которое мы создали и заполнили в предыдущей статье. Напомним, что это хранилище содержит таблицу фактов Sales_Fact и таблицы измерений Employee_Dim, Customer_Dim, Product_Dim, Time_Dim, Shipper_Dim. Отметим, что в процессе создания куба нам придется несколько модифицировать наше хранилище данных, с тем чтобы оно позволяло производить некоторые специальные виды анализа данных.

Для выполнения этого примера следует установить аналитические службы Microsoft SQL Server (напоминаем, что они входят в комплект поставки Microsoft SQL Server Enterprise Edition, Standard Edition, Developer Edition и Personal Edition) и запустить утилиту Analysis Manager, с помощью которой обычно и создаются многомерные базы данных.

Прежде всего следует зарегистрировать в Analysis Manager OLAP-сервер (он может находиться как на локальном компьютере, так и на другом компьютере в рамках локальной сети), выбрав пункт Register Server: из контекстного меню элемента Analysis Servers в левой части главного окна Analysis Manager. Затем нужно соединиться с OLAP-сервером, выбрав пункт Connect контекстного меню соответствующего элемента.

Поскольку OLAP-кубы хранятся в многомерных базах данных, создадим таковую, выбрав пункт New Database: из контекстного меню элемента, соответствующего OLAP-серверу, и введем имя базы данных и ее описание.

Прежде чем создавать OLAP-кубы, необходимо описать источники исходных данных для них. В нашем примере таким источником является созданное ранее хранилище Northwind_Mart. Для описания источника данных выберем из контекстного меню элемента Data Sources пункт New Data Source: и заполним поля стандартной диалоговой панели Data Link Properties: в качестве провайдера данных укажем OLE DB Provider for SQL Server и выберем базу данных Northwind_Mart (рис. 1).

Рис. 1. Создание источника данных

Теперь можно приступать к созданию измерений и кубов.

Создание коллективных измерений

Как мы уже знаем из предыдущих статей данного цикла, у OLAP-куба должно быть как минимум одно измерение. В Microsoft SQL Server Analysis Services измерения делятся на коллективные (shared dimensions) и частные (private dimensions).

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

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

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

Создание измерения типа <дата/время>

В качестве примера создадим коллективное измерение, основанное на таблице хранилища данных Time_Dim, воспользовавшись мастером создания измерений (Dimension wizard). Запустить его можно с помощью команды New Dimension | Wizard из контекстного меню элемента Shared Dimensions. Затем необходимо ответить на вопросы мастера создания измерений. В первую очередь следует выбрать, на основании чего мы создаем измерение. Поскольку исходное хранилище данных основано на схеме <звезда>, выберем в мастере создания измерений опцию Star Schema: a single dimension table, а затем - имя таблицы, служащей источником данных для создаваемого измерения (в нашем примере - Time_Dim, рис. 2):

Рис. 2. Выбор таблицы для создания измерения

Иерархия данных в измерениях, основанных на данных типа <дата/время>, подчиняется определенным стандартным правилам - ведь время измеряется в годах, месяцах, днях, часах, минутах независимо от того, какую предметную область мы анализируем. Поэтому измерения в OLAP-средствах обычно делятся на стандартные (не имеющие отношения ко времени) и временные. Поскольку наше измерение относится к последним, в диалоговой панели Select the dimension type выберем опцию Time Dimension и в качестве колонки, в которой содержатся данные типа <дата/время>, укажем поле TheDate.

Теперь нам необходимо выбрать уровни иерархии измерений (например, решить, интересна ли нам информация о часах и минутах, нужны ли нам номера недель года и т.д.), а также определить, когда начинается год с точки зрения данного измерения. Это довольно важная возможность - ведь во многих странах начало финансового года не совпадает с началом года календарного. В нашем случае выберем уровни Year, Quarter, Month, Day и согласимся с тем, что год начинается 1 января (рис. 3).

Рис. 3. Создание измерения типа <дата/время>

Далее нам предстоит выбрать, является ли измерение изменяющимся (changing dimension). В изменяющихся измерениях (новинка в SQL Server 2000) можно перемещать члены измерений между уровнями без перерасчета данных измерения, что во многих случаях бывает удобно. Однако измерения типа Time, как правило, не делают изменяющимися - обычно никто не перемещает месяцы из одного года в другой. Поэтому в данном случае мы не будем выбирать эту опцию.

В заключительной диалоговой панели мы должны ввести имя будущего измерения и, если есть необходимость, создать иерархию в измерении и задать ее имя. Дело в том, что при необходимости можно создать еще одно измерение, основанное на тех же данных, с тем же именем, но с другой иерархией, например Year, Week, Day; в этом случае мы имеем разное представление одних и тех же данных. Присвоим созданной иерархии имя YQMD (рис. 4).

Рис. 4. Создание источника данных

Создание измерения заканчивается запуском редактора измерений - Dimension Editor. В нем при необходимости можно внести изменения в структуру измерения, например добавив дополнительные уровни или свойства членов измерения. Так, если мы планируем анализировать зависимость продаж от дня недели или сравнивать продажи в выходные, праздничные и будние дни, можно перенести в раздел Member Properties уровня Day поля Day of Week, Holiday и Weekend исходной таблицы Time_Dim (рис. 5).

Рис. 5. Dimension Editor

Теперь можно сохранить созданное измерение, выбрав пункт меню File | Save, и закрыть редактор измерений.

Повторим все указанные действия, выбрав при этом другую иерархию - Year, Week, Day, и назовем вновь созданное измерение Time.YWD.

Создание регулярного измерения

Следующее коллективное измерение создадим с помощью редактора измерений. Запустить его можно с помощью команды New Dimension | Editor из контекстного меню элемента Shared Dimensions. Далее в диалоговой панели Select the dimension table выберем таблицу Product_Dim. В редакторе измерений создадим два уровня иерархии этого измерения - CategoryName и ProductName - и перенесем мышью соответствующие имена полей в левую часть редактора измерений. В качестве свойств членов измерения уровня ProductName выберем поля SupplierName и ListUnitPrice. Поскольку переносить продукты из одной категории в другую представляется более разумным, чем переносить месяцы из одного года в другой, сделаем это измерение изменяющимся - соответствующее свойство доступно на вкладке Advanced панели Properties в левой нижней части редактора измерений. Сохраним созданное измерение под именем Product (рис. 6).

Рис. 6. Создание регулярного измерения в Dimension Editor

Создание измерения с несбалансированной иерархией

Следующее измерение будет содержать географические сведения. Такие измерения являются типичными кандидатами для создания так называемых неровных (ragged) иерархий - частного случая несбалансированных (unbalanced) иерархий. Как известно, административно-территориальное деление в разных странах осуществляется по разным правилам: в некоторых странах есть регионы, штаты, административные округа, а в некоторых достаточно указать населенный пункт, и в этом случае сведения о штате или регионе могут отсутствовать.

Чтобы создать измерение с несбалансированной иерархией, добавим в хранилище данных представление, которое будет содержать исходные данные для создания этого измерения:

CREATE VIEW dbo.CustomerView_Dim
AS
SELECT
CustomerKey, CustomerID, CompanyName, ContactName, ContactTitle, Address, City,
Region,
REPLACE(Region, 'Other', Country) AS Region1,
PostalCode, Country, Phone, Fax
FROM dbo.Customer_Dim

Это представление содержит данные из таблицы Customer_Dim, а также вычисляемое поле Region1, содержащее название страны вместо строки в тех случаях, когда сведения о клиенте не содержат данных о регионе или штате. Это поле нам потребуется в дальнейшем для создания несбалансированной иерархии.

Теперь создадим измерение, основанное на вновь созданном представлении CustomerView_Dim хранилища данных. Последовательность действий в этом случае сходна с предыдущим примером. В качестве уровней иерархии этого измерения мы выберем поля Country, Region1, City, CompanyName. Добавим в раздел Member Properties уровня Company Name поля Contact Name и Contact Title.

Несбалансированные иерархии обычно базируются на сокрытии членов измерения, содержащих избыточные сведения. В данном случае таковым является уровень Region1. Выберем его в редакторе измерений и на странице Advanced раздела Properties установим свойство Hide Member If равным Parent's name. В этом случае все члены уровня Region1, содержащие названия стран, будут скрыты (рис. 7).

Рис. 7. Несбалансированная иерархия

Именно для этого мы и создавали представление в хранилище данных - строка , вполне устраивавшая нас при обращении к самому хранилищу данных, будучи именем члена измерения, не может выступать в качестве условия его скрытия.

Создание измерения типа <родитель-потомок>

Следующее измерение, которое мы создадим, будет основано на таблице Employee_Dim хранилища данных. Обычно измерения, содержащие сведения об административной подчиненности сотрудников, содержат еще один тип несбалансированных иерархий - иерархии типа <родитель-потомок> (parent-child). Такие иерархии нередко основаны на таблицах, где первичный ключ является одновременно и внешним ключом. Исходная таблица Employees базы данных Northwind действительно содержит сведения об административной подчиненности сотрудников (и имеет соответствующий внешний ключ), а таблица Employee_Dim - нет. Поэтому в первую очередь модифицируем ее, добавив к ней поле Reports_To:

ALTER TABLE dbo.Employee_Dim ADD Reports_To int

Затем с помощью DTS добавим в это поле данные из таблицы Employees (рис. 8).

Рис. 8. Добавление данных в таблицу Employee_Dim

Теперь можно создать измерение на основе таблицы Employee_Dim с помощью Dimension Wizard. В его первой диалоговой панели выберем опцию Parent-Child: Two related columns in a single dimension table. Далее опишем связь между двумя полями таблицы Employee_Dim, указав имя поля EmployeeID в качестве свойства Member_Key создаваемого измерения, Reports_To - в качестве его свойства Parent_Key, EmployeeName - в качестве его свойства Member Name (рис. 9).

Рис. 9. Определение параметров иерархии <родитель-потомок>

В диалоговой панели Select advanced options следует выбрать опцию Members with data, а в панели Set members with data property - опции Nonleaf members have associated data и Data members are visible. Это позволит анализировать как собственные результаты работы сотрудников, имеющих подчиненных, так и результаты работы их подчиненных. Создадим иерархию в этом измерении, назвав ее Employee.PC, и укажем в качестве свойства члена измерения поле Hire Date. В результате мы получим иерархию, показанную на рис. 10.

Рис. 10. Иерархия <родитель-потомок>

В качестве альтернативы создадим еще одну иерархию - Employee.Regular, содержащую один уровень Employee_Name; в качестве свойств члена этого уровня выберем поля Hire Date и Reports_To.

На этом мы закончим создание коллективных измерений и приступим к созданию куба.

Создание OLAP-кубов

Как и измерение, куб можно создать с помощью соответствующего мастера или непосредственно в редакторе кубов. В качестве примера создадим куб, основанный на нашем хранилище данных Northwind_Mart и использующий созданные выше измерения. Запустить мастер создания кубов можно командой New Cube | Wizard из контекстного меню элемента Cubes.

Создание описания куба

Первое, что следует сделать после запуска мастера, - выбрать таблицу фактов для будущего куба. В нашем случае это таблица Sales_Fact. Далее из таблицы фактов следует выбрать одно или несколько полей, на основе которых вычисляются меры куба (то есть поля, данные которых подлежат суммированию либо обработке с помощью других агрегатных функций). Выберем поля Line Item Total, Line Item Quantity и Line Item Discount (рис. 11).

Рис. 11. Выбор мер куба

Следующим шагом будет выбор коллективных измерений, используемых в этом кубе, а также создание недостающих частных измерений. Выберем коллективные измерения Employee.PC, Employee.Regular, Time.YQMD, Product и Customer. Кроме того, добавим новое измерение Shipper, нажав кнопку New Dimension в диалоговой панели выбора измерений. Это приведет к запуску уже знакомого нам мастера создания измерений; в последней из диалоговых панелей мастера в этом случае мы можем выбрать, каким будет создаваемое измерение - частным или коллективным (рис. 12).

Рис. 12. Выбор мер куба

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

Рис. 13. Редактор кубов

Создание вычисляемых выражений

Теперь попробуем добавить к нашему кубу вычисляемые значения, то есть значения, которые не хранятся в самом кубе, а вычисляются <на лету>. Типичным примером такого значения может быть дополнительная мера, вычисленная на основе уже имеющихся. При вычислениях можно использовать как функции из библиотеки, входящей в состав Analysis Services, так и выражения VBA, а также собственные библиотеки функций (последние следует зарегистрировать в Analysis Services).

Для создания вычисляемых выражений следует выбрать раздел Calculated Members и из контекстного меню выбрать опцию New Сalculated Member. После этого будет запущен построитель выражений (Calculated Member Builder), в котором можно создавать и редактировать выражения, перетаскивая мышью имена измерений и их уровней, мер, имена функций. Например, перенесем в поле для выражения имена мер [Measures].[Line Item Total] и [Measures].[Line Item Discount], поставим между ними знак вычитания, а в качестве значения Member Name ведем Discounted Total (рис. 14).

Рис. 14. Редактор вычисляемых выражений

В результате мы получили еще одну меру - значение суммы, вырученной за товар, с учетом скидки.

Теперь можно сохранить определение куба, выбрав пункт меню File | Save редактора кубов.

Создание многомерного хранилища данных

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

Еще один вопрос, который следует решить при создании многомерного хранилища данных - сколько агрегатов следует хранить? Агрегаты - это заранее вычисленные агрегатные данные, соответствующие ячейкам куба. Чем их больше, тем быстрее выполняются запросы к многомерному хранилищу и тем больше объем самого хранилища. Поэтому в общем случае требуется некое их количество, позволяющее осуществить разумный баланс между компактностью и производительностью.

Для определения количества агрегатов и их вычисления следует запустить Storage Design wizard - мастер создания многомерного хранилища. Для этого в редакторе кубов следует выбрать пункт меню Tools | Design Storage.

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

Рис. 15. Определение количества агрегатов

И наконец, нам необходимо вычислить сами агрегатные данные. Это можно сделать как в том же мастере создания хранилища, так и в редакторе кубов (команда Tools | Process Cube, рис. 16).

Рис. 16. Вычисление агрегатных данных

Теперь, когда куб готов, можно заняться его просмотром в редакторе кубов (для этого нужно выбрать закладку Data в нижней части экрана, рис. 17).

Рис. 17. Просмотр сечений куба

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

Заключение

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

• в многомерных базах данных содержится несколько типов объектов, в том числе кубы и коллективные измерения;
• в Microsoft Analysis Services измерения делятся на коллективные и частные. Коллективные измерения могут быть использованы одновременно в нескольких кубах, а частные - принадлежат конкретному кубу.

Мы также научились создавать измерения с различными типами иерархий и узнали, что:

• иерархии в измерениях могут быть сбалансированными и несбалансированными. Несбалансированные иерархии обычно базируются на скрытии членов измерения, содержащих избыточные сведения;
• иерархии типа <родитель-потомок> обычно основаны на таблицах, чей первичный ключ является внешним ключом;
• можно создать несколько иерархий, используя одни и те же данные.

Кроме того, мы обсудили создание многомерных кубов и теперь знаем, что:

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

Следующая статья данного цикла будет посвящена работе с Microsoft Excel как c OLAP-клиентом.