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

В данной статье я расскажу о наиболее интересных реализациях таких задач в порядке “от простого к сложному”. Сначала – несколько слов о ручном переносе данных из чартов QlikView в Excel, CSV и другие форматы; далее – способы автоматизированного переноса данных из ассоциативной модели QlikView в CSV/таблицу СУБД; ну и «на десерт» – задача автоматического переноса данных из Qlik-а с помощью NPrinting.

 

I. Ручной перенос данных из чартов QV в Excel/CSV/QVD

 

Самая часто встречающаяся задача: пользователь хочет перенести набор данных (значения показателей в разрезе выбранных элементов измерений) из чарта в интерфейсе приложения QV (плоской или сводной таблицы) к себе в настольное приложение MS Excel/Access/QV/Sense, чтобы самостоятельно проработать с ним – связать с другими данными, построить свои сводные таблицы и графики.

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

 

Способ 1. Простейший способ – команда контекстного меню в интерфейсе: Send To Excel (Отправить в Excel).

Автоматически созданный замещающий текст: QIikViewx54 - File Edit View Selections Layout Settings Bookmarks F o rward Reports Tools Unlock Object Window Help Clear O npMopMTeTbl noapa3aeneHk1R O Back Ha60p noKa3aTene% OCHOBHble 6 noKa3aTen* 4. nonq 06tueM wcne KweHTOB (%) 64.66% "Tor pacCWTbIBaTb KM CpeaHee O L.IToro "CpeAHee"; nepH0A 2. nonq cnquvx* B 110) 12.2015 O bl-roro (6e3 EpoH3a 08.11.2015-20. 3. nonq 06tueM wcne KweHTOB (%) 0.23% Ba nepH0A: 20.12.2015; Bb16paHHbl% nor - "Tor OT Bb160pa) 6aHK 6aHK 6aHK AanbHesocToqHbü 6aHK 6aHK 6aHK 6aHK 6aHK CesepHbü 6aHK Cesepo-3anaaHbü 6aHK 6aHK 6aHK 6aHK I. nonq KweHTOB OT cnquVN (%) 1.58% O Properties.. Detach Clone Fit Columns to Data Equal Column Width Sot Custom Format Cell Order Clear All Selections Print... Print PDF... Send to Excel Export... Copy to Clipboard Maximize Help Remove

 

Этот способ отлично работает, когда количество строк в чарте QlikView невелико – существенно меньше одного миллиона. Если до 65 тысяч – выгрузка будет выполнена в формате XLS, если больше – то в CSV. В обоих случаях будет запущен MS Excel, и QV некоторое время будет передавать данные непосредственно в его окно (т.е. оба процесса ненадолго “подвиснут”). После того, как в окно Excel будут переданы все данные, книгу можно сохранить в любом поддерживаемом формате, и далее работать как с любой другой книгой Excel, ну и, конечно, использовать как источник данных.

 

Способ 2. Способ посложнее – команда контекстного меню в интерфейсе: ExportCSV  (Экспорт – CSV).

Автоматически созданный замещающий текст: QlikVievvxS4 - File Edit View Selections Layout Settings 800kmarks Forward Reports Tools ДГ Ип[осК 0 bject Window Неф Clear О Приоритеты Подразделения Васк 12.2015 Набор показателей Основные показателей 4. Доля активные в общем числе клиентов (%) Итог рассчитывать Среднее О Итого “среднее"; период 2. Доля спящие в общем числе клиентов (%) Properties.. Detach Clone Fit Columns to 0ata Equal Column Width Sort Custom Format Сел 0rder Clear ДП Selecticns Print... РОЕ... Send to Excel Export... Сору to Clipboard Maximize Неф Remove О Итого (без Бронза динамики: 08.11.2015-20. З. Доля уснувшие в общем числе клиентов (%) за период: 20.12.2015; выбранный итог - 1. Доля активизированных клиентов от спящих (%) Итог (зависит от выбора) Байкальский банк Волго-Вятский банк Восточно-Сибирский банк Дальневосточный банк Западно-Сибирский банк Западно-уралвский банк Московский банк Поволжский банк Северный банк Северо-Западный банк Северо-Кавказский банк Сибирский банк Среднерусский банк Уральский банк

 

В появившемся диалоговом окне нужно выбрать тип файла – Comma Delimited CSV:

Автоматически созданный замещающий текст: QlikViewxS4 - File Edit View Selections Layout Settings 800kmarks Forward Reports Tools Ип|осК 0 bject Window Неф Clear О Приоритеты Подразделения Васк 12.2015 Набор показателей Основные показателей 4. Доля активные в общем числе клиентов (%) Итог рассчитывать Среднее О Итого “среднее"; период 2. Доля спящие в общем числе клиентов (%) О Итого (без Бронза динамики: 08.11.2015-20. З. Доля уснувшие в общем числе клиентов (%) за период: 20.12.2015; выбранный итог - 1. Доля активизированных клиентов от спящих (%) Итог (зависит от выбора) Байкальский банк Save С-ЈС_)' Desktop File пате: Save as type: 8rowse Folde Северо-Кавказский банк likView Files QlikViewEx FilesC. мс) Сстта Delimited Semicolon Delimited Т Вб Delimited Hypertext ХМИ Ехсе| C.xls) QlikView 0ata File C.qvd)

 

НА ЗАМЕТКУ! CSV (данные в виде простого текста, разделители запятые) – распространенный формат для обмена данных между различными системами и приложениями.

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

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

Процесс может ненадолго “подвиснуть”.

Получившийся CSV-файл можно загрузить:

  • в Excel (на обычный лист через команду “Данные – Получение внешних данных – Из текста” или в модель PowerPivot),
  • Access
  • и многие другие настольные приложения и системы.

Однако способ нужно применять с осторожностью – он ресурсоемкий по RAM и есть риск того, что процесс QV займет всю доступную оперативную память, а локальная рабочая станция “зависнет”. Зато следующий способ лишен этого недостатка.

 

Способ 3. Продвинутый способ – команда контекстного меню в интерфейсе: ExportQVD (Экспорт – QVD) и последующее преобразование в CSV

Как и в предыдущем способе, команда Экспорт, в типе файла выбрать QlikView Data File QVD:

 

Автоматически созданный замещающий текст: QIikViewx54 - File Edit View Selections Layout Settings Bookmarks F or-ward Reports Tools Unlock Object Window Help Clear O npMopMTeTbl noapa3aeneHk1R O Back 110) 12.2015 Ha60p noKa3aTene% OCHOBHble 6 noKa3aTen* 4. nonq 06tueM wcne KweHTOB (%) 64.66% "Tor pacCWTbIBaTb KM CpeaHee O L.IToro "CpeAHee"; nepH0A 2. nonq cnquvx* B 06tueM wcne KweHTOB (%) 6.29% O bl-roro (6e3 EpoH3a 08.11.2015-20. 3. nonq 06tueM wcne KweHTOB (%) 0.23% Ba nepH0A: 20.12.2015; Bb16paHHbl% nor - I. nonq KweHTOB OT cnquVN (%) "Tor OT 1.58% Bb160pa) 6aHK Save As 00 v Desktop File name: Save as type: ikView Data File QIikView Export Files C.qvo) Comma Delimited Browse Folders Search Desktop neepHbM Cesepo-3anaaHbü 6aHK Semicolon Delimited Tab Delimited Hypertext XML Excel C.xls) QIikView Data File t.

 

НА ЗАМЕТКУ! QVD – проприетарный формат хранения данных в системах Qlik, оптимизированный для очень быстрой загрузки и поддерживающий компрессию данных.

Способ подходит для случаев, когда количество строк данных слишком велико для выгрузки их предыдущими способами – сотни тысяч и даже миллионы.

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

Получившийся QVD-файл можно либо непосредственно загрузить в пользовательское QlikView/Qlik Sense-приложение, либо преобразовать его в CSV с помощью такого простейшего QlikView-скрипта (запускать в локальном QV-приложении, сохраненном в том же расположении что и исходный QVD-файл; процесс также НЕ ресурсоемкий):

 

 

II. Автоматизированный перенос данных из ассоциативной модели QV в CSV/таблицу СУБД

Такая задача иногда встречается в компаниях, где QlikView является “единым источником правды”: требуется в автоматизированном режиме регулярно передавать “сырые” либо агрегированные/обогащенные/обработанные/вновь порожденные данные из ассоциативной модели приложения QlikView в другую корпоративную систему.

Вот способы, которыми это можно сделать.

 

Способ 1. Сохранение указанной таблицы модели данных в файл CSV

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

 

 

В качестве filepath нужно указать локальный или сетевой (в формате UNC) ресурс, доступ к которому есть у системы-рецепиента. В качестве filename можно использовать либо статичное название (тогда CSV-файл будет каждый раз перезаписываться), либо использовать Dollar-sign expansion и в название файла ставить суффикс даты-времени создания (тогда каждый раз будет создаваться новый CSV-файл). Случай, когда CSV-файл нужно отправлять на FTP, будет рассмотрен в следующем разделе.

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

Способ 2. Передача данных из указанной таблицы модели данных в таблицу СУБД

QlikView в своем развитии затачивался на развитие функций импорта данных из возможно более широкого перечня источников, чего нельзя сказать о функциях экспорта. Задача BI-инструмента – это, все-таки, “интеграция IN”; a “интеграция OUT” – вторична. Хотя Qlik в последние годы уделяет большее внимание этому направлению – Embedded Analytics, API.

Однако даже в привычном QlikView версий 11 и 12 есть встроенная возможность работы “на запись” с другими системами, основанными на СУБД.

Для этого используется команда скрипта (script statement) – SQL, которая позволяет отправить сформированную QlikView SQL-инструкцию к СУБД по открытому ODBC/OLE DB соединению.

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

 

Примеры передачи данных в таблицу СУБД Oracle

Далее будет рассмотрено несколько примеров передачи данных в таблицу СУБД Oracle на этапе выполнения скрипта QlikView-приложения.

Пример 1. Построчная передача данных

Стандартный SQL-синтаксис предлагает следующую команду для вставки значений в таблицу: INSERT INTO <tablename> VALUES ( <comma-separated list of values> ). Создадим цикл, где количество итераций будет равно количеству строк в таблице данных, и в каждой итерации запустим выполнение SQL-команды.

Ограничением данного способа является низкая производительность – передача сотен тысяч строк может сильно затянуться.

Пример 2. Пакетная передача данных

Способ подходит для тех случаев, когда количество строк данных велико, и построчная обработка может затянуться на неприемлемое время. Некоторые СУБД (Oracle) предлагают специальные SQL-команды, в которых можно передать значения полей для группы строк.

 

 

В заключение этой части статьи стоит отметить, что описанные способы реализуются на уровне скрипта приложения QlikView, и, следовательно, автоматизация их регламентного выполнения делается точно также, как и для ETL/конечных аналитических приложений QV – через создание задания (Task) в QV Management Console (QMC).

 

 

III. Автоматизированный перенос данных из чартов QlikView в Excel/CSV с помощью NPrinting.

Эта задача аналогична первой – “Ручной перенос данных из чарта QV в Excel/CSV/QVD”, но с тем отличием, что выгрузку требуется выполнять автоматически по регламенту.

Самым удобным ее решением в границах ландшафта QV мне представляется использование NPrinting (про него я уже писал статьи на этом блоге, см. ссылки в конце статьи).

У NPrinting есть как собственный Scheduler, так и возможность быть запущенным из соответствующего задания (Task) в QMC, что лично мне представляется более удобным (триггеры в таком случае могут быть настроены на события от других заданий).

 

Решение задачи здесь будет в несколько шагов:

  1. В новом проекте NPrinting создается Connection к целевому QV-приложению, настраиваются Filters (например, фильтры на определенные значения в полях, активация указанных Bookmarks, изменение значений Variables);
  2. Создается Report, для Excel все очевидно и рассмотрено во множестве других статей, а вот для выгрузки в CSV делается по-другому, поэтому приведу несколько скриншотов:

 

Автоматически созданный замещающий текст: Ноте Entity Report Ехсе| reports HTML reports ТооК Search Delete С|опе nctions Мате Dependencies Lookup данных [Отчет kVI].nsq - Description NPrinting for QlikVie. Гибкий отчет с набором столбцов 01 - QlikView Entity Report Гибкий отчет с набором столбцов 01 Гибкий отчет с набором столбцов 02 PixePerfect reports PowerPoint reports QlikVia,» Entity reports Word reports Schedules Iasks Save апј Close Мате QlikVia,» Entity Report Delete апј nctions ибкий отчет с набо ом столбцов 01 General Filters Сус|е Sh0',» Conditions Description Соппесејоп ОЗ. Товародвижение - 0bject 0ject Ю ороситепе\СН17 0utput НЕ Конструктор отчетов. Сокращенный.ьееа File пате: Выгрузка данных [Отчет kVI] ЕпаЬ|е Dynamic Naming Custom Dynamic Naming Separator Previa,» Report пате QlikVia,» variable QlikVia,» variable Выгрузка данных [Отчет kVI] Выгрузка данных [Отчет kVI] ОЗ. Товародвижение - Конструктор отчетов.Сокращенный.Ьееа vContext Ск ОЗ. Товародвижение - Конструктор отчетов.Сокращенный.Ьееа vContext D Товародвижение - Конструктор отчетов.Сокращенный.Ьееа vContext Товародвижение - Конструкто

В диалоговом окне Report нужно указать Object ID того чарта, данные из которого требуется выгрузить, и целевое имя CSV-файла (с динамическим формированием суффикса, если необходимо).

3. Создается Report Task, в диалоговом окне на закладке General указывается целевое назначение для файла, на закладке Reports добавляется нужный отчет, и в выпадающем списке Output Format – выбирается CSV.

 

 

Автоматически созданный замещающий текст: Home Task Report Tasks Reload Tasks Tools x Search Name 3KcnopT aaHHblX.QV Entity OTqeT c Ha60poM CTon6uos 01 3KcnopT aaHHblX.QV Entity OTqeT c Ha60poM CTon6uos 01 3KcnopT aaHHblX.QV Entity OTqeT c Ha60poM CTon6uos 01 3KcnopT aaHHblX.QV Entity OTqeT c Ha60poM CTon6uos 02 Dependencies Lookup R Task View Clear C:XNPrintingXProjectsXBbIrpy3Ka aaHHbIN LOTqeT KVIl.nsq - Description NPrinting for QIikView Delete Clone Actions KaapTa MecRu . nonroaa Reports: OTqeT c Ha60poM CTon6uos 01 Reports: OTqeT c Ha60poM CTon6uos 01 Reports: OTqeT c Ha60poM CTon6uos 01 Reports: OTqeT c Ha60poM CTon6uos 02 Partial Reload Tasks Reduce Tasks Run Macro Tasks Import Recipient Tasks Name Description Connection Reports Name Close connection when task ends Output Format Advanced 03. Tosapo. csv 3KcnopT aaHHbIN.QV Entity OTqeT c Ha60poM CTon6uoB 01.KBapTan - Report Task Report Task Delete and New Actions Kcno T aaHHblX. General Reports Recipients Show Remove V Entit Re OTqeT c Ha60 0M CTon6uos 01 .Ksa Tan KOHCTpyKTop OTqeTOB. COKpaueHHbü.beta Description 03. Tosapoasvwer-wle - OTqeT c Ha60poM CTon6uos 01

 

4. Создается Job, туда включается Report Task и, при необходимости, Reload Task для QV-приложения с преобразованиями формата чисел в CSV. Такое приложение может понадобиться, если исходный чарт в QlikView содержит форматирование чисел в ячейках (пробелы как десятичные разделители, символ “%” для дробных величин).

В таком случае можно создать новое простое QV-приложение, в скрипте которого выполняется загрузка CSV-файла, преобразование значений и повторное сохранение в CSV-файл с форматированием чисел в стандартном формате (способ описан выше в статье). Если CSV будет использоваться только в QlikView/Qlik Sense – это необязательно.

И еще одна интересная фишка: NPrinting может отправлять отчеты (в т.ч. выгрузки в формате CSV) не только на Windows shared folders, но и на FTP – сам QlikView этого сделать бы не смог. На одном из моих проектов это реально пригодилось.

 

IV.               Кейсы

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

Так, для примера:

  • Крупная розничная сеть создала мобильное приложение для сотрудников магазинов, позволяющее мгновенно получить информацию о товаре на полке, просто наведя камеру смартфона на штрихкод. На экран тут же будет выведена информация об остатке товара в данном магазине, дате его последней продажи и уровне сервиса за последний месяц. Эти данные поступают в backend приложения из QlikView, передается примерно 1 млн.строк данных. Что интересно, запросы, сделанные frontend-ом, в backend-е обрабатывает Yandex ClickHouse, open source columnar analytical database, почитайте обязательно!
  • Телеком-компания настроила процесс отлова фактов, требующих внимания, в режиме реального времени на основе CDR (Call Detail Record). Так, в системе мастер-данных заводится набор профилей, в которых описываются количественные пороги (thresholds), при превышении которых за указанный период нужно подавать сигнализацию. Профиль может быть привязан на любом уровне детализации – например, только вендор, или вендор/префикс и т.п. и может включать в себя один или несколько количественных показателей для контроля.
    QlikView каждые несколько минут агрегирует CDR за указанный период (или несколько периодов) и выявляет случаи для сигнализации, после чего передает в ServiceDesk-систему данные для открытия заявки – для какого среза детализации зафиксирован факт, каковы пороги и их превышение и т.п. Дальше заявка маршрутизируется средствами ServiceDesk ответственному инженеру, и он может приступить к выяснению причин и действиям спустя не более чем 10 минут.

P.S. За рамками данной статьи осталось такое направление интеграции QV с другими системами как QlikView API Automation. На этом блоге на такие темы пишут Степан Мотовилов и Илья Голев – рекомендую их статьи к прочтению (ключевое слово для поиска – PowerShell).

На этом по переносу данных из QlikView на сегодня все.

А вот и обещанные ссылки по работе с NPrinting:

До новых встреч!