С помощью потрясающего DAX Studio мы можем сделать это (хотя, на наш взгляд, это все еще неудобно и сложно) по крайней мере локально - из той же книги Excel или из Power BI Desktop.
Все, что вам нужно, это открыть вашу книгу Excel, запустить надстройку DAX Studio и подключить ее к этой книге. Затем вы можете просто подключиться к модели PowerPivot, как к кубу служб аналитики SQL Server.
Но это недокументированная и крайне ограниченная функция, не поддерживаемая Microsoft, которую можно использовать только на свой страх и риск.
Шаг 1: Старт
Во-первых, нам нужен Источник. Другими словами, книга Excel с некоторыми данными, загруженными в модель данных PowerPivot. Если вы видите таблицу в окне PowerPivot, это хорошо.
У нас есть рабочая книга с этими образцами таблиц:
Которые формируют эту простую модель данных:
В таблице «Продажи» мы уже создали вычисляемый столбец для расчета совокупной суммы для продукта по дате:
И простая мера:
Sum of Sales := SUM(Sales[Value])
Источником таблиц в модели PowerPivot являются таблицы из этой книги, но они также могут быть из Power Query.
Во-вторых, нам нужна надстройка DAX Studio для Excel. Если вы работаете с Power BI или PowerPivot и у вас не установлена DAX Studio, у нас для вас плохие новости... Если это так, или если вы не установили надстройку для Excel, перейдите на https://daxstudio.org, нажмите зеленую кнопку, чтобы загрузить последнюю версию и установить ее. Во время установки не забудьте установить не только приложение, но и надстройку Excel. Это необходимо.
Шаг 2
Чтобы подключить Power Query к вашей модели данных рабочей книги, откройте рабочую книгу. По некоторым причинам мы настаиваем на том, чтобы была открыта только одна рабочая книга! Если одновременно открыть несколько рабочих книг, могут возникнуть проблемы.
Запустите надстройку DAX Studio из группы лент «Надстройки» в Excel. После запуска DAX Studio вы увидите окно подключения, где вы должны выбрать первый вариант:
Нажмите «Подключиться». После того, как соединение установлено, не закрывайте DAX Studio, программа должна быть открыта.
Теперь все готово для подключения.
Шаг 3
Теперь перейдите к Power Query и подключитесь к «базе данных служб аналитики SQL Server (импорт)».
Для этого нам нужно знать адрес сервера. Если бы это подключение было к какому-либо серверу или Power BI Desktop, мы могли бы увидеть его адрес в правом нижнем углу окна DAX Studio:
Но при подключении к файлу Excel мы видим только это:
Теперь мы раскрываем главный секрет:
Вам необходимо использовать http://localhost:9000/xmla в качестве имени/адреса сервера. Вот и все.
Конечно, вы также можете использовать это имя базы данных (необходимо записать его, если вы хотите использовать пользовательский запрос MDX или DAX в соединении): «Microsoft_SQLServer_AnalysisServices».
Вам нужно знать, что эти адрес сервера и имя базы данных всегда совпадают, по крайней мере, во время написания этой статьи, поэтому вам не нужно каждый раз искать их заново.
Когда Power Query спросит вас об учетных данных источника данных, просто используйте текущую авторизацию Windows:
Ура!
Теперь вы видите модель, все ее вычисляемые столбцы и показатели как куб OLAP. Из него вы можете создавать любые нужные вам таблицы.
Если вы знакомы с кубами MD и их поведением, то все в порядке. Если нет, вам потребуется некоторое время, чтобы приспособиться к логике и использованию кубов.
Читать модель PowerPivot из Power BI
Если по какой-либо причине вы не хотите импортировать модель данных Excel в Power BI, вы можете использовать этот прием для подключения к PowerPivot в Power BI Desktop. Но опять же, только в режиме импорта.
Все шаги одинаковы.
Для нас этот трюк - один из длинного списка «что мы можем». Если честно, он один из самых полезных для этой задачи. Да, он сложен и ограничен ручным обновлением. Но если вам необходимо срочно извлечь некоторые данные из модели данных Excel в PQ, вы можете осторожно использовать этот подход.
Например, вам может потребоваться выполнить некоторые операции по слиянию данных в Power Query, но некоторые из них слишком сложны в Power Query и имеют очень плохую производительность (обычно речь идет о мэшапах, которые требуют много операций сканирования одной и той же таблицы) или есть другие причины для выполнения некоторых вычислений в DAX, а не в M. Вы можете экспортировать промежуточные результаты в модель данных и выполнить необходимые вычисления в PowerPivot, а затем снова прочитать их в Power Query.
Наши курсы по Power BI:
Курс Аналитик BI
Курс DAX Mastering
Курс Финансовый анализ в Power BI
Наши каналы:
VK
Telegram