“Синтетика” и методы борьбы с оной

Коллеги, приветствую всех Вас.

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

Для начала небольшое отступление от основной темы поста…

Для тех кто вдруг не в курсе, QlikView имеет такое полезное (правда, не всегда) свойство как автоматическая простановка связей между таблицами, загружаемыми в документ. И делается это по самому, что ни на есть, простейшему алгоритму – по одинаковому наименованию полей. Иными словами, QlikView, всякий раз когда видит одинаковые наименования полей в разных таблицах, автоматически выстраивает связь между ними по этому самому полю. И как все уже, наверное, догадались, это поле становится ключевым. Вот и весь принцип построения связей между таблицами внутри одного документа QlikView.

На этом я позволю себе закончить с вводной частью и…

"Поехали!"

Copyright © 12 апреля 1961, Ю.А. Гагарин

Синтетические ключи возникают, когда мы загружаем в документ таблицы со сложными составными ключами(2 и более полей входят в уникальный ключ). Рассмотрим простейший пример. Необходимо загрузить 2 таблицы: Детали заказа и Отгрузки. Эти таблицы д.б. связаны по сложному ключу, состоящему из 2х полей: № Заказа(OrderID) и № Товара(ProductID). После загрузки этих 2х таблиц мы получим следующую модель данных:

А именно, мы получили синтетический ключ “$Syn 1” и некую промежуточную таблицу с наименование “$Syn 1Table”, в которой хранятся все имеющиеся комбинации значений полей OrderID и  ProductID. Все бы ничего, т.к. по логике вещей все нормально и с этой точки зрения синтетические ключи не являются ошибкой. Однако, они очень сильно влияют на производительность документа. По идеи, если у Вас небольшие объемы данных, то Вам можно, что называется, не заморачиваться над этой проблемой. Однако, я все же советую Вам стараться всегда избавляться от этой картины в модели данных и избегать появления “синтетики” в Ваших приложениях. Если хотите можете воспринимать этот совет как некий пункт Best Practiсes при построению модели данных.

Так почему же эти ключи оказывают такое воздействие на производительность Вашего приложения? Как я уже сказал чуть выше, QlikView хранит ВСЕ возможные комбинации значений полей составного ключа, в нашем случае это значения полей OrderID и  ProductID.  Таким образом на больших объемах данных мы получаем дополнительную головную боль… Помимо этого, мы можем получить не соответствие в структуре данных, которое не всегда легко и быстро заметить. И чтобы совсем уж Вас “добить” и избавить от привычки “забивать” на эту ситуацию скажу еще об одной вещи. При наличии нескольких составных ключей (читай синтетических) в приложении QlikView, которые ко всему прочему зачастую пересекаются между собой своими составными частями или являются частями друг друга, система будет строить дополнительные синтетические ключи на основе других более мелких синтетических ключей. Во как завернул… В общем, мы добьемся того, что наше приложение даже на очень небольших объемах “встанет колом” на этапе построения связей между таблицами. Что естественно не есть гуд.

Как же от них избавляться? Есть несколько путей решения данной проблемы, о тех, что известны мне на данный момент я Вам сейчас и поведаю…

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

Показать...

OrderDetails:
LOAD
     OrderID & '~' & ProductID        as OrderShipmentKey,
     OrderID,
     ProductID,
     Quantity,
     UnitPrice,
     Discount
INLINE [
    OrderID, ProductID, Quantity, UnitPrice, Discount
    OR001, Item01, 1, 5, 10
    OR001, Item15, 2, 34, 0
    OR002, Item01, 5, "7,5", 0
];

Shipments:
LOAD
     OrderID & '~' & ProductID        as OrderShipmentKey,
//     OrderID,
//     ProductID,
     ShipmentDate,
     ShipmentMethod    
INLINE [
    OrderID, ProductID, ShipmentDate, ShipmentMethod
    OR001, Item01, 11.02.2009, 0
    OR001, Item15, 11.02.2009, 0
    OR002, Item01, 19.09.2009, 1
];

Символ тильда(“~”) используется здесь в качестве разделителя между полями составного ключа и не должен встречаться в наборе данных. Кроме того, необходимо исключить из загрузки дубликаты наименования полей путем их переименования или (как это сделано в моем примере) комментирования.

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