Глава 8. Импорт в Power Query из баз данных

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

Ваша организация может предоставить вам доступ к базам данных. Загрузка из баз данных, как правило, более эффективна, чем загрузка из файлов. Для подключения укажите расположение базы данных, введите логин и пароль, выберите таблицы, с которыми вы хотите работать. Power Query поддерживает подключение к большому разнообразию баз данных без необходимости установки каких-либо дополнительных драйверов. Доступные подключения можно найти в Excel на вкладке Данные –> Получить данные –> Из базы данных, Из Azure, Из других источников.

Ris. 8.1. Interfejs podklyucheniya k bazam dannyh

Рис. 8.1. Интерфейс подключения к базам данных

Скачать заметку в формате Word или pdf

Вы подключитесь к базе данных AdventureWorks, расположенной на SQL Server, и проанализируете объем продаж по годам и регионам для компании AdventureWorks.

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

Итак:

  • Создайте новую книгу Excel
  • Данные –> Получить данные –> Из Azure –> Из базы данных SQL Microsoft Azure
  • Сервер: azuredb.powerqueryworkshop.com
  • Базы данных: Adventureworks2012

Ris. 8.2. Podklyuchenie k baze dannyh Azure

Рис. 8.2. Подключение к базе данных Azure

Появится окно для ввода учетных данных. Возможно несколько вариантов:

  • Если это ваш рабочий ПК, используйте учетные данные Windows для входа на компьютер (рис. 8.3).
  • Вы также можете Использовать другие учетные данные.
  • В нашем примере перейдите на вкладку Базы данных. Для доступа к данным мы используем проверку безопасности базы данных, а не проверку безопасности Windows. На этой вкладке необходимо ввести следующие учетные данные (рис. 8.4):
    • Имя пользователя: DataMonkey@ptypanama
    • Пароль: D4t4M0nk3y!
  • Наконец, можно воспользоваться Учетной записью Майкрософт.

Ris. 8.3. Ispolzovanie uchetnoj zapisi Windows

Рис. 8.3. Использование учетной записи Windows

Ris. 8.4. Podklyuchenie k baze dannyh s ispolzovaniem uchetnyh dannyh bezopasnosti bazy dannyh

Рис. 8.4. Подключение к базе данных с использованием учетных данных безопасности базы данных. Нажмите Подключение. Появится окно с вопросом о поддержке шифрования. Кликните Ok.

Используемые учетные данные пользователя кэшируются в файле, который находится на  локальном ПК. Это означает, что имя пользователя и пароль не могут быть переданы с решением, отправленным по электронной почте. Созданное вами подключение не будет доступно другому пользователю.

Управление подключениями

Если вы неправильно ввели имя подключения, имя базы данных, идентификатор пользователя или пароль и вам нужно что-либо изменить, вы можете сделать это, пройдя по меню Данные –> Получить данные –> Параметры источника данных:

Ris. 8.5. Okno Nastrojki istochnika dannyh otfiltrovannyj dlya slova powerquery

Рис. 8.5. Окно Настройки источника данных, отфильтрованный для слова powerquery

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

Ris. 8.6. Okno Pravit razresheniya

Рис. 8.6. Окно Править разрешения

Использование навигатора

После нажатия на кнопку Подключение (см. рис. 8.4) Power Query отобразит окно Навигатора для  выбора таблиц, к которым вы хотите подключиться. Поскольку таблиц много, можете использовать поиск. Введите salesorder в область поиска. Кликните на таблицу SalesOrderHeader. Справа появится предварительный просмотр. Нажмите кнопку Преобразовать данные.

Ris. 8.7. Okno Navigator

Рис. 8.7. Окно Навигатор; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Преобразование данных

Откроется редактор Power Query (рис. 8.8). Справа в области ПРИМЕНЕННЫЕ ШАГИ есть две строчки: Источник и Навигация. Если перейти на шаг Источник, вы увидите исходный список всех таблиц базы данных. Шаг Навигации раскрывает выбранную таблицу.

Ris. 8.8. Neobrabotannye dannye tablitsy SalesOrderHeader

Рис. 8.8. Необработанные данные таблицы SalesOrderHeader

  • Выберите столбцы OrderDate, SalesOrderNumber, SubTotal, TaxAmt, Freight и SalesTerritory
  • Щелкните правой кнопкой мыши один из заголовков выбранных столбцов –> Удалить другие столбцы
  • Щелкните правой кнопкой мыши столбец OrderDate –> Преобразование –> Год –> Год
  • Щелкните правой кнопкой мыши столбец OrderDate –> Переименовать –> Year
  • Щелкните правой кнопкой мыши столбец SalesOrderNumber –> Переименовать –> Order#

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

Ris. 8.9. Predvaritelno obrabotannaya tablitsa SalesOrderHeader

Рис. 8.9. Предварительно обработанная таблица SalesOrderHeader

Большинство столбцов имеют говорящие названия, и релевантные числовые или текстовые значения. А вот столбец Sales.SaleTerritory имеет странное название, и отражает значение Value. Если кликнуть на одно из Value, в нижней части увидим поля из таблицы SaleTerritory. Т.е., столбец Sales.SaleTerritory не показывает данные из таблицы SalesOrderHeader, а дает ссылку на данные из таблицы SaleTerritory.

Ris. 8.10. Stolbets Sales.SaleTerritory ssylaetsya na dannye iz tablitsy SaleTerritory

Рис. 8.10. Столбец Sales.SaleTerritory ссылается на данные из таблицы SaleTerritory

Это одна из самых замечательных вещей при подключении к базам данных: большинство баз данных поддерживают связи между таблицами, поэтому вы можете просматривать связанные записи даже без необходимости настраивать отношения самостоятельно или выполнять какие-либо слияния. Более того, вы можете развернуть этот столбец, кликнув на двуглавую стрелку на рис. 8.10, и увидите поля, поступающих из других таблиц. Power Query спросит, какие столбцы вы хотите развернуть (см. рис. 8.11).

Ris. 8.11. Polya svyazannoj tablitsy

Рис. 8.11. Поля связанной таблицы

  • Снимите флажок (Выбрать все столбцы)
  • Выберите Group
  • Снимите флажок Использовать исходное имя столбца как префикс
  • Нажимать OK
  • Переименуйте запрос RegionByYear

Набор данных готов к загрузке (рис. 8.12). Главная –> Закрыть и загрузить. Данные загрузят на лист Excel в Таблицу.

Ris. 8.12. Finalnyj vid zaprosa

Рис. 8.12. Финальный вид запроса

Как обычно, для проверки данных создайте сводную таблицу:

Ris. 8.13. Svodnaya tablitsa sozdannaya iz bazy dannyh SQL Windows Azure

Рис. 8.13. Сводная таблица, созданная из базы данных SQL Windows Azure

Вы также можете добавить срезы, сводные диаграммы и др. Но самое приятное, что с помощью простого Данные –> Обновить всё вы можете обновить данные из онлайн-базы в любое время.

Использование SSAS в качестве источника данных

Службы SQL Server Analysis Services (SSAS) – это еще один источник, используемый в корпоративной среде. SSAS можно разделить на табличные и многомерные модели (кубы), и обе могут быть импортированы в Power Query. Для подключения к службам SSAS необходимо создать новый запрос: Данные –> Получить данные –> Из базы данных –> Из базы данных служб SQL Server Analysis Services (импорт). В открывшемся окне необходимо ввести имя (или адрес) вашего сервера:

Ris. 8.14. Import iz bazy dannyh sluzhb SQL Server Analysis Services SSAS

Рис. 8.14. Импорт из базы данных служб SQL Server Analysis Services (SSAS)

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

Power Query также может подключение и к Google Analytics. К сожалению, это работает только в Power BI Desktop, но не в Excel.


Прокомментировать