4-1. Что такое аналитическая таблица и QVD-файлы

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

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

Специфика QVD-файла в том, что даныне в нем хранятся поколоночно, а не построчно (в отличие от транзакционных БД и табличных файлов типа CSV или Excel). Это значит, что суммарный вес файла определяется не кол-вом всех значений во всех полях. А кол-вом уникальных значений в каждом поле, и их индексами. В итоге, данные в QVD файле могут занимать место на несколько порядков меньше, чем те же данные в таблице с построчным хранением данных.

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

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

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

Аналитические таблицы

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

За формирование аналитических таблиц отвещают специальные приложения Qlik. Обычно их называют QVD-генераторы. Эти приложения не содержат никаких дашбордов, они с помощью скрипта подключатся к источникам, преобразовывают данные и сохраняют их в QVD-файлы. Потом эти файлы загружаются в аналитические приложения, собираясь в модель данных. В аналитических приложениях на основе модели данных создаются дашборды.

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

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

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

Создание аналитических таблиц в Qlik Sense

Откройте в новом приложении скрипт загрузки, и создайте в нем 2 источника данных:

  • Папку Data, в которой вы разместите файл с данными для занятия;
  • Папку QVD Layer, которая должна вести на произвольную пустую папку на вашем компьютере.

Создайте секцию скрипта под названием Raw Data, и вставьте на нее скрипт загрузки данных со всех листов Excel-Таблицы.

Разберем, какие данные нам доступны. У нас есть 2 таблицы фактов:

  • Продажи;
  • Поставки;

А также, пачка справочников:

  • Клиенты — связаны с продажами;
  • Поставщики — связаны с поставками;
  • Товары — связаны с продажами и поставками;
  • Регионы — связаны с клиентами и поставщиками;
  • Типы клиентов — связаны с клиентами;
  • Пользователи — связаны с продажами и клиентами;

Давайте сформируем из этого добра красивый аналитический ландшафт. Начнем со справочников.

QVD-справочники

Пропишите в начале вкладки Raw Data команду «exit script», и создайте секцию скрипта Справочники над Raw Data.

Таким образом мы не будем загружать все сырые данные, и сможем копировать из Raw Data нужные фрагменты кода. Начнем со справочника клиентов. Вставьте в секцию Справочники код загрузки справочника клиентов.

Мы не будем делать никаких сложных преобразований, т.к. данный мини-курс не про это. Обратим внимание на критически важный аспект в формировании QVD-слоя: систему именования полей.

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

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

В Qlik существует несколько подходов к переименованию полей, в т.ч. автоматические. Но мы для простоты восопльзуемся пока ручным способом.

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

Важным аспектом сохранения QVD-справочников является денормализация (т.е. объединение нескольких таблиц в одну через Join).

Смысл в том, что нормализованное хранение данных обоснованно в реляционных СУБД, для экономии занимаемого данными места. Но т.к. у нас поколоночное хранение, мы можем совместить несколько справочников в один, для упрощения своего аналитического ландшафта, без увеличения занимаемого данными объема.

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

Допишем присоединение типа клиентов в справочник клиентов.

Напоминаю, что join объединяет 2 таблицы в одну на основе совпадающих значений полей с одинаковыми названиями межды ними. Поле «Тип клиента» сразу назовем «КЛ.Тип клиента», ведь оно будет присутствовать в справочнике Клиенты.

Мы не будем объединять с Клиентами справочники Сотрудников и Регионов, т.к. они задействуются в т.ч. для связи с другими таблицами — Продажами и Поставщиками. Но при необходимости вы могли бы добавить сюда наименование менеджеров и регионов под видом «Менеджер клиента» и «Регион клиента», если вам нужно будет анализировать продажи в разрезе и тех кто продажу осуществил, и тех кто отвечает за этого клиента. Либо можно было назвать поле @User_ID как @Client_User_ID, и справочник пользователей сохранить 2 раза с разными названиями полей, для той же самой цели. Т.е. был бы справочник пользователей, отвечающих за продажи, и справочник пользователей, отвечающих за клиента.

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

drop field @Client_Type_ID удалит данное поле из всех таблиц модели (которая в настоящий момент у нас одна).

Сохраним нашу таблицу в QVD с помощью команды store:

Store Клиенты into [lib://QVD Layer/Analytical/DICT/Клиенты.qvd] (qvd);

В конце обязательно удаляем таблицу Клиенты через drop table. Ведь мы будем загружать другие таблицы, и нам не нужно, чтобы они собирались в модель данных тут.

После выполнения скрипта вы увидите в папке, которую вы создали как источник QVD Layer, подпапку Analytical, в ней папку DICT, в ней файл Клиенты.qvd.

Да, команда store умеет создавать папки при сохранении файлов.

Структура папок для хранения QVD

Как лучше организовать структуру папок для хранения QVD? Мы пришли к такой логике.

Есть папка QVD layer, в ней находится 2 подпапки:

  • Analytical — для чистовых таблиц;
  • Temporary — для промежуточных qvd файлов, которые не исопльзуются в аналитических приложениях, но задействуются в процессе преобразования данных.

Внутри Temporary может быть любая структура, которая вам удобна.

Внутри Analytical находятся 2 папки: DICT и FACT. Это для таблиц справочников и фактов соответственно. Внутри DICT и FACT никаких подкаталогов не содержится. Потому что разбивать таблицы на системы смысла нет — аналитические таблицы могут быть результатом связывания данных из разных источников.

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

Допишите сохранение в QVD остальных справочников:

Регион:
LOAD
    "ИД региона" as @Region_ID,
    Регион as РГ.Регион
FROM [lib://Data/Analytical Landscape DS.xlsx]
(ooxml, embedded labels, table is Регионы);

Store Регион into [lib://QVD Layer/Analytical/DICT/Регион.qvd] (qvd);
drop table Регион;

Товары:
LOAD
    "ИД товара" as @Product_ID,
    Товар as ТВ.Товар
FROM [lib://Data/Analytical Landscape DS.xlsx]
(ooxml, embedded labels, table is Товары);

Store Товары into [lib://QVD Layer/Analytical/DICT/Товары.qvd] (qvd);
drop table Товары;

Пользователи:
LOAD
    "UserID" as @User_ID,
    Пользователь as ПЛ.Пользователь,
    Отдел as ПЛ.Отдел
FROM [lib://Data/Analytical Landscape DS.xlsx]
(ooxml, embedded labels, table is Пользователи);

Store Пользователи into [lib://QVD Layer/Analytical/DICT/Пользователи.qvd] (qvd);
drop table Пользователи;

Поставщики:
LOAD
    "ИД поставщика" as @Supplier_ID,
    Поставщик as ПС.Поставщик,
    "ИД региона" as [@Region_ID]
FROM [lib://Data/Analytical Landscape DS.xlsx]
(ooxml, embedded labels, table is Поставщики);

Store Поставщики into [lib://QVD Layer/Analytical/DICT/Поставщики.qvd] (qvd);
drop table Поставщики;

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

Как результат, у вас должно появиться 5 справочников.

Таблицы фактов

Сохраним в наши QVD-файлы табилцы фактов. Начнем с продаж.

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

Продажи:
load *, @Client_ID&'|'&@Product_ID&'|'&@User_ID&'|'&ПР.Дата as @SALES_CK;

LOAD
    Дата as ПР.Дата,
    "ID клиента" as @Client_ID,
    "ID продукта" as @Product_ID,
    Сумма as ПР.Сумма,
    "Кол-во" as [ПР.Кол-во],
    "ID пользователя" as @User_ID
FROM [lib://Data/Analytical Landscape DS.xlsx]
(ooxml, embedded labels, table is Продажи);

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

Если в таблице нет явного поля первичного ключа (прям как у нас в Продажах), то можно использовать вместо него составной ключ, который является комбинацией всех полей связи и полей дат, которые нужно выводить на единую временную ось (см. мини-курс 3 про таблицы связей).

Такое поле мы тоже помечаем через @, чтобы была видна его причастность к логике связей данных. Хотя скорее всего на это поле никто никогда не будет ссылаться.

Дабы подтвердить наше стремление не просто пересохранять исходные данные в QVD, но обогащать их средствами скрипта Qlik, добавим под загрузку продаж вот такой код:

Продажи2: load *,
if(Previous(@Client_ID)<>@Client_ID,1,peek('ПР.Номер продажи')+1) as [ПР.Номер продажи]
Resident Продажи order by @Client_ID, ПР.Дата asc;
drop table Продажи; Rename table Продажи2 to Продажи;

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

Сохраним таблицу Продажи в QVD, и удалим ее из модели:

Добавим код загрузки и сохранения поставок:

Поставки:
load *, @Supplier_ID&'|'&@Product_ID&'|'&ПСТ.Дата as @SUPP_CK;

LOAD
    Дата as ПСТ.Дата,
    "ИД поставщика" as @Supplier_ID,
    "ИД товара" as [@Product_ID],
    "Кол-во" as  [ПСТ.Кол-во],
    Сумма as ПСТ.Сумма
FROM [lib://Data/Analytical Landscape DS.xlsx]
(ooxml, embedded labels, table is Поставки);

Store Поставки into [lib://QVD Layer/Analytical/FACT/Поставки.qvd] (qvd);
drop table Поставки;

Убедитесь, что в фактах появилось 2 qvd файла:

Заключение

Мы собрали свой простой генератор QVD-слоя. Конечно, в ваших реальных задачах скрипты могут быть намного сложнее. В этом случае нужно выводить под формирование каждой сложной таблицы отдельную секцию кода. Если вам нужно добавить в таблицу данные из других таблиц, всегда берите их из других QVD файлов, временных или аналитических. Не допускайте, чтобы в текущей вкладке задействовались данные через resident из таблиц, созданных в других вкладках.

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

Что мы имеем на данный момент? Набор подготовленных к аналитике QVD файлов. Неплохо, но все еще не ясно: как 2 каталога со справочниками и фактами помогут нам ориентироваться во всех доступных данных? Об этом вы узнаете в следующем занятии.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *