Смотрите также:
- Часть I: Когда создавать хранилище данных
- Часть II: Создание новой схемы
- Часть III: Местоположение вашего хранилища данных
В первой части мы рассмотрели преимущества построения хранилища данных, независимого от кубов / системы BI, а во второй части мы рассмотрели, как создать схему таблицы хранилища данных. В третьей части мы рассмотрели, где разместить таблицы хранилища данных. Сегодня мы рассмотрим, как заполнять эти таблицы и синхронизировать их с вашей системой OLTP.
Независимо от того, где находится ваше хранилище данных, самая большая проблема с хранилищем данных, особенно с тем, где вы собираетесь создавать отчеты в реальном времени, заключается в том, что данные публикуются из транзакционной системы (OLTP). По определению, хранилище данных устарело по сравнению с системой OLTP.
Usually when you ask the boss, “How much latency can you accept between the OLTP and data warehouse systems” the boss will reply: none. While that is impossible, the more time to develop and the more money you have will to develop said system, the closer to real time you can get. Always bring up the American Express example used in Part I for proof that your system can accept at least some latency.
Если вы помните примеры кода из части II, большую часть времени вам приходится запрашивать данные OLTP, агрегировать их, а затем загружать в новую схему. Это называется процессом извлечения, преобразования и загрузки данных (ETL) из системы OLTP в хранилище данных. Рабочий процесс обычно происходит следующим образом: в заранее установленное время (при каждом изменении, ежечасно, еженедельно или еженедельно) запрашивать данные OLTP (извлечение), а затем объединять и выравнивать их (преобразование), а затем копировать преобразованные данные в таблицы хранилища данных (звезды или снежинки) (загрузка).
У вас есть несколько вариантов, начиная от ETL почти в реальном времени (очень сложный и дорогой) и заканчивая ночными дампами данных (наименее сложный и наименее дорогой). Вы также можете выполнить полный ETL (полностью стереть таблицы хранилища данных и заполнить каждую операцию) и частично ETL (добавляйте только инкрементные данные согласно временному ряду.) Фактическая загрузка может быть выполнена с помощью триггеров базы данных при добавлении / обновлении / удалении почти в реальном времени к простым запланированным пакетам SQL, которые стирают данные и запускают ваши скрипты загрузки. Скорее всего, вы захотите использовать гибридный подход, в зависимости от зрелости вашей системы. У вас есть три основных варианта, от наименее сложных до самых сложных:
- Прямой дамп базы данных
- Инструмент ETL
- Триггеры базы данных
Если у вас есть длинные временные ряды для публикации ваших данных, скажем, еженедельно или еженедельно, вы можете сделать прямой дамп базы данных. Процесс будет довольно простым. Через регулярные промежутки времени (или вручную) запускается процесс, который запрашивает базу данных OLTP и выполняет все агрегации и т. Д., А затем загружает ее в базу данных промежуточного хранилища данных, затем стирает хранилище производственных данных и загружает данные в него.
Другой вариант — использовать инструмент ETL. Хорошим примером являются службы интеграции SQL Server (SSIS), если вы используете Microsoft SQL Server. (На самом деле SSIS будет работать с несколькими базами данных, вам просто нужен хост SQL Server.) Современный инструмент ETL даст вам возможность разбить работу на логические группы, иметь поток управления, основанный на успехе и неудаче условия, и позволяет откаты.
Типичный рабочий процесс с использованием инструмента ETL заключается в том, что ETL будет работать по расписанию (или на основе условия, такого как сообщение, прибывающее из очереди, или записи, записанной в таблицу администратора), и будет передавать ему параметр (ы). , Этот параметр обычно является временным рядом, и ETL будет выполнять все извлечение данных из базы данных OLTP, отфильтрованных по этому параметру. Средство ETL является наиболее вероятным решением, которое вы будете использовать, особенно если вам приходится часто обновлять систему OLTP в хранилище данных.
Другой вариант — использовать триггеры базы данных. Для тех из вас, кто мало знает о триггерах, они могут привести ко злу. 😉 Тем не менее, это события, которые запускаются при изменении данных. Затем вы можете написать код SQL для запуска при изменении данных, даже код ETL. Триггеры трудно отлаживать и сложно обслуживать, поэтому я рекомендую использовать триггер только тогда, когда вам необходимо обновить хранилище данных в режиме реального времени, и даже в этом случае триггер должен записывать только запись в таблицу администрирования, которую ваш ETL процесс опроса, чтобы начать.
Обычный шаблон проектирования со сложным ETL состоит в том, чтобы сделать все ETL для промежуточного хранилища данных и позволить механизму проверки убедиться, что ETL был успешным. После того, как эта проверка будет выполнена (или компьютером, или человеком, в зависимости от системы), вы можете затем перенести данные из промежуточных таблиц в рабочие таблицы. Специально для этого процесса есть оператор SQL, оператор MERGE. MERGE просматривает две таблицы, просматривает их объединения, сравнивает данные и выполняет добавление, обновление, вставку и удаление, чтобы синхронизировать две таблицы. Вот как мы могли бы сделать это с помощью заявления MERGE и нашей таблицы фактов из части II.
--MERGE the FACT Local to FACT Remote
MERGE dbo.FactOrder as t--target
USING Northwind.dwh.FactOrder as s--source
ON t.ProductID = s.ProductID AND
t.EmployeeID = s.EmployeeID AND
t.ShipperID = s.ShipperID AND
t.TimeKey = s.TimeKey --joining keys
--record exists in target, but data different
WHEN MATCHED AND (s.OrderDate != t.OrderDate OR
s.PostalCode != t.PostalCode OR
s.[Total Sales] != t.[Total Sales] OR
s.Discount != t.Discount OR
s.[Unit Sales] != t.[Unit Sales]) THEN
UPDATE SET t.OrderDate = s.OrderDate,
t.PostalCode = s.PostalCode,
t.[Total Sales] = s.[Total Sales],
t.Discount = s.Discount,
t.[Unit Sales] = s.[Unit Sales]
WHEN NOT MATCHED BY SOURCE THEN
--only in target, get rid of it
Delete
WHEN NOT MATCHED BY TARGET THEN
--record only exists in source
INSERT VALUES (s.OrderDate, s.PostalCode, s.ProductID,s.EmployeeID,
s.ShipperID, s.[Total Sales], s.Discount, s.[Unit Sales], s.[TimeKey]);--required!!
Далее мы разберемся с вариантами разработки приложений.