Правообладателям!
Представленный фрагмент книги размещен по согласованию с распространителем легального контента ООО "ЛитРес" (не более 20% исходного текста). Если вы считаете, что размещение материала нарушает ваши или чьи-либо права, то сообщите нам об этом.Читателям!
Оплатили, но не знаете что делать дальше?![](/books_files/covers/thumbs_150/biznes-analitika-izvlechenie-preobrazovanie-izagruzka-dannyh-uchebnoe-posobie-14577.jpg)
Текст бизнес-книги "Бизнес-аналитика. Извлечение, преобразование и загрузка данных. Учебное пособие"
Автор книги: Валентин Арьков
Раздел: О бизнесе популярно, Бизнес-книги
Возрастные ограничения: +12
Текущая страница: 1 (всего у книги 1 страниц)
Бизнес-аналитика. Извлечение, преобразование и загрузка данных
Учебное пособие
Валентин Юльевич Арьков
© Валентин Юльевич Арьков, 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).
![](image0_5e6761b4378e983c94b8c0b8_jpg.jpeg)
Рис. 1.1. Power Query в Центре загрузки
Задание. Найдите страницу Power Query в Центре загрузки и выясните год выпуска этой надстройки и поддерживаемые версии Excel.
Кроме ссылок для скачивания файлов, здесь имеются пояснения по поводу инструментов загрузки данных. Переходим по следующей ссылке:
Get & Transform Data.
Выясняем, как всё это связано с надстройкой Power Query и пакетом PowerBI (рис. 1.2).
![](image1_5e676277378e983c94b8c1ba_jpg.jpeg)
Рис. 1.2. Инструменты загрузки данных
Задание. Выясните, как связан инструментарий Get & Transform с Power BI и Power Query.
В пакете Excel версий 2016, 2019 и 365 функции ETL доступны в группе инструментов Get & Transform (рис. 1.3).
![](image2_5e676275378e983c94b8c1b7_jpg.jpeg)
Рис. 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 по-прежнему жив и здоров.
![](image3_5e676272378e983c94b8c1b4_jpg.jpeg)
Рис. 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. Скопируем адрес выбранной страницы в буфер обмена.
![](image4_5e676270378e983c94b8c1b1_jpg.jpeg)
Рис. 2.1. Страница сайта в интернете
Задание. Найдите таблицу по лицензиям в статье «Power BI».
Возвращаемся в Excel.
Выбираем в верхнем меню загрузку данных из интернета (рис. 2.2):
Data – Get & Transform Data – From Web.
![](image5_5e67626e378e983c94b8c1ae_jpg.jpeg)
Рис.2.2. Получение данных из интернета
В диалоговом окне From Web вставляем адрес интернет-страницы из буфера обмена (рис. 2.3). По умолчанию выбран вариант загрузки Basic. Нажимаем кнопку OK.
![](image6_5e67626c378e983c94b8c1ab_jpg.jpeg)
Рис. 2.3. Ввод адреса страницы
Задание. Вызовите загрузку данных со страницы статьи «Power BI» в Википедии.
В диалоговом окне Navigator можно увидеть список из нескольких таблиц, которые были найдены на указанной странице сайта. Выбираем таблицу по лицензированию. Рассматриваем нашу таблицу в окне предварительного просмотра
Table View.
Убеждаемся, что выбрали нужную таблицу (рис. 2.4). Нажимаем кнопку
Load.
![](image7_5e67626a378e983c94b8c1a8_jpg.jpeg)
Рис. 2.4. Выбор таблицы для загрузки
Задание. Выберите для загрузки таблицу по лицензиям.
Таблица загружается в Excel (рис. 2.5). Сравниваем с источником (рис. 2.1). Заголовки столбцов и содержимое ячеек распознаны и загружены без ошибок. Таблица оформлена как таблица Excel.
Переходим в группу Table Tools в верхнем меню ленты. Выбираем раздел Design. Проверяем название таблицы:
Table Name – Лицензирование…
Значит мы загрузили не только саму таблицу, но и её заголовок (название).
![](image8_5e676268378e983c94b8c1a5_jpg.jpeg)
Рис. 2.5. Загруженная таблица
Задание. Загрузите в Excel таблицу «Лицензирование» из статьи «Power BI» на сайте Википедии и ознакомьтесь с результатами.
Рассмотрим поподробнее название таблицы и сравним его с заголовком раздела в источнике данных (рис. 2.1).
На исходной странице были ссылки на инструменты для редактирования статьи. Напомним, что Википедию создают сами пользователи. Поэтому каждый может предложить свои исправления и дополнения. В результате название таблицы содержит все эти лишние для нас слова.
Кроме сведений о таблице можно увидеть запрос на загрузку данных:
Queries & Connections – Лицензирование…
Пока в этом разделе у нас есть один запрос (рис. 2.6). Название запроса тоже соответствует заголовку раздела в источнике данных (рис. 2.1).
![](image9_5e676265378e983c94b8c1a2_jpg.jpeg)
Рис. 2.6. Запрос для загрузки
Задание. Сравните название запроса и заголовок раздела статьи.
Исправим название таблицы.
Вызываем инструмент Диспетчер имён:
Formulas – Defined Names – Name Manager (рис. 2.7).
![](image10_5e676263378e983c94b8c19f_jpg.jpeg)
Рис. 2.7. Запуск Диспетчера имён объектов
Задание. Запустите Диспетчер имён.
Появляется диалоговое окно Диспетчера имён:
Name Manager (рис. 2.8).
Рассмотрим сведения о нашем объекте.
В графе Name (Название) указано:
Лицензирование_править___править_код.
В графе Value (Значения) можно видеть содержимое ячеек таблицы по строкам.
В графе Refers To указан лист рабочей книги Excel и диапазон ячеек, в который была загружена наша таблица.
![](image11_5e676261378e983c94b8c19c_jpg.jpeg)
Рис. 2.8. Сведения о названии таблицы
Задание. Изучите сведения об объекте «Лицензирование…».
Нажимаем кнопку Edit.
В окне редактирования Edit Name исправляем название и оставляем слово «Лицензирование». Добавляем комментарий (рис. 2.9).
![](image12_5e67625f378e983c94b8c199_jpg.jpeg)
Рис. 2.9. Редактирование названия объекта
Задание. Исправьте название таблицы.
Вернёмся к списку запросов (рис. 2.6). Для этого укажем на таблицу и выберем в верхнем меню
Data – Queries & Connections – Queries & Connections.
Видим, что название запроса не изменилось.
Подводим курсор к запросу и рассматриваем вплывающее окно с подробным описанием запроса (рис. 2.10).
В верхней части окна можно видеть название запроса.
Ниже видим саму таблицу.
Далее идёт список столбцов
Columns.
Здесь же указано количество столбцов:
(3).
Кроме того, нам сообщают время, когда было сделано последнее обновление данных из источника Last refreshed.
В нижней части окна указан источник данных
Data Sources.
Это ссылка на страницу в интернете.
Нам также сообщают, что мы использовали всего один источник данных
(1).
![](image13_5e67625c378e983c94b8c196_jpg.jpeg)
Рис. 2.10. Сведения о запросе
Задание. Изучите сведения о запросе.
Запустим редактор запросов.
Нажимаем на наш запрос правой кнопкой мыши и выбираем в контекстном меню
Edit (рис. 2.11).
![](image14_5e676258378e983c94b8c193_jpg.jpeg)
Рис. 2.11. Вызов редактора запросов
Задание. Запустите редактирование своего запроса.
Появляется окно Power Query Editor (рис. 2.12).
Как видим, редактор запросов по-прежнему называется Power Query.
Исправим название запроса и оставим только слово «Лицензирование».
Закрываем окно редактора:
Home – Close – Close & Load.
![](image15_5e676256378e983c94b8c190_jpg.jpeg)
Рис. 2.12. Редактирование названия запроса
Задание. Измените название запроса.
Обратим внимание на сведения о запросе (рис. 2.13).
Название запроса изменилось. Мы убрали лишние слова и знаки. В дальнейшей работе будет гораздо проще работать с простыми и понятными названиями.
![](image16_5e676253378e983c94b8c18d_jpg.jpeg)
Рис. 2.13. Запрос с новым названием
Задание. Убедитесь, что название запроса изменилось.
Мы загрузили данные из Википедии. И мы знаем, про какие лицензии идёт речь. Но через пару дней или недель всё забудется. А в нашем файле будет ещё несколько десятков разных запросов.
Так что лучше бы нам сразу дать информативное название.
Например, сказать, что это сведения о вариантах лицензий на программные средства из линейки Power BI и что мы взяли эти данные в статье на сайте Википедии. Объяснение получается длинное. Попробуем оставить буквально несколько слов – то, что уже нельзя сократить без потери информации.
Теперь начинает проясняться смысл высказывания «Краткость – сестра таланта». Надо по-настоящему понять материал, чтобы кратко что-то сообщить, но при этом передать смысл, идею. Интересно, кто-нибудь вспомнит автора этой фразы?
Задание. Сделайте название запроса коротким и информативным.
3. Загрузка таблицы дивидендов
Загрузим ещё одну табличку из интернета. На этот раз нас будет интересовать таблица размеров дивидендов на сайте компании «Газпром».
Открываем в браузере страницу сайта компании ПАО «Газпром»:
https://www.gazprom.ru/
Переходим в следующий раздел:
Акционерам и инвесторам – Дивиденды – История дивидендных выплат (рис. 3.1).
![](image17_5e676251378e983c94b8c18a_jpg.jpeg)
Рис. 3.1. Таблица дивидендов
Задание. Откройте страницу дивидендов на сайте ПАО «Газпром».
Скопируем адрес страницы в буфер обмена.
Вызываем функцию загрузки данных из интернета:
Data – Get & Transform – From Web.
Указываем анонимный доступ к сайту без ввода пароля (рис. 3.2).
Нажимаем кнопку:
Connect.
![](image18_5e67624f378e983c94b8c187_jpg.jpeg)
Рис. 3.2. Анонимный доступ
Задание. Выберите анонимный доступ к сайту.
Появляется диалоговое окно
Navigator.
Выбираем таблицу дивидендов (рис. 3.3).
Нажимаем кнопку
Load.
![](image19_5e67624d378e983c94b8c184_jpg.jpeg)
Рис. 3.3. Выбор таблицы для загрузки
Задание. Загрузите таблицу дивидендов Газпрома.
Рассмотрим загруженную таблицу (рис. 3.4). Нам предстоит кое-что исправить. Причём почти всё.
На этот раз название таблицы и соответствующего запроса получилось не очень информативное:
Table_0.
Заголовки столбцов после загрузки были продублированы.
Числовые значения распознаны как текстовые строки. Содержимое ячеек прижато влево – это текстовый формат по умолчанию.
Английская версия Excel ожидает точку как десятичный разделитель целой и дробной частей. А русская страница сайта использует для этой цели запятую.
Мало того, пробел разделяет группы по три разряда – тысячи, миллионы, миллиарды.
![](image20_5e67624b378e983c94b8c181_jpg.jpeg)
Рис. 3.4. Загруженная таблица дивидендов
Задание. Изучите результаты загрузки и перечислите в отчёте все необходимые преобразования.
Переходим к преобразованиям.
Открываем редактор запросов (рис. 3.4):
Data – Get & Transform – Get Data – Launch Power Query Editor.
![](image21_5e676248378e983c94b8c17e_jpg.jpeg)
Рис. 3.4. Запуск редактора запросов
Задание. Запустите редактор запросов.
Появляется окно редактора запросов (рис. 3.5).
Видим название запроса:
Table_0.
В заголовках всех колонок указано, что тип данных – текст:
ABC
В верхнем меню тоже говорится, что здесь данные текстового типа:
Home – Transform – Data Type – Text.
![](image22_5e676246378e983c94b8c17b_jpg.jpeg)
Рис. 3.5. Редактор запросов
Задание. Обратите внимание на название запроса и тип данных.
Начнём исправления загруженной таблицы.
Зададим новое название запроса:
Дивиденды Газпрома.
Изменим тип данных для первого столбца:
Home – Transform – Data Type – Decimal Number.
![](image23_5e676243378e983c94b8c178_jpg.jpeg)
Рис. 3.6. Изменение типа данных
Задание. Измените тип данных.
Появляется диалоговое окно
Change Column Type.
Нам предлагают определиться, как именно делать преобразование. Если добавить новый этап работы с данными, изменения можно будет откатить при желании.
Нам не потребуется возвращаться к текстовому типу номера года. Поэтому выбираем первый вариант:
Replace current.
![](image24_5e676241378e983c94b8c175_jpg.jpeg)
Рис. 3.7. Параметры преобразования типа
Задание. Выберите замену преобразования.
Рассмотрим результаты преобразования типа данных (рис. 3.8).
Тип данных для первого столбца теперь установлен ка числовой:
Data Type – Decimal Number.
В заголовке столбца тоже указано, что здесь находятся числа:
1.2.
Сами числа в первой колонке теперь прижаты вправо. Это формат числа по умолчанию.
В разделе Применённые шаги указано преобразование типа:
Query Settings – Applied Steps – Changed Type.
![](image25_5e67623f378e983c94b8c172_jpg.jpeg)
Рис. 3.8. Результаты преобразования типа
Задание. Рассмотрите результаты преобразования типа данных в первом столбце.
Первая строка, которая дублирует заголовок, содержит текст. Поэтому преобразование завершилось ошибкой, о чём нам и сообщают:
Error.
Щёлкнем по этой ячейке и получим более подробное пояснение (рис. 3.9).
В разделе Применённые шаги появляется новая строка:
Год.
Щёлкаем по красному крестику и возвращаемся к предыдущему шагу.
![](image26_5e67623e378e983c94b8c16f_jpg.jpeg)
Рис. 3.9. Сообщение об ошибке
Задание. Изучите сообщение об ошибке формата данных.
Удалим первую строку таблицы, в которой появилась ошибка преобразования типа.
Выбираем в верхнем меню
Home – Reduce Rows – Remove Rows – Remove Errors.
Всплывающая подсказка намекает, что программа удалит все строки, в которых есть ошибки. У нас одна такая строка.
![](image27_5e67623b378e983c94b8c16c_jpg.jpeg)
Рис. 3.10. Удаление строк с ошибками
После удаления строк с ошибками получаем более красивую таблицу (рис. 3.11).
Кроме в разделе Применённые шаги добавилась строка
Removed Errors.
Обратим внимание на количество строк в таблице до и после удаления ошибок. Для этого мы можем откатить последний шаг, нажав красный крестик в разделе Применённые шаги. Потом снова вызываем удаление строк с ошибками.
![](image28_5e676239378e983c94b8c169_jpg.jpeg)
Рис. 3.11. Таблица после удаления ошибок
Задание. Удалите строки с ошибками и обратите внимание на количество строк.
Переходим ко второму столбцу – величине дивидендов.
Зададим числовой тип данных.
Содержимое действительно стало числовым (рис. 3.12).
Сравним с оригиналом – числа стали гораздо больше. Программа просто проигнорировала запятые. Это искажает данные. Откатим изменения.
![](image29_5e676237378e983c94b8c166_jpg.jpeg)
Рис. 3.12. Результаты преобразования второго столбца
Задание. Задайте целый тип данных для второго столбца и обратите внимание на изменения.
Попробуем заменить запятые на точки.
Находим инструмент поиска и замены в меню (рис. 3.13):
Home – Transform – Replace Values
![](image30_5e676235378e983c94b8c163_jpg.jpeg)
Рис. 3.13. Функция замены значений
Задание. Прочитайте всплывающую подсказку для кнопки Замена значений.
Нажимаем кнопку Замена значений:
Replace Values.
Заменим запятые на точки (рис. 3.14).
![](image31_5e676233378e983c94b8c160_jpg.jpeg)
Рис. 3.14. Поиск и замена
Задание. Замените запятые на точки.
Рассмотрим результаты поиска и замены (рис. 3.15).
Действительно, теперь у нас во втором столбце точки вместо запятых.
При этом тип данных по-прежнему текстовый.
![](image32_5e676231378e983c94b8c15d_jpg.jpeg)
Рис. 3.15. Результаты поиска и замены
Задание. Замените запятые на точки во втором столбце.
Изменим тип данных во втором столбце с текстового на числовой:
Decimal Number.
Обращаем внимание на изменение форматирования и сообщение о типе в заголовке столбца.
В одной ячейке за 1998 год был прочерк. Возможно, это означает, что дивиденды не выплачивались. То есть равны нулю.
Теперь в этой ячейке выводится сообщение об ошибке (рис. 3.16).
![](image33_5e67622e378e983c94b8c15a_jpg.jpeg)
Рис. 3.16. Преобразование типа второго столбца
Задание. Измените тип данных на числовой и обратите внимание на сообщение об ошибке.
Отменяем последний шаг преобразований.
Заменяем прочерк, то есть символ тире, на ноль.
Щёлкаем по ячейке с неправильным содержимым и копируем её в буфер обмена. Затем вызываем функцию поиска и замены. В строке поиска будет загадочное тире. В строке замены – ничего.
Снова изменяем тип данных на числовой.
Теперь всё прошло успешно (рис. 3.17).
В ячейке за 1998 год стоит ноль.
Ошибок нет.
Все выполненные шаги по преобразованию данных выводятся в списке
APPLIED STEPS
![](image34_5e67622c378e983c94b8c157_jpg.jpeg)
Рис. 3.17. Второй столбец преобразован
Задание. Завершите преобразование второго столбца и убедитесь в отсутствии ошибок.
Здесь мы столкнулись с длинным тире. Это не просто чёрточка. Неплохо было бы с этими чёрточками познакомиться. А ещё есть символ минуса. Это тоже какая-то чёрточка. Или это одно и то же?
Задание. Прочитайте в Википедии статьи «Тире», «Дефис» и «Минус». Выясните английские названия этих символов, какие символы для каких целей используют, а также как вводить разные виды тире в текстовом редакторе Microsoft Word.
Преобразование данных в третьем столбце потребует нескольких действий:
Внимание! Это ознакомительный фрагмент книги.
Если начало книги вам понравилось, то полную версию можно приобрести у нашего партнёра - распространителя легального контента ООО "ЛитРес".Правообладателям!
Представленный фрагмент книги размещен по согласованию с распространителем легального контента ООО "ЛитРес" (не более 20% исходного текста). Если вы считаете, что размещение материала нарушает ваши или чьи-либо права, то сообщите нам об этом.Читателям!
Оплатили, но не знаете что делать дальше?