Бизнес-аналитика. Извлечение, преобразование и загрузка данных. Учебное пособие

© Валентин Юльевич Арьков, 2020

ISBN 978-5-4498-4086-8

Создано в интеллектуальной издательской системе Ridero

Введение

Системы бизнес-аналитики работают с различными источниками данных с помощью функций ETL (Extract-Transform-Load). Название ETL можно перевести как «извлечение, преобразование и загрузка данных». Имеется в виду загрузка в хранилище данных для дальнейшей обработки в системе бизнес-аналитики [1—5]. В простейшем случае это загрузка данных в виде одной, объединённой таблицы Excel.

Задание. Прочитайте в Википедии статью «ETL» и выясните, какие действия над данными производят на каждом из трёх этапов.

Отчёт оформляем в виде рабочей книги Excel – как и в предыдущих работах. Процедура оформления отчёта подробно описана в работе [6].

Не забывайте записывать в отчёт ответы на задания типа «выясните…». Когда вы что-то узнали, зафиксируйте это в своём произведении. Упражнения на изучение – это часть данной лабораторной работы.

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

Задание. Создайте файл отчёта. Оформите титульный лист и оглавление. Сохраните файл под коротким информативным названием.

В качестве исходных данных мы будем использовать сведения об акциях. Это так называемые ЦЕННЫЕ БУМАГИ. Акцию конкретной компании иногда называют просто «бумага». Акции приобретают с разными целями. Торговля акциями, в основном, происходит на бирже.

Задание. Прочитайте в Википедии статью «Акция (финансы)» и выясните, какие права она даёт владельцу, а также какие два вида акций существуют.

В оформлении обложки использованы изображения, разработанные компанией OnlineWebFonts.

1. Инструменты ETL в Microsoft Excel

В пакете Microsoft Excel версий 2010 и 2013 функции ETL реализованы с помощью надстройки Power Query.

Power Query позволяет решать следующие задачи:

– извлечение – импорт данных из различных источников;

– преобразование – приведение данных к единому формату;

– загрузка – консолидация / объединение данных из нескольких источников в виде одной таблицы Excel.

Название Power Query можно расшифровать как «Средство из комплекта PowerBI для выполнения запросов к источникам данных». В свою очередь, PowerBI – это программный комплекс (или платформа) бизнес-аналитики (BI – Business Intelligence), включающий целый ряд отдельных компонентов.

Задание. Прочитайте в Википедии статью «Power BI» и выясните, какие компоненты входят в настольную (локальную) версию Power BI Desktop и какова стоимость этой версии.

Платформа Power BI доступна в разных вариантах. Есть версии программы, которые можно скачать и установить бесплатно. Естественно, с рядом ограничений. Но это уже позволяет познакомиться с ключевыми возможностями продукта.

Задание. Перейдите на следующий сайт:

https://powerbi.microsoft.com.

Выясните, какие версии PowerBI доступны для скачивания.

Для установки Power Query потребуется сказать эту надстройку. Заходим на страницу Центра загрузки Microsoft:

https://download.microsoft.com

Вызываем поиск:

Power Query Excel.

Получаем ссылки для скачивания (рис. 1.1).

Рис. 1.1. Power Query в Центре загрузки

Задание. Найдите страницу Power Query в Центре загрузки и выясните год выпуска этой надстройки и поддерживаемые версии Excel.

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

Get & Transform Data.

Выясняем, как всё это связано с надстройкой Power Query и пакетом PowerBI (рис. 1.2).

Рис. 1.2. Инструменты загрузки данных

Задание. Выясните, как связан инструментарий Get & Transform с Power BI и Power Query.

В пакете Excel версий 2016, 2019 и 365 функции ETL доступны в группе инструментов Get & Transform (рис. 1.3).

Рис. 1.3. Группа инструментов ETL

Задание. Найдите группу Get & Transform в разделе Data и изучите названия основных кнопок на ленте, а также всплывающую подсказку для кнопки Get Data.

В группе Get & Transform скрывается кнопка запуска редактора запросов Power Query Editor:

Data – Get & Transform – Get Data – Launch Power Query Editor.

Всплывающая подсказка поясняет, что Power Query позволяет нам создавать новые запросы и редактировать существующие (рис. 1.4). Значит, Power Query по-прежнему жив и здоров.

Рис. 1.4. Запуск редактора запросов

Задание. Прочитайте статью Get & Transform and Power Pivot in Excel (Получение и преобразование и Power Pivot в Excel) в Центре справки и поддержки Microsoft Office:

https://support.office.com

Обратите внимание на следующие разделы:

– What is Get & Transform?

– What is Power Pivot?

– What is Power BI?

Кратко запишите в отчёт, что удалось выяснить про три варианта инструмента ETL.

2. Загрузка таблицы из Википедии

Один из стандартных источников данных —это интернет. Из интернет-страницы можно извлечь и загрузить выбранную таблицу.

Перейдём на сайт Википедии в браузере и найдём статью «Power BI» (рис. 2.1). В тексте статьи есть таблица с описанием лицензий. Попробуем загрузить эту таблицу в Excel. Скопируем адрес выбранной страницы в буфер обмена.

Рис. 2.1. Страница сайта в интернете

Задание. Найдите таблицу по лицензиям в статье «Power BI».

Возвращаемся в Excel.

Выбираем в верхнем меню загрузку данных из интернета (рис. 2.2):

Data – Get & Transform Data – From Web.

Рис.2.2. Получение данных из интернета

В диалоговом окне From Web вставляем адрес интернет-страницы из буфера обмена (рис. 2.3). По умолчанию выбран вариант загрузки Basic. Нажимаем кнопку OK.

Рис. 2.3. Ввод адреса страницы

Задание. Вызовите загрузку данных со страницы статьи «Power BI» в Википедии.

В диалоговом окне Navigator можно увидеть список из нескольких таблиц, которые были найдены на указанной странице сайта. Выбираем таблицу по лицензированию. Рассматриваем нашу таблицу в окне предварительного просмотра

Table View.

Убеждаемся, что выбрали нужную таблицу (рис. 2.4). Нажимаем кнопку

Load.

Рис. 2.4. Выбор таблицы для загрузки

Задание. Выберите для загрузки таблицу по лицензиям.

Таблица загружается в Excel (рис. 2.5). Сравниваем с источником (рис. 2.1). Заголовки столбцов и содержимое ячеек распознаны и загружены без ошибок. Таблица оформлена как таблица Excel.

Переходим в группу Table Tools в верхнем меню ленты. Выбираем раздел Design. Проверяем название таблицы:

Table Name – Лицензирование…

Значит мы загрузили не только саму таблицу, но и её заголовок (название).

Рис. 2.5. Загруженная таблица

Задание. Загрузите в Excel таблицу «Лицензирование» из статьи «Power BI» на сайте Википедии и ознакомьтесь с результатами.

Рассмотрим поподробнее название таблицы и сравним его с заголовком раздела в источнике данных (рис. 2.1).

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

Кроме сведений о таблице можно увидеть запрос на загрузку данных:

Queries & Connections – Лицензирование…

Пока в этом разделе у нас есть один запрос (рис. 2.6). Название запроса тоже соответствует заголовку раздела в источнике данных (рис. 2.1).

Рис. 2.6. Запрос для загрузки

Задание. Сравните название запроса и заголовок раздела статьи.

Исправим название таблицы.

Вызываем инструмент Диспетчер имён:

Formulas – Defined Names – Name Manager (рис. 2.7).

Рис. 2.7. Запуск Диспетчера имён объектов

Задание. Запустите Диспетчер имён.

Появляется диалоговое окно Диспетчера имён:

Name Manager (рис. 2.8).

Рассмотрим сведения о нашем объекте.

В графе Name (Название) указано:

Лицензирование_править___править_код.

В графе Value (Значения) можно видеть содержимое ячеек таблицы по строкам.

В графе Refers To указан лист рабочей книги Excel и диапазон ячеек, в который была загружена наша таблица.

Рис. 2.8. Сведения о названии таблицы

Задание. Изучите сведения об объекте «Лицензирование…».

Нажимаем кнопку Edit.

В окне редактирования Edit Name исправляем название и оставляем слово «Лицензирование». Добавляем комментарий (рис. 2.9).

Рис. 2.9. Редактирование названия объекта

Задание. Исправьте название таблицы.

Вернёмся к списку запросов (рис. 2.6). Для этого укажем на таблицу и выберем в верхнем меню

Data – Queries & Connections – Queries & Connections.

Видим, что название запроса не изменилось.

Подводим курсор к запросу и рассматриваем вплывающее окно с подробным описанием запроса (рис. 2.10).

В верхней части окна можно видеть название запроса.

Ниже видим саму таблицу.

Далее идёт список столбцов

Columns.

Здесь же указано количество столбцов:

(3).

Кроме того, нам сообщают время, когда было сделано последнее обновление данных из источника Last refreshed.

В нижней части окна указан источник данных

Data Sources.

Это ссылка на страницу в интернете.

Нам также сообщают, что мы использовали всего один источник данных

(1).

Рис. 2.10. Сведения о запросе

Задание. Изучите сведения о запросе.

Запустим редактор запросов.

Нажимаем на наш запрос правой кнопкой мыши и выбираем в контекстном меню

Edit (рис. 2.11).

Рис. 2.11. Вызов редактора запросов

Задание. Запустите редактирование своего запроса.

Появляется окно Power Query Editor (рис. 2.12).

Как видим, редактор запросов по-прежнему называется Power Query.

Исправим название запроса и оставим только слово «Лицензирование».

Закрываем окно редактора:

Home – Close – Close & Load.

Рис. 2.12. Редактирование названия запроса

Задание. Измените название запроса.

Обратим внимание на сведения о запросе (рис. 2.13).

Название запроса изменилось. Мы убрали лишние слова и знаки. В дальнейшей работе будет гораздо проще работать с простыми и понятными названиями.

Рис. 2.13. Запрос с новым названием

Задание. Убедитесь, что название запроса изменилось.

Мы загрузили данные из Википедии. И мы знаем, про какие лицензии идёт речь. Но через пару дней или недель всё забудется. А в нашем файле будет ещё несколько десятков разных запросов.

Так что лучше бы нам сразу дать информативное название.

Например, сказать, что это сведения о вариантах лицензий на программные средства из линейки Power BI и что мы взяли эти данные в статье на сайте Википедии. Объяснение получается длинное. Попробуем оставить буквально несколько слов – то, что уже нельзя сократить без потери информации.

Теперь начинает проясняться смысл высказывания «Краткость – сестра таланта». Надо по-настоящему понять материал, чтобы кратко что-то сообщить, но при этом передать смысл, идею. Интересно, кто-нибудь вспомнит автора этой фразы?

Задание. Сделайте название запроса коротким и информативным.

3. Загрузка таблицы дивидендов

Загрузим ещё одну табличку из интернета. На этот раз нас будет интересовать таблица размеров дивидендов на сайте компании «Газпром».

Открываем в браузере страницу сайта компании ПАО «Газпром»:

https://www.gazprom.ru/

Переходим в следующий раздел:

Акционерам и инвесторам – Дивиденды – История дивидендных выплат (рис. 3.1).

Рис. 3.1. Таблица дивидендов

Задание. Откройте страницу дивидендов на сайте ПАО «Газпром».

Скопируем адрес страницы в буфер обмена.

Вызываем функцию загрузки данных из интернета:

Data – Get & Transform – From Web.

Указываем анонимный доступ к сайту без ввода пароля (рис. 3.2).

Нажимаем кнопку:

Connect.

Рис. 3.2. Анонимный доступ

Задание. Выберите анонимный доступ к сайту.

Появляется диалоговое окно

Navigator.

Выбираем таблицу дивидендов (рис. 3.3).

Нажимаем кнопку

Load.

Рис. 3.3. Выбор таблицы для загрузки

Задание. Загрузите таблицу дивидендов Газпрома.

Рассмотрим загруженную таблицу (рис. 3.4). Нам предстоит кое-что исправить. Причём почти всё.

На этот раз название таблицы и соответствующего запроса получилось не очень информативное:

Table_0.

Заголовки столбцов после загрузки были продублированы.

Числовые значения распознаны как текстовые строки. Содержимое ячеек прижато влево – это текстовый формат по умолчанию.

Английская версия Excel ожидает точку как десятичный разделитель целой и дробной частей. А русская страница сайта использует для этой цели запятую.

Мало того, пробел разделяет группы по три разряда – тысячи, миллионы, миллиарды.

Рис. 3.4. Загруженная таблица дивидендов

Задание. Изучите результаты загрузки и перечислите в отчёте все необходимые преобразования.

Переходим к преобразованиям.

Открываем редактор запросов (рис. 3.4):

Data – Get & Transform – Get Data – Launch Power Query Editor.

Рис. 3.4. Запуск редактора запросов

Задание. Запустите редактор запросов.

Появляется окно редактора запросов (рис. 3.5).

Видим название запроса:

Table_0.

В заголовках всех колонок указано, что тип данных – текст:

ABC

В верхнем меню тоже говорится, что здесь данные текстового типа:

Home – Transform – Data Type – Text.

Рис. 3.5. Редактор запросов

Задание. Обратите внимание на название запроса и тип данных.

Начнём исправления загруженной таблицы.

Зададим новое название запроса:

Дивиденды Газпрома.

Изменим тип данных для первого столбца:

Home – Transform – Data Type – Decimal Number.

Рис. 3.6. Изменение типа данных

Задание. Измените тип данных.

Появляется диалоговое окно

Change Column Type.

Нам предлагают определиться, как именно делать преобразование. Если добавить новый этап работы с данными, изменения можно будет откатить при желании.

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

Replace current.

Рис. 3.7. Параметры преобразования типа

Задание. Выберите замену преобразования.

Рассмотрим результаты преобразования типа данных (рис. 3.8).

Тип данных для первого столбца теперь установлен ка числовой:

Data Type – Decimal Number.

В заголовке столбца тоже указано, что здесь находятся числа:

1.2.

Сами числа в первой колонке теперь прижаты вправо. Это формат числа по умолчанию.

В разделе Применённые шаги указано преобразование типа:

Query Settings – Applied Steps – Changed Type.

Рис. 3.8. Результаты преобразования типа

Задание. Рассмотрите результаты преобразования типа данных в первом столбце.

Первая строка, которая дублирует заголовок, содержит текст. Поэтому преобразование завершилось ошибкой, о чём нам и сообщают:

Error.

Щёлкнем по этой ячейке и получим более подробное пояснение (рис. 3.9).

В разделе Применённые шаги появляется новая строка:

Год.

Щёлкаем по красному крестику и возвращаемся к предыдущему шагу.

Рис. 3.9. Сообщение об ошибке

Задание. Изучите сообщение об ошибке формата данных.

Удалим первую строку таблицы, в которой появилась ошибка преобразования типа.

Выбираем в верхнем меню

Home – Reduce Rows – Remove Rows – Remove Errors.

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

Рис. 3.10. Удаление строк с ошибками

После удаления строк с ошибками получаем более красивую таблицу (рис. 3.11).

Кроме в разделе Применённые шаги добавилась строка

Removed Errors.

Обратим внимание на количество строк в таблице до и после удаления ошибок. Для этого мы можем откатить последний шаг, нажав красный крестик в разделе Применённые шаги. Потом снова вызываем удаление строк с ошибками.

Рис. 3.11. Таблица после удаления ошибок

Задание. Удалите строки с ошибками и обратите внимание на количество строк.

Переходим ко второму столбцу – величине дивидендов.

Зададим числовой тип данных.

Содержимое действительно стало числовым (рис. 3.12).

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

Рис. 3.12. Результаты преобразования второго столбца

Задание. Задайте целый тип данных для второго столбца и обратите внимание на изменения.

Попробуем заменить запятые на точки.

Находим инструмент поиска и замены в меню (рис. 3.13):

Home – Transform – Replace Values

Рис. 3.13. Функция замены значений

Задание. Прочитайте всплывающую подсказку для кнопки Замена значений.

Нажимаем кнопку Замена значений:

Replace Values.

Заменим запятые на точки (рис. 3.14).

Рис. 3.14. Поиск и замена

Задание. Замените запятые на точки.

Рассмотрим результаты поиска и замены (рис. 3.15).

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

При этом тип данных по-прежнему текстовый.

Рис. 3.15. Результаты поиска и замены

Задание. Замените запятые на точки во втором столбце.

Изменим тип данных во втором столбце с текстового на числовой:

Decimal Number.

Обращаем внимание на изменение форматирования и сообщение о типе в заголовке столбца.

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

Теперь в этой ячейке выводится сообщение об ошибке (рис. 3.16).

Рис. 3.16. Преобразование типа второго столбца

Задание. Измените тип данных на числовой и обратите внимание на сообщение об ошибке.

Отменяем последний шаг преобразований.

Заменяем прочерк, то есть символ тире, на ноль.

Щёлкаем по ячейке с неправильным содержимым и копируем её в буфер обмена. Затем вызываем функцию поиска и замены. В строке поиска будет загадочное тире. В строке замены – ничего.

Снова изменяем тип данных на числовой.

Теперь всё прошло успешно (рис. 3.17).

В ячейке за 1998 год стоит ноль.

Ошибок нет.

Все выполненные шаги по преобразованию данных выводятся в списке

APPLIED STEPS

Рис. 3.17. Второй столбец преобразован

Задание. Завершите преобразование второго столбца и убедитесь в отсутствии ошибок.

Здесь мы столкнулись с длинным тире. Это не просто чёрточка. Неплохо было бы с этими чёрточками познакомиться. А ещё есть символ минуса. Это тоже какая-то чёрточка. Или это одно и то же?