Введение в OLAP
Часть 4. Создание и заполнение хранилищ данных с помощью Data Transformation Services
Заполнение хранилища данных с помощью Data Transformation Services
Описание потоков данных и последовательности выполнения задач
Описание преобразования данных
В предыдущей статье данного цикла (см. КомпьютерПресс № 6’2001) мы рассмотрели архитектуру OLAP-служб на примере Microsoft Analysis Services — OLAP-сервера фирмы Microsoft, входящего в комплект поставки Microsoft SQL Server 2000 Enterprise Edition и на сегодняшний день признанного аналитиками Gartner Group одним из наиболее популярных продуктов этого класса. Среди ближайших тем, которые мы планируем рассмотреть в данном цикле, будет создание многомерных баз данных и OLAP-кубов с помощью Microsoft Analysis Services.
Прежде чем обсуждать создание OLAP-кубов, вспомним, что, как правило, исходные данные для их создания хранятся не в оперативной базе данных, с которой работают пользователи, а в специализированном хранилище данных. Настоящая статья будет посвящена вопросам заполнения хранилищ данных и синхронизации их с содержимым оперативной базы данных.
Создание хранилищ данных
Как мы уже знаем (см. КомпьютерПресс № 5’2001), типичная структура хранилища данных существенно отличается от структуры традиционной реляционной СУБД. Как правило, эта структура денормализована с целью повышения скорости выполнения запросов, поэтому она может допускать избыточность данных. Типичное хранилище данных содержит таблицу фактов со сведениями об объектах или событиях, совокупность которых будет в дальнейшем анализироваться, и несколько таблиц измерений, содержащих неизменяемые либо редко изменяемые данные. Подробнее о проектировании хранилищ данных можно прочесть во второй статье данного цикла (№ 5’2001).
В качестве оперативной базы данных для нашего примера мы будем использовать базу данных Northwind из комплекта поставки Microsoft SQL Server 2000. На ее основе мы построим хранилище данных, использующее схему «звезда». Структура данных этого хранилища приведена на рис. 1, а скрипт для создания базы данных с такой структурой (назовем ее Northwind_Mart) — в листинге 1.
Отметим, однако, что проектирование хранилища и создание базы данных соответствующей структуры — лишь первый шаг к созданию хранилища данных. Далее нам следует позаботиться о том, чтобы таблицы этого хранилища были заполнены данными, соответствующими текущему состоянию оперативной базы данных.
 |
|
Заполнение хранилища данных с помощью Data Transformation Services
Что представляют собой DTS
Data Transformation Services (DTS) — это набор служб SQL Server, предназначенных для организации импорта, экспорта, преобразования данных и переноса их между любыми источниками, доступными через интерфейсы OLE DB. С их помощью можно копировать структуры данных и сами данные из одной базы данных в другую, создавать средства для переноса данных, встроенные в приложения, а также пополнять хранилища данных из разнообразных источников (которые в общем случае вовсе не обязательно должны быть базами данных SQL Server).
Для заполнения хранилища данных обычно требуется создать и выполнить так называемый пакет DTS (DTS package), содержащий описание последовательности всех действий, которые следует выполнить при переносе данных (включая преобразование типов данных, выполнение SQL-запросов и т.д.). Такой пакет можно выполнить с помощью SQL Server Enterprise Manager или утилиты dtsrun, сохранить его в службах метаданных (Meta Data Services; в прежних версиях SQL Server это хранилище называлось репозитарием) либо в виде структурированного файлового хранилища. Также возможно программное выполнение DTS-пакетов с помощью свойств и методов соответствующих объектов SQL DMO — для этого можно автоматически сгенерировать код на языке Visual Basic. В SQL Server 2000 также поддерживается возможность сохранения DTS-пакетов в формате XML.
Ниже мы рассмотрим процесс создания пакета DTS, заполняющего хранилище Northwind_Mart данными из оперативной базы данных Northwind. На этом примере мы изучим разнообразные возможности сервисов преобразования данных, доступные с помощью DTS.
|
|
Описание источников данных
Создать пакет DTS можно с помощью соответствующего редактора — DTS package editor. Для его запуска следует с помощью SQL Server Enterprise Manager соединиться с сервером, содержащим хранилище данных, найти в разделе Data Transformation Services элемент Meta Data Service Packages и выбрать опцию New Package из его контекстного меню.
Далее нам требуется описать базу данных, в которой находится наше хранилище. Для этого необходимо перенести на рабочее пространство редактора пакетов DTS пиктограмму Microsoft OLE DB Provider for SQL Server с палитры Data tool в левой части окна редактора. После этого появится диалоговая панель Connection Properties для описания источников данных OLE DB, в которой нужно выбрать базу данных Northwind_Mart, указать параметры доступа к ней (например, Use Windows NT authentication). Присвоим этому источнику данных имя NW_OLAP. Для наглядности создаваемой диаграммы сделаем копию этого же источника данных, перенеся на рабочее пространство редактора пакетов DTS еще одну такую же пиктограмму, отметив в диалоговой панели Connection Properties опцию Existing Connection и выбрав из списка имеющихся источников данных NW_OLAP.
Тем же способом опишем источник исходных данных — базу данных Northwind, присвоим ему имя NW и создадим еще пять его копий, так как в нашем хранилище данных содержится шесть таблиц, и нам потребуется шесть отдельных операций по их заполнению.
|
|
Описание потоков данных и последовательности выполнения задач
В нашем примере перед заполнением таблиц в хранилище данных мы будем полностью очищать их содержимое. Для этой цели мы перенесем в рабочее пространство редактора пиктограмму Execute SQL Task. При этом на экране появится диалоговая панель Execute SQL Task Properties, в которой мы заполним поля Description (описание задачи) и SQL Statement (сюда мы добавим операторы для удаления данных из всех таблиц хранилища данных, рис. 2).
Отметим, что при большом объеме данных удаление данных из хранилища обычно не применяется — в этом случае к уже существующим данным добавляются новые.
Далее нам следует определить, какие потоки данных нужны для заполнения хранилища. С этой целью с помощью щелчков мыши при нажатой клавише Ctrl выберем один из шести экземпляров источника данных NW и один из двух экземпляров источника данных NW_OLAP. Когда обе пиктограммы будут выделены, следует выбрать опцию WorkFlow из контекстного меню источника данных NW_OLAP, и тогда пиктограммы окажутся соединенными стрелкой, соответствующей одной из задач преобразования и переноса данных. Далее повторим эту же операцию с четырьмя другими экземплярами источника данных NW и с тем же самым экземпляром источника данных NW_OLAP.
Таким образом, мы создали задания для переноса данных в пять таблиц измерений нашего хранилища. Эти задачи могут выполняться параллельно, ведь таблицы измерений в нашем хранилище не связаны друг с другом. Однако они могут быть выполнены только после полной очистки всего хранилища. Чтобы описать это условие (такие условия определяются словосочетанием precedence constraint), нам следует одновременно выбрать пиктограмму Execute SQL Task и одну из пяти уже задействованных пиктограмм источника данных NW, а затем из контекстного меню источника данных NW выбрать опцию Workflow | On Success. Появившаяся зеленая пунктирная стрелка между пиктограммами означает, что перенос данных в соответствующую таблицу изменений будет осуществлен только после успешного завершения очистки хранилища. Далее следует повторить это действие с оставшимися четырьмя используемыми экземплярами источника данных NW.
Что же касается задачи заполнения данными таблицы фактов, она может быть выполнена только после того, как будут заполнены все таблицы измерений. Поэтому сначала мы выделим оставшиеся экземпляры источника данных NW и источника данных NW_OLAP, затем выберем опцию WorkFlow из контекстного меню этого экземпляра источника данных NW_OLAP — при этом пиктограммы окажутся соединены стрелкой, соответствующей задаче заполнения таблицы фактов. Далее нам следует одновременно выбрать пиктограмму NW_OLAP, участвующую в описании пяти задач заполнения таблиц измерений, и пиктограмму NW, участвующую в описании задачи заполнения таблицы фактов, а затем из контекстного меню выделенного источника данных NW выбрать опцию Workflow | On Success. Таким образом, мы указали, что заполнение таблицы фактов осуществляется только после успешного заполнения таблиц измерений (рис. 3).
|
|
Описание преобразования данных
Далее нам следует описать, откуда берутся и как преобразовываются данные при переносе из оперативной базы данных в хранилище. Мы начнем с таблицы Time_Dim. Для этой цели дважды щелкнем мышью по одной из пяти стрелок, соответствующих задачам заполнения таблиц измерений. В появившейся диалоговой панели заполним поле Description, выберем опцию SQL Query и введем текст SQL-запроса, результат которого должен быть помещен в таблицу Time_Dim:
SELECT DISTINCT S.ShippedDate AS TheDate, DateName(dw, S.ShippedDate) AS DayOfWeek, DatePart(mm, S.ShippedDate) AS [Month], DatePart(yy, S.ShippedDate) AS [Year], DatePart(qq, S.ShippedDate) AS [Quarter], DatePart(dy, S.ShippedDate) AS DayOfYear, 'N' AS Holiday, case DatePart(dw, S.ShippedDate) when (1) then 'Y' when (7) then 'Y' else 'N' end AS Weekend, DateName(month, S.ShippedDate) + '_' + DateName(year,S.ShippedDate) AS YearMonth, DatePart(wk, S.ShippedDate) AS WeekOfYear FROM Orders S WHERE S.ShippedDate IS NOT NULL
Щелкнем по закладке Destination и выберем из списка таблиц хранилища данных таблицу Time_Dim. Далее можно перейти на страницу Transformations и проверить правильность соответствий между полями исходного набора данных и таблицы Time_Dim. Если они не соответствуют желаемым, их можно отредактировать с помощью кнопок New, Edit, Delete (рис. 4).
Следующая таблица измерений, Customer_Dim, будет заполняться не результатами запроса, а данными из таблицы Customers. Поэтому на странице Source следует отметить опцию Table/View, выбрать таблицу Customers в списке таблиц базы данных Northwind, на странице Destination выбрать таблицу Customer_Dim и проверить правильность соответствий между полями исходного набора данных и таблицы Customer_Dim. Однако в этом случае было бы желательно преобразовать некоторые значения, содержащиеся в поле Region исходной таблицы (для одних стран это поле не содержит данных, тогда как для других может потребоваться анализ продаж по регионам или другим административным единицам). С этой целью мы удалим соответствие между полем Region обеих таблиц, нажмем кнопку New, из списка в диалоговом окне New Transformation выберем опцию ActiveX Script и в появившейся диалоговой панели ActiveX Script Transformation Properties отредактируем код на языке VBScript, описывающий преобразование данных в этом поле:
Function Main()
If IsNull(DTSSource("Region")) Then
DTSDestination("Region") = "Other"
Else
DTSDestination("Region") = DTSSource("Region")
End If Main = DTSTransformStat_OK End Function
Здесь курсивом выделены фрагменты, добавленные к коду, сгенерированному по умолчанию (рис. 5).
Для таблицы измерений Product_Dim последовательность действий сходна с той, что мы применяли при создании таблицы Time_Dim. Однако здесь, выбрав на странице Source диалоговой панели Transform Data Task Properties опцию SQL Query, мы нажмем кнопку Build Query и создадим запрос с помощью DTS Query Designer (рис. 6).
В запросе используются таблицы Products и Categories базы данных Northwind, при этом поле UnitPrice таблицы Products переименовывается в ListUnitPrice.
Выбрав на странице Destination таблицу Product_Dim, проверим корректность соответствий между полями исходного набора данных и таблицы Products_Dim. В данном случае мы видим, что поля SupplierID и SupplierName – разных типов, при этом то и другое поле описывают, по существу, одно и то же свойство члена измерения. В этой ситуации нам поможет подстановка значений (lookup). Перейдем на страницу Lookups, нажмем кнопку Add, придумаем имя для подстановки, например SupplierLookup, щелкнем по кнопке Query и в появившемся редакторе DTS Query Designer введем текст запроса:
SELECT CompanyName FROM Suppliers WHERE (SupplierID = ?)
Далее на странице Transformations опишем соответствие между полями SupplierId и SupplierName. С этой целью нажмем кнопку New, из списка в диалоговом окне New Transformation выберем опцию ActiveX Script, укажем имена исходного и получаемого полей и в появившейся диалоговой панели ActiveX Script Transformation Properties отредактируем код на языке VBScript, описывающий преобразование данных в этом поле:
Function Main() DTSDestination(“SupplierName”) = _ DTSLookups(“SupplierLookup”).Execute(DTSSource(“SupplierID”).Value) Main = DTSTransformStat_OK End Function
Для заполнения данными следующей таблицы измерений, Employee_Dim, нам нужно указать, что два поля исходной таблицы Customers, FirstName и LastName, соответствуют одному полю EmployeeName таблицы Customer_Dim. Для этого нажмем кнопку New на странице Transformations, выберем опцию ActiveX Script и отметим оба поля, FirstName и LastName, в качестве исходных. Далее модифицируем код в диалоговой панели панели ActiveX Script Transformation Properties:
Function Main() DTSDestination(“EmployeeName”) = DTSSource(“FirstName”) & _ “ “ & DTSSource(“LastName”) Main = DTSTransformStat_OK End Function
И наконец, при описании преобразования данных для таблицы Shipper_Dim нам нужно проверить соответствие между полем CompanyName таблицы Shippers базы данных Northwind и полем ShipperName таблицы Shipper_Dim.
Завершив работу с таблицами измерений, займемся преобразованием данных для таблицы фактов. В данном случае исходный набор данных, преобразуемый в таблицу Sales_Fact, представляет собой результат следующего запроса:
SELECT Northwind_Mart.dbo.Time_Dim.TimeKey, Northwind_Mart.dbo.Customer_Dim.CustomerKey, Northwind_Mart.dbo.Shipper_Dim.ShipperKey,
Northwind_Mart.dbo.Product_Dim.ProductKey, Northwind_Mart.dbo.Employee_Dim.EmployeeKey, Northwind.dbo.Orders.RequiredDate, Orders.Freight * [Order Details].Quantity / (SELECT SUM(Quantity) FROM [Order Details] od WHERE od.OrderID = Orders.OrderID) AS LineItemFreight, [Order Details].UnitPrice * [Order Details].Quantity AS LineItemTotal, [Order Details].Quantity AS LineItemQuantity, [Order Details].Discount * [Order Details].UnitPrice * [Order Details].Quantity AS LineItemDiscount FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN Northwind_Mart.dbo.Product_Dim ON [Order Details].ProductID = Northwind_Mart.dbo.Product_Dim.ProductID INNER JOIN Northwind_Mart.dbo.Customer_Dim ON Orders.CustomerID = Northwind_Mart.dbo.Customer_Dim.CustomerID INNER JOIN Northwind_Mart.dbo.Time_Dim ON Orders.ShippedDate = Northwind_Mart.dbo.Time_Dim.TheDate INNER JOIN Northwind_Mart.dbo.Shipper_Dim ON Orders.ShipVia = Northwind_Mart.dbo.Shipper_Dim.ShipperID INNER JOIN Northwind_Mart.dbo.Employee_Dim ON Orders.EmployeeID = Northwind_Mart.dbo.Employee_Dim.EmployeeID WHERE (Orders.ShippedDate IS NOT NULL)
Итак, мы описали все шесть преобразований данных, необходимых для заполнения хранилища данными. Осталось их выполнить, о чем мы и расскажем в следующем разделе.
|
|
Выполнение пакетов DTS
Созданный пакет DTS следует сохранить, выбрав опцию Package | Save из меню редактора пакетов DTS. Выполнить его можно, выбрав пункт меню Package | Execute. После этого начнется процесс преобразования данных и заполнения ими таблиц хранилища данных.
Для того чтобы данные в хранилище соответствовали текущему или недавнему состоянию оперативной базы данных, можно создать расписание, согласно которому будет автоматически выполняться данный пакет. Для этого следует выбрать его в Enterprise Manager и опцию Schedule Package — из контекстного меню. Далее следует выбрать нужный режим обновления данных в диалоговой панели Edit Recurring Job Schedule (рис. 7).
Отметим, что для запуска пакета по расписанию необходимо, чтобы был запущен SQL Server Agent — служба, инициирующая выполнение различных заданий по расписанию.
|
|
Заключение
В данной статье мы рассмотрели процесс создания хранилищ данных и заполнения их с помощью Data Transformation Services. Мы узнали, что:
- Data Transformation Services (DTS) — это набор служб SQL Server, предназначенных для организации импорта, экспорта, преобразования данных и переноса их между любыми источниками, доступными с помощью OLE DB.
- Для заполнения хранилища данных обычно требуется создать и выполнить так называемый пакет DTS (DTS package), содержащий описание последовательности всех действий, которые следует выполнить при переносе данных (включая преобразование типов данных, выполнение SQL-запросов и т.д.).
- Пакет DTS можно сохранить в службах метаданных либо в виде структурированного хранилища. Можно также сгенерировать код на языке Visual Basic, с помощью которого можно выполнить пакет DTS или сохранить этот пакет в формате XML.
Далее мы рассмотрели создание пакетов DTS с помощью DTS package editor. Мы изучили, как описывать последовательность выполнения действий при преобразовании данных, как использовать для преобразования данных SQL-запросы и подстановки, как создавать код преобразования данных на языке VBScript. Мы также рассказали о запуске пакетов DTS и об организации их автоматического выполнения согласно расписанию.
Итак, теперь мы можем создавать хранилища, регулярно обновляемые в соответствии с расписанием данными из оперативной базы данных. И как мы уже говорили в предыдущих статьях нашего цикла, такое хранилище данных — это идеальный источник для cоздания OLAP-кубов. Именно этой теме — созданию OLAP-кубов — и будет посвящена следующая статья.
КомпьютерПресс 7'2001
