Синтетические ключи в Qlik

Янв 4, 2022 Qlik

Часто при создании приложений Qlik Sense (QlikView) формирует синтетические ключи, выводя предупреждения об их создании в журнал загрузки. При этом распространено мнение о том, что любой синтетический ключ это плохо, от него следует непременно избавляться: часто в требованиях к разработке и архитектуре приложений в крупных организациях даже указываются запреты на публикацию приложений содержащих синтетические ключи в продуктивной среде.

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

Согласно официальной документации Qlik

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


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


Наличие множества синтетических ключей зачастую свидетельствует о неправильной модели данных. Тем не менее, верный признак неправильной модели данных — это наличие синтетических ключей на основе других синтетических ключей.

Давайте резюмируем:

  • Синтетический ключ —​ это всего лишь связь нескольких таблиц по двум и более полям;
  • Синтетический ключ физически — это невидимое для составления визуализаций поле, которое не может быть использовано в выражениях на листе или в скрипте;
  • Наличие синтетического ключа может означать проблемы в модели данных, а может не означать. Обратите внимание, это действительно важно – синтетический ключ может означать проблему, но не обязательно означает проблему. В каждом конкретном случае ответ будет разный;
  • Синтетические ключи состоящие из синтетических ключей явный признак проблемы;

Проблемы синтетических ключей

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

Согласно официальной документации Qlik

Если число синтетических ключей возрастает в зависимости от объемов данных, структуры таблиц и других факторов, в программе Qlik Sense они могут или не могут быть полноценно обработаны, а также может потребоваться дополнительное количество времени и/или памяти. В таком случае необходимо разработать скрипт заново, удалив все синтетические ключи.

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

Давайте рассмотрим несколько примеров синтетических ключей и определим проблемы, возникающие при их формировании, а также определим способы решения проблем.

Пример 1: Одинаковые названия свойств разных сущностей

Пусть у нас есть две таблицы Товары и Контрагенты. Таблица Товары выглядит следующим образом:

КодНаименованиеКатегория
1ГвоздиСкобяные
2ШурупыСкобяные
3СкобыСкобяные

Таблица Контрагенты выглядит следующим образом:

КодНаименование
1Вектор+
2Прогресс
3Центр-сервис
4Меркурий

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

Модель данных будет выглядеть следующим образом (слева вид внутренней таблицы, справа вид исходной таблицы):

Появился ключ по двум полям Код и Наименование. И здесь нам необходимо задать следующие вопросы:

  • В поле Код которое собрано из двух таблиц одно и тоже свойство или нет? Когда мы выбираем в поле Код значение 1 какой результат мы ожидаем?
  • В поле Наименование собрано из двух таблиц одно и тоже свойство или нет? Когда мы выбираем в поле Наименование значение что мы отбираем — товары или контрагентов?
  • Как связаны товары и контрагенты в реальности?

Ответив на эти вопросы мы понимаем что поле Код содержит как свойство Код товара так и свойство Код контрагента, поле Наименование в свою очередь содержит как Наименование товара так и Наименование контрагента. То есть мы загрузили в одно поле разные сущности! Пользователь, отбирающий данные в поле Код не будет понимать что он выбирает — то ли товары по коду, то ли контрагентов.

Для исправления данной проблемы нам необходимо просто переименовать поля или применить оператор QUALIFY, например так:

QUALIFY Код, Наименование;

Однако я настоятельно не рекомендую использовать операторы QUALIFY/UNQUALIFY в приложениях размещаемых на рабочем сервере, поскольку они делают результат выполнения скрипта труднопредсказуемым и при разработке приходится держать в голове какие поля подлежат добавлению наименования таблицы в поле, а какие нет. Поэтому давайте просто переименуем поля при загрузке:

Товары:
LOAD
Код as ТоварКод,
Наименование as ТоварНаименование,
Категория as КатегорияНаименование
INLINE [
Код Наименование Категория
1 Гвозди Скобяные
2 Шурупы Скобяные
3 Скобы Скобяные
] (delimiter is spaces);

Контрагенты:
LOAD
Код as КонтрагентКод,
Наименование as КонтрагентНаименование

INLINE [
Код Наименование
1 Вектор+
2 Прогресс
3 Центр-сервис
4 Меркурий
] (delimiter is spaces);

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

Пример 2: Дублирование свойств

Пусть у нас есть две таблицы Товары и Продажи. Таблица Товары выглядит следующим образом:

ТоварКодТоварНаименованиеКатегория
1ГвоздиСкобяные
2ШурупыСкобяные
3СкобыСкобяные

Таблица Продажи содержит следующие данные:

МагазинКодТоварКодТоварНаименованиеКатегорияКоличествоСумма
11ГвоздиСкобяные5450
21ГвоздиСкобяные6540
22ШурупыСкобяные81600
11ГвоздиСкобяные10900
32ШурупыСкобяные2400
32ШурупыСкобяные112200
33ШурупыСкобяные4800
13СкобыСкобяные3270

Модель данных после загрузки будет выглядеть следующим образом (слева вид внутренней таблицы, справа вид исходной таблицы):

Появился ключ по трем полям ТоварКод и ТоварНаименование и Категория. Мы понимаем что поля ТоварКод, ТоварНаименование и Категория содаржат то, что в них и должно быть и никакой путаницы с другими сущностями (как в предыдущем примере нет). И здесь нам необходимо задать следующие вопросы:

  • Что из полей ТоварКод, ТоварНаименование, Категория является идентификатором а что свойством?
  • Для какой таблицы свойства первичны? Для какой строго необходимы?
  • Что будет если мы выберем значение в поле ТоварНаименование?

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

Потому мы просто не загружаем или удаляем поля ТоварНаименование и Категория из таблицы Продажи:

DROP FIELDS ТоварНаименование, Категория FROM Продажи;

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

Пример 3: Похожие сущности в одну таблицу

Пусть у нас есть две таблицы Поставки и Списания. Таблица Поставки выглядит следующим образом:

ПоставщикКодПодразделениеКодДатаТоварКодКоличествоСумма
1431.01.2020319111651
1513.09.20203221342
1323.04.2020724915189
4410.10.2020321713237
2215.05.2020234621106

Таблица Списания выглядит следующим образом:

СписаниеСтатьяПодразделениеКодДатаТоварКодКоличествоСумма
Статья 1111.10.202028844
Статья 2218.08.202028844
Статья 3526.04.202038844
Статья 4216.07.202065527,5
Статья 5507.01.202034422
Статья 1318.09.202056633

При загрузке таких таблиц в приложение мы получаем следующую модель данных:

Qlik Sense создал синтетический ключ сразу по пяти полям! Давайте подумаем похож ли этот пример на предыдущие два примера и можем ли мы применить решения, озвученные выше:

  1. Можем ли мы попросту не загружать какие либо поля потому что есть таблица где они первичны и таблица где они вторичны? Нет, все поля нам нужны и отражают важные и неповторимые в других таблицах свойства сущностей — Поставок и Списаний.
  2. Можем ли мы переименовать поля так чтобы не было одинаковых наименований? Да, но появится проблема — например если мы добавил в модель данных справочник товаров из предыдущего примера то к какой таблице его добавлять к Поставкам или к Списаниям? К обоим мы добавить не сможем, так как образуется циклическая ссылка.

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

Факты:
LOAD
*,
'Поставка' as ТипДокумента
INLINE [
ПоставщикКод ПодразделениеКод Дата ТоварКод Количество Сумма
1 4 43861 3 191 11651
1 5 44087 3 22 1342
1 3 43944 7 249 15189
4 4 44114 3 217 13237
2 2 43966 2 346 21106
](delimiter is spaces);

CONCATENATE(Факты)
LOAD
*,
'Списание' as ТипДокумента
INLINE [
СписаниеСтатья ПодразделениеКод Дата ТоварКод Количество Сумма
Статья 1 1 44115 2 8 844
Статья 2 2 44061 2 8 844
Статья 3 5 43947 3 8 844
Статья 4 2 44028 6 5 527,5
Статья 5 5 43837 3 4 422
Статья 1 3 44092 5 6 633
](delimiter is spaces);

При выполнении скрипта мы получим следующую одну таблицу в модели данных:

Теперь для вычисления мер, в которых отделены Поставки и Списания мы можем использовать Анализ множеств. Например следующим образом:

Мера «Сумма списаний»

SUM({<ТипДокумента={'Списание'}>} Сумма)

Мера «Сумма поставок»

SUM({<ТипДокумента={'Поставка'}>} Сумма)

Пример 4: Добавляем дополнительное свойство к таблице

Пусть у нас есть две таблицы Продажи и Ответственные. Таблица Ответственые содержит фамилии отвественных сотрудников за товар в конкретном подразделении. Таблица Отвественные выглядит следующим образом:

ТоварКодПодразделениеКодМенеджерТовара
11Иванов
21Иванов
31Иванов
41Петров
12Сидоров
22Сидоров
32Сидоров
42Сидоров
13Николаев
23Николаев
33Александров
43Александров

Таблица Продажи выглядит следующим образом:

ПодразделениеКодДатаТоварКодКоличествоСумма
203.10.2020444251051
313.07.2020146453592
302.03.2020241047355
115.01.202029711203,5
228.11.2020335440887

При загрузке двух таблиц мы получаем следующую модель данных:

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

Продажи:
LOAD *
INLINE [
ПодразделениеКод Дата ТоварКод Количество Сумма
2 03.10.2020 4 442 51051
3 13.07.2020 1 464 53592
3 02.03.2020 2 410 47355
1 15.01.2020 2 97 11203,5
2 28.11.2020 3 354 40887
](delimiter is spaces);

LEFT JOIN(Продажи)
LOAD *
INLINE [
ТоварКод ПодразделениеКод МенеджерТовара
1 1 Иванов
2 1 Иванов
3 1 Иванов
4 1 Петров
1 2 Сидоров
2 2 Сидоров
3 2 Сидоров
4 2 Сидоров
1 3 Николаев
2 3 Николаев
3 3 Александров
4 3 Александров
](delimiter is spaces);

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

Итоговая модель данных после использования LEFT JOIN будет выглядеть следующим образом:

Пример 5: Превращаем синтетический ключ в обычный

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

Например, мы имеем таблицы Продажи и НормыСписания. Таблица Продажи имеет следующую структуру:

ПодразделениеКодДатаМесяцТоварКодКоличествоСумма
203.10.2020Март 2020444251051
313.07.2020Июль 2020146453592
302.03.2020Март 2020241047355
115.01.2020Январь 202029711203,5
228.11.2020Ноябрь 2020335440887

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

МесяцПодразделениеКодНормаСписания
Январь 202011000000
Март 202011500000
Июль 202012000000
Ноябрь 202011700000
Январь 202021000000
Март 202021500000
Июль 202022000000
Ноябрь 202021700000
Январь 202031000000
Март 202031500000
Июль 202032000000
Ноябрь 202031700000

Если загрузить обе эти таблицы мы получим следующую модель данных:

Допустим, мы хотим считать по данной модели данных сумму по норме списаний за каждый месяц мерой SUM(НормаСписания), вариант с JOIN нам не подойдет, потому что он попросту задублирует нам данные так как к одному сочетанию ПодразделениеКод + Месяц из таблицы НормаСписания может быть сколько угодно (например 0 или 50) значений таблицы Продажи.

Есть еще несколько вариантов которые мы не опробовали в предыдущих примерах. Первый из них оставить данный синтетический ключ в покое, пусть себе будет. Какие проблемы от того что в этой модели данных есть синтетический ключ? Правильный ответ — никаких. В обеих таблицах не миллиард строк, поэтому проблем с производительностью не будет. Будут ли неправильно работать отборы? Нет не будут, потому что эти две таблицы действительно связываются логически по комбинации двух полей.

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

Самый простой способ сделать из двух полей одно — это сложить их как строки. Например таким образом:

ПодразделениеКод & ‘-‘ & Месяц as Ключ_ПодразделениеКодМесяц

Может появится вопрос — почему я не сложил просто две строки так:

ПодразделениеКод & Месяц as Ключ_ПодразделениеКодМесяц

Зачем потребовалось добавлять тире между значениями ПодразделениеКод и Месяц? Ответ очень простой — если ПодразделениеКод равно 1 и Месяц равен 12, то в поле Ключ_ПодразделениеКодМесяц будет значение 112. Но с другой стороны если ПодразделениеКод равно 11 и Месяц равен 2, то в поле Ключ_ПодразделениеКодМесяц будет тоже 112. А это неправильно, ключ должен быть уникален для уникальных пар кода подразделения и месяца. Разделитель в виде тире или любого другого символа позволяет сделать наше новое ключевое поле правильным для примеров указанных выше (1-12 и 11-2 соотвственно).

Продажи:
LOAD
//ПодразделениеКод,
Дата,
//Месяц,
ТоварКод,
Количество,
Сумма,

ПодразделениеКод & ‘-‘ & Месяц as Ключ_ПодразделениеКодМесяц
INLINE [
ПодразделениеКод Дата Месяц ТоварКод Количество Сумма
2 03.10.2020 ‘Март 2020’ 4 442 51051
3 13.07.2020 ‘Июль 2020’ 1 464 53592
3 02.03.2020 ‘Март 2020’ 2 410 47355
1 15.01.2020 ‘Январь 2020’ 2 97 11203,5
2 28.11.2020 ‘Ноябрь 2020’ 3 354 40887
](delimiter is spaces);

НормыСписания:
LOAD
Месяц,
ПодразделениеКод,
НормаСписания,

ПодразделениеКод & ‘-‘ & Месяц as Ключ_ПодразделениеКодМесяц
INLINE [
Месяц ПодразделениеКод НормаСписания
‘Январь 2020’ 1 1000000
‘Март 2020’ 1 1500000
‘Июль 2020’ 1 2000000
‘Ноябрь 2020’ 1 1700000
‘Январь 2020’ 2 1000000
‘Март 2020’ 2 1500000
‘Июль 2020’ 2 2000000
‘Ноябрь 2020’ 2 1700000
‘Январь 2020’ 3 1000000
‘Март 2020’ 3 1500000
‘Июль 2020’ 3 2000000
‘Ноябрь 2020’ 3 1700000
](delimiter is spaces);

После загрузки мы получим следующую модель данных:

В модели нет синтетических ключей, но вот некоторым разработчикам не нравится то, что есть поле Ключ_Подразделения в котором очень длинная строка (Кстати не важно насколько длинное ключевое поле с точки зрения производительности работы связи между таблицами). Эту строку можно заменить на более короткий код следующим образом:

Autonumber(ПодразделениеКод & ‘-‘ & Месяц) as Ключ_ПодразделениеКодМесяц

Функция AutoNumber возвращает уникальное значение в виде целого числа для каждого уникального значения указанного в выражении.

Либо можно не меняя LOAD оператор в конце скрипта записать следующую строку:

Autonumber Ключ_ПодразделениеКодМесяц;

Оператор AutoNumber для всех полей указанных через запятую (в нашем случае всего одно поле) создает для каждого уникального значения поля уникальное целочисленное значение и записывает его вместо значения поля.

Итоги

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

  • Не загружать или удалить поля которые дублируют друг друга;
  • Переименовать поля которые названы одинаково, но являются свойствами разных сущностей;
  • Произвести изменения в модели данных, например — использовать JOIN или CONCATENATE для объединения двух таблиц в одну. Можно также использовать различные паттерны, например таблицу линковки;
  • Исследовать синтетический ключ и понять что он не создает дополнительных проблем, оставить его в покое или преобразовать в новое ключевое поле.

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