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

Размеры в управление данными и хранилище данных содержат относительно статические данные о таких объектах, как географические местоположения, клиенты или продукты. Данные, собранные Медленно меняющиеся размеры (SCD) меняются медленно, но непредсказуемо, а не по регулярному графику.^[1]

Некоторые сценарии могут вызвать ссылочная целостность проблемы.

Например, база данных может содержать таблица фактов хранит записи о продажах. Эта таблица фактов будет связана с измерениями с помощью внешние ключи. Одно из этих измерений может содержать данные о продавцах компании: например, о региональных офисах, в которых они работают. Однако иногда продавцов переводят из одного регионального офиса в другой. Для целей исторической отчетности по продажам может потребоваться учет того факта, что конкретный продавец был назначен в конкретный региональный офис ранее, тогда как этот продавец теперь назначен в другой региональный офис.^{[требуется разъяснение ]}

Решение этих проблем включает в себя методологии управления SCD, которые называются типами от 0 до 6. SCD типа 6 также иногда называют гибридными SCD.

Тип 0: сохранить оригинал

Атрибуты измерения Тип 0 никогда не меняются и назначаются атрибутам, которые имеют постоянные значения или описаны как «Исходные». Примеры: Дата рождения, Первоначальный кредитный рейтинг. Тип 0 применяется к большинству атрибутов измерения даты.^[2]

Тип 1: перезапись

Этот метод перезаписывает старые данные новыми и поэтому не отслеживает исторические данные.

Пример таблицы поставщиков:

Supplier_Key	Код поставщиком	Наименование поставщика	Supplier_State
123	ABC	Acme Supply Co	CA

В приведенном выше примере Supplier_Code - это естественный ключ а Supplier_Key - это суррогатный ключ. Технически суррогатный ключ не нужен, поскольку строка будет уникальной по естественному ключу (Supplier_Code).

Если поставщик перенесет штаб-квартиру в Иллинойс, запись будет перезаписана:

Supplier_Key	Код поставщиком	Наименование поставщика	Supplier_State
123	ABC	Acme Supply Co	IL

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

Если кто-то рассчитал агрегированную таблицу, суммирующую факты по состояниям, ее необходимо будет пересчитать при изменении Supplier_State.^[1]

Тип 2: добавить новую строку

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

Например, если поставщик переезжает в Иллинойс, номера версий будут последовательно увеличиваться:

Supplier_Key	Код поставщиком	Наименование поставщика	Supplier_State	Версия
123	ABC	Acme Supply Co	CA	0
124	ABC	Acme Supply Co	IL	1

Другой способ - добавить столбцы «Дата вступления в силу».

Supplier_Key	Код поставщиком	Наименование поставщика	Supplier_State	Дата начала	Дата окончания
123	ABC	Acme Supply Co	CA	2000-01-01T00: 00: 00	2004-12-22T00: 00: 00
124	ABC	Acme Supply Co	IL	2004-12-22T00: 00: 00	`НОЛЬ`

Дата и время начала второй строки равны дате / времени окончания предыдущей строки. Нулевое значение End_Date во второй строке указывает текущую версию кортежа. Вместо этого в качестве конечной даты может использоваться стандартизованная суррогатная высокая дата (например, 9999-12-31), чтобы поле можно было включить в индекс и чтобы при запросе не требовалась подстановка нулевого значения.

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

Supplier_Key	Код поставщиком	Наименование поставщика	Supplier_State	Дата вступления в силу	Current_Flag
123	ABC	Acme Supply Co	CA	2000-01-01T00: 00: 00	N
124	ABC	Acme Supply Co	IL	2004-12-22T00: 00: 00	Y

Значение Current_Flag «Y» указывает текущую версию кортежа.

Транзакции, которые ссылаются на конкретный суррогатный ключ (Supplier_Key) затем навсегда привязаны к временным отрезкам, определенным этой строкой медленно меняющейся таблицы измерений. Сводная таблица, обобщающая факты по штатам, продолжает отражать историческое состояние, то есть состояние, в котором поставщик находился во время транзакции; обновление не требуется. Чтобы ссылаться на объект через естественный ключ, необходимо удалить уникальное ограничение, создающее Ссылочная целостность СУБД невозможно.

Если в содержимое измерения были внесены ретроактивные изменения, или если в измерение добавлены новые атрибуты (например, столбец Sales_Rep), даты вступления которых в силу отличаются от уже определенных, то это может привести к тому, что существующие транзакции должны быть обновлено, чтобы отразить новую ситуацию. Это может быть дорогостоящей операцией с базой данных, поэтому SCD 2-го типа не лучший выбор, если размерная модель подвержена частым изменениям.^[1]

Тип 3: добавить новый атрибут

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

Supplier_Key	Код поставщиком	Наименование поставщика	Original_Supplier_State	Дата вступления в силу	Current_Supplier_State
123	ABC	Acme Supply Co	CA	2004-12-22T00: 00: 00	IL

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

Одним из вариантов этого является создание поля Previous_Supplier_State вместо Original_Supplier_State, которое будет отслеживать только самые последние исторические изменения.^[1]

Тип 4: добавить таблицу истории

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

В приведенном выше примере исходное имя таблицы Поставщик и таблица истории Supplier_History.

Поставщик
Supplier_key	Код поставщиком	Наименование поставщика	Supplier_State
124	ABC	Acme & Johnson Supply Co	IL

Supplier_History
Supplier_key	Код поставщиком	Наименование поставщика	Supplier_State	Create_Date
123	ABC	Acme Supply Co	CA	2003-06-14T00: 00: 00
124	ABC	Acme & Johnson Supply Co	IL	2004-12-22T00: 00: 00

Этот метод похож на то, как таблицы аудита базы данных и изменение сбора данных функции техники.

Тип 5

Техника типа 5 основывается на мини-измерении типа 4 путем встраивания ключа мини-измерения «текущего профиля» в базовое измерение, которое перезаписывается как атрибут типа 1. Этот подход, названный типом 5, поскольку 4 + 1 равно 5, позволяет получить доступ к назначенным в данный момент значениям атрибутов мини-измерения вместе с другими значениями базового измерения без привязки через таблицу фактов. По логике, мы обычно представляем базовое измерение и текущую опору профиля мини-измерения в виде единой таблицы на уровне представления. Атрибуты аутригеров должны иметь отдельные имена столбцов, например «Текущий уровень дохода», чтобы отличать их от атрибутов в мини-измерении, связанном с таблицей фактов. Команда ETL должна обновлять / перезаписывать ссылку на мини-измерение типа 1 всякий раз, когда текущее мини-измерение изменяется с течением времени. Если подход аутригера не обеспечивает удовлетворительной производительности запроса, тогда атрибуты мини-измерения могут быть физически встроены (и обновлены) в базовое измерение.^[3]

Тип 6: комбинированный подход

В Тип 6 Метод сочетает подходы 1, 2 и 3 типов (1 + 2 + 3 = 6). Одним из возможных объяснений происхождения термина было то, что он был придуман Ральф Кимбалл во время разговора со Стивеном Пейсом из Калидо^{[нужна цитата ]}. Ральф Кимбалл называет этот метод «непредсказуемыми изменениями с наложением одной версии» в Набор инструментов хранилища данных.^[1]

Таблица Supplier начинается с одной записи для нашего примера поставщика:

Supplier_Key	Row_Key	Код поставщиком	Наименование поставщика	Текущее состояние	Historical_State	Дата начала	Дата окончания	Current_Flag
123	1	ABC	Acme Supply Co	CA	CA	2000-01-01T00: 00: 00	9999-12-31T23: 59: 59	Y

Current_State и Historical_State одинаковы. Необязательный атрибут Current_Flag указывает, что это текущая или самая последняя запись для этого поставщика.

Когда Acme Supply Company переезжает в Иллинойс, мы добавляем новую запись, как при обработке типа 2, однако ключ строки включен, чтобы гарантировать, что у нас есть уникальный ключ для каждой строки:

Supplier_Key	Row_Key	Код поставщиком	Наименование поставщика	Текущее состояние	Historical_State	Дата начала	Дата окончания	Current_Flag
123	1	ABC	Acme Supply Co	IL	CA	2000-01-01T00: 00: 00	2004-12-22T00: 00: 00	N
123	2	ABC	Acme Supply Co	IL	IL	2004-12-22T00: 00: 00	9999-12-31T23: 59: 59	Y

Мы перезаписываем информацию Current_State в первой записи (Row_Key = 1) новой информацией, как при обработке типа 1. Мы создаем новую запись для отслеживания изменений, как при обработке типа 2. И мы сохраняем историю во втором столбце State (Historical_State), который включает обработку типа 3.

Например, если поставщик снова переместится, мы добавим еще одну запись в измерение «Поставщик» и перезапишем содержимое столбца Current_State:

Supplier_Key	Row_Key	Код поставщиком	Наименование поставщика	Текущее состояние	Historical_State	Дата начала	Дата окончания	Current_Flag
123	1	ABC	Acme Supply Co	Нью-Йорк	CA	2000-01-01T00: 00: 00	2004-12-22T00: 00: 00	N
123	2	ABC	Acme Supply Co	Нью-Йорк	IL	2004-12-22T00: 00: 00	2008-02-04T00: 00: 00	N
123	3	ABC	Acme Supply Co	Нью-Йорк	Нью-Йорк	2008-02-04T00: 00: 00	9999-12-31T23: 59: 59	Y

Реализация фактов типа 2 / типа 6

Суррогатный ключ типа 2 с атрибутом типа 3

Во многих реализациях SCD типа 2 и типа 6 суррогатный ключ из измерения помещается в таблицу фактов вместо естественный ключ когда данные фактов загружаются в хранилище данных.^[1] Суррогатный ключ выбирается для данной записи факта на основе ее даты вступления в силу, а также Start_Date и End_Date из таблицы измерений. Это позволяет легко присоединить данные фактов к правильным данным измерений для соответствующей даты вступления в силу.

Вот таблица поставщиков, которую мы создали выше, используя методологию гибрида типа 6:

Supplier_Key	Код поставщиком	Наименование поставщика	Текущее состояние	Historical_State	Дата начала	Дата окончания	Current_Flag
123	ABC	Acme Supply Co	Нью-Йорк	CA	2000-01-01T00: 00: 00	2004-12-22T00: 00: 00	N
124	ABC	Acme Supply Co	Нью-Йорк	IL	2004-12-22T00: 00: 00	2008-02-04T00: 00: 00	N
125	ABC	Acme Supply Co	Нью-Йорк	Нью-Йорк	2008-02-04T00: 00: 00	9999-12-31T23: 59: 59	Y

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

ВЫБРАТЬ  Доставка.стоимость доставки,  поставщик.наименование поставщика,  поставщик.историческое_стейт,  поставщик.Текущее состояниеИЗ ДоставкаВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ поставщик  НА Доставка.поставщик_key = поставщик.поставщик_key;

Реализация чистого типа 6

Наличие суррогатного ключа типа 2 для каждого временного интервала может вызвать проблемы, если размерность может измениться.^[1]

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

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

Это также дает больше возможностей при запросе транзакций.

Вот таблица поставщиков с использованием чистой методологии Типа 6:

Supplier_Key	Код поставщиком	Наименование поставщика	Supplier_State	Дата начала	Дата окончания
456	ABC	Acme Supply Co	CA	2000-01-01T00: 00: 00	2004-12-22T00: 00: 00
456	ABC	Acme Supply Co	IL	2004-12-22T00: 00: 00	2008-02-04T00: 00: 00
456	ABC	Acme Supply Co	Нью-Йорк	2008-02-04T00: 00: 00	9999-12-31T23: 59: 59

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

ВЫБРАТЬ  поставщик.Код поставщиком,  поставщик.provider_stateИЗ поставщикВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ Доставка  НА поставщик.поставщик_key = Доставка.поставщик_key И Доставка.Дата доставки >= поставщик.Дата начала И Доставка.Дата доставки < поставщик.Дата окончания;

Запись факта с датой вступления в силу (Delivery_Date) 9 августа 2001 г. будет связана с Supplier_Code в ABC с Supplier_State как «CA». Запись факта с датой вступления в силу 11 октября 2007 г. также будет связана с тем же кодом Supplier_Code ABC, но со статусом Supplier_State "IL".

Этот более сложный подход имеет ряд преимуществ, в том числе:

Ссылочная целостность СУБД теперь возможно, но нельзя использовать Supplier_Code как иностранный ключ в таблице Product и используя Supplier_Key в качестве внешнего ключа, каждый продукт привязан к определенному временному интервалу.
Если в факте указано более одной даты (например, Дата заказа, Дата доставки, Дата оплаты счета), можно выбрать, какую дату использовать для запроса.
Вы можете выполнять запросы «как сейчас», «как во время транзакции» или «как в определенный момент времени», изменив логику фильтрации даты.
Вам не нужно повторно обрабатывать таблицу фактов, если есть изменение в таблице измерений (например, добавление дополнительных полей ретроспективно, которые изменяют временные интервалы, или если кто-то допустил ошибку в датах в таблице измерений, их можно легко исправить) .
Вы можете представить двухвременной даты в таблице измерений.
Вы можете присоединить этот факт к нескольким версиям таблицы измерений, чтобы предоставить одну и ту же информацию с разными датами вступления в силу в одном запросе.

В следующем примере показано, как можно использовать конкретную дату, например '2012-01-01T00: 00: 00' (которая может быть текущим datetime).

ВЫБРАТЬ  поставщик.Код поставщиком,  поставщик.provider_stateИЗ поставщикВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ Доставка  НА поставщик.поставщик_key = Доставка.поставщик_key И поставщик.Дата начала <= '2012-01-01T00: 00: 00' И поставщик.Дата окончания > '2012-01-01T00: 00: 00';

И суррогатный, и естественный ключ

Альтернативная реализация - разместить обе то суррогатный ключ и естественный ключ в таблицу фактов.^[4] Это позволяет пользователю выбирать соответствующие записи измерений на основе:

первичная дата вступления в силу записи факта (см. выше),
самая свежая или актуальная информация,
любая другая дата, связанная с записью факта.

Этот метод обеспечивает более гибкие связи с измерением, даже если вы использовали подход Типа 2 вместо Типа 6.

Вот таблица поставщиков, которую мы могли бы создать, используя методологию типа 2:

Supplier_Key	Код поставщиком	Наименование поставщика	Supplier_State	Дата начала	Дата окончания	Current_Flag
123	ABC	Acme Supply Co	CA	2000-01-01T00: 00: 00	2004-12-22T00: 00: 00	N
124	ABC	Acme Supply Co	IL	2004-12-22T00: 00: 00	2008-02-04T00: 00: 00	N
125	ABC	Acme Supply Co	Нью-Йорк	2008-02-04T00: 00: 00	9999-12-31T23: 59: 59	Y

Следующий SQL извлекает самые последние Supplier_Name и Supplier_State для каждой записи факта:

ВЫБРАТЬ  Доставка.стоимость доставки,  поставщик.наименование поставщика,  поставщик.provider_stateИЗ ДоставкаВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ поставщик  НА Доставка.Код поставщиком = поставщик.Код поставщикомКУДА поставщик.current_flag = 'Y';

Если в записи факта указано несколько дат, факт можно присоединить к измерению, используя другую дату вместо первичной даты вступления в силу. Например, таблица Delivery может иметь первичную дату вступления в силу Delivery_Date, но также может иметь Order_Date, связанный с каждой записью.

Следующий SQL извлекает правильные Supplier_Name и Supplier_State для каждой записи факта на основе Order_Date:

ВЫБРАТЬ  Доставка.стоимость доставки,  поставщик.наименование поставщика,  поставщик.provider_stateИЗ ДоставкаВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ поставщик  НА Доставка.Код поставщиком = поставщик.Код поставщиком И Доставка.Дата заказа >= поставщик.Дата начала И Доставка.Дата заказа < поставщик.Дата окончания;

Некоторые предостережения:

Ссылочная целостность СУБД невозможно, поскольку нет уникального ключа для создания связи.
Если для решения проблемы, описанной выше, устанавливается связь с суррогатом, то одно заканчивается сущностью, привязанной к определенному временному интервалу.
Если запрос на соединение написан неправильно, он может вернуть повторяющиеся строки и / или дать неправильные ответы.
Сравнение дат может работать некорректно.
Немного Бизнес-аналитика инструменты плохо справляются с созданием сложных объединений.
В ETL Процессы, необходимые для создания таблицы измерений, должны быть тщательно спроектированы, чтобы гарантировать отсутствие дублирования периодов времени для каждого отдельного элемента справочных данных.

Комбинирование типов

Пример модели scd

К разным столбцам таблицы можно применять разные типы SCD. Например, мы можем применить Тип 1 к столбцу Supplier_Name и Тип 2 к столбцу Supplier_State той же таблицы.

Смотрите также

Примечания

^ ^а ^б ^c ^d ^е ^ж ^грамм Кимбалл, Ральф; Росс, Марджи. Набор инструментов хранилища данных: полное руководство по размерному моделированию.
^ http://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/
^ https://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/
^ Росс, Марджи; Кимбалл, Ральф (1 марта 2005 г.). «Медленно меняющиеся размеры не всегда так просто, как 1, 2, 3». Интеллектуальное предприятие.