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

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

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

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

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

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

Эта задача может быть решена с помощью 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).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 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 можно добавлять двумя способами.

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

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

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

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

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

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

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

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

Рис. 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.

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

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

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

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

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

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

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

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

Архитектура 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.

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

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

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

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

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

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

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

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

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

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

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

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

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