SSIS - Slowly Changing Dimensions

Столкнувшись с минусами стандартного компонента Slowly Changing Dimension (далее SCD) в SQL Server Integration Services (далее SSIS), я разработал собственное решение.

Минусы стандартного SCD

Начну с минусов стандартного компонента SCD из SSIS:

1. Работает медленно. Потому, что сравнивает каждое поле с каждым полем, извлекая из таблиц все данные. Здесь моё решение выигрывает в разы.
2. Генерирует последовательность трансформаций в потоке данных, которая управляется через настройки компонента. При изменении настроек можно потерять изменение, сделанное на уровне одной трансформации.
3. Иногда ложно определяет изменения (с чем связано не знаю, но предполагаю, что с полями измерения, содержащими NULL). В результате получается постоянно растущее измерение.

Что нужно сделать?

1. В измерении DWH создать поле для контрольной суммы изменяющихся атрибутов. Пусть оно называется [Checksum].
2. В измерении DWH для полей, которые являются бизнес ключами (ключами в источниках данных), а также для поля признака действующей записи измерения, создать составной индекс и добавить в него (INCLUDE) поле контрольной суммы (Checksum), наложив фильтр по признаку действующей записи.
3. В потоке SSIS расчитывать контрольную сумму (для этого подойдет Konesans Checksum Transformation, но не обязательно), затем сравнивать через lookup (у меня написан на скрипте) с контрольной суммой в измерении DWH, затем выполнять одну из двух ветвей потока данных — либо создание новой записи измерения (в случае нового бизнес-ключа), либо создание измененной записи измерения (в случае имеющегося бизнес-ключа).

Запрос lookup должен быть написан таким образом, чтоб использовать индекс, поэтому стандартная транформация SSIS, называемая "Lookup" не подойдёт. Такой индекс, как описано в пункте 2, нужен, чтоб не дергать таблицу, поскольку все необходимые поля и контрольная сумма включена в него.

Вот как объявляется индекс (бизнес ключ в данном случае составной — биллинговая система и 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:

SELECT [Checksum] FROM [Dim].[Customer] WITH(INDEX([IX_SCDLookup]))
WHERE [BillingCustomerID] = ? AND [BillingName] = ? AND EndTime IS NULL

В плане выполнения этого запроса должен быть только Index Seek и не должно быть обращения к таблицам — это то место, где возникает преимущество в скорости, по сравнению со стандартным компонентом SCD.

Вот как выглядит поток данных в SSIS:

Для скрипта lookup создается три выхода — New, Untouched и Updated:

Выходы объединяются в одну группу взаимоисключения (ExclusionGroup) и объявляются синхронными со входом (Input 0).

Строка, поступившая на вход, должна выйти только через один из выходов.

В коде скрипта делаем так:

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

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

Мой вариант решения хорош, если все атрибуты измерения обрабатываются как type 2 (читай Кимбала), иначе нужно что-то к этому добавить.

P.S. А вот мой усовершенствованный вариант в посте на хабре.