ДЕНЬ 04
Подготовка и загрузка данных
СЕГОДНЯ
Осваиваем подключение данных в Qlik Sense, останавливаемся на всех этапах процесса, от настройки режимов работы Диспетчера данных до тюнинга свойств в Редакторе загрузки. Кратко обсудим ключевые моменты подготовки данных, что вызывает наибольшие споры в подходах в комьюнити. Практиковаться будем на собственных телефонных транзакциях, что даст возможность подумать о вопросах к своим данных и о гипотезах, которые будем проверять с помощью визуальной аналитики в последующие четыре дня. На книжной полке сегодня тема Алгоритмов, для легкого перехода из гуманитарных тем в язык обработки данных. Удачного дня.
Осваиваем подключение данных в Qlik Sense, останавливаемся на всех этапах процесса, от настройки режимов работы Диспетчера данных до тюнинга свойств в Редакторе загрузки. Кратко обсудим ключевые моменты подготовки данных, что вызывает наибольшие споры в подходах в комьюнити. Практиковаться будем на собственных телефонных транзакциях, что даст возможность подумать о вопросах к своим данных и о гипотезах, которые будем проверять с помощью визуальной аналитики в последующие четыре дня. На книжной полке сегодня тема Алгоритмов, для легкого перехода из гуманитарных тем в язык обработки данных. Удачного дня.
Разбираемся
В процессах работы с данными
Подключение данных в Qlik Sense
Практическая теория
Данные в Qlik Sense Desktop & Cloud
В бесплатной версии Qlik Sense Cloud вам доступна загрузка только файловых источников данных с ограничением по объему в 50 Мб. Основные возможности по подключению разных источников данных от баз данных до облачных сервисов – у Qlik Sense Desktop.
Список доступных источников данных в Qlik Sense Desktop →
В бесплатной версии Qlik Sense Cloud вам доступна загрузка только файловых источников данных с ограничением по объему в 50 Мб. Основные возможности по подключению разных источников данных от баз данных до облачных сервисов – у Qlik Sense Desktop.
Список доступных источников данных в Qlik Sense Desktop →
Процесс подключения данных
У Qlik Sense есть несколько ключевых интерфейсов, которые вам доступны при работе с данными. Это Диспетчер данных, Редактор загрузки данных и Просмотр модели данных.
На базовом уровне, процесс можно описать так:
1. Добавление подключения к источнику и выбор нужных таблиц
2. Выбор полей загружаемой таблицы
3. Предварительный просмотр данных в Диспетчере данных и проставление взаимосвязей между несколькими таблицами
4. Загрузка данных в приложение
Первый шаг – загрузка данных.
У Qlik Sense есть несколько ключевых интерфейсов, которые вам доступны при работе с данными. Это Диспетчер данных, Редактор загрузки данных и Просмотр модели данных.
На базовом уровне, процесс можно описать так:
1. Добавление подключения к источнику и выбор нужных таблиц
2. Выбор полей загружаемой таблицы
3. Предварительный просмотр данных в Диспетчере данных и проставление взаимосвязей между несколькими таблицами
4. Загрузка данных в приложение
Первый шаг – загрузка данных.
Загрузка данных в Qlik Sense Cloud проходит через кнопку "Импортировать данные"
Окно добавления данных в Qlik Sense Desktop
A – Добавление источника
B – Прикрепленные к приложению файлы
С – Подключения к данным
D – DataMarket Qlik
E – Зона подключения данных
F – Список коннекторов
G – Кнопка добавления данных
После загрузки, вы увидите окно подготовки данных: список столбцов с опцией фильтрации и выбора нужных полей.
Шаг 2. Выбор полей и фильтрация
В верхнем левом углу, вы можете переходить на другие вкладки для работы с данными.
Если у вас есть несколько загруженных источников, то диспетчер данных покажет связи между ними.
Объединение двух таблиц в Диспетчере данных по автоматической рекомендации Qlik
У вас есть несколько дополнительных возможностей по более точной настройке источника данных.
Например, из Диспетчера данных вы можете перейти в режим редактирования таблицы и поменять тип загружаемого поля или создать в таблице новое вычисление. Поле может быть общего типа или специального: датой, временем или иметь географические значения. Qlik определяет подходящий тип поля автоматически. Выставление соответствующего типа поля влияет на визуализацию этих данных на диаграммах.
Например, из Диспетчера данных вы можете перейти в режим редактирования таблицы и поменять тип загружаемого поля или создать в таблице новое вычисление. Поле может быть общего типа или специального: датой, временем или иметь географические значения. Qlik определяет подходящий тип поля автоматически. Выставление соответствующего типа поля влияет на визуализацию этих данных на диаграммах.
Также, на экране редактирования свойств, вам доступно превью значений по столбцам таблицы, изменение значений данных, определение пустых значений.
В Редакторе загрузки вы можете более детально настроить скрипт загрузки данных. Скрипт загрузки данных подключает приложение к источнику данных и загружает данные в приложение. При этом, скрипт создается как автоматически, так и доступен вам для настройки вручную. При автоматической загрузке, в скрипте проставляются форматы используемых полей, например, формат времени или первый день недели.
Подробнее про работу с редактором загрузки →
Редактор загрузки также используется для сложных операций по переподготовке данных для анализа в Qlik. Код скрипта пишет продвинутый пользователь. Во второй части лонгрида, мы привели пример загрузки и обработки источника данных в редакторе загрузки.
Вот некоторые из возможностей по моделированию данных через скрипты:
Подробнее про работу с редактором загрузки →
Редактор загрузки также используется для сложных операций по переподготовке данных для анализа в Qlik. Код скрипта пишет продвинутый пользователь. Во второй части лонгрида, мы привели пример загрузки и обработки источника данных в редакторе загрузки.
Вот некоторые из возможностей по моделированию данных через скрипты:
- преобразование столбцов данных в строки
- преобразование строк данных в поля
- загрузка данных, упорядоченных по иерархическим уровням, например, схема организации
- загрузка только новых или обновленных записей из базы данных большого размера
- объединение данных из двух таблиц с общим полем
- сопоставление дискретного значения с интервалом
- обработка несогласованных значений полей
После выполнения всех необходимых действий, вы можете загрузить данные в свое приложение для дальнейшего анализа.
После загрузки данных в приложение вам становится доступен Просмотр модели данных, который показывает взаимосвязи между таблицами и ключевые поля.
После загрузки данных в приложение вам становится доступен Просмотр модели данных, который показывает взаимосвязи между таблицами и ключевые поля.
* Подготовка данных для Qlik (раздел со звездочкой)
Дальнейший блок показывает пример загрузки и обработки данных при помощи скрипта и является продвинутым
Data Preparation – это процесс комбинирования и трансформации данных из различных источников для создания аналитических наборов данных при принятии решений, например:
Необходимость в ETL обусловлена разнообразием источников, в которых хранятся данные. Причем для проведения комплексного анализа зачастую требуется анализировать данные из разных источников совместно. Источники могут сильно отличатся как платформами, так и внутренней архитектурой: структура таблиц, разные справочники, различная детализация данных.
- выбор места открытия нового магазина
- управление бизнес-процессами
- создание аналитики по продажам, используя пользовательские данные
Необходимость в ETL обусловлена разнообразием источников, в которых хранятся данные. Причем для проведения комплексного анализа зачастую требуется анализировать данные из разных источников совместно. Источники могут сильно отличатся как платформами, так и внутренней архитектурой: структура таблиц, разные справочники, различная детализация данных.
Extract. Первым шагом является извлечение данных из системы источника данных. Чтобы это определить, в скрипте используются операторы SELECT или LOAD. Различия между этими операторами заключаются в следующем:
SELECT используется для выбора данных из источника базы данных. Оператор SELECT SQL выполняется поставщиком данных, а не программой QlikSense.
SELECT используется для выбора данных из источника базы данных. Оператор SELECT SQL выполняется поставщиком данных, а не программой QlikSense.
Оператор LOAD используется для загрузки данных из файла, из данных, определенных в скрипте, из ранее загруженной таблицы, с веб-страницы, из результата последующего оператора SELECT или путем автоматической генерации данных.
Transform. Этап преобразования включает в себя работу с данными с помощью функций скрипта программы и правил извлечения необходимой структуры модели данных. Типичные операции:
Load. На последнем этапе скрипт запускается для загрузки определенной модели данных в документ исходя из подготовленных qvd файлов на этапе трансформации. В идеале создавать qvd файлы нужно, т.к. загрузка данных в таком формате проходит в разы быстрее.
Файл QVD (QlikView Data) — это файл, в котором содержится таблица данных, экспортируемых из программы Qlik Sense. Формат файла оптимизирован для скорости при чтении данных из скрипта, но в то же время не занимает много места. Чтение данных из файла QVD обычно в 10–100 раз быстрее, чем чтение из других источников данных.
Transform. Этап преобразования включает в себя работу с данными с помощью функций скрипта программы и правил извлечения необходимой структуры модели данных. Типичные операции:
- Вычисление новых значений. Для этого используют арифметические операции с загруженными полями, сумму, минимум, максимум и т.д.
- Переименование полей
- Соединение таблиц. Например, join (объединение) или concatenate (вертикальное присоединение)
- Агрегирование значений. С использованием функции агрегации group by по вычисляемым полям
Load. На последнем этапе скрипт запускается для загрузки определенной модели данных в документ исходя из подготовленных qvd файлов на этапе трансформации. В идеале создавать qvd файлы нужно, т.к. загрузка данных в таком формате проходит в разы быстрее.
Файл QVD (QlikView Data) — это файл, в котором содержится таблица данных, экспортируемых из программы Qlik Sense. Формат файла оптимизирован для скорости при чтении данных из скрипта, но в то же время не занимает много места. Чтение данных из файла QVD обычно в 10–100 раз быстрее, чем чтение из других источников данных.
СОВЕТ
В QlikSense нельзя несколько внутренних таблиц записать в один QVD. Также нельзя аддитивно накапливать данные в один файл (т.е. каждый раз STORE table INTO table.qvd перезаписывает файл table.qvd). Но при высокой скорости данное замечание некритично
Лучший вид данных для загрузки данных – нормализованный вид. Преобразование ненормализованных данных в нормализованные можно сделать и на уровне ETL, но лучше изначально хранить данные на источнике в таком виде. Это сократит время на подготовку данных для анализа.
Давайте рассмотрим пример приведения данных к нормальной форме средствами ETL на примере ненормализованной таблицы выше.
Загрузка данных
Сначала загрузим данные в проект. Префикс crosstable используется для преобразования перекрестной таблицы в прямую, т. е. широкая таблица со множеством столбцов преобразуется в длинную таблицу с размещением заголовков столбцов в одном столбце атрибутов (Pivot).
Extract_sale_tmp:
CrossTable(Период, Продажи_руб)
LOAD
"ID товара",
"сентябрь 18",
"октябрь 18",
"ноябрь 18",
"декабрь 18",
"январь 19"
FROM [lib://QS7/qfs_KPI_Dashboard_Сборы - Copy\марафон подготовка данных.xlsx]
(ooxml, embedded labels, table is Data);
Промежуточный результат данных после первого шага
Настройка значений
Следующим этапом мы заменим текстовые значения периода на дату. Для начала создадим mapping таблицу. Префикс mapping используется для создания таблицы сопоставления, которую можно использовать, например, для замены значений полей и имен полей в ходе выполнения скрипта.
После этого создадим новую таблицу из уже загруженной и применим функцию ApplyMap для замены даты. Функция скрипта ApplyMap используется для сопоставления результата выражения с ранее загруженной таблицей сопоставления. Функция скрипта Resident позволяет загружать данные из ранее загруженных таблиц в проект.
Временную таблицу следует удалить, т.к. все необходимые преобразования мы уже провели. Для этого используем функцию Drop table.
После этого создадим новую таблицу из уже загруженной и применим функцию ApplyMap для замены даты. Функция скрипта ApplyMap используется для сопоставления результата выражения с ранее загруженной таблицей сопоставления. Функция скрипта Resident позволяет загружать данные из ранее загруженных таблиц в проект.
Временную таблицу следует удалить, т.к. все необходимые преобразования мы уже провели. Для этого используем функцию Drop table.
Calendar_Map:
Mapping LOAD * Inline
[
Text_Clndr, Date_Clndr
'сентябрь 18', 01.09.2018
'октябрь 18', 01.10.2018
'ноябрь 18', 01.11.2018
'декабрь 18', 01.12.2018
'январь 19', 01.01.2019
];
Extract_sale:
LOAD
"ID товара" as ID_продукта,
date(ApplyMap('Calendar_Map', Период),'DD.MM.YYYY') as Дата,
Продажи_руб
Resident Extract_sale_tmp;
Drop table Extract_sale_tmp;
Промежуточный результат в нормальной форме после второго шага
Далее давайте обогатим полученные данные информацией о товаре из таблицы ниже. Между этими двумя таблицами есть общее поле – ID товара – по которому мы можем сделать объединение для получения всех данных по товару. После обогащения таблицы продаж, мы сможем посчитать себестоимость всех товаров, наценку, какой сыр продавался больше на протяжении всего периода и так далее.
Таблица для присоединения
Джойн таблиц
Для того, чтобы обогатить наши данные, необходимо использовать функцию left join.
Если этот префикс используется перед join, то он указывает, что необходимо выполнить левое объединение. Результирующая таблица будет содержать только комбинации значений полей из таблиц исходных данных с представлением связанных значений полей в первой таблице. Если простыми словами, то к нашей таблице подтянется только та информация, которая есть в справочнике, оставив полный набор данных в таблице Extract_sale.
Если этот префикс используется перед join, то он указывает, что необходимо выполнить левое объединение. Результирующая таблица будет содержать только комбинации значений полей из таблиц исходных данных с представлением связанных значений полей в первой таблице. Если простыми словами, то к нашей таблице подтянется только та информация, которая есть в справочнике, оставив полный набор данных в таблице Extract_sale.
left join(Extract_sale)
LOAD
"ID товара" as ID_продукта,
Наименование,
"Страна производитель",
"Вес гр.",
"Себестоимость руб",
"Цена продажи"
FROM [lib://QS7/qfs_KPI_Dashboard_Сборы - Copy\марафон подготовка данных.xlsx]
(ooxml, embedded labels, table is Справочник);
Итоговая таблица
Сохранение QVD файла
Нам осталось сохранить таблицу в QVD файл для дальнейшей загрузки в проект и удалить все таблицы, чтобы приложение ETL не хранило в себе данные. Mapping таблицу удалять не нужно, т.к. она существует только во время загрузки скрипта.
Store Extract_sale into C:\Windows\tracing\Extract_sale.qvd (qvd);
Drop tables Extract_sale;Готово! Таблицы готовы для дальнейшего анализа в Qlik Sense.
Практикуемся
В решении кейсов
Мы будем выкладывать решения на практические задачи предыдущего дня. Поэтому ниже вы можете посмотреть решение задач дня 03 (по фильмам и смертям в кинолентах). Кликните на изображения, чтобы увеличить их.
 |  |  |  |
О сегодняшней практике
Сегодня мы попробуем сформировать свой собственный, уникальный источник данных – каждый выгрузит свою статистику звонков у мобильного оператора, сформирует CSV или Excel файл, который затем загрузит в Qlik Sense.
У каждого из операторов своя методология выгрузки детализации счета. У разных операторов доступна разная глубина бесплатной выгрузки, где-то – всего месяц, где-то – полгода. Нам не важен как таковой объем данных, важна структура и само наличие звонков или транзакций на оплату смс, интернета, роуминга.
У каждого из операторов своя методология выгрузки детализации счета. У разных операторов доступна разная глубина бесплатной выгрузки, где-то – всего месяц, где-то – полгода. Нам не важен как таковой объем данных, важна структура и само наличие звонков или транзакций на оплату смс, интернета, роуминга.
Пример выгрузки детализации Билайн
Чтобы запросить выгрузку детализации у Билайн нужно сделать следующее:
Чтобы запросить выгрузку детализации у Билайн нужно сделать следующее:
- Перейти на сайт https://beeline.ru и зайти в личный кабинет по своему номеру
- Слева в меню выбрать Детализация
- Выбрать период отчета (максимальный период к выгрузке – 1 месяц, а глубина доступности данных – 7 месяцев, поэтому если необходимо выгрузить более 1 месяца, то приходится делать несколько выгрузок)
- Нажать на кнопку Сохранить отчёт, выбрать интересующий нас формат – нам нужен XLS или XLSX – не забыть поставить галочку на поле Отправить на адрес эл. почты и нажать Сохранить детализацию
- Открыть документ Excel, где-то с 60 строки начнётся сама детализация, под заголовками Детализация услуг связи и Основной баланс, поэтому все что перед самой таблицей можно смело удалять
Задания
1. Перейдите на сайт своего оператора, зайдите в личный кабинет. Где-то в глубинах ЛК, есть ссылка на выгрузку детализации по счету. Ниже мы подобрали ссылки на основных операторов РФ с инструкцией по выгрузкам:
2. Выгрузите детализацию в табличной формате (xls, xlsx, csv)
3. Зайдите в интерфейс Qlik и создайте новое приложение.
4. Загрузите файл как новый источник в Qlik Sense. Изучите доступные вам меню интерфейса работы с данными.
Вопросы со звездочкой.
5. *Какая итоговая сумма потраченных денег за ваш период?
6. *Топ-3 любимых номеров по количеству звонков?
7. *Сколько Гб интернета израсходовалось на музыку, соц.сети и сёрфинг?
2. Выгрузите детализацию в табличной формате (xls, xlsx, csv)
3. Зайдите в интерфейс Qlik и создайте новое приложение.
4. Загрузите файл как новый источник в Qlik Sense. Изучите доступные вам меню интерфейса работы с данными.
Вопросы со звездочкой.
5. *Какая итоговая сумма потраченных денег за ваш период?
6. *Топ-3 любимых номеров по количеству звонков?
7. *Сколько Гб интернета израсходовалось на музыку, соц.сети и сёрфинг?
Задание со звездочкой
Рассмотрим данные по продажам товаров, содержащиеся в Excel-файле на листе "Исходные данные"
Скачать источник данных
Скачать источник данных
* Задание со звездочкой
1. Загрузите исходные данные в проект используя префикс crosstable
2. Создайте mapping таблицу для замены значения периода на корректный формат даты
3. Замените текстовые значения периода на корректный формат даты используя функцию applymap и созданную mapping таблицу
4. Обогатите данные справочной информацией из листа "Справочник"
5. Проверьте полученные данные и сохраните результирующую таблицу в qvd файл
2. Создайте mapping таблицу для замены значения периода на корректный формат даты
3. Замените текстовые значения периода на корректный формат даты используя функцию applymap и созданную mapping таблицу
4. Обогатите данные справочной информацией из листа "Справочник"
5. Проверьте полученные данные и сохраните результирующую таблицу в qvd файл
Вдохновляемся
Изучаем прекрасное в сети
Полезные ссылки по теме
Сегодня в подборке поиск и устранение неисправностей в загрузке данных, плюс пару видео на инструкции по преобразованию данных для загрузки и как джойнить таблицы.
Погружаемся
В тематические книжки и видео
Артефакты
Помогут Вам лучше усвоить и вовремя вспомнить основные элементы методик, подходов, последовательностей действий, проверенных практик
Книги
Все материалы предназначены только для персонального ознакомительного использования в личных образовательных целях
Итоги этапа
Ну вот мы и ознакомились с возможными формами загрузки данных, рассмотрели в деталях процесс импорта датасетов и потренировались на своих телефонных транзакциях. Готовность максимальная для разбора видов визуализаций, чтобы находить инсайты и проверять гипотезы. Записаны вопросы для всемирного чата комьюнити, чтобы коллегам было о чем подумать. Всем хорошего дня. Виз виз ураааа!
~
