Медленно-меняющиеся измерения
Проектирую Data Warehouse с использованием технологий Microsoft SQL Server.
После анализа имеющегося решения для медленно-меняющихся измерений в SQL Server Integration Services (SSIS), я придумал альтернативную реализацию, основанную на сравнении контрольных сумм, а не значений полей.
Опишу некоторые характеристики обоих реализаций — стандартной и моей альтернативной.
Стандартный компонент SCD в SSIS
Плюсы
- Простота в начальной настройке.
- Абсолютная гарантия, что изменение атрибута будет отражено в измерении. Достигается тем, что сравнивается каждое поле с каждым полем, а не контрольные суммы (которые с микроскопической вероятностью могут совпасть для разных кобинаций значений полей).
Минусы
- Работает медленно, поскольку сравнивает каждое поле с каждым полем, извлекая из таблиц все данные.
- Некоторая хрупкость при последующей настройке. Компонент генерирует последовательность трансформаций, которая управляется через настройки компонента. Однако, трансформации можно кастомизировать по отдельности и, затем, при изменении настроек компонента потерять кастомизацию.
- Иногда ложно определяет изменения, когда их не было, в результате получается быстро растущее измерение (что весьма губительно для хранилища данных). Не знаю с чем именно это связано, возможно, влияют поля, содержащие NULL.
Альтернативная реализация
Плюсы
- Работает быстро, поскольку сравнивает контрольные суммы значений полей, сохранённые на уровне строк и использует индексы, куда входят только ключи и эти контрольные суммы.
- Полностью контролируемая реализация.
Минусы
- Реализация требует освоения того, что написано ниже и некоторой аккуратности. Впрочем, для BI-разработчика это в любом случае обязательно.
Краткое описание
- В измерении нужно создать поле для контрольной суммы изменяющихся атрибутов. Пусть оно называется [Checksum].
- В измерении, для полей, которые являются бизнес-ключами (ключами в источниках данных) нужно создать составной индекс и добавить в него (INCLUDE) поле контрольной суммы ([Checksum]), наложив в индексе фильтр по признаку действующей записи.
- В потоке трансформации (SSIS) расчитать контрольную сумму потенциальной строки измерения, затем в кастомном lookup (C#) попытаться присоединить имеющуюся строку измерения (её материализацию из индекса), если получилось, по контрольной сумме определить изменилась ли строка (изменилась — вставить запись измерения, закрыть предыдущую), если не получилось — вставить запись измерения (появился новый бизнес-ключ).
Для вычисления контрольной суммы в потоке транформации я использовал компонент Konesans Checksum Transformation, хотя позднее отказался от него и начал считать её на уровне SQL-запроса при выборке из нормализованной части хранилища.
Полное описание по шагам
Создание индекса
Вот как объявляется индекс (бизнес-ключ в данном случае составной — биллинговая система и ID абонента в биллинговой системе):
-- SCD Lookup index
CREATE UNIQUE INDEX IX_SCDLookup ON [Dim].[Customer] ([BillingCustomerID],[BillingName],[EndTime]) INCLUDE ([Checksum]) WHERE [EndTime] IS NULL;
GO
Для контроля актуальности записи измерения используются поля [StartTime] и [EndTime]. В данном случае NULL в [EndTime] означает, что запись действующая для текущего момента.
Обратите внимание! SCD Lookup index фильтрован по [EndTime] IS NULL — содержит только актуальные записи измерения.
Устройство потока трансформации

Устройство lookup
В lookup должен быть выполнен вот такой запрос:
SELECT [Checksum] FROM [Dim].[Customer] WITH(INDEX([IX_SCDLookup]))
WHERE [BillingCustomerID] = ? AND [BillingName] = ? AND EndTime IS NULL
В плане выполнения этого запроса должен быть только Index Seek и не должно быть обращения к таблицам — это то место, где возникает преимущество в скорости, по сравнению со стандартным компонентом SCD.
Для скрипта lookup создается три выхода — New, Untouched и Updated:

Выходы объединяются в одну группу взаимоисключения (ExclusionGroup) и объявляются синхронными со входом (Input 0).
Строка, поступившая на вход, должна выйти только через один из выходов.
Код делаем так:

Добавляем инициализацию соединения и lookup-запрос в PreExecute:

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

В этом простом примере все атрибуты измерения имеют тип 2 (читай Ральфа Кимбала). Более сложная логика потребует отдельных контрольных сумм для атрибутов разных типов, однако, может быть реализована путём расширения вышеописанного подхода.