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

Медленно меняющееся измерение ( SCD ) в управлении данными и хранилищах данных — это измерение , которое содержит относительно статические данные , которые могут изменяться медленно, но непредсказуемо, а не по регулярному графику. Некоторыми примерами типичных медленно меняющихся измерений являются такие сущности, как названия географических мест, клиентов или продуктов.

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

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

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

Тип 0: сохранить исходное

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

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

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

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

Ключ_поставщика	Код_поставщика	Имя_поставщика	Поставщик_Штат
123	АВС	Компания Acme Supply Co.	ЧТО

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

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

Ключ_поставщика	Код_поставщика	Имя_поставщика	Поставщик_Штат
123	АВС	Компания Acme Supply Co.	ТО

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

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

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

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

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

Ключ_поставщика	Код_поставщика	Имя_поставщика	Поставщик_Штат	Версия
123	АВС	Компания Acme Supply Co.	ЧТО	0
124	АВС	Компания Acme Supply Co.	ТО	1
125	АВС	Компания Acme Supply Co.	Нью-Йорк	2

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

Ключ_поставщика	Код_поставщика	Имя_поставщика	Поставщик_Штат	Дата начала	Конечная_дата
123	АВС	Компания Acme Supply Co.	ЧТО	2000-01-01T00:00:00	2004-12-22T00:00:00
124	АВС	Компания Acme Supply Co.	ТО	2004-12-22T00:00:00	NULL

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

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

Ключ_поставщика	Код_поставщика	Имя_поставщика	Поставщик_Штат	Дата вступления в силу	Текущий_флаг
123	АВС	Компания Acme Supply Co.	ЧТО	2000-01-01T00:00:00	Н
124	АВС	Компания Acme Supply Co.	ТО	2004-12-22T00:00:00	И

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

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

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

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

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

Ключ_поставщика	Код_поставщика	Имя_поставщика	Исходное_состояние_поставщика	Дата вступления в силу	Текущее_состояние_поставщика
123	АВС	Компания Acme Supply Co.	ЧТО	2004-12-22T00:00:00	ТО

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

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

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

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

В приведенном ниже примере исходное имя таблицы — «Поставщик», а таблица истории — «Поставщик_история»:

Поставщик

Ключ_поставщика	Код_поставщика	Имя_поставщика	Поставщик_Штат
124	АВС	Acme & Johnson Supply Co.	ТО

Поставщик_История

Ключ_поставщика	Код_поставщика	Имя_поставщика	Поставщик_Штат	Create_Date
123	АВС	Компания Acme Supply Co.	ЧТО	2003-06-14T00:00:00
124	АВС	Acme & Johnson Supply Co.	ТО	2004-12-22T00:00:00

Этот метод аналогичен тому, как работают таблицы аудита базы данных и методы сбора измененных данных .

Тип 5

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

:

Метод 6-го типа объединяет подходы 1-го, 2-го и 3-го типов (1+2+3=6). Одним из возможных объяснений происхождения этого термина было то, что он был придуман Ральфом Кимбаллом во время разговора со Стивеном Пейсом из Калидо. . Ральф Кимбалл называет этот метод «Непредсказуемые изменения с наложением одной версии» в The Data Warehouse Toolkit .

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

Ключ_поставщика	Row_Key	Код_поставщика	Имя_поставщика	Текущее_состояние	Историческое_государство	Дата начала	Конечная_дата	Текущий_флаг
123	1	АВС	Компания Acme Supply Co.	ЧТО	ЧТО	2000-01-01T00:00:00	9999-12-31T23:59:59	И

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

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

Ключ_поставщика	Row_Key	Код_поставщика	Имя_поставщика	Текущее_состояние	Историческое_государство	Дата начала	Конечная_дата	Текущий_флаг
123	1	АВС	Компания Acme Supply Co.	ТО	ЧТО	2000-01-01T00:00:00	2004-12-22T00:00:00	Н
123	2	АВС	Компания Acme Supply Co.	ТО	ТО	2004-12-22T00:00:00	9999-12-31T23:59:59	И

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

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

Ключ_поставщика	Row_Key	Код_поставщика	Имя_поставщика	Текущее_состояние	Историческое_государство	Дата начала	Конечная_дата	Текущий_флаг
123	1	АВС	Компания Acme Supply Co.	Нью-Йорк	ЧТО	2000-01-01T00:00:00	2004-12-22T00:00:00	Н
123	2	АВС	Компания Acme Supply Co.	Нью-Йорк	ТО	2004-12-22T00:00:00	2008-02-04T00:00:00	Н
123	3	АВС	Компания Acme Supply Co.	Нью-Йорк	Нью-Йорк	2008-02-04T00:00:00	9999-12-31T23:59:59	И

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

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

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

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

Ключ_поставщика	Код_поставщика	Имя_поставщика	Текущее_состояние	Историческое_государство	Дата начала	Конечная_дата	Текущий_флаг
123	АВС	Компания Acme Supply Co.	Нью-Йорк	ЧТО	2000-01-01T00:00:00	2004-12-22T00:00:00	Н
124	АВС	Компания Acme Supply Co.	Нью-Йорк	ТО	2004-12-22T00:00:00	2008-02-04T00:00:00	Н
125	АВС	Компания Acme Supply Co.	Нью-Йорк	Нью-Йорк	2008-02-04T00:00:00	9999-12-31T23:59:59	И

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

SELECT
  delivery.delivery_cost,
  supplier.supplier_name,
  supplier.historical_state,
  supplier.current_state
FROM delivery
INNER JOIN supplier
  ON delivery.supplier_key = supplier.supplier_key;

типа

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

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

Ключ_поставщика	Код_поставщика	Имя_поставщика	Поставщик_Штат	Дата начала	Конечная_дата
456	АВС	Компания Acme Supply Co.	ЧТО	2000-01-01T00:00:00	2004-12-22T00:00:00
456	АВС	Компания Acme Supply Co.	ТО	2004-12-22T00:00:00	2008-02-04T00:00:00
456	АВС	Компания Acme Supply Co.	Нью-Йорк	2008-02-04T00:00:00	9999-12-31T23:59:59

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

SELECT
  supplier.supplier_code,
  supplier.supplier_state
FROM supplier
INNER JOIN delivery
  ON supplier.supplier_key = delivery.supplier_key
 AND delivery.delivery_date >= supplier.start_date AND delivery.delivery_date < supplier.end_date;

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

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

1. Ссылочная целостность в СУБД теперь возможна, но нельзя использовать «Supplier_Code» в качестве внешнего ключа в таблице «Product», а использовать «Supplier_Key» в качестве внешнего ключа, каждый продукт привязан к определенному интервалу времени.
2. Если факт содержит более одной даты (например, Order_Date, Delivery_Date, Invoice_Payment_Date), можно выбрать, какую дату использовать для запроса.
3. Вы можете выполнять запросы «как сейчас», «как во время транзакции» или «как в определенный момент времени», изменив логику фильтра даты.
4. Вам не нужно повторно обрабатывать таблицу фактов, если в таблице измерений есть изменения (например, ретроспективное добавление дополнительных полей, которые меняют временные интервалы, или если вы допустили ошибку в датах в таблице измерений, их можно легко исправить) .
5. Вы можете ввести двухвременные даты в таблицу измерений.
6. Вы можете объединить факт с несколькими версиями таблицы измерений, чтобы обеспечить отчетность по одной и той же информации с разными датами вступления в силу в одном запросе.

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

SELECT
  supplier.supplier_code,
  supplier.supplier_state
FROM supplier
INNER JOIN delivery
  ON supplier.supplier_key = delivery.supplier_key
 AND supplier.start_date <= '2012-01-01T00:00:00' AND supplier.end_date > '2012-01-01T00:00:00';

Тип 7: Гибрид - И суррогатный, и естественный ключ

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

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

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

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

Ключ_поставщика	Код_поставщика	Имя_поставщика	Поставщик_Штат	Дата начала	Конечная_дата	Текущий_флаг
123	АВС	Компания Acme Supply Co.	ЧТО	2000-01-01T00:00:00	2004-12-22T00:00:00	Н
124	АВС	Компания Acme Supply Co.	ТО	2004-12-22T00:00:00	2008-02-04T00:00:00	Н
125	АВС	Компания Acme Supply Co.	Нью-Йорк	2008-02-04T00:00:00	9999-12-31T23:59:59	И

Чтобы получить текущие записи:

SELECT
  delivery.delivery_cost,
  supplier.supplier_name,
  supplier.supplier_state
FROM delivery
INNER JOIN supplier
  ON delivery.supplier_code = supplier.supplier_code
WHERE supplier.current_flag = 'Y';

Чтобы получить записи истории:

SELECT
  delivery.delivery_cost,
  supplier.supplier_name,
  supplier.supplier_state
FROM delivery
INNER JOIN supplier
  ON delivery.supplier_code = supplier.supplier_code;

Чтобы получить записи истории на основе определенной даты (если в таблице фактов существует более одной даты):

SELECT
  delivery.delivery_cost,
  supplier.supplier_name,
  supplier.supplier_state
FROM delivery
INNER JOIN supplier
  ON delivery.supplier_code = supplier.supplier_code
  AND delivery.delivery_date BETWEEN supplier.Start_Date AND supplier.End_Date

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

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

Объединение типов

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

Различные типы SCD можно применять к разным столбцам таблицы. Например, мы можем применить тип 1 к столбцу «Название_поставщика», а тип 2 — к столбцу «Поставщик_состояние» той же таблицы.

См. также

• Изменить сбор данных
• Временная база данных
• Триггер журнала
• Модель сущность-атрибут-значение
• Мультиарендность

Ссылки

• Брюс Оттманн, Крис Ангус: Система обработки данных , Патентное ведомство США, номер патента 7 003 504 . 21 февраля 2006 г.
• Ральф Кимбалл : Университет Кимбалла: обработка произвольных пересказов истории [1] . 9 декабря 2007 г.