Медленно меняющиеся измерения (Slowly Changing Dimensions) в корпоративном хранилище данных

Медленно меняющиеся измерения (Slowly Changing Dimensions) в корпоративном хранилище данных

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

Отслеживание изменений значений аналитических измерений в хранилище данных решается путем применения механизма медленно меняющихся измерений (Slowly Changing Dimensions, SCD).

В данной статье рассматриваются наиболее популярные типы медленно меняющихся измерений – SCD Type 1, SCD Type 2 и SCD Type 3.

Общий принцип отслеживания изменений

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

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

В зависимости от действия, производимого с записью (вставка, изменение, удаление), для записи может быть определен один из следующих статусов:

  • На добавление - запись новая и её необходимо добавить в таблицу.
  • На изменение – запись существует в таблице, но в каких-то полях изменились содержимое.
  • На удаление - запись существует в таблице, но теперь её необходимо удалить из неё.

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

SCD Type 1

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

Действия, производимые с записями таблицы SCD Type 1 в зависимости от их статуса, представлены в таблице ниже.

Статус записи Действие
На добавление Записи присваивается следующий по порядку уникальный идентификатор. Запись добавляется в таблицу.
На изменение Запись изменяется.
На удаление Никаких действий над записью не производится. Удалять запись из таблицы нельзя, потому что к ней могут быть «привязаны» фактические данные.

Приведем наглядный пример отслеживания изменений для случая, когда изменяется содержимое одного из полей записи (вставка и удаление не представляют интереса).

Допустим, существует таблица, в которой хранится информация по клиентским данным (CST), состоящая из 2х полей: ID – первичный ключ записи и NAME – наименование клиента.

ID NAME
1 ИП Иванов

В случае изменения существующего наименования «ИП Иванов» на «ООО "Иванов и Ко."» запись в таблице изменится следующим образом:

ID NAME
1 ООО "Иванов и Ко."

SCD Type 2

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

Структура таблицы типа SCD Type 2 помимо основных её полей, несущих информацию для пользователя, включает в себя следующие поля:
ID – уникальный идентификатор записи (входит в состав первичного ключа таблицы);
EFCT_DT – дата, с которой запись действительна (входит в состав первичного ключа таблицы);
END_DT – дата, до которой запись действительна (для всех активных записей она установлена по умолчанию, например, в 01.01.2999);
IS_ACT_IND – индикатор активной записи: 1 – активна; 0 – не активна;
IS_DEL_IND – индикатор удаленной записи: 1 – удалена; 0 – не удалена.

Действия, производимые с записями таблицы SCD Type 2 в зависимости от их статуса, представлены в таблице ниже.

Статус записи Действие
На добавление Полю ID присваивается следующий по порядку уникальный идентификатор.
Полю EFCT_DT присваивается текущая дата (SYSDATE).
Полю END_DT присваивается дата 01.01.2999.
Полю IS_ACT_IND присваивается 1.
Полю IS_DEL_IND присваивается 0.
Запись добавляется в таблицу.
На изменение Полю END_DT изменившейся записи присваивается текущая дата (SYSDATE).
Полю IS_ACT_IND изменившейся записи присваивается 0.
Добавляется новая запись в таблицу, у которой:
Полю ID присваивается такой же идентификатор, как и у измененной записи.
Полю EFCT_DT присваивается текущая дата (SYSDATE).
Полю END_DT присваивается дата 01.01.2999.
Полю IS_ACT_IND присваивается 1.
Полю IS_DEL_IND присваивается 0.
На удаление Полю END_DT присваивается текущая дата (SYSDATE).
Полю IS_ACT_IND присваивается 0.
Полю IS_DEL_IND присваивается 1.

Приведем наглядный пример для случая, когда запись изменяется.

ID NAME EFCT_DT END_DT IS_ACT_IND IS_DEL_IND
1 ИП Иванов 01.10.2010 01.01.2999 1 0

В случае изменения существующего наименования «ИП Иванов» на «ООО "Иванов и Ко."» записи в таблице будут выглядеть следующим образом:

ID NAME EFCT_DT END_DT IS_ACT_IND IS_DEL_IND
1 ИП Иванов 01.10.2010 10.11.2010 0 0
1 ООО "Иванов и Ко." 10.11.2010 01.01.2999 1 0

SCD Type 3

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

Если SCD Type 2 позволяет отслеживать неограниченное число изменений, то в SCD Type 3 количество отслеживаемых изменений ограничивается количеством дополнительных полей.

Структура таблицы типа SCD Type 3 помимо основных ее полей, несущих информацию для пользователя, включает в себя следующие поля:
NAME_OLD – предыдущее значение поля NAME, значение до изменения (NAME приведено в качестве примера, отслеживать изменения можно любых полей таблицы);
NAME_UPD_DT – дата изменения значения поля NAME.

Действия, производимые с записями таблицы SCD Type 3 в зависимости от их статуса, представлены в таблице ниже.

Статус записи Действие
На добавление Записи присваивается следующий по порядку уникальный идентификатор (в поле ID).
В поле NAME присваивается загружаемое значение.
В поле NAME_OLD присваивается значение по умолчанию, например «NA».
Полю NAME_UPD_DT присваивается текущая дата (SYSDATE) или дата по умолчанию, например 01.01.1900.
Запись добавляется в таблицу.
На изменение В поле NAME_OLD присваивается значение из поля NAME.
В поле NAME присваивается загружаемое значение.
На удаление Никаких действий над записью не производится.

Приведем наглядный пример для случая, когда запись изменяется.

ID NAME NAME_OLD NAME_UPD_DT
1 ИП Иванов NA 01.10.2010

В случае изменения существующего наименования «ИП Иванов» на «ООО "Иванов и Ко."» записи в таблице будут выглядеть следующим образом:

ID NAME NAME_OLD NAME_UPD_DT
1 ООО "Иванов и Ко." ИП Иванов 10.11.2010