Медленно меняющиеся измерения (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