При разработке аналитического приложения иногда нужно объединить в модели данных таблицы фактов с разным уровнем детализации. Например,

  • фактические данные по продажам из 1С представлены в детализации до даты, а плановые данные из файла Excel — до недели или месяца;
  • фактические данные в детализации до номенклатуры, а план — до номенклатурной группы.

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

Итак, постановка задачи: в модели данных требуется совместить данные из двух или более различных источников с разным уровнем гранулярности (детализации).

Ситуация 1: два источника данных с разной гранулярностью времени 

Ситуация 1: у двух источников данных есть общее измерение — время, и его гранулярность различается, например, Неделя в одном источнике и Месяц в другом.

В таком случае ничто не мешает сопоставить каждой Неделе/Месяцу определенную дату (первое либо последнее число), и конкатенировать две таблицы фактов в одну, как в примере на скриншоте ниже.

То, что в такой таблице будет наблюдаться разреженность — абсолютно нормально, ничего страшного. Для каждой из секций желательно создать поле-флаг и проставить в нем единички для всех строк соответствующей секции — это поможет и в конструировании анализа множеств в выражениях Мер, и в восприятии структуры таблицы, и в понимании доли секции в таблице (статистика «информационная плотность» поля).

Есть следующий риск — если пользователь в аналитическом интерфейсе применит фильтр по некоей дате (или другому атрибуту времени, отсутствующему в одном из источников), он может тем самым отсечь строки плановых данных из текущей выборки, и соответственно плановые показатели в объектах визуализации будут нулевыми.

Как обойти — думать над проектированием интерфейса и логики расчетов, как не дать пользователю задать некорректный фильтр.

Ситуация 2: два источника данных с разной гранулярностью измерений, не являющихся временем

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

Здесь есть два способа решения.

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

Обязательно условие, чтобы каждая строка одного источника с более низким уровнем детализации (план ТФП в примере ниже) была связана со строкой источника с более высоким уровнем.

Способ прост в реализации, но обладает недостатком — повышается ресурсоемкость за счет того, что «маленькая» таблица связана с таблицами измерений через «крупную» таблицу. Пример такой структуры приведен на скриншоте ниже:

Способ 2: таблицы фактов конкатенировать, а для измерений с различной детализацией создать поле двойственной природы. В это поле для секции каждой из таблиц ставить значение, соответствующее ее уровню детализации. В примере ниже, в выделенном поле, ставить код товара для секции Продажи, и товарное направление — группу для секции Плановых данных. Таблицу измерения также составить из двух секций:

  • одна секция будет просто товарным справочником (в ключевом поле стоит «честный» ключ, заполнены все поля атрибутов),
  • вторая секция — узким подмножеством этого справочника, с заполненными полями атрибутов более высокого уровня, а в ключевом поле — суррогат.

Примеры структуры и наполнения таблиц фактов и измерений приведены на скриншотах ниже.

Из недостатков, на мой взгляд, — посложнее воспринимать модель, потому что неочевидно, какая секция таблицы фактов на каком уровне детализации представлена. А с технической точки зрения — оптимальная схема звезда, минимум ресурсоемкости, просто в реализации.

 Ситуация 3: два источника данных с разной кодификацией времени

Ситуация 3: два источника данных, вроде и связаны со временем, но кодификация различна. В одном источнике время закодировано как интервалы «с-по», в другом — даты.

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

Примеры структуры и наполнения таблиц фактов, таблиц-мостов и измерений приведены на скриншотах ниже:

В заключение

Вендором Qlik в качестве «умолчального» предлагается способ «таблица связей». Те из вас, кто проходил курс QlikView Developer или Qlik Sense Data Modeling, наверняка помнят это упражнение.

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

На мой взгляд, не очень хороший способ — и ресурсоемкий, и трудный в реализации, и читаемость модели сильно ухудшает: так, атрибуты времени в примере раскинуты по двум таблицам. Способы, описанные выше, кажутся более предпочтительными.

Всем оптимальных моделей данных в Qlik Sense и QlikView!