Моделирование неравномерных временных иерархий
Эта статья описывает подход к обработке временных неравномерных иерархий в модели измерений. Такие структуры данных часто встречаются в производстве, где компоненты продукта имеют как родителей, так и детей произвольной глубины, и эти компоненты могут заменяться в течение жизненного цикла продукта. Описанная здесь стратегия упрощает многие распространенные типы аналитических и отчетных запросов.
Чтобы помочь визуализировать эти данные, мы представим, что мы — компания, которая производит и сдает в аренду электровелосипеды в приложении для совместного использования. Когда мы собираем велосипед, мы отслеживаем серийные номера компонентов, из которых он состоит. Каждый раз, когда что-то ломается и нуждается в замене, мы отслеживаем старые детали, которые были удалены, и новые детали, которые были установлены. Мы также точно отслеживаем пробег, накопленный на каждом из наших велосипедов. Наша основная аналитическая цель — иметь возможность сообщать о предполагаемом сроке службы каждого компонента, чтобы мы могли приоритизировать улучшение этого компонента и снизить затраты на обслуживание.
Модель данных
Очевидно, что у настоящего велосипеда может быть сотня или более отдельных компонентов. Чтобы упростить эту статью, давайте рассмотрим велосипед, раму, колесо, обод колеса, шину и камеру. Наша иерархия компонентов выглядит следующим образом:
Иерархия eBikeЭта иерархия неравномерная, потому что разные пути через иерархию заканчиваются на разных уровнях. Она временная, потому что конкретные компоненты могут быть добавлены и удалены.
Теперь давайте посмотрим, как эти данные представлены в наших исходных системах данных и как их можно преобразовать, чтобы упростить аналитические запросы.
Транзакционная модель
Наша система ERP (планирование ресурсов предприятия) содержит записи, которые фиксируют, когда конкретный серийный номер компонента (component_id) был установлен в или удален из родительского сборочного компонента (assembly_id). Верхний сборочный компонент — это сам eBike, у которого нет родительской сборки. Таким образом, когда eBike (конкретно, eBike с серийным номером "Bike-1") изначально собирается, система ERP будет содержать записи, которые выглядят следующим образом.
erp_components:
assembly_id | component_id | installed_at | removed_at |
|---|---|---|---|
| Bike-1 | 2023-01-01 | ||
| Bike-1 | Frame-1 | 2023-01-01 | |
| Bike-1 | Wheel-1 | 2023-01-01 | |
| Wheel-1 | Rim-1 | 2023-01-01 | |
| Wheel-1 | Tire-1 | 2023-01-01 | |
| Tire-1 | Tube-1 | 2023-01-01 |
Теперь предположим, что этот велосипед был в эксплуатации некоторое время, и 1 июня пользователь велосипеда сообщил о проколотой шине. Техник по обслуживанию затем отправился на место, заменил камеру в колесе и установил новую. Они зафиксировали это в системе ERP, обновив одну запись с датой removed_at и создав другую запись с новым component_id камеры.
erp_components:
assembly_id | component_id | installed_at | removed_at |
|---|---|---|---|
| ... | ... | ... | ... |
| Tire-1 | Tube-1 | 2023-01-01 | 2023-06-01 |
| Tire-1 | Tube-2 | 2023-06-01 | |
| ... | ... | ... | ... |
Через несколько месяцев происходит небольшая авария. Не волнуйтесь, все в порядке! Однако колесо (Wheel-1) полностью сломано и должно быть заменено (на Wheel-2). Когда техник обновляет ERP, вся иерархия под замененным колесом также обновляется, как показано ниже.
erp_components:
assembly_id | component_id | installed_at | removed_at |
|---|---|---|---|
| Bike-1 | Wheel-1 | 2023-01-01 | 2023-08-01 |
| Wheel-1 | Rim-1 | 2023-01-01 | 2023-08-01 |
| Wheel-1 | Tire-1 | 2023-01-01 | 2023-08-01 |
| Tire-1 | Tube-2 | 2023-06-01 | 2023-08-01 |
| Bike-1 | Wheel-2 | 2023-08-01 | |
| Wheel-2 | Rim-2 | 2023-08-01 | |
| Wheel-2 | Tire-2 | 2023-08-01 | |
| Tire-2 | Tube-3 | 2023-08-01 |
После всех вышеуказанных обновлений и добавлений наши данные ERP выглядят следующим образом.
erp_components:
assembly_id | component_id | installed_at | removed_at |
|---|---|---|---|
| Bike-1 | 2023-01-01 | ||
| Bike-1 | Frame-1 | 2023-01-01 | |
| Bike-1 | Wheel-1 | 2023-01-01 | 2023-08-01 |
| Wheel-1 | Rim-1 | 2023-01-01 | 2023-08-01 |
| Wheel-1 | Tire-1 | 2023-01-01 | 2023-08-01 |
| Tire-1 | Tube-1 | 2023-01-01 | 2023-06-01 |
| Tire-1 | Tube-2 | 2023-06-01 | 2023-08-01 |
| Bike-1 | Wheel-2 | 2023-08-01 | |
| Wheel-2 | Rim-2 | 2023-08-01 | |
| Wheel-2 | Tire-2 | 2023-08-01 | |
| Tire-2 | Tube-3 | 2023-08-01 |
Итак, с точки зрения системы ERP все в порядке. Но эта структура данных может быть сложной для работы, если мы хотим создавать отчеты, которые рассчитывают общий пробег, накопленный на различных компонентах, или средний пробег определенного типа компонента, или как один тип компонента может влиять на срок служ бы другого компонента.
Многозначная модель измерений
В моделировании измерений у нас есть факт таблицы, содержащие измерения, и измерительные таблицы, содержащие контекст для этих измерений (атрибуты). В нашем хранилище данных eBike у нас есть факт таблица, содержащая одну запись для каждого eBike за каждый день, когда он используется, и измеренный пробег, накопленный в течение этого дня. Эта факт таблица содержит столбцы суррогатных ключей, обозначенные суффиксом _sk. Это обычно системно сгенерированные ключи, используемые для соединения с другими таблицами в базе данных; конкретные значения этих ключей не важны.
fct_daily_mileage:
bike_sk | component_sk | ride_at | miles |
|---|---|---|---|
| bsk1 | csk1 | 2023-01-01 | 3 |
| bsk1 | csk1 | 2023-01-02 | 2 |
| bsk1 | csk1 | 2023-01-03 | 0 |
| bsk1 | csk1 | 2023-01-04 | 0 |
| ... | ... | ... | ... |
| bsk1 | csk3 | 2023-08-01 | 7 |
| bsk1 | csk3 | 2023-08-02 | 8 |
| bsk1 | csk3 | 2023-08-03 | 4 |
Одна из измерительных таблиц — это простая таблица, содержащая информацию о отдельных велосипедах, которые мы произвели.
dim_bikes:
bike_sk | bike_id | color | model_name |
|---|---|---|---|
| bsk1 | Bike-1 | Orange | Wyld Stallyn |
Существует простое отношение "многие к одному" между fct_daily_mileage и dim_bikes. Если нам нужно рассчитать общий пробег, накопленный для каждого велосипеда в нашем парке eBike, мы просто соединяем две таблицы и агрегируем по измерению miles.
select
dim_bikes.bike_id,
sum(fct_daily_mileage.miles) as miles
from
fct_daily_mileage
inner join
dim_bikes
on
fct_daily_mileage.bike_sk = dim_bikes.bike_sk
group by
1
Расширение этого запроса для определения, используются ли оранжевые велосипеды больше, чем красные, или предпочитаются ли определенные модели, также является простым запросом.
Работа со всеми компонентами более сложна, потому что на один и тот же день установлено много компонентов. Отношение между днями, когда велосипеды используются, и компонентами, таким образом, многозначное. В dim_bikes есть одна запись на велосипед и суррогатный ключ. В нашей измерительной таблице компон ентов будет несколько записей с тем же суррогатным ключом, и, следовательно, это будет многозначная измерительная таблица. Конечно, чтобы сделать все еще более сложным, компоненты могут меняться изо дня в день. Чтобы построить многозначную измерительную таблицу, мы разбиваем временную иерархию компонентов на отдельные временные диапазоны, в которых все компоненты в конкретном велосипеде остаются постоянными. В определенные моменты времени, когда компоненты изменяются, создается новый суррогатный ключ. Конечная измерительная таблица для нашего примера выше выглядит следующим образом, где valid_from_at и valid_to_at представляют начало и конец временного диапазона, в котором все компоненты eBike остаются неизменными.
mdim_components:
component_sk | assembly_id | component_id | depth | installed_at | removed_at | valid_from_at | valid_to_at |
|---|---|---|---|---|---|---|---|
| csk1 | Bike-1 | 0 | 2023-01-01 | 2023-01-01 | 2023-06-01 | ||
| csk1 | Bike-1 | Frame-1 | 1 | 2023-01-01 | 2023-01-01 | 2023-06-01 | |
| csk1 | Bike-1 | Wheel-1 | 1 | 2023-01-01 | 2023-08-01 | 2023-01-01 | 2023-06-01 |
| csk1 | Wheel-1 | Rim-1 | 2 | 2023-01-01 | 2023-08-01 | 2023-01-01 | 2023-06-01 |
| csk1 | Wheel-1 | Tire-1 | 2 | 2023-01-01 | 2023-08-01 | 2023-01-01 | 2023-06-01 |
| csk1 | Tire-1 | Tube-1 | 3 | 2023-01-01 | 2023-06-01 | 2023-01-01 | 2023-06-01 |
| csk2 | Bike-1 | 0 | 2023-01-01 | 2023-06-01 | 2023-08-01 | ||
| csk2 | Bike-1 | Frame-1 | 1 | 2023-01-01 | 2023-06-01 | 2023-08-01 | |
| csk2 | Bike-1 | Wheel-1 | 1 | 2023-01-01 | 2023-08-01 | 2023-06-01 | 2023-08-01 |
| csk2 | Wheel-1 | Rim-1 | 2 | 2023-01-01 | 2023-08-01 | 2023-06-01 | 2023-08-01 |
| csk2 | Wheel-1 | Tire-1 | 2 | 2023-01-01 | 2023-08-01 | 2023-06-01 | 2023-08-01 |
| csk2 | Tire-1 | Tube-2 | 3 | 2023-06-01 | 2023-08-01 | 2023-06-01 | 2023-08-01 |
| csk3 | Bike-1 | 0 | 2023-01-01 | 2023-08-01 | |||
| csk3 | Bike-1 | Frame-1 | 1 | 2023-01-01 | 2023-08-01 | ||
| csk3 | Bike-1 | Wheel-2 | 1 | 2023-08-01 | 2023-08-01 | ||
| csk3 | Wheel-2 | Rim-2 | 2 | 2023-08-01 | 2023-08-01 | ||
| csk3 | Wheel-2 | Tire-2 | 2 | 2023-08-01 | 2023-08-01 | ||
| csk3 | Tire-2 | Tube-3 | 3 | 2023-08-01 | 2023-08-01 |
Теперь давайте посмотрим, как эта структура может помочь в написании запросов. В следующем разделе этой статьи мы рассмотрим SQL-код, который может взять нашу таблицу ERP и преобразовать ее в эту модель измерений.
Пробег для компонента
Предположим, мы хотим узнать общий пробег, накопленный на "Wheel-1". SQL-код для определения этого очень похож на код для определения пробега для данного велосипеда.
select
mdim_components.component_id,
sum(fct_daily_mileage.miles) as miles
from
fct_daily_mileage
inner join
mdim_components
on
fct_daily_mileage.component_sk = mdim_components.component_sk
group by
1
where
component_id = 'Wheel-1'
Одно, о чем нужн о быть очень осторожным, работая с многозначными измерениями, это интерпретация агрегаций. Например, предположим, что мы выбрали агрегирование по top_assembly_id (чтобы уменьшить загромождение, это поле не показано в модели данных выше, потому что оно просто "Bike-1" для каждой записи). Для этой агрегации мы бы переоценили общий пробег на этой верхней сборке, потому что соединение привело бы к декартовому произведению, и, следовательно, мы бы получили "ситуацию раздувания".
Бонус: Поиск компонентов, установленных одновременно с другими компонентами
Эта структура упрощает другие виды интересного анализа. Предположим, мы хотим начать исследовать, как один компонент влияет на другой, например, требуются ли определенные бренды камер для замены чаще, если они находятся в новой шине. Мы можем сделать это, разделив данные на сегменты времени, в которых компоненты не меняются, и ищем другие компоненты, установленные одновременно. Например, чтобы найти все компоненты, которые когда-либо были установлены одновременно с "Tube-3", мы можем собрать их с помощью простой оконной функции. Мы могли бы затем использовать результаты этого запроса в регрессии или другом типе статистического анализа.
select distinct
component_id
from
mdim_components
qualify
sum(iff(component_id = 'Tube-3', 1, 0)) over (partition by valid_from_at, valid_to_at) > 0
SQL-код для построения модели измерений
Теперь мы переходим к самой интересной части! В этом разделе показано, как взять исходные данные ERP и превратить их в многозначную модель измерений. Этот SQL-код был написан и протестирован с использованием Snowflake, но должен быть адаптируем к другим диалектам.
Обход иерархии
Первым шагом будет обход иерархии компонентов, чтобы найти все компоненты, которые принадлежат одной и той же верхней сборке. В нашем примере выше у нас был только один велосипед и, следовательно, только одна верхняя сборка; в реальной системе их будет много (и мы даже можем менять компоненты между разными верхними сборками!).
Ключ здесь — использовать рекурсивное соединение, чтобы перемещаться от вершины иерархии ко всем детям и внукам. Вершина иерархии легко идентифицируется, потому что это единственные записи без родителей.
with recursive
-- Содержит наши исходные данные с записями, которые связывают ребенка с родителем
components as (
select
*,
-- Даты действия начинаются как установленные/удаленные, но могут быть изменены по мере обхода иерархии ниже
installed_at as valid_from_at,
removed_at as valid_to_at
from
erp_components
),
-- Получить все исходные записи, которые находятся на вершине иерархии
top_assemblies as (
select * from components where assembly_id is null
),
-- Здесь происходит рекурсия, которая обходит иерархию
traversal as (
-- Начать с вершины иерархии
select
-- Отслеживать глубину по мере продвижения вниз
0 as component_hierarchy_depth,
-- Флаг для определения, вошли ли мы в циклическое отношение
false as is_circular,
-- Определить массив, который будет отслеживать всех предков компонента
[component_id] as component_trace,
-- На вершине иерархии компонент является верхней сборкой
component_id as top_assembly_id,
assembly_id,
component_id,
installed_at,
removed_at,
valid_from_at,
valid_to_at
from
top_assemblies
union all
-- Соединить текущий слой иерархии со следующим слоем вниз, связывая
-- текущий идентификатор компонента с идентификатором сборки ребенка
select
traversal.component_hierarchy_depth + 1 as component_hierarchy_depth,
-- Проверить наличие циклических зависимостей
array_contains(components.component_id::variant, traversal.component_trace) as is_circular,
-- Добавить в массив трассировки
array_append(traversal.component_trace, components.component_id) as component_trace,
-- Отслеживать вершину сборки
traversal.top_assembly_id,
components.assembly_id,
components.component_id,
components.installed_at,
components.removed_at,
-- По мере рекурсии вниз по иерархии, учитывать только временные диапазоны, в которых и родитель, и ребенок установлены; поэтому выбираем последнюю "с" метку времени и первую "до".
greatest(traversal.valid_from_at, components.valid_from_at) as valid_from_at,
least(traversal.valid_to_at, components.valid_to_at) as valid_to_at
from
traversal
inner join
components
on
traversal.component_id = components.assembly_id
and
-- Исключить сборки компонентов, которые не были установлены одновременно
-- Это может произойти из-за проблем с качеством исходных данных
(
traversal.valid_from_at < components.valid_to_at
and
traversal.valid_to_at >= components.valid_from_at
)
where
-- Остановиться, если обнаружена циклическая иерархия
not array_contains(components.component_id::variant, traversal.component_trace)
-- Может быть плохие данные, которые могут оказаться в иерархиях, которые искусственно чрезвычайно глубокие
and traversal.component_hierarchy_depth < 20
),
final as (
-- Обратите внимание, что на этом этапе могут быть дубликаты (поэтому "distinct").
-- Дубликаты могут возникнуть, когда родитель компонента перемещается от одного дедушки к другому.
-- На этом этапе мы только проследили родословную компонента и исправили даты действия/от, чтобы все диапазоны детей содержались в диапазонах родителей.
select distinct *
from
traversal
where
-- Предотвратить появление ассоциаций с нулевым временем (или меньше)
valid_from_at < valid_to_at
)
select * from final
В конце вышеуказанного шага у нас есть таблица, которая очень похожа на erp_components, которая использовалась в качестве источника, но с несколькими дополнительными ценными столбцами:
top_assembly_id- Это самый важный результат обхода иерархии. Он связывает все подкомпоненты с их общим родителем. Мы будем использовать это на следующем шаге, чтобы разбить иерархию на все отдельные временные диапазоны, в которых компоненты, которые имеют общий верхний сборочный узел, остаются постоянными (и каждый отдельный временной диапазон иtop_assembly_idполучают свой собственный суррогатный ключ).component_hierarchy_depth- Указывает, насколько удален компонент от верхнего сборочного узла.component_trace- Содержит массив всех компонентов, связывающих этот компонент с верхним сборочным узлом.valid_from_at/valid_to_at- Если у вас действительно качественные исходные данные, они будут идентичныinstalled_at/removed_at. Однако в реальном мире мы обнаружили случаи, когда даты установки и удаления не согласованы между родителем и ребенком, либо из-за ошибки ввода данных, либо из-за того, что техник забыл отметить, когда компонент был удален. Таким образом, например, у нас может быть родительская сборка, которая была удалена вместе со всеми ее детьми, но только у родительской сборки заполненоremoved_at. На этом э тапеvalid_from_atиvalid_to_atупорядочивают такие сценарии.
Временное соединение диапазонов
Последний шаг — выполнить временное соединение диапазонов между верхним сборочным узлом и всеми его потомками. Это то, что разделяет все временные изменения компонентов на отдельные временные диапазоны, в которых иерархия компонентов постоянна. Это соединение диапазонов использует макрос dbt в этом gist, работа которого выходит за рамки этой статьи, но вам рекомендуется изучить его и обсуждение, упомянутое ранее.
-- Начать со всех сборок на вершине (глубина иерархии = 0)
with l0_assemblies as (
select
top_assembly_id,
component_id,
-- Подготовить поля, необходимые для временного соединения диапазонов
{{ dbt_utils.surrogate_key(['component_id', 'valid_from_at']) }} as dbt_scd_id,
valid_from_at as dbt_valid_from,
valid_to_at as dbt_valid_to
from
component_traversal
where
component_hierarchy_depth = 0
),
components as (
select
top_assembly_id,
component_hierarchy_depth,
component_trace,
assembly_id,
component_id,
installed_at,
removed_at,
-- Подготовить поля, необходимые для временного соединения диапазонов
{{ dbt_utils.surrogate_key(['component_trace', 'valid_from_at'])}} as dbt_scd_id,
valid_from_at as dbt_valid_from,
valid_to_at as dbt_valid_to
from
component_traversal
),
-- Выполнить временное соединение диапазонов
{{
trange_join(
left_model='l0_assemblies',
left_fields=[
'top_assembly_id',
],
left_primary_key='top_assembly_id',
right_models={
'components': {
'fields': [
'component_hierarchy_depth',
'component_trace',
'assembly_id',
'component_id',
'installed_at',
'removed_at',
],
'left_on': 'component_id',
'right_on': 'top_assembly_id',
}
}
)
}}
select
surrogate_key,
top_assembly_id,
component_hierarchy_depth,
component_trace,
assembly_id,
component_id,
installed_at,
removed_at,
valid_from_at,
valid_to_at
from
trange_final
order by
top_assembly_id,
valid_from_at,
component_hierarchy_depth
Бонус: замена компонентов
Прежде чем мы закончим, давайте рассмотрим еще один интересный сценарий. Предположим, у нас есть два велосипеда, "Bike-1" и "Bike-2". Во время обслуживания техник замечает, что цвет обода "Bike-2" совпадает с рамой "Bike-1" и наоборот. Возможно, была допущена ошибка во время первоначальной сборки? Техник решает поменять колеса между двумя велосипедами. Система ERP затем показывает, что "Wheel-1" был удален из "Bike-1" в дату обслуживания и что "Wheel-1" был установлен в "Bike-2" в ту же дату (аналогично для "Wheel-2"). Чтобы уменьшить загромождение ниже, мы проигнорируем рамы и камеры.
erp_components:
assembly_id | component_id | installed_at | removed_at |
|---|---|---|---|
| Bike-1 | 2023-01-01 | ||
| Bike-1 | Wheel-1 | 2023-01-01 | 2023-06-01 |
| Wheel-1 | Rim-1 | 2023-01-01 | |
| Wheel-1 | Tire-1 | 2023-01-01 | |
| Bike-2 | 2023-02-01 | ||
| Bike-2 | Wheel-2 | 2023-02-01 | 2023-06-01 |
| Wheel-2 | Rim-2 | 2023-02-01 | |
| Wheel-2 | Tire-2 | 2023-02-01 | |
| Bike-2 | Wheel-1 | 2023-06-01 | |
| Bike-1 | Wheel-2 | 2023-06-01 |
Когда эти данные ERP преобразуются в многозначную измерительную м одель, мы получаем таблицу ниже. В данных ERP только один вид сборки компонентов, колесо, был удален/установлен, но в измерительной модели все дочерние компоненты идут вместе. В таблице ниже мы видим, что у "Bike-1" и "Bike-2" есть два отдельных диапазона времени действия, один до замены колес и один после.
mdim_components:
component_sk | top_assembly_id | assembly_id | component_id | valid_from_at | valid_to_at |
|---|---|---|---|---|---|
| sk1 | Bike-1 | Bike-1 | 2023-01-01 | 2023-06-01 | |
| sk1 | Bike-1 | Bike-1 | Wheel-1 | 2023-01-01 | 2023-06-01 |
| sk1 | Bike-1 | Wheel-1 | Rim-1 | 2023-01-01 | 2023-06-01 |
| sk1 | Bike-1 | Wheel-1 | Tire-1 | 2023-01-01 | 2023-06-01 |
| sk2 | Bike-1 | Bike-1 | 2023-06-01 | ||
| sk2 | Bike-1 | Bike-1 | Wheel-2 | 2023-06-01 | |
| sk2 | Bike-1 | Wheel-2 | Rim-2 | 2023-06-01 | |
| sk2 | Bike-1 | Wheel-2 | Tire-2 | 2023-06-01 | |
| sk3 | Bike-2 | Bike-2 | 2023-02-01 | 2023-06-01 | |
| sk3 | Bike-2 | Bike-2 | Wheel-2 | 2023-02-01 | 2023-06-01 |
| sk3 | Bike-2 | Wheel-2 | Rim-2 | 2023-02-01 | 2023-06-01 |
| sk3 | Bike-2 | Wheel-2 | Tire-2 | 2023-02-01 | 2023-06-01 |
| sk4 | Bike-2 | Bike-2 | 2023-06-01 | ||
| sk4 | Bike-2 | Bike-2 | Wheel-1 | 2023-06-01 | |
| sk4 | Bike-2 | Wheel-1 | Rim-1 | 2023-06-01 | |
| sk4 | Bike-2 | Wheel-1 | Tire-1 | 2023-06-01 |
Резюме
В этой статье мы исследовали стратегию создания модели измерений для неравномерных временных иерархий. Мы использовали простую игрушечную систему, включающую один или два eBike. В реальном мире было бы гораздо больше отдельных продуктов, более глубокие иерархии, больше атрибутов компонентов, и даты установки/удаления, вероятно, также фиксировались бы с компонентом временной метки. Описанная здесь модель работает очень хорошо даже в этих более сложных реальных случаях.
Если у вас есть вопросы или комментарии, пожалуйста, свяжитесь со мной, оставив комментарий к этому посту или связавшись со мной в dbt slack (@Sterling Paramore).
Comments