Генератор заготовок для таблиц связи

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

А сами модели получаются не масштабируемыми, медленными, и возможно даже неправильно работающими.

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

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

Конструктор выполнен в виде таблицы Google, вы можете создать ее копию для самостоятельного использования.

Все работат по следующей логике:

Сначала вы загружаете через скрипт таблицы с данными (можете взять пример на вкладке Demo Data в конструкторе). Например:

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

Для генерации кода нужно заполнить настройки модели в Google Sheet.

Чтобы все получилось, в таблицах с данными должны быть соблюдены следующие условия:

1. В каждой таблице должен быть первичный ключ. Именно по нему таблица с данными будет соединяться с таблицей связи. Если в самой табилце нет первичного ключа, то его нужно создать самостоятельно одним из 2-х способов: либо с помощью нумерации строк rowno(), либо как составной ключ, собранный из всех полей связи и дат.
2. Поля, использующиеся для связи, должны называться одинаково во всех таблицах. Если у нас планируется связь по полю ContactID, то именно так оно должно называться в других таблицах (а не contact_id или ИД контакта).
3. Поля дат, которые вы хотите использовать в едином календаре, должны быть приведены ко дню. Т.е. если у вас есть таблица План по месяцам, где значения месяцев прописаны тектом типа «Март 2021», то нужно проеобразовать это к виду 01.03.2021.
4. Пустые даты должны обозначаться значениями null(), чтобы не получить дат типа 01.01.1970 в календаре.
5. Все поля, кроме полей связи, должны иметь уникальные имена.

В Google Sheets мы прописываем для каждой таблицы на в кладке Table Input:

1. Первичные ключи;
2. Поля, которые хотим использовать для связи;
3. Поля для дат.

В третьей колонке указываем соответствующий тип поля. Теперь на вкладке Code можно забрать сгенерированный код, скопировав его через Ctrl+C из ячейки B2. В ячейке B1 есть дополнительный фрагмент для квалификации названий таблиц, если она вам нужна. Будут квалифицированы все названия полей, кроме полей связи.

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

Логика сгенерированного кода следующая:

1. Для каждой таблицы создается временная таблица связей, к которой добавляются даты для создания единой временной оси;
2. Временные таблицы связей склеиваются в одну;
3. Из таблиц с данными удаляются все поля связей кроме первичных ключей, т.к. теперь связь будет идти через центральную таблицу.

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

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

Технические поля

В таблице связи создается поле Canonical_Date, которое можно использовать как единую временную ось. Можете приделать к нему свой календарь.

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

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

Название этих полей в генерируемом коде можно изменить на вкладке Settings.