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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В заключение

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

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

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

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