Как вы уже знаете, в 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 каталога со справочниками и фактами помогут нам ориентироваться во всех доступных данных? Об этом вы узнаете в следующем занятии.