Анализ данных из произвольных источников с использованием PowerPivot

Последнее обновление: 06.06.2011

См. также: Новое в Excel 2013 Preview для бизнес-аналитики

Зачем нужен PowerPivot?

В стандартной аналитической системе (в данном случае имеется в виду промышленное решение Business Intelligence) пользователи работают с аналитическими кубами, которые сформированы на основе хранилища данных.  В хранилище данные загружаются с помощью пакетов интеграции из различных баз данных конкретной организации.

Срок выполнения проектов по внедрению такого рода аналитических систем может составлять от нескольких месяцев до полугода и даже больше, в зависимости от количества и сложности источников, количества измерений, сложности вычислений в аналитических кубах, количества стандартных (предварительно настроенных) отчетов.

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

Классическая бизнес-аналитики и задача подключения новых данных

Рис. 1. Классическая бизнес-аналитики и задача подключения новых данных

Эта задача может быть решена с помощью PowerPivot для Excel.

Оперативное подключение данных для анализа с помощью PowerPivot для Excel

Рис. 2. Оперативное подключение данных для анализа с помощью PowerPivot для Excel

Итак, PowerPivot — это набор приложений и сервисов, которые позволяют бизнес-пользователям самостоятельно создавать аналитические решения.

PowerPivot поддерживанит связывание между собой и расширение показателей в больших объемах данных, загруженных из гетерогенных источников, таких как:

  • MS SQL Server;
  • MS Access;
  • Книги Excel;
  • MS SQL Azure;
  • MS SSAS;
  • Oracle;
  • Teradata;
  • Sybase;
  • Informix;
  • DB2;
  • текстовые файлы;
  • другие файлы PowerPivot;
  • потоки данных.

На основании этих данных PowerPivot  позволяет создавать таблицы и графики (так называемые PivotTables и PivotCharts), управляемые с помощью обычных и визуальных фильтров (на английском – Filters и Slicers).

Файл PowerPivot, созданный с помощью Excel, можно опубликовать на портале  SharePoint. Таким образом, функционал PowerPivot предоставляются двумя надстройками:

  • SQL Server PowerPivot для Excel;
  • SQL Server PowerPivot для SharePoint.

О них и пойдет речь в последующих двух разделах этой статьи.

PowerPivot для Excel

Надстройка расширяет Excel 2010 и поддерживает большие объемы данных. Это достигается благодаря использованию клиентской версии аналитических сервисов, другое имя которой  — VertiPaq-обработчик.

Этот обработчик сжимает данные в памяти и позволяет быстро рассчитывать в памяти требуемые агрегаты.

PowerPivot для Excel представляет собой отдельное Win32-приложение (отдельное окно), предназначенное для работы с данными.

В это приложение можно импортировать данные из произвольных источников, создать связи между колонками загруженных таблиц, создать дополнительные расчетные колонки. Таким образом, в PowerPivot создается требуемая модель предметной области.

При сохранении файла Excel загруженные данные внедряются непосредственно в книгу Excel.

Для работы с большими объемами данных рекомендуется использовать 64-х битную систему (чтобы для данных можно было использовать всю имеющуюся оперативную память). Объемы данных, с которыми можно работать в PowerPivot для Excel, ограничены только объемом оперативной памяти, поэтому ее можно расширять по необходимости.

Процесс работы с PowerPivot состоит из 3-х основных шагов и одного дополнительного (рис. 3).

Процесс работы с PowerPivot

Рис. 3. Процесс работы с PowerPivot

В первую очередь необходимо загрузить данные в PowerPivot и подготовить их для создания отчетов. В том числе необходимо создать связи между таблицами из различных источников и при необходимости определить расчетные колонки с помощью языка DAX (Data Analysis Expressions).

Вторым шагом необходимо создать непосредственно отчеты PowerPivot  для анализа данных. При этом можно также определить новые меры (или показатели) с помощью DAX

После того как отчеты созданы, их можно отформатировать, т.е. оформить в соответствии с требованиями, предъявляемыми к оформлению документов организации.

Дополнительно, если в организации используется SharePoint в качестве портального решения, отчет PowerPivot можно разместить на портале, после чего им можно будет пользоваться с помощью браузера через Интернет.

Шаг 1. Загрузка и подготовка данных

На шаге загрузки и подготовки данных мы должны открыть окно PowerPivot. Это можно сделать с закладки PowerPivot в Excel 2010.

Окно PowerPivot для Excel

Рис. 4. Окно PowerPivot для Excel

Интерфейс системы позволяет выбрать загрузку из широкого набора баз данных, потоков данных, Excel-таблиц и буфера обмена.

Для того, чтобы можно было анализировать данные в разрезе справочников, интерфейс предоставляет возможности создания связей, как в автоматическом, так и в ручном режиме.

В интерфейсе окна PowerPivot  можно создать дополнительные расчетные колонки, что позволяет фильтровать данные по связанным колонкам и извлекать данные из связанных таблиц.

Следует также обратить внимание, что загруженные данные и мета-данные хранятся непосредственно в листе Excel с использованием сжатия данных, а при его открытии загружаются в оперативную память компьютера также с использованием сжатия данных. Это позволяет использовать для анализа очень большие объемы данных и обрабатывать их с высокой скоростью, т.к. при работе с данными не выполняется дисковых операций.

Шаг 2. Разработка отчетов PowerPivot

Отчеты PowerPivot  могут быть созданы на основании построенной модели PowerPivot с помощью трех инструментов.

  • PivotTables (используются для создания сводных таблиц);
  • PivotCharts (используются для создания сводных диаграмм, рис. 5);
  • функции CUBE (используются для создания отчетов в произвольной форме).

Создание сводных диаграмм с помощью PivotCharts

Рис. 5. Создание сводных диаграмм с помощью PivotCharts

Модели PowerPivot, на основании которых строятся отчеты, могут быть локальными (то есть располагаться непосредственно в книге Excel), либо можно использовать подсоединение к книге Excel, опубликованной на портале SharePoint.

Опубликованные книги PowerPivot также могут стать источниками данных для отчетов аналогично кубам аналитических сервисов.

Создание отчетов произвольного вида

Для создания отчетов  произвольной формы с помощью моделей PowerPivot доступны две из семи функций Excel «CUBE»:

  • CUBEMEMBER – возвращает элемент;
  • CUBEVALUE – возвращает агрегированное значение.

Остальные функции CUBE нельзя использовать для работы с моделями PowerPivot.

Источник данных постоянно связан с книгой PowerPivot и называется Sandbox (или по-русски – «песочница»).

Совет: Вы можете преобразовать PivotTable в произвольный вид с помощью команды Convert to Formulas.

Пример создания отчетов  произвольной формы: http://powerpivotpro.com/2010/03/01/guest-post-dany-hoter-on-cube-formulas/

Шаг 3. Форматирование отчетов PowerPivot

Отчеты PivotTables и PivotCharts могут быть отформатированы с помощью:

  • стандартных стилей;
  • настроек форматирования.

Пример стандартного форматирования отчета

Рис. 6. Пример стандартного форматирования отчета

Формат значений можно настроить путем вызова окна «Value Field Settings» из контекстного меню к ячейке со значением.

Вызов настройки формата значений

Рис. 7. Вызов настройки формата значений

Создание вычислений с помощью DAX (шаг 2 и/или 3)

В модель PowerPivot можно добавлять сложные расчеты данных. Для этого используется язык выражений DAX (расшифровывается как Data Analysis Expressions или «выражения для анализа данных»). Конструкции DAX просто использовать, так как они позволяют абстрагироваться от концепции многомерных данных и не требуют изучения языка MDX (multidimensional expressions).

Расчеты в модель PowerPivot можно добавлять двумя способами.

  1. Путем создания вычисляемых колонок в окне PowerPivot (что соответствует этапу подготовки данных). При этом вычисления производятся для каждой строки в таблице.
  2. Путем создания мер, которые определяют новые поля в Панели задач PowerPivot. В данном случае вычисления производятся когда поле помещено в область значений объекта PivotTable или PivotChart. При расчете мер учитывается значение визуальных фильтров.

PowerPivot позволяет аналитикам работать с данными как с реляционными таблицами, и DAX предоставляет функции в терминах концепции реляционных данных.

Библиотека функций DAX содержит:

  • множество знакомых функций Excel;
  • функции перехода по связям многие-к-одному и один-ко-многим;
  • функции агрегации значений в таблицах;
  • функции контекстной фильтрации;
  • функции операций со временем.

Если вам знаком язык MDX, примите во внимание, что, в отличие от MDX, DAX не может создавать расчетные элементы на основании иерархий и создавать связи между ячейками.

Дополнительно: Александр Усольцев. Видео: Использование языка Data Analysis Expressions в PowerPivot

Демонстрация PowerPivot для Excel

Архитектура PowerPivot для Excel

Рассмотрим  архитектуру PowerPivot для Excel (рис. 8).

Архитектура PowerPivot для Excel

Рис. 8. Архитектура PowerPivot для Excel

PowerPivot для Excel – это надстройка для Excel 2010, которая предоставляет средства для добавления и объединения больших объемов данных в книгах Excel.

Книга PowerPivot – это книга Excel, которая содержит данные PowerPivot и объекты Excel для визуализации данных, такие как PivotTables и PivotCharts.

Для данных PowerPivot нет ограничений на количество колонок и строк, поэтому для анализа можно использовать значительно большие объемы данных. Анализируемые данные хранятся в памяти в сжатом виде. Таким образом, дублирующиеся наименования атрибутов данных, в разрезе которых анализируются числовые данные, не занимают память.

Данные для анализа можно импортировать (и обновлять) из источников различных форматов. Возможность задания связей между числовыми данными и справочниками позволяет интегрировать данные из различных источников и учитывать в анализе историю изменения данных.

Данные содержатся непосредственно в файле Excel. Таким образом, не требуется управлять внешними соединениями с источниками данных. Если вы публикуете книгу PowerPivot, данные остаются в ней же…

Данные в файле Excel извлекаются и обрабатываются внутренним движком. Аналитические графики и таблицы отображаются через интерфейс Excel. Сжатие и обработка производится аналитическими сервисами в режиме VertiPaq. Обработка выполняется в фоновом режиме, чтобы обеспечить поддержку больших объемов данных с возможностью мгновенной сортировки и фильтрации.

PowerPivot для SharePoint

Организация совместной работы

Список задач, выполняемых сотрудниками в зависимости от их роли в организации совместной работы с отчетами PowerPivot на портале SharePoint 2010, приведен на рис. 9.

Задачи, выполняемые сотрудниками при использовании PowerPivot для SharePoint

Рис. 9. Задачи, выполняемые сотрудниками при использовании PowerPivot для SharePoint

С точки зрения организации совместной работы автор книги PowerPivot после ее создания в Excel имеет возможность опубликовать эту книгу на портале организации, реализованном с использованием портала SharePoint 2010. Так как в книге PowerPivot содержатся данные из различных источников, можно настроить автоматическое обновление этих данных при открытии страницы портала с данной книгой и обновление данных в книге по расписанию. В результате пользователи данной книги PowerPivot получат возможность всегда видеть в ней актуальную информацию.

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

IT-профессионалы имеют возможность непосредственно на портале отслеживать использование решений PowerPivot, выявлять редко используемые решения и по возможности указывать пользователям на более правильные отчеты для решения их задач, а также, самое важное, выявлять наиболее часто запрашиваемые решения PowerPivot используемые большим количеством пользователей. Данные из этих часто запрашиваемых отчетов являются первыми кандидатами для помещения в хранилище данных, а форма отчета позволит проанализировать, в какой форме пользователям удобнее анализировать данные и предложить наиболее оптимальное промышленное решение.

Демонстрация PowerPivot для SharePoint

Функции PowerPivot для SharePoint

Далее перечислены основные функции PowerPivot для SharePoint.

  1. Книги PowerPivot могут быть размещены на портале SharePoint и отображаются для пользователей с помощью Excel Services.
  2. На портале присутствует галерея PowerPivot, которая позволяет видеть внешний вид отчетов еще до момента их открытия для просмотра.
  3. Для каждой книги PowerPivot можно настроить периодичность обновления данных – как на уровне книги, так и на уровне каждого источника данных.
  4. Панель управления PowerPivot  позволяет администраторам отслеживать частоту использования, количество пользователей и размер книг PowerPivot.
  5. Данные из книги PowerPivot, размещенной на портале SharePoint, можно использовать в качестве потоков данных ATOM и SSRS для последующего создания новых отчетов в Excel и ReportBuilder.
  6. Экземпляр аналитических сервисов, размещенный в пределах фермы SharePoint, использует систему аутентификации и модель авторизации, предоставленную серверами SharePoint. Для каждого отчета PowerPivot необходимо задать параметры аутентификации – под каким пользователем будет осуществляться подключение к источникам данных при обновлении данных в отчете. Таким образом, при открытии книги PowerPivot анализируются права доступа пользователя к этой книге, а при обновлении данных из источников – права доступа указанной в отчете учетной записи к этим источникам.

Замечание. Несколько дней назад на конференции по Microsoft BI мне удалось побеседовать с Рафалем Лукавецки, которые рассказал, что у компании Panorama есть решение для PowerPivot, позволяющее настраивать права доступа на уровне куба PowerPivot. Вот ссылка на решение: http://www.panorama.com/products/powerpivot/powerpivot.html.

Архитектура PowerPivot для SharePoint

Компоненты и инструменты PowerPivot для SharePoint

Рассмотрим компоненты PowerPivot для SharePoint.

Компоненты и инструменты PowerPivot для SharePoint

Рис. 10. Компоненты и инструменты PowerPivot для SharePoint

При установке PowerPivot для SharePoint к ферме серверов SharePoint добавляется экземпляр аналитических сервисов. Этот экземпляр предоставляет на уровне сервера функции обработки и совместной работы с книгами PowerPivot.

Для создания книги PowerPivot на клиентском компьютере используется надстройка над Excel 2010.

Для просмотра книги PowerPivot на портале SharePoint необходимо установить компонент «PowerPivot for SharePoint».

Для визуализации данных PowerPivot на портале используется «Системный сервис PowerPivot», который передает данные из листа Excel в «Аналитические сервисы в режиме VertiPaq». Данные загружаются в многомерном виде в память сервера аналитических сервисов для последующего анализа.

Запрос к данным PowerPivot в SharePoint

Выполнение запроса к данным PowerPivot производится в соответствии со схемой, приведенной на рис. 11.

Последовательность выполнения запроса к данным PowerPivot

Рис. 11. Последовательность выполнения запроса к данным PowerPivot

Обработка запроса инициируется когда пользователь открывает книгу Excel из библиотеки SharePoint и взаимодействует со сводной таблицей или графиком, содержащими данные PowerPivot.

Сервисы Excel и сервис PowerPivot обрабатывают  различные части книги Excel (то есть файла с расширением XLSX).

Сервисы Excel обнаруживают данные PowerPivot и запрашивают обработку у сервера PowerPivot. Сервер PowerPivot направляет запрос в экземпляр аналитических сервисов в режиме VertiPaq, который извлекает данные из книги Excel в библиотеке и загружает данные. Данные, которые хранятся в памяти, направляются обратно в обрабатываемую книгу Excel, а затем направляются обратно в «Веб-доступ» Excel для отображения в окне браузера.

Запрос к данным PowerPivot  из клиентских приложений

Последовательность выполнения запроса к данным PowerPivot из клиентских приложений

Рис. 12. Последовательность выполнения запроса к данным PowerPivot из клиентских приложений

Запросы, возникающие вне фермы серверов обычно создаются клиентскими приложениями, которые используют данные PowerPivot как внешний источник данных.

Например, это может быть ReportBuilder, ссылающийся на данные из книги PowerPivot как на источник данных с помощью веб-адреса SharePoint для подсоединения к серверу. Либо это может быть Excel, использующий соединение с базой данных аналитических сервисов для запроса данных из книги PowerPivot.

Веб-адрес SharePoint предоставляет путь к документу, который содержит адрес сервера SharePoint и информацию о веб-приложении. Заданные заранее связи между веб-приложением и приложением сервиса PowerPivot в ферме позволяют веб-сервису PowerPivot найти приложение сервиса PowerPivot и перенаправить запрос к нему.

Приложение сервиса PowerPivot извлекает книгу PowerPivot из  библиотеки контента и сохраняет ее локально.

Затем приложение сервиса PowerPivot перенаправляет запрос в  локальный экземпляр аналитических сервисов на том же сервере приложений. Данные загружаются и возвращаются клиенту, от которого получен запрос.

Дополнительные ресурсы

Официальные ресурсы:

Блоги:

Партнерские решения:

Видео:

Статьи:

Схемы

Обучение:

комментариев 14

  1. […] Сейчас для MS с точки зрения BI одна из самыз горячих тем это безусловно PowerPivot. Еще не знаете что это такое?! Почитайте отличный обзор на русском от Ивана Косякова – Анализ данных из произвольных источников с использова…. […]

  2. […] Вообще говоря, это нормально. Подход ad-hoc очень удобен большинству людей, а в анализе данных хочется иметь возможность быстро что-либо изменить и посмотреть на ситуацию по другому. Мы это понимали, понимают аналитики и прочат Excel и PowerPivot большое будущее , а его распространение сравнивают со степным пожаром — PowerPivot — нарушитель спокойствия на рынке BI. (Вот здесь очень подробный обзор – с чего начать) […]

  3. Добрый день , есть вопрос не могу найти на него ответ :
    как сделать групировку по датам ( в разрезе месяц ) , данные беруться из таблиц SQL 2008 , используется Exel 2010 .
    Все вроде работает , только не как не могу сделать групировку по датам ( поле просто не активно ) приходиться работать с датами в разрезе день ( очень не удобно )
    в одной вашей презентации в Ех 2007 хорошо видно как это делается но вот у меня поле групировка не активно , что то н пойму в чем дело ?

  4. Данные цепляю Пивотом из таблиц в SQL , далее строю в Екселе 2010 куб .
    Хочу видеть продажи в разрезе месяца , а сейчас только день .

    У вас есть МSN , OCS , Skype ? может так просче будет написать и объеснить или показать на экране или скрин шот ?

    • А у вас в таблице есть колонка месяц для каждой даты? Может, потом ее можно будет просто перетащить в сводную таблицу?

    • Константин правильно говорит, нужно использовать колонку с именем месяца из таблицы дат… При необходимости ее можно рассчитать формулой непосредственно в PowerPivot.

  5. Коллеги, если у вас отключилась надстройка PowerPivot для ее включения нужно выполнить следующие действия:

    1. Откройте Excel.

    2. Выберите пункт меню Файл -> Параметры.

    3. В открывшемся диалоговом окне на закладке «Надстройки» в выпадающем списке «Управление» выберите «Отключенные объекты» и нажмите на кнопку «Перейти…».

    4. В открывшемся диалоговом окне выберите надстройку PowerPivot и нажмите на кнопку «Включить».

  6. У меня возникла такая же проблема как и у Сергея, то есть не группируется дата (источник тот же — SQL 2008R2). При этом в окне PowerPivot`a поле с датой определяется в правильном формате ‘01.01.2011’, а в сводной таблице Excel она почему-то выглядит ‘1/1/2011’. Естественно я пытался изменять это с помощью формата ячеек, а также использовал функцию DATAVALUE. В региональных настройках у меня стоит Россия. Подскажите, как вывести в сводную таблицу даты в правильном формате.

  7. Если при попытке обратиться к отчету PowerPivot как к многомерной модели данных (кубу, UDM) у вас возникает ошибка «unable to connect to data source» (или «невозможно подключиться к источнику данных») воспользуйтесь решением на следующих страницах:
    http://blogs.msdn.com/b/johndesch/archive/2012/04/23/using-powerpivot-workbooks-from-a-mid-tier-server-configured-for-kerberos-authentication.aspx
    или
    http://summit7systems.com/blogs/resolving-powerpivot-kerberos-permission-error/

  8. Добрый день, Иван.
    В одном из Ваших «скринкастов» книга PowerPivot подключается к книгам xlsx, размещенных на портале Sharepoint. Подскажите, пожалуйста, как реализовать такую связь, с учетом обеспечения возможности обновления данных в книге PowerPivot при изменении книг на портале?

    • Добрый день, Геннадий!
      В данном случае мы подключаемся загружаем Excel-таблицы в PowerPivot (источник — файлы Excel на портале). Затем, после размещения отчета PowerPivot на портале, мы можем настроить расписание обновления данных в PowerPivot из файлов-источников (индивидуально для каждого источника).

      • Добрый день, Иван. Спасибо за ответ. Я подключил подключил файлы Excel как источник excel через WebDav, по другому не получилось. Иногда по непонятным причинам соединения «отваливаются» и приходится восстанавливать соединение через кнопку «Обзор…» в источнике соединения. Я также пытался подключить части excel с использованием atom. Но весь необходимый диапазон и таблица возвращается в одном поле «Content». В связи с этим вопрос — есть ли возможность подключить файлы в Excel по технологии atom, подобно тому как подключаются списки shatrepoint?
        И еще вопрос:
        Не выполняются обновления, причем создано конечное приложение SecureStore в котором задана УЗ (не пула PowerPivot) и пароль, а учетная запись пула PowerPivot — владелец этой учетной записи и администратор OLAP, настроено делегирование kerberos. Но возникает Ошибка: 7493 «В конечном приложении не найдены учетные данные для текучего пользователя». Причем пользователь — пул приложений. Подскажите, пожалуйста, в чем может быть причина?

  9. Такой вот вопрос. В PowerPivot уже загружены 2 таблицы.
    Необходимо загрузить еще одну, большую таблицу из внешнего источника (Oracle) но не в PowerPivot, а на основной лист таблицы Excel начиная с ячейки например BB1 (свои причины из-за множества формул).
    Как это лучше сделать? И как лучше, если это разовая загрузка из-за больших объемов, а те 2 таблицы можно было бы не обновлять.
    1. Тоже качать в PowerPivot, а потом отключать, потому что уже потом будет работа с этими данными и редактированием их?
    2. Или можно все же загружать помимо PowerPivot? У меня не получилось так.

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

%d такие блоггеры, как: