Версионное хранение данных

О чем поговорим?
Что такое техническая история?
Что такое бизнес-история? Рассмотрим на примере ассортимента в магазине
Двойная история
В каких объектах может быть историчность?
5. PIT – зачем он нужен?
6. Примеры работы с историей

По типу
Техническая
Бизнес
Двойная
Виды историчности
По способу хранения
SCD0 - значения атрибутов не будут меняться
SCD1 – значения атрибутов полностью заменяются на новые
SCD2 - добавление новой строки с сохранением предыдущей и сохранение в дополнительных столбцах признаков актуальности. Такой подход позволяет сохранить историчность.
Срезы

Техническая история
1. Запись с источника – 14.02.2020
2. Загружаем в GRP_RV.S_PLU_PACKAGING_MDM
3. Запись изменилась на источнике – 18.02.2020
4. Делаем версионность в GRP_RV.S_PLU_PACKAGING_MDM

- это хронология изменения атрибутов какого-либо объекта
- происходит вследствие обновления записи на источнике или расчета нового значения
Техническая история
от даты "сотворения мира" - '01-01-1960' ('01-01-1960 00:00:00')
до даты "апокалипсиса" - '01-01-5999' ('01-01-5999 00:00:00')
Непрерывность:

Техническая история
Разбор инцидентов
Восстановление отчетности
Нужна пользователям, когда отражает бизнес-процесс

Бизнес - история
ассортимента в магазине
Источник ERP:
После загрузки в EDW - GRP_RV.S_ASSORTMENT_X_STORE_WRSZ_ERP

Бизнес - история
рассчитываемая
Даты действия товара в ассортименте – GRP_RV.M_ASSORTMENT_X_PLU_WLK1_ERP
Даты действия локального ассортимента – GRP_RV.S_ASSORTMENT_WRS1_ERP
Результат расчета в Bridge - GRP_BV.B_ASSORTMENT_X_PLU_TYPE

Бизнес - история
– хронология изменения атрибутов, отражающие время их действия, описывающих бизнес-процесс.
от даты "сотворения мира" - '01-01-1960' ('01-01-1960 00:00:00')
до даты "апокалипсиса" - '01-01-5999' ('01-01-5999 00:00:00')
Непрерывность:

Двойная история
Бизнес-ключ: link_key, business_from_dttm
Связь существует, но не действует
Товар в ассортименте GRP_RV.M_ASSORTMENT_X_PLU_WLK1_ERP
Ассортимент в магазине GRP_RV.S_ASSORTMENT_X_STORE_WRSZ_ERP
Полный ключ: valid_from_dttm, link_key, business_from_dttm

Какая историчность возможна?

Хранение по способу извлечения

В каких объектах может быть историчность?

Point-in-Time (PIT)
– предназначен для построения сводной истории изменения атрибутов сущности.
Критерии создания
Пересечение истории, из нескольких сателлитов/бриджей сущности
Расчет интервалов актуальности записей (valid_from_dttm → valid_from_dttm + valid_to_dttm)

Point-in-Time (PIT)
Структура таблицы

Point-in-Time (PIT)
Структура таблицы для
двойной версионности

Point-in-Time (PIT)
Как формируется история в PIT-таблице
PIT

Point-in-Time (PIT)
PIT
Как формируется история в PIT-таблице

Примеры работы с историей

Алгоритм перенарезки истории
Дано товар в магазине в нескольких типах ассортимента
GRP_BV.B_ASSORTMENT_X_PLU_X_STORE
Что хотим получить:

Алгоритм перенарезки истории
1. ORIGINAL_INTERVALS: Разметили типы ассортимента – определили sale_plan_flg
* Для разметки должна быть использована TUNE - таблица

Алгоритм перенарезки истории
2. CALENDAR: Разворачиваем исходные интервалы в календарь
SELECT PLU_RK
, STORE_RK , BUSINESS_FROM_DTTM CAL_DTTM
FROM ORIGINAL_INTERVALS
UNION
SELECT PLU_RK
, STORE_RK
, BUSINESS_TO_DTTM + INTERVAL '1 SECOND'
FROM ORIGINAL_INTERVALS
WHERE BUSINESS_TO_DTTM <> '5999-01-01'::TIMESTAMP

Алгоритм перенарезки истории
3. NEW_INTERVALS_WITH_SALE_PLAN_FLG: Расставляем каждому дню календаря флаги с учетом приоритета
SELECT C.PLU_RK
, C.STORE_RK
, COALESCE(MIN(OI.SALE_PLAN_FLG),0) AS SALE_PLAN_FLG
, C.CAL_DTTM
FROM CALENDAR C
LEFT JOIN ORIGINAL_INTERVALS OI
ON C.PLU_RK = OI.PLU_RK
AND C.STORE_RK = OI.STORE_RK
AND C.CAL_DTTM >= OI.BUSINESS_FROM_DTTM
AND C.CAL_DTTM <= OI.BUSINESS_TO_DTTM
GROUP BY C.PLU_RK
, C.STORE_RK
, C.CAL_DTTM

Алгоритм перенарезки истории
4. LG_SALE_PLAN_FLG Присваиваем предыдущее состояние флага
SELECT NI2.PLU_RK
, NI2.STORE_RK
, NI2.SALE_PLAN_FLG
, NI2.CAL_DTTM
, LAG(SALE_PLAN_FLG, 1, -1)
OVER (PARTITION BY NI2.PLU_RK, NI2.STORE_RK ORDER BY NI2.CAL_DTTM) LG_SALE_PLAN_FLG
FROM NEW_INTERVALS_WITH_ASSORTMENT_TYPE_FLG NI2

Алгоритм перенарезки истории
5. Итог. Нарезаем историю. Убираем строки, у которых состояние флага не изменилось
SELECT PLU_RK
, STORE_RK
, SALE_PLAN_FLG
, CAL_DTTM AS BUSINESS_FROM_DTTM
, LEAD(CAL_DTTM - INTERVAL '1 SECOND', 1, '5999-01-01'::TIMESTAMP)
OVER (PARTITION BY PLU_RK, STORE_RK ORDER BY CAL_DTTM) BUSINESS_TO_DTTM FROM LG_SALE_PLAN_FLG
WHERE LG_SALE_PLAN_FLG != SALE_PLAN_FLG