Проектирование модели данных с таблицами фактов разной детализации
При разработке аналитического приложения иногда нужно объединить в модели данных таблицы фактов с разным уровнем детализации. Например,фактические данные по продажам из 1С представлены в детализации до даты, а плановые данные из файла Excel — до недели или месяца;фактические данные в детализации до номенклатуры, а план — до номенклатурной группы.
Сегодня мы разберем несколько подходов создания моделей данных в таких случаях, с плюсами и минусами разных релизаций.
Итак, постановка задачи: в модели данных требуется совместить данные из двух или более различных источников с разным уровнем гранулярности (детализации).
СИТУАЦИЯ 1: ДВА ИСТОЧНИКА ДАННЫХ С РАЗНОЙ ГРАНУЛЯРНОСТЬЮ ВРЕМЕНИ
Ситуация 1: у двух источников данных есть общее измерение — время, и его гранулярность различается, например, Неделя в одном источнике и Месяц в другом.
В таком случае ничто не мешает сопоставить каждой Неделе/Месяцу определенную дату (первое либо последнее число), и конкатенировать две таблицы фактов в одну, как в примере на скриншоте ниже.
То, что в такой таблице будет наблюдаться разреженность — абсолютно нормально, ничего страшного. Для каждой из секций желательно создать поле-флаг и проставить в нем единички для всех строк соответствующей секции — это поможет и в конструировании анализа множеств в выражениях Мер, и в восприятии структуры таблицы, и в понимании доли секции в таблице (статистика «информационная плотность» поля).
Есть следующий риск — если пользователь в аналитическом интерфейсе применит фильтр по некоей дате (или другому атрибуту времени, отсутствующему в одном из источников), он может тем самым отсечь строки плановых данных из текущей выборки, и соответственно плановые показатели в объектах визуализации будут нулевыми.
Как обойти — думать над проектированием интерфейса и логики расчетов, как не дать пользователю задать некорректный фильтр.
СИТУАЦИЯ 2: ДВА ИСТОЧНИКА ДАННЫХ С РАЗНОЙ ГРАНУЛЯРНОСТЬЮ ИЗМЕРЕНИЙ, НЕ ЯВЛЯЮЩИХСЯ ВРЕМЕНЕМ
Ситуация 2: два источника данных, гранулярность различается по одному или более общим измерениям, НЕ являющимся временем.
Здесь есть два способа решения.
Способ 1: две таблицы фактов оставить отдельными, связать их по составному ключу, а таблицы измерений связать с таблицей большей детализации.
Обязательно условие, чтобы каждая строка одного источника с более низким уровнем детализации (план ТФП в примере ниже) была связана со строкой источника с более высоким уровнем.
Способ прост в реализации, но обладает недостатком — повышается ресурсоемкость за счет того, что «маленькая» таблица связана с таблицами измерений через «крупную» таблицу. Пример такой структуры приведен на скриншоте ниже:
Способ 2: таблицы фактов конкатенировать, а для измерений с различной детализацией создать поле двойственной природы. В это поле для секции каждой из таблиц ставить значение, соответствующее ее уровню детализации. В примере ниже, в выделенном поле, ставить код товара для секции Продажи, и товарное направление — группу для секции Плановых данных. Таблицу измерения также составить из двух секций:
- одна секция будет просто товарным справочником (в ключевом поле стоит «честный» ключ, заполнены все поля атрибутов),
- вторая секция — узким подмножеством этого справочника, с заполненными полями атрибутов более высокого уровня, а в ключевом поле — суррогат.
Примеры структуры и наполнения таблиц фактов и измерений приведены на скриншотах ниже.
Из недостатков, на мой взгляд, — посложнее воспринимать модель, потому что неочевидно, какая секция таблицы фактов на каком уровне детализации представлена. А с технической точки зрения — оптимальная схема звезда, минимум ресурсоемкости, просто в реализации.
СИТУАЦИЯ 3: ДВА ИСТОЧНИКА ДАННЫХ С РАЗНОЙ КОДИФИКАЦИЕЙ ВРЕМЕНИ
Ситуация 3: два источника данных, вроде и связаны со временем, но кодификация различна. В одном источнике время закодировано как интервалы «с-по», в другом — даты.
В таком случае также выполняется конкатенация двух таблиц фактов и создается поле двойственной природы. Связь с таблицей измерения выполняется через таблицу-мост, в которой для каждого значения двойственного поля заводится одна или несколько строк, связывающих с основным ключом общего измерения.
Примеры структуры и наполнения таблиц фактов, таблиц-мостов и измерений приведены на скриншотах ниже:
В ЗАКЛЮЧЕНИЕ
Вендором Qlik в качестве «умолчального» предлагается способ «таблица связей». Те из вас, кто проходил курс QlikView Developer или Qlik Sense Data Modeling, наверняка помнят это упражнение.
Суть его в следующем — две таблицы фактов связываются через таблицу связей по общему составному ключу, и в эту таблицу связей переносятся атрибуты общего уровня детализации.
На мой взгляд, не очень хороший способ — и ресурсоемкий, и трудный в реализации, и читаемость модели сильно ухудшает: так, атрибуты времени в примере раскинуты по двум таблицам. Способы, описанные выше, кажутся более предпочтительными.
Всем оптимальных моделей данных в Qlik Sense и QlikView!
Источник - Блог Data Daily
