Подключение Power Query к модели данных Excel Power Pivot
Мы можем подключить практически любой источник данных в Power Query, но модель данных PowerPivot не включена в этот обширный список источников.
С помощью потрясающего DAX Studio мы можем сделать это (хотя, на наш взгляд, это все еще неудобно и сложно) по крайней мере локально — из той же книги Excel или из Power BI Desktop.
Все, что вам нужно, это открыть вашу книгу Excel, запустить надстройку DAX Studio и подключить ее к этой книге. Затем вы можете просто подключиться к модели PowerPivot, как к кубу служб аналитики SQL Server.
Но это недокументированная и крайне ограниченная функция, не поддерживаемая Microsoft, которую можно использовать только на свой страх и риск.
Шаг 1: Старт
Во-первых, нам нужен Источник. Другими словами, книга Excel с некоторыми данными, загруженными в модель данных PowerPivot. Если вы видите таблицу в окне PowerPivot, это хорошо.
У нас есть рабочая книга с этими образцами таблиц:
Excel, Power Pivot, Power BI и DAX. В чем различие и что использовать? (часть урока из курса по DAX)
Которые формируют эту простую модель данных:
В таблице «Продажи» мы уже создали вычисляемый столбец для расчета совокупной суммы для продукта по дате:
И простая мера:
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 в качестве имени/адреса сервера. Вот и все.
PowerPivot. 01. Введение. Первое знакомство
Конечно, вы также можете использовать это имя базы данных (необходимо записать его, если вы хотите использовать пользовательский запрос 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.
Источник: iqbi.pro
Powerpivot что это за программа
В конце прошлого года компания ALG Systems Ukraine начала активную работу над проектами по внедрению BI систем в финансовом и банковском секторе. Пока что мы не можем назвать перечень этих проектов, но можем сказать, какое программное обеспечение мы используем: Microsoft SQL Server, SharePoint и новую BI систему PowerPivot.
В этом блоге мы расскажем как можно эффективнее работать с Excel, используя вспомогательную программу от компании Microsoft – PowerPivot.
В основе любых успешных бизнеса процессов лежат точные, актуальные и своевременные данные. Современные источники данных ничем не отличаются от тех, которые существовали 10 или даже 20 лет тому назад. Их можно поделить на 2 вида: таблицы, заполненные вручную сотрудниками структурных подразделений или же те, которые генерируются и обрабатываются ИТ системами.
Каждый из вышеуказанных методов обработки данных имеет свои преимущества. Таблицы, созданные вручную, предоставляют большую гибкость, которой не обладают другие методы. В свою очередь, BI системы могут обрабатывать большие объемы данных с меньшими затратами времени, автоматически проводя анализ и формируя отчетность. Обработка больших массивов данных с помощью ИТ систем позволяет существенно сократить негативное влияние человеческого фактора.
Тем не менее, существуют и недостатки. Во-первых, для внедрения современных BI систем требуются немалые инвестиции; во-вторых, эти ИТ системы не являются достаточно гибкими для того, чтобы подстраиваться под быстро меняющуюся бизнес среду. Зачастую такие системы применяются для решения самых важных задач, связанных с обработкой и анализом информации. Что составляет 5-10% от всех информационных потребностей.
Остальные же потребности удовлетворяются с помощью сводных таблиц. Для проведения разового анализа электронные таблицы являются рациональным решением. На рисунке ниже Вы можете ознакомиться с необходимыми финансовыми и временными затратами для внедрения BI систем или использования электронных таблиц.
Недостатки каждого из методов ставят бизнес перед трудным выбором: заплатить очень высокую цену сейчас или заплатить высокую цену в будущем. Более того, избрав один из методов, впоследствии очень сложно изменить его на другой.
Управляемость таблиц с помощью BI
Уникальность программы PowerPivot заключается в том, что она предоставляет бизнесу гибридные опции, которые актуальны как в краткосрочном, так и в долгосрочном периоде. Расширенный функционал «Excel plus» позволяет использовать все имеющиеся навыки пользователей.
Ниже приведен пример рабочей книги программы PowerPivot на базе Excel 2010. Оранжевым цветом выделены новые функции:
С помощью использования BI систем PowerPivot существенно сокращает текущие расходы на обработку информации, содержащейся в таблицах. Продвинутые пользователи также могут заметить расширенные функции Excel:
- Возможность обрабатывать до 100 миллионов строк в одной рабочей книге, сохранив при этом управляемость размер файла и высокую производительность
- Сводные Таблицы (PivotTables) и Сводные Диаграммы (PivotCharts), построенные на базе множества таблиц данных не требуют, чтобы к этим таблицам применяли функцию VLOOKUP
- Возможность быстро создать коллажи, которые интегрируют данные из разных источников
- Расширенные возможности Pivot в сравнении с любой существующей на сегодняшний день Excel Pivot
- Также компания создала веб-приложение, которое предоставляет интерактивный доступ к данным. Это позволяет получить доступ к данным с использованием любого браузера, даже если Excel не установлен на компьютере пользователя.
Все что нужно – преобразовать рабочую книгу PowerPivot в веб-приложение. Это делается с помощью одного действия: «Сохранить Как» («Save As») на сервере.
Особенности использования программы как веб-приложения:
- У пользователей есть интерактивный доступ, но нет возможности загрузить или изменить рабочую книгу. Это сделано с целью защиты данных
- У пользователе не возникает никаких сложностей при работе с устаревшей копией на их десктопе
- Нет необходимости вручную ежедневно обновлять отчеты, так как сервер делает это автоматически
- Никто, кроме согласованного перечня авторов (пользователей) не может менять созданную рабочую книгу. При внесении любых изменений программа автоматически сохраняет информацию о том кто и когда что-либо менял.
В следующих блогах мы расскажем о прочих особенностях и преимуществах программы PowerPivot
Источник: devopshub.net
Powerpivot что это за программа
Всем добрый день.
Вот решил попытаться освоить PowerPivot с подключенной к нему базой 1с. Админ 1с дал на растерзание копию базы, подключение совершить удалось. После подключения предлагает выбрать таблицы для работы, этих таблиц тут больше 100, все имеют название типа: _1SACCSEL dbo, DH1396, DT1644, RA2795, RG1067 — как понимать что есть что, как потом это сводить в какой либо отчет. Кто уже работал с такими вещами или знает где подробно на русском много об этом описано — буду благодарен за помощь.
Всем добрый день.
Вот решил попытаться освоить PowerPivot с подключенной к нему базой 1с. Админ 1с дал на растерзание копию базы, подключение совершить удалось. После подключения предлагает выбрать таблицы для работы, этих таблиц тут больше 100, все имеют название типа: _1SACCSEL dbo, DH1396, DT1644, RA2795, RG1067 — как понимать что есть что, как потом это сводить в какой либо отчет. Кто уже работал с такими вещами или знает где подробно на русском много об этом описано — буду благодарен за помощь. DJ_Marker_MC
Сообщение Всем добрый день.
Вот решил попытаться освоить PowerPivot с подключенной к нему базой 1с. Админ 1с дал на растерзание копию базы, подключение совершить удалось. После подключения предлагает выбрать таблицы для работы, этих таблиц тут больше 100, все имеют название типа: _1SACCSEL dbo, DH1396, DT1644, RA2795, RG1067 — как понимать что есть что, как потом это сводить в какой либо отчет. Кто уже работал с такими вещами или знает где подробно на русском много об этом описано — буду благодарен за помощь. Автор — DJ_Marker_MC
Дата добавления — 13.09.2012 в 15:38
Источник: www.excelworld.ru
PowerPivot преодолевает ограничения на число строк
С помощью драйвера ODBC я организовал связь PowerPivot с MySQL
PowerPivot позволяет работать в Excel с гораздо более объемными таблицами
Средство импортирования данных в PowerPivot позволяет без труда определить, какие таблицы, столбцы и записи надо загружать
Microsoft PowerPivot представляет собой надстройку к анонсированному корпорацией табличному процессору Excel 2010. Мое внимание программа привлекла прежде всего тем, что с ее помощью можно работать с гораздо большими массивами данных, чем в самом Excel. К примеру, мне удавалось загружать и просматривать таблицы, содержащие более 3,9 млн. строк — это в четыре раза больше предельного для Excel значения. При этом прокрутка таблицы осуществлялась так же легко, как если бы речь шла о документе всего из нескольких сот строк.
Если вы при работе с электронной таблицей упираетесь в ограничение на число строк, это, как правило, означает, что для решения вашей задачи лучше подошла бы СУБД. Однако для человека, привыкшего иметь дело с редактором таблиц, не так-то просто отказаться от любимого инструмента.
PowerPivot радует не только скоростью работы с длинными таблицами — этот бесплатный продукт еще и очень эффективно обеспечивает связь между рабочими книгами Excel и одной или несколькими базами данных. Я с помощью драйвера ODBC привязывал таким образом данные из Access, из “плоских” (бесструктурных) файлов и из MySQL.
В PowerPivot реализован новый язык для составления реляционных выражений — Data Analysis Expressions (DAX). Он напоминает имеющиеся в Excel средства для работы с функциями, но при этом гораздо лучше подходит для задач, ориентированных на использование баз данных. К примеру, DAX может работать с целыми столбцами или даже таблицами, а не только с диапазонами или отдельными ячейками, что избавляет от необходимости перетаскивать формулы через всё пространство таблицы, когда требуется переопределить диапазоны после внесения изменений в данные.
Тестировавшиеся мною версии PowerPivot и Excel 2010 еще не завершены, и я при работе несколько раз натыкался на шероховатости. В ряде случаев встроенные в таблицу данные оказывались испорченными, были проблемы и при работе с MySQL.
Однако после устранения этих недочетов PowerPivot станет действительно мощным инструментом, полезным как для ИТ-администраторов, стремящихся обеспечить пользователям более гибкий доступ к корпоративным данным, так и для бизнес-аналитиков, желающих работать в привычной программе, но с большими объемами информации. Добавлю также, что в PowerPivot есть средство интеграции с SharePoint (я его не тестировал), упрощающее коллективный доступ к аналитическим данным.
PowerPivot в лаборатории
Тестирование PowerPivot проводилось в бета-версии Excel 2010 на виртуальной машине под управлением 64-разрядной версии Windows 7. Вообще-то и у PowerPivot, и у Excel 2010 существуют 64-разрядные версии, но я использовал при испытаниях 32-разрядные версии обеих программ. Бета-версии этих продуктов можно получить по адресу соответственно microsoft.com/office/2010 и powerpivot.com. Если вы решили установить не весь набор приложений Office 2010, а только Excel, то вам нужно инсталлировать также “Общие средства Office” (Office Shared Features), без которых PowerPivot не сможет нормально функционировать.
Для испытаний я взял выборку из базы данных Access и таблицы Excel из проекта Codeplex. Кроме того, я взял с сайта OpenSecrets.org данные о финансировании кампании, проводимой Центром за ответственную политику, и импортировал их в MySQL.
После инсталляции PowerPivot в “ленте” Excel появилась новая вкладка для этого плагина. С ее помощью можно открыть отдельное окно для работы с PowerPivot, и там имеются собственные элементы управления для импортирования и обработки данных. При этом среда самой программы Excel никуда не исчезает. PowerPivot “живет” внутри рабочей книги Excel, вместе с данными. Эти данные могут включать таблицы и сводные диаграммы, которые, в свою очередь, могут либо заполняться информацией из PowerPivot, либо поставлять информацию через связанные таблицы.
В PowerPivot есть удобный мастер для закачки внешних данных из СУБД, плоских файлов и других источников. Я подключал свою тестовую таблицу к базе данных Access, переходя к нужному ACCDB-файлу, и затем либо выбирал объект из списка таблиц и представлений в СУБД, либо использовал SQL-запрос. При работе со списком можно выбирать таблицы вместе с фильтрами, определяющими правила включения записей.
При осуществлении аналогичных операций с данными из MySQL (это делалось через драйвер ODBC, который был у меня уже установлен и сконфигурирован в Windows) всё поначалу шло примерно так же — надо было указывать мышью и нажимать кнопку. Но когда я задействовал графическое средство для выбора таблиц и фильтров, программа стала выдавать сообщения об ошибке. С помощью SQL-запросов я все же смог задать требуемые условия, но это оказалось несколько сложнее, чем с помощью мастера.
После импортирования набора данных в PowerPivot каждая таблица заняла свое место на соответствующей вкладке с ярлыком внизу окна, как и у обычных таблиц рабочей книги. Я без труда добавлял новые таблицы и представления с данными из различных источников. Столь же легко можно было удалять любые из добавленных таблиц, а также создавать отношения между таблицами — в последнем случае надо было щелчком выделить столбец в таблице на одном конце отношения, а затем в диалоговом окне указать таблицу и столбец на другом конце этого отношения.
В справочных материалах PowerPivot имеется учебник, и с его помощью мне удалось привязать таблицы с данными о продажах велосипедов некоей фиктивной компании к таблицам о продажах в целом по отрасли. Столь же легко я визуализировал данные таблиц с использованием функций PivotCharts и Slicers, обеспечивающих наглядное представление числовых данных для анализа.
Источник: www.itweek.ru