Отчёты за закрытые месяцы поехали после соединения с SCD2-измерением
Витрина продаж соединяет факт с измерением клиентов, которое ведётся по SCD2 (поля valid_from, valid_to, is_current):
SELECT f.order_date, d.region, sum(f.amount) AS amount
FROM dds.fct_orders f
JOIN dds.dim_client d ON d.client_id = f.client_id
GROUP BY f.order_date, d.region;
Последствия: во-первых, число строк в витрине выросло примерно вдвое, во-вторых, выручка по регионам за давно закрытые месяцы меняется от пересчёта к пересчёту.
Что здесь не так?
- Соединение идёт по бизнес-ключу без учёта интервала действия версии, поэтому факт множится на все версии клиента; нужно добавить в условие valid_from <= order_date < valid_to.
- Нужно добавить в соединение фильтр d.is_current = 1: тогда у каждого клиента останется ровно одна активная версия, размножение строк исчезнет и суммы по регионам сойдутся с источником.
- Проблема в дублях самого факта: один и тот же заказ приезжает из источника несколькими версиями, отсюда и удвоение строк, и плавающие суммы; соединение с измерением здесь ни при чём.
- Нужно заменить JOIN на LEFT JOIN и добавить DISTINCT перед агрегацией: внешнее соединение перестанет терять заказы, а DISTINCT уберёт размножение строк по версиям измерения.