Проектирую Data Warehouse с использованием технологий Microsoft SQL Server.

После анализа имеющегося решения для медленно-меняющихся измерений в SQL Server Integration Services (SSIS), я придумал альтернативную реализацию, основанную на сравнении контрольных сумм, а не значений полей.

Опишу некоторые характеристики обоих реализаций — стандартной и моей альтернативной.

Стандартный компонент SCD в SSIS

Плюсы

1. Простота в начальной настройке.
2. Абсолютная гарантия, что изменение атрибута будет отражено в измерении. Достигается тем, что сравнивается каждое поле с каждым полем, а не контрольные суммы (которые с микроскопической вероятностью могут совпасть для разных кобинаций значений полей).

Минусы

1. Работает медленно, поскольку сравнивает каждое поле с каждым полем, извлекая из таблиц все данные.
2. Некоторая хрупкость при последующей настройке. Компонент генерирует последовательность трансформаций, которая управляется через настройки компонента. Однако, трансформации можно кастомизировать по отдельности и, затем, при изменении настроек компонента потерять кастомизацию.
3. Иногда ложно определяет изменения, когда их не было, в результате получается быстро растущее измерение (что весьма губительно для хранилища данных). Не знаю с чем именно это связано, возможно, влияют поля, содержащие NULL.

Альтернативная реализация

Плюсы

1. Работает быстро, поскольку сравнивает контрольные суммы значений полей, сохранённые на уровне строк и использует индексы, куда входят только ключи и эти контрольные суммы.
2. Полностью контролируемая реализация.

Минусы

1. Реализация требует освоения того, что написано ниже и некоторой аккуратности. Впрочем, для BI-разработчика это в любом случае обязательно.

Краткое описание

1. В измерении нужно создать поле для контрольной суммы изменяющихся атрибутов. Пусть оно называется [Checksum].
2. В измерении, для полей, которые являются бизнес-ключами (ключами в источниках данных) нужно создать составной индекс и добавить в него (INCLUDE) поле контрольной суммы ([Checksum]), наложив в индексе фильтр по признаку действующей записи.
3. В потоке трансформации (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 (читай Ральфа Кимбала). Более сложная логика потребует отдельных контрольных сумм для атрибутов разных типов, однако, может быть реализована путём расширения вышеописанного подхода.