© Валентин Юльевич Арьков, 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 и что мы взяли эти данные в статье на сайте Википедии. Объяснение получается длинное. Попробуем оставить буквально несколько слов – то, что уже нельзя сократить без потери информации.

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

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