Меню

Что такое медленно изменяющиеся измерения



Корпоративные хранилища данных. Интеграция систем. Проектная документация.

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

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

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

SCD Type 3

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

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

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

Читайте также:  Приборы для измерения петли фаза ноль ифн 200

Действия, производимые с записями таблицы 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

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

Источник

Медленно-меняющиеся измерения

Проектирую 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 абонента в биллинговой системе):

Для контроля актуальности записи измерения используются поля [StartTime] и [EndTime]. В данном случае NULL в [EndTime] означает, что запись действующая для текущего момента.

Обратите внимание! SCD Lookup index фильтрован по [EndTime] IS NULL — содержит только актуальные записи измерения.

Устройство потока трансформации

Устройство lookup

В lookup должен быть выполнен вот такой запрос:

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

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

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

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

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

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

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

Источник

Статьи / Медленно меняющиеся измерения (часть 1)

17.09.2018 г., перевод статьи Ralph Kimball

Понятие времени пронизывает каждый уголок хранилища данных. Большинство фундаментальных мер, которые мы храним в наших таблицах фактов, являются временными рядами, которые мы тщательно аннотируем метками времени и внешними ключами, соединяющимися с измерениями календарных дат. Но эффект времени не ограничивается только временными метками активностей. Все другие измерения, связанные с таблицами фактов, включая фундаментальные сущности, такие, как «Клиент», «Продукт», «Услуга», «Условия», «Местоположение» и «Сотрудник», также зависят от времени.

Как администраторы хранилищ данных, мы регулярно сталкиваемся с откорректированными описаниями этих объектов. Иногда откорректированное описание просто исправляет ошибку в данных. Но часто оно представляет собой настоящее изменение в определенный момент времени какого-либо элемента, например, «Клиента» или «Продукта». Поскольку эти изменения поступают неожиданно, от случая к случаю, и гораздо реже, чем изменения в таблице фактов, мы называем этот раздел – разделом медленно меняющихся измерений (SCDs).

Читайте также:  Гравиметрический метод измерения кровопотери это

Три типа

Удивительно, но за более чем 30 лет изучения временной дисперсии измерений, я обнаружил, что хранилище данных нуждается только в трех основных реакциях, когда сталкивается с откорректированным или обновленным описанием элемента измерения. Я называю их, соответственно, тип 1, 2 и 3. В этой статье я хочу рассмотреть 1-ый тип. Для того, чтобы обсуждение не было слишком абстрактным я буду использовать измерение «Сотрудник».

Тип 1: Перезапись (SCD Type 1)

Предположим, нас уведомляют, что поле «Город проживания» для Ральфа Кимбалла в измерении «сотрудник» изменилось с Санта-Круз на Боулдер-Крик. Кроме того, нам сообщают, что это исправление ошибки, а не фактическое изменение местоположения. В этом случае, мы можем принять решение перезаписать поле «Город проживания» в измерении «сотрудник» новым значением. Это классическое изменение Type 1 SCD. Изменения Type 1 SCD подходят для исправления ошибок и ситуаций, когда отслеживание истории осознанно игнорируется. Большинство хранилищ данных начинаются с Type 1 SCD по умолчанию.

В то время как Type 1 SCD является самым простым и, казалось бы, самым аккуратным изменением, есть ряд тонких моментов, о которых нужно подумать:

  1. Type 1 SCD уничтожает журнал истории определенного поля. В нашем примере отчеты, ограничивающие или группирующие поле «Город проживания», изменятся. Конечные пользователи должны знать, что это может произойти. Для хранилища данных нужна явная видимая политика для полей Type 1 SCD, в которой говорится: «Мы исправим ошибки» и/или «Мы не сохраняем историю в этом поле, даже если она изменяется».
  2. Предварительно вычисленные агрегаты (включая материализованные представления и автоматические сводные таблицы), зависящие от поля «Город проживания», должны быть переведены в автономный режим в момент перезаписи и повторно вычислены перед возвратом в оперативный режим. Агрегаты, не зависящие от поля «Город проживания», не затрагиваются.
  3. В среде финансовой отчетности с процессами закрытия на конец месяца и в любой среде, соответствующей нормативным и правовым требованиям, изменения Type 1 SCD могут противоречить существующему законодательству. В этих случаях необходимо использовать метод типа 2 (SCD Type 2).
  4. Перезапись одного поля измерения в реляционной среде оказывает довольно небольшое влияние, но может иметь катастрофические последствия в среде интерактивной аналитической обработки (OLAP), если перезапись приводит к перестроению Куба. Внимательно изучите справочное руководство системы OLAP, чтобы узнать, как избежать непредвиденных перестроений Куба.
  5. Все распределенные на предприятии копии измерения «сотрудник», а также агрегаты, при изменении Type 1 SCD должны обновляться одновременно, иначе логика детализации будет повреждена. В распределенной среде изменения Type 1 SCD (и SCD Type 3) должны привести к обновлению номера версии измерения, а все кросс-детализированные приложения должны включать номер версии измерения в свои запросы. Этот процесс был подробно описан в моих статьях, посвященных архитектуре интегрированного корпоративного хранилища данных.
  6. В простом измерении Type 1 SCD, где все поля подлежат перезаписи, изменение Type 1 SCD, например, изменение города проживания для Ральфа Кимбалла, обычно влияет только на одну запись (запись для Ральфа Кимбалла). Но в более сложной среде, где некоторые поля имеют Type 1 SCD, а другие – SCD Type 2, акт перезаписи поля «Город проживания» должен перезаписать все записи для Ральфа Кимбалла. Другими словами, Type 1 SCD влияет на всю историю, а не только на текущую перспективу.

В следующей статье, посвященной медленно меняющимся измерениям, вместо того, чтобы реагировать на изменения перезаписью, я буду тщательно отслеживать изменения, выпуская новую запись измерения. Это классический Type 2 SCD. И, наконец, я покажу, как обрабатывать запрошенное изменение, которое устанавливает альтернативную реальность, которая сосуществует с текущей. Это Type 3 SCD.

Источник

Метод многомерного моделирования

Моделирование таблиц измерений

Медленно меняющиеся измерения

Значение полей таблиц измерений может изменяться со временем . Такие изменения приводят к проблемам в проектировании таблиц измерений . Это связано с тем, что ХД, по определению и своему назначению, предназначены хранить исторические данные и обеспечивать доступ пользователей к ним.

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

В качестве примера рассмотрим таблицы измерений «Покупатель» или «Товары». Предположим, что первичные ключи таблиц не меняются, а атрибуты таблицы измерений могут изменяться со временем . В таком случае выделяют три типа действий.

  • Тип 1. Изменить значение атрибута таблицы измерений на новое значение. При этом будет потеряна хронология.
  • Тип 2. Создать новую строку в таблице измерений с новым значением суррогатного ключа .
  • Тип 3. Создать дополнительный атрибут таблицы измерений с новым значением.

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

Пример 9.3. Медленно меняющиеся измерения . Тип 1

Во втором случае создается новая запись в таблице измерения с новым суррогатным ключом (пример 9.4). С точки зрения бизнес-операций организации у женщины, изменившей семейное положение, меняется табельный номер. При этом строки таблицы фактов «до замужества» будут относиться к строке таблицы измерения с табельным номером 332201, а «после замужества» – к строке таблицы измерения с табельным номером 332209.

Читайте также:  Изохорная теплоемкость единицы измерения

Пример 9.4. Медленно меняющиеся измерения . Тип 2

Табельный номер Фамилия Имя Семейное положение
332201 Иванова Анна Не замужем
332209 Иванова Анна Замужем

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

Пример 9.5. Медленно меняющиеся измерения . Тип 3

Табельный номер Фамилия Имя Предыдущее семейное положение Текущее семейное положение Дата изменений
332201 Иванова Анна Не замужем Замужем 02.05.2009

Обратимся к схеме на рис. 9.8. Рассмотрим измерение «Покупатели». Покупатель может изменить район проживания, и следовательно, изменятся атрибуты «Город», «Адрес» и «Почтовый индекс». Допустим, что организация анализирует факты продаж по регионам. Тогда измерение «Покупатели» может быть отнесено к типу 3. В этом случае необходимо изменить модель измерения «Покупатели», как показано на рис. 9.14.

Измерение «Товары» на схеме рис. 9.8 может быть отнесено к медленно меняющимся измерениям типа 2. Организация может перевести товар в другую категорию (при создании, например, новой категории товаров) и при этом хранить историю изменений, т.е. требуется изменить значение атрибута описание товара. В этом случае, как описано выше, создается новая запись. Обратим внимание на то, что если при этом номер товара не меняется, то нужно ввести в измерение суррогатный ключ , как показано на рис. 9.15.

Измерение «Категория товара» на схеме рис. 9.8 может быть отнесено к медленно меняющимся измерениям типа 1. Маркетологи торговой организации могут уточнить описание категории товара и новое описание должно фигурировать во всех отчетах.

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

При выборе типа медленно меняющихся измерений проектировщику ХД следует придерживаться следующей схемы принятия решений:

Суррогатные ключи предпочтительнее естественных ключей (бизнес-ключей), особенно в случае использования типа 2.

Быстро меняющиеся измерения

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

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

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

В качестве примера быстро меняющихся измерений рассмотрим измерение «Покупатели» ( рис. 9.16). Для больших торговых компаний это измерение содержит значительное число записей и включает в себя большое количество атрибутов , в том числе демографических. Демографические атрибуты часто используются в исследовании предпочтений различных социальных групп, причем покупатели часто мигрируют между социальными группами.

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

Суть приема логического разбиения состоит в следующем: создаются две сущности, одна из которых содержит атрибуты , которые меняются медленно, а другая сущность включает в себя атрибуты , которые меняются быстро. Для измерения «Покупатели», рассмотренного на рис. 9.16, это может быть сделано, как показано на рис. 9.17 ниже.

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

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

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

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

Источник