Обновление данных в витринах

Роберт Пфайф, SQL Magazine OnLine, #05/2001

Изменения в исходной базе данных происходят часто, а обновлять витрину лучше большими порциями.

В первых двух статьях этой серии, "От реляционного к многомерному" и "Построение куба из многомерной базы данных", я рассказал о простом решении, которое обеспечивало подготовку нестандартных отчетов и проведение анализа данных в вымышленной торговой компании Northwind Traders Company. Предложенный подход включал построение новой базы данных, спроектированной специально для удобства обработки запросов пользователей, а также загрузку в нее данных из реляционной базы данных NorthWind и применение SQL Server 2000 Analysis Services для создания куба OLAP. В настоящей статье описаны шаги, необходимые для периодического обновления данных в витрине, чтобы при этом можно было провести перезагрузку сразу большого массива данных, а не множества мелких обновлений при каждом изменении исходной базы данных.

Изменение размерностей

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

Рис. 1. Структура многомерной БД NorthWind

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

В таких ситуациях имет смысл просто игнорировать те члены размерностей, которые выпадают из общего правила. При этом и сами члены размерностей, и относящиеся к ним факты просто удаляются. Однако при этом искажается история. Товар, который компания Northwind Traders прекратила продавать, тем не менее, имеет определенные объемы продаж, относящиеся к прошлым периодам, когда этот товар еще продавался компанией. И история его продаж вошла в итоговые суммы для более общих категорий товаров, то есть тех членов размерности товаров, которые расположены на более высоких уровнях иерархии, поскольку являются его родителями.

Что делать в том случае, когда две компании сливаются в одну? Хотя существует множество возможностей, все зависит от того, как организован бизнес. О трех возможных методах модификации медленно меняющихся размерностей расказано в статье Джо Людке "Медленно меняющиеся размерности", опубликованной в третьем номере журнала SQL Server Magazine Online за 2000 год, а также в книгах Ральфа Кимбала "Инструментарий хранилищ данных: практический подход к построению многомерных хранилищ данных" ("The Data Warehouse Toolkit: Practical Techniques for Building Dimensional Data Warehouses", Wiley & Sons, 1996) и "Инструментарий создания и сопровождения хранилищ данных: экспертные методы проектирования, разработки и внедрения" ("The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing, and Deploying Data Warehouses", Wiley & Sons, 1998). Каждый их этих методов по-разному влияет на возможность просмотреть историю деловой активности компании.

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

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

Третья возможная стратегия отслеживания изменений заключается в формировании ссылки на прежнее значение. В рассматриваемом примере изменения семейного положения заказчика его текущий статус будет соответствовать тому, что женщина замужем, а мужчина женат. В то же время их прежний статус будет соответственно не замужем. или холост. Поскольку статус женат является вполне определенным, вряд ли понадобится возвращаться к прежнему значению статуса. Однако третий вариант обработки изменения размерностей оказывается гораздо выгоднее использовать при менее определенных обстоятельствах. К примеру, Ральф Кимбал в своей книге "Инструментарий создания и сопровождения хранилищ данных: экспертные методы проектирования, разработки и внедрения" предлагает использовать в этом случае границы регионов продаж, принятые в конкретной компании. Они со временем могут претерпевать изменения. В Analysis Services также предусмотрены средства отражения изменений размерностей.

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

Обнаружение перемен

Чтобы обновить многомерную базу данных, мне необходимо всего лишь определить, существуют ли нужные строки в базе данных NorthWind (а не в таблицах многомерной базы данных Northwind_Star). И если это так, то следует просто добавить их в таблицу соответствующей размерности. Такую задачу чрезвычайно просто реализовать средствами T-SQL в модулях Data Transformation Services (DTS). Эта техника ориентирована на обработку наборов данных. Другие способы реализации этой задачи: построение программы на ActiveX, в которой будет воплощена заданная логика обработки; применение преобразований ActiveX; формирование задачи обработки управляемого данными запроса, Data Driven Query. Все они могут вести поиск определенных строк в таблице размерностей, а в случае отсутствия нужных строк будут добавлять их. Однако все перечисленные методы в состоянии за один раз обработать только одну строку таблицы, что далеко не так эффективно, как при использовании T-SQL для обработки целых наборов данных.

Еще один вопрос, который непременно следует продумать, решая вопрос о том, как отслеживать изменения многомерной базы данных, связан с местоположением этой базы данных. Я решил поместить многомерную базу данных Northwind_Star в отдельную систему SQL Server, чтобы частично разгрузить от ответа на запросы промышленную систему обработки транзакций (transaction processing, TP). В связи с этим мне придется использовать связанные серверы, для того чтобы при помощи кода T-SQL выполнять соединение таблиц исходной реляционной и результирующей многомерной систем для загрузки в многомерную базу данных новых сведений. На рисунке 2 и рисунке 3 показаны диалоговые окна свойств связанных серверов, Linked Server Properties, которые используются для соединения серверов в единую систему.

Рис. 2. Описательное имя, которое ссылается на удаленный сервер

Рис. 3. Использование на удаленном сервере учетной записи системного администратора

На рисунке 2 для связанного сервера показано описательное имя, которое ссылается на удаленный сервер. Читатели могут присвоить этому серверу любое имя по своему вкусу. То имя, которым воспользовался я сам, относится к многомерной базе данных. Кроме того, в этом диалоговом окне не должно быть незаполненных полей. В роли источника данных выступает удаленный сервер (в рассматриваемом примере применяются двухуровневые имена для экземпляров SQL Server 2000), а в качестве каталога - та база данных, которую предполагается использовать на удаленном сервере. Следует задать конфигурацию связанных серверов в исходной системе SQL Server (где находится база данных Northwind) для соединения с другой системой SQL Server (где размещается витрина данных Northwind_Star). Это обусловлено тем, что входящие в состав модулей DTS задачи T-SQL производят соединение таблиц этих двух баз данных в системе связанных серверов. В приведенном примере я воспользовался имеющейся на удаленном сервере учетной записью системного администратора sa, чтобы получить к нему доступ, как показано на рисунке 3.

Построение пакета DTS

Теперь я могу построить пакет DTS, который будет обновлять витрину данных. На рисунке 4 показан пакет, который сначала очищает все таблицы витрины, а затем производит полную загрузку новых данных.

Рис.4. Пакет очистки всех таблиц витрины и загрузки новых данных

В этом пакете предусмотрены последовательности шагов для загрузки размерности времени и таблицы перекрестных ссылок заказчиков. Пакет заполняет размерность времени последовательностью дат, покрывающих интервал с начала 1995 и до конца 2010 года. Таким образом, размерность времени будет содержать не только все даты, которые встречаются в базе данных NorthWind, но и резервные даты для дальнейшего роста. В таблице перекрестных ссылок заказчиков каждому значению идентификатора заказчика ставится в соответствие целое число, которое будет использоваться в витрине в качестве первичного ключа. (В статье "От реляционного к многомерному", опубликованной в первом номере SQL Server Magazine Online за 2001 год, поясняется, почему было выбрано такое представление ключа). Остальная часть пакета заполняет таблицы размерностей и фактографическую таблицу сведениями из производственной базы данных NorthWind.

Мне не хотелось полностью перезагружать витрину данных каждый вечер. Поэтому понадобился второй пакет, который будет запускаться периодически, после того, как отработает пакет загрузки изменений. Второй пакет будет обновлять данные витрины по мере изменений членов размерностей и обработки новых заказов в базе данных NorthWind исходной системой. Второй пакет показан на рисунке 5.

Рис. 5. Пакет обновления данных витрины

Первым делом этот пакет добавляет новых заказчиков в таблицу Customer_Xref многомерной базы данных Northwind_Star. Задача ExecuteSQL в пакете DTS вставляет полученные из исходной базы данных Northwind значения идентификаторов заказчиков customerid, которых еще нет в таблице Customer_Xref многомерной базы данных Northwind_Star. В листинге 1 приведен код T-SQL, который выполняется этой задачей.

ЛИСТИНГ 1: Код T-SQL для обновления сведений о заказчиках в витрине данных.

/*Загрузить сведения о новых заказчиках в таблицу перекрестных ссылок

 для трансляции идентификаторов заказчиков CustomerID из базы данных

  Northwind для многомерной базы данных  Northwind_Star.*/

INSERT INTO

               --вставка в таблицу перекрестных ссылок заказчиков в витрине данных

               --через систему связанных серверов

               northwind_star.northwind_star.dbo.customer_xref

               (

               sourcecustomerid

               )

SELECT

               c.customerid

FROM

               customers as c

WHERE

               --только те строки, которые отсутствуют в таблице

               -- перекрестных ссылок в витрине данных

               NOT EXISTS

               (

               SELECT

                               *

               FROM

                               --связанного сервера к исходным данным о заказчиках

                               northwind_star.northwind_star.dbo.customer_xref as cx

               WHERE

                               cx.sourcecustomerid = c.customerid

               )

На следующем шаге выполняются запросы SQL, содержащие проверки EXISTS, чтобы обновить таблицы размерностей информацией о новых товарах, служащих и поставщиках. Пакет DTS обновляет таблицы размерностей при помощи задачи Transform Data и применяет операторы T-SQL для соединения таблиц исходной базы данных Northwind, расположенной на одном сервере, с таблицами многомерной базы данных Northwind_ Star на другом сервере. На рисунке 6 показано одно из таких предложений JOIN.

Рис. 6. Соединение таблиц с помощью предложения JOIN

Обратите внимание на состоящее из четырех частей имя таблицы в базе данных Northwind_Star, на которую дается ссылка в системе связанных серверов. Задача Transform Data также обновляет информацию о заказчиках. При этом используется суррогатный ключ заказчика в таблице Customer_Xref для добавления сведений о новом заказчике. Проверки EXISTS позволяют выяснить, какие строки следует вставить.

И в завершение процесса в фактографическую таблицу Orders_Fact записываются данные о новых заказах, которые поступили в исходную базу данных Northwind после последнего обновления фактографической таблицы. Рассматривая вопрос о том, каким образом идентифицировать новые заказы, следует принимать во внимание различные соображения. Дата заказа может быть неадекватной, поскольку она зависит от критерия, используемого системой при назначении даты исполнения нового заказа. К примеру, я полагаю, что заказу присваивается та дата, когда сведения о нем были введены в систему. Поэтому я ищу только те заказы, у которых дата более поздняя, чем у самого нового заказа в витрине данных, но при этом она не должна совпадать с датой запуска пакета. При проверке даты заказа я привязываюсь к текущей дате, чтобы загружать сведения только о заказах, введенных вчера и в предшествующие дни. Без такой проверки можно было бы загрузить лишь часть данных, введенных сегодня до обеда. А сведения о заказах, поступивших начиная с обеда и до полуночи сегодняшнего дня, так и не попали бы в витрину, поскольку соответствующие им строки исходной базы данных не будут удовлетворять условию, что дата заказа должна быть позднее даты самого свежего заказа в витрине. Такой подход был бы упрощенным и требовал бы запуска пакета DTS строго после окончания рабочего дня. В листинге 2 приведен код T-SQL, реализующий данную задачу.

ЛИСТИНГ  2: Код T-SQL для обновления сведений о заказах в витрине данных.

/*Загрузить информацию в фактографическую таблицу заказов из таблиц order и order details базы данных NorthWind, которые содержат сведения о заказах и их деталях, используя при этом таблицу перекрестных ссылок заказчиков и таблицу размерности времени для формирования суррогатных ключей для timeid и customerid.*/

SELECT

               od.productid,

               x.destcustomerid,

               o.employeeid,

               t.timeid,

               p.supplierid,

               od.unitprice,

               od.quantity

FROM

               [order details] AS od

JOIN

               orders AS o

ON

               od.orderid = o.orderid

JOIN

               products AS p

ON

               od.productid = p.productid

JOIN

               --используется связанный сервер

               northwind_star.northwind_star.dbo.customer_xref AS x

ON

               o.customerid = x.sourcecustomerid

JOIN

               --используется связанный сервер

               northwind_star.northwind_star.dbo.time AS t

ON

               o.orderdate = t.date

WHERE

               --Загружать из исходной базы данных только те заказы orders,

               --у которых дата формирования предшествует дате загрузки

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

               -- Это позволит загрузить те заказы, которые еще не существуют

               -- в витрине данных, и предотвратит частичную загрузку сведений о заказах

               -- текущего дня, который еще не  кончился.

               o.orderdate < current_timestamp

AND

               o.orderdate >

               (

               SELECT

                               max(t.date)

               FROM

                               northwind_star.northwind_star.dbo.time AS t

               JOIN

                               northwind_star.northwind_star.dbo.orders_fact AS f

               ON

                               t.timeid = f.timeid

               )

Обновление куба

Итак, я обновил витрину данных, построенную по схеме звезды. Теперь мне необходимо обновить куб Analysis Services, построение которого описано в статье "Построение куба из многомерной базы данных", опубликованной в четвертом номере SQL Server Magazine Online за 2001 год. Эта часть задачи легко выполняется в том случае, если имеется SQL Server 2000 DTS или если загружен пакет Data Transformation Services Task Kit 1 для SQL Server 7.0, который позволяет установить дополнительно задачу обработки OLAP Services. Этот пакет можно загрузить из сети по адресу http://www.microsoft.com/sql/downloads/dtskit.htm. Задача обработки Analysis Services предоставляет возможность формировать кубы данных. Далее я покажу, как выполнять частичные обновления куба данных и размерностей для рассматриваемого примера. Предложенный подход позволяет добавлять новые факты в таблицу Orders_Fact, оставляя в ней уже существующие данные, а также вводить новую информацию в размерности. На рисунке 7 показано диалоговое окно задачи обработки Analysis Services.

Рис. 7. Диалоговое окно задачи обработки Analysis Services.

Теперь пакеты DTS готовы к запуску. После первоначальной загрузки сначала что-то должно измениться в исходной базе данных Northwind, и только после этого изменения попадут в многомерную базу Northwind_Star и куб данных Analysis Services. Чтобы увидеть, как работает пакет, добавьте нового заказчика, служащего, поставщика, товар и заказ. В процессе исполнения пакета следите за счетчиками строк, добавляемых на каждом шаге. После этого можно будет обратиться с запросом к базе данных Northwind_Star, чтобы убедиться в наличии сведений о новых заказах и новых членах размерностей. Увидеть новые данные можно при помощи Analysis Manager, просматривая в нем данные куба. Конечно же, если этот куб используется какими-либо клиентскими приложениями, которые также необходимо обновлять, придется выполнить еще и их обновление.

Простое решение сложных проблем

Используя статьи данной серии в качестве руководства к действию, читатели смогут сами создать простую систему для составления отчетов и проведения анализа. Применение методики проектирования многомерных баз данных, Analysis Services и DTS позволят создать витрину данных, сформировать куб OLAP и выполнять частичные обновления этой витрины данных. На первый взгляд такое решение может показаться сложным, но статьи этой серии послужат хорошим ориентиром при построении витрины данных.

Об авторе

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