Принципы работы PowerPivot и Power View с Microsoft Excel 2013

.

PowerPivotи Power View в Excel 2013

PowerPivot — надстройка, с помощью которой конечные пользователи могут собирать, хранить, моделировать и анализировать большие объемы данных в Excel. PowerView дает интуитивно понятное наглядное представление данных в моделях PowerPivot и базах данных в табличном режиме SQL ServerAnalysisServices (SSAS). Тем, кто не знаком с PowerPivot или PowerView, я рекомендую прочесть опубликованные ранее в SQL ServerPro статьи, перечисленные во врезке «Рекомендуемая литература», чтобы разобраться в принципах работы этих технологий.

В Excel 2013 PowerPivot и PowerView перестали быть отдельными надстройками, которые требуется загружать и устанавливать. Теперь эти надстройки изначально входят в состав продукта. Функционально PowerPivot в Excel 2013 очень напоминает надстройку PowerPivot для Excel 2010. Аналогично, возможности версии PowerView в Excel 2013 похожи на PowerView для Microsoft SharePoint 2013 и SharePoint 2010 посредством SQL Server 2012 SP1.

Однако следует отметить некоторые различия. В первую очередь это архитектурные особенности, о которых рассказано в статье Колина Банфильда «Excel 2013: Implications for PowerPivot and Excel Data Import Users, Part I» (http://www.powerpivotpro.com/2012/09/excel-2013-implications-for-powerpivot-and-excel-data-import-userspart-i/). На рисунке 1 показана архитектура Excel 2010.

Рисунок 1. Архитектура Excel 2010

На рисунке 2 показана архитектура Excel 2013 (различия отмечены красным шрифтом).

Рисунок 2. Архитектура Excel 2013

Как мы видим, некоторые части архитектуры PowerPivot встроены в Excel 2013. Например, появилась новая модель данных, которая будет рассматриваться ниже. Среди прочих различий можно назвать следующие.

• В версии PowerPivot в Excel 2013 более не используется отдельный список PowerPivotFields. Вместо него применяется встроенный список PowerPivotFields. В результате некоторые возможности надстройки Excel 2010 (например, поиск полей по имени, создание срезов из списка полей, отображение описаний полей при размещении курсора мыши над полем) более недоступны.
• В Excel 2013 размер книг с моделями PowerPivot не ограничивается объемом 2 Гбайт. Однако предел в 2 Гбайт по-прежнему действует для книг, публикуемых в SharePoint. Дополнительные сведения приведены во врезке «Сколько данных может обработать PowerPivot?».
• В Excel 2013 обновление сводной таблицы или сводной диаграммы по умолчанию приводит к обновлению базовых соединений с данными в модели данных. В Excel 2010 все по-другому: при обновлении сводной таблицы лишь направляется повторный запрос к модели. Новый способ обновления можно изменить, щелкнув Connections («Подключения») на вкладке Data («Данные»), выбрав пункт Properties («Свойства») и сняв флажок Refresh this connection on Refresh All (Обновлять это подключение по команде «Обновить все»).
• В Excel 2013 «отчет» PowerView представляет собой лист, а не rdlx-файл. Концепция нескольких представлений отчетов не предусмотрена. Зато можно создать несколько листов PowerView в одной книге Excel.

PowerPivot и DataModel

На веб-странице Create a DataModelвExcel модель данных описывается как «новый подход для интеграции данных из нескольких таблиц, фактически построения источника реляционных данных внутри книги Excel». Для иллюстрации этого подхода покажем, как использовать обычную сводную таблицу для анализа данных из таблицы Excel, а затем извлечь дополнительную таблицу из внешнего источника с использованием надстройки PowerPivot. Пользователи Microsoft OfficeProfessionalPlus 2013 могут воспроизвести эту ситуацию, загрузив пример книги. Обратите внимание, что в установке Office 2013 надстройка PowerPivot по умолчанию отключена. Пока включать надстройку PowerPivot мы не будем, сделаем это позднее.

Сначала откроем книгу с именем PP2013_Article_Example1_Start.xlsx, в которой содержится таблица данных о валовом внутреннем продукте (ВВП) США. Нажмите кнопку PivotTable на вкладке Insert («Вставка»), чтобы открыть диалоговое окно CreatePivotTable («Создание сводной таблицы»). Введите в нем таблицу для анализа и укажите, что сводную таблицу нужно поместить на новый лист, как показано на экране 1.

Экран 1. Диалоговое окно Create PivotTable

Обратите внимание на новый параметр Choose whether you want to analyze multiple tables («Укажите, следует ли анализировать несколько таблиц»). Под ним установите флажок Add this data to the Data Model («Добавить эти данные в модель данных»).

На рисунке 3 показана результирующая сводная таблица, которая отображает ВВП по финансовым годам (столбцы) и показателям (строки).

Рисунок 3. Полученная в результате работы сводная таблица

Показатели удобны для анализа областей ВВП, но имеется несколько показателей с одинаковыми названиями. К счастью, все эти показатели объединяются в различные группы. Группы показателей в исходной таблице отсутствуют, но они содержатся во внешней таблице. Как объединить эти две таблицы? До появления Excel 2013 приходилось вручную копировать и вставлять показатели как новые столбцы в таблицу ВВП или скопировать таблицу показателей в новый лист Excel, а затем применить функцию VLOOKUP. В Excel 2013 эта задача решается гораздо проще благодаря модели данных.

Для начала щелкните где-нибудь вне сводной таблицы. Это необходимо при получении внешних данных. В противном случае нужные функции будут отключены.

Затем в разделе Get External Data («Получить внешние данные») на вкладке Data («Данные») щелкните раскрывающийся список From Other Sources («Из других источников») и выберите пункт From OData Data Feed («Из веб-канала данных ODATA»), как показано на экране 2.

Экран 2. Получение внешних данных

В результате запускается мастер импорта данных. На первой странице мастера введите следующий веб-канал данных с моего сайта (http://www.understandingtheusdebt.com/odata/WcfDataSvcUSDebtv2.svc) и выберите таблицу с именем USGDP_Accounts. На последней странице мастера (показанном на экране 2) выберите Only Create Connection («Только создать подключение») в разделе Select how you want to view this data in your workbook («Выберите способ представления данных в книге»). Если выбран этот параметр, Excel устанавливает соединение в веб-каналом OData и загружает данные в модель данных, не показывая в книге саму таблицу. После завершения работы мастера импорта данных щелкните где-нибудь в сводной таблице, чтобы список полей стал видимым. Существует две таблицы, из которых можно выбирать поля: первоначальная таблица ВВП в Excel и таблица USGDP_Accounts из веб-канала OData.

Перенесите с помощью мыши столбец GDP_Level1 из таблицы USGDP_Accounts в область Rows («Строки») в списке полей сводной таблицы. Появится уведомление Relationships between tables may be needed («Могут потребоваться связи между таблицами»), как показано на экране 3.

Экран 3. Создание взаимосвязей между таблицами

В сводной таблице используются столбцы из двух не связанных между собой таблиц, поэтому Excel не может правильно подсчитать ВВП. Как показано на экране 3, все значения AccountDescription отображаются под каждым значением GDP_Level1, которое в сущности представляет собой внешнее соединение. Чтобы устранить эту проблему, нажмите кнопку Create («Создать») в уведомлении. В диалоговом окне CreateRelationship («Создать отношение») укажите отношение между двумя таблицами, используя значения, показанные на экране 3. Нажмите кнопку OK, чтобы сохранить отношение. После этого значения AccountDescription в сводной таблице группируются в соответствующие GDP_Level1, как показано на рисунке 4.

Рисунок 4. Корректная группировка значений AccountDescription в сводной таблице

Если у вас возникнут трудности при подготовке таблицы, можете воспользоваться готовой книгой с именем PP2013_Article_Example1_Finish.xlsx.

Затем выберите пункт Options («Параметры») в меню File («Файл») и щелкните Add-Ins («Надстройки»). Выберите COM Add-ins («Надстройки для модели компонентных объектов (COM)») из раскрывающегося списка Manage («Управление»), нажмите кнопку Go («Переход») и выберите Microsoft Office PowerPivot for Excel 2013. Выберите также PowerView. Нажмите кнопку OK, чтобы включить надстройки PowerPivot и PowerView.

На вкладке PowerPivot щелкните пиктограмму Manage, чтобы запустить надстройку PowerPivot. На экране 4 показаны уже загруженные таблицы GDP и USGDP_Accounts. Они загружены, потому что встроенный механизм сжатия столбцов (то есть xVelocity), появившийся в PowerPivot для Excel 2010, превратился в DataModel в Excel 2013. Другими словами, PowerPivot в Excel 2013 — необязательная надстройка (хотя и встроенная), но DataModel использует механизм xVelocity независимо от того, включена ли надстройка.

Экран 4. Использование дополнений для загрузки созданных таблиц

Возникает несколько вопросов. И прежде всего, какой подход следует избрать при загрузке и организации связей между несколькими таблицами — новые функции на вкладке PivotTable или надстройку PowerPivot? Ответ зависит от вашего опыта работы с Excel и PowerPivot. Для «классических» пользователей Excel (не знакомых с PowerPivot) использование модели данных с новыми функциями на вкладке PivotTable наряду с мастером импорта данных будет естественным расширением имеющихся навыков. Опытному специалисту, работающему с PowerPivot, эти функции, скорее всего, покажутся окольным путем к цели. В этом случае предпочтительным и более прямым путем будет использование надстройки PowerPivot.

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

• создание вычисляемых столбцов и мер (обратите внимание, что меры теперь называются вычисляемыми полями);
• создание иерархий;
• определение ключевых индикаторов производительности (KPI);
• скрытие столбцов;
• добавление описания столбцов и мер.

Что произойдет, если вы использовали доступные только через надстройку функции, а затем ваши коллеги откроют книгу на своих компьютерах без включенной надстройки? Они получат все преимущества этих элементов, но не смогут изменять их. При этом открывается ряд интересных возможностей. Предположим, что пользователь создает модель данных с применением всех возможностей PowerPivot с мерами, иерархиями, KPI и прочими функциями. Другие пользователи могут задействовать модель данных через сводные таблицы, сводные диаграммы и PowerView, но при этом от них скрыто сложное внутреннее устройство модели данных.

Power View и Data Model

Как отмечалось выше, PowerView обеспечивает визуализацию моделей данных PowerPivot и баз данных SSAS в табличном режиме. PowerView не работает непосредственно поверх реляционных данных или локальных данных Excel, но в Excel 2013 при необходимости можно создать модель данных «на лету». Чтобы увидеть этот процесс в действии, выполните следующие шаги.

1. С помощью надстройки PowerPivot удалите таблицы из модели данных в примере книги PP2013_Article_Example1_Start.xlsx.
2. На листе GDP выделите элемент внутри таблицы ВВП и щелкните на вкладке Insert. Будет создан новый лист PowerView, как показано на экране 5.
3. Нажмите кнопку Manage («Управление») на вкладке PowerPivot, чтобы открыть окно PowerPivot. Обратите внимание, что Excel добавил таблицу GDP в модель данных.

Экран 5. Создание новой страницы Power View

Как мы видим, для конечного пользователя PowerView — просто новая визуализация поверх локальных данных Excel. Однако на заднем плане модель данных всегда присутствует.

Чтобы вставить дополнительный лист PowerView, можно просто щелкнуть PowerView на вкладке Insert. Модель данных будет автоматически загружена в список полей PowerView. Если выбрать таблицу ВВП перед вставкой листа, то он не будет добавлен в модель данных второй раз.

Так же просто вставить лист PowerView, подключаемый к базе данных SSAS в табличном режиме. В разделе Get External Data на вкладке Data щелкните раскрывающийся список и выберите параметр From Analysis Services («Из служб аналитики»). На первой странице мастера импорта данных введите информацию о соединении в базу данных SSAS. На последней странице мастера выберите PowerView Report («Отчет PowerView»).

Обратите внимание, что лист PowerView — автономный объект; иными словами, визуализации PowerView нельзя поместить в традиционный лист Excel наряду с другими элементами, такими как сводные таблицы или сводные диаграммы. Кроме того, в отличие от версии PowerView, размещенной в SharePoint, лист PowerView не может быть экспортирован в Microsoft PowerPoint. Желающие поэкспериментировать с полноценным образцом PowerView могут загрузить Excel 2012 U.S. DebtWorkbook.

Автоматизация PowerPivot

Я всегда был сторонником применения макрокоманд Excel для автоматизации задач, поэтому с интересом услышал, что PowerPivot представляет собой «собственный компонент» Excel 2013. PowerPivot для Excel 2010 не показывает никаких API-интерфейсов. Однако сейчас доступны только определенные сценарии автоматизации (в основном относящиеся к хранению данных с использованием механизма xVelocity). В документе What'sNewforExcel 2013 Developers(http://msdn.microsoft.com/en-us/library/office/ff837594.aspx#xl15WhatsNew_DataModel) отмечается, что «новая объектная модель DataModel (в дополнение к существующей объектной модели Visual Basic for Applications) позволяет программно загружать и обновлять источники данных». В DataModel появилось четыре основных объекта: Model, ModelChanges, ModelRelationship и ModelTable. Кроме того, существующий объект Connections в объектной модели Excel был обновлен для интеграции с объектной моделью DataModel.

Например, если требуется:

• обновить существующую модель данных;
• добавить новую таблицу в модель данных;
• создать отношение между одной из существующих таблиц и новой таблицей.

В приведенном листинге содержится макрокоманда, которая использует объект Model в объектной модели DataModel для выполнения этих шагов. В частности, макрокоманда вызывает метод Model.Refresh, добавляет соединение в книгу, затем использует аргумент CreateModelConnection, чтобы добавить таблицу DateTbl в модель данных. Наконец, макрокоманда создает отношение между таблицами DateTbl и GDP. Рабочий экземпляр этой макрокоманды есть в книге PP2013_Article_Example1_WithMacro.xlsm.

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

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

Большой шаг вперед

Excel 2013 — значительный шаг вперед в бизнес-аналитике. Встроенная интеграция PowerPivot и PowerView обеспечивает совершенно новый уровень масштабируемости и функциональности. Благодаря этим двум технологиям достигается более высокий уровень единообразия при сборе, хранении, моделировании, анализе и общем доступе к информации в работе ИТ-специалистов и пользователей из сферы бизнеса.

Sub AddDateTableToModel()
On Error GoToThe_Error
' Обновление существующей модели.
ActiveWorkbook.Model.Refresh
' Добавление соединения к модели данных.
Dim oWorkbookConn As Excel.WorkbookConnection
Set oWorkbookConn = ActiveWorkbook.Connections.Add2( _
Name:=«DateTable», Description:=«Date Table from» & _
«www.UnderstandingTheUSDebt.com», ConnectionString:= _
Array(«DATAFEED;Data Source=http://www.understanding» & _
«usdebt.com/odata/WcfDataSvcUSDebtv2.svc/;Namespaces» & _
«to Include=*;Max Received Message Size=439804651110» & _
«4; Integrated Security=SSPI;Keep Alive=true;Persist» & _
«Security Info=false;Service Document Url=http://» & _
«www.understandingtheusdebt.com/odata/WcfDataSvcUS» & _
«Debtv2.svc/»), CommandText:=Array(«DateTbl»), _
lCmdType:=6, CreateModelConnection:=True, _
ImportRelationships:=False)
' Добавление отношения между таблицами Date и GDP.
With ActiveWorkbook.Model
. ModelRelationships.Add. ModelTables(«GDP»). _
ModelTableColumns(«Date»), _
. ModelTables(«DateTbl»).ModelTableColumns(«Date»)
End With
Exit Sub
The_Error:
MsgBox «Error Encountered:» &Err.Description& _
vbTab&Err.Source, vbCritical, «Error.»
EndSub