Мониторинг компонентов бизнес-аналитики SQL Server и SharePoint

В статье «Бизнес-анализ средствами SQL Server и SharePoint», опубликованной в Windows IT Pro/RE № 2 за 2013 год, я рассматривал последние версии компонентов бизнес-аналитики Business Intelligence (BI), реализованные Microsoft с помощью SQL Server 2012 и SharePoint 2010. Эти BI-компоненты на приведенном рисунке отмечены желтым цветом.

Рисунок. Компоненты бизнес-аналитики, предоставляемые с помощью SQL Server 2012 и SharePoint 2010

Данная статья посвящена возможностям протоколирования, доступным для мониторинга производительности и использования BI-компонентов. В частности, будут обсуждаться вопросы мониторинга:

• использование служб SQL Server Reporting Services (SSRS) и Power View;
• применение службы PowerPivot для SharePoint;
• использование служб PerformancePoint Services (PPS) и служб Excel.

Мониторинг применения SSRS и Power View

Администраторам важно знать, какие отчеты SSRS используются чаще других, какие отчеты самые медленные, использовались ли новые отчеты и т.д. Найти ответы на подобные вопросы достаточно просто. Службы SSRS используют базу данных, называемую базой данных сервера отчетов, для хранения следующей информации:

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

Данные журнала выполнения отчетов содержат подробную информацию о каждом запросе на выполнение отчета. Эта информация хранится в таблице и доступна через представление ExecutionLog. Заметим, что в зависимости от версии SSRS может использоваться до двух дополнительных версий этого представления (ExecutionLog2 и ExecutionLog3), в которых отражены возможности более современных продуктов или поля с более понятными для пользователя названиями.

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

Существует несколько бесплатных решений для извлечения и объединения данных журнала выполнения, включая Microsoft SQL Server Community Project, решение CodePlex под названием SCRUBS и решение, разработанное Родни Лэндрумом.

Microsoft SQL Server Community Project. Это решение включает в себя схему базы данных, набор отчетов SSRS и пакет служб SQL Server Integration Services (SSIS) RSReportExecutionLog_Update.dtsx для извлечения данных журналов выполнения и некоторых других таблиц. Этот проект базируется на SSRS 2005 и не обновлялся для более новых версий SQL Server. Он работает и для SSRS 2008, но не функционирует с экземплярами SSRS 2008 R2 или SSRS 2012. Хотя я уже встречал несколько статей на тему обновления оригинального пакета и отчетов (например, «Execution Log Sample Reports SSRS 2008 R2», http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/8b849083-136e-40f4-84b1-c60bfca4e6b9), но если у вас используется SSRS 2012 или SSRS 2008 R2, стоит подумать о применении SCRUBS.

SCRUBS. Данное решение разработано на основе шаблонов Microsoft и использует в своих отчетах формат языка определения отчетов Report Definition Language (RDL) версии 2008. Хотя SCRUBS был предназначен для работы с SSRS 2008 и SSRS 2008 R2, после небольшой модификации он работает и с SSRS 2012.

SCRUBS использует экземпляр служб SSRS, запущенный либо в собственном режиме, либо в режиме интеграции с SharePoint. В набор файлов для загрузки, прилагающийся к данной статье, я включил копии отчетов SCRUBS, базу данных и пакет SSIS (включая исправления, необходимые для SSRS 2012).

Для работы с этими шаблонами в SQL Server 2012 откройте файл BIMonitoringProjects.sln в наборе инструментов SQL Server Data Tools. Перед использованием отчетов, которые запускаются с помощью Scrub_, необходимо выполнить следующие действия.

1. Подключить базу данных SummitCloud_SCRUBS в имеющемся экземпляре SQL Server 2012. Файлы базы данных содержатся в архиве SummitCloud_SCRUBS_DBFiles.zip.
2. Запустить SSIS-пакет SCRUBS_SSRS_Log_Feed.dtsx для заполнения базы данных.
3. Отредактировать в проекте ExecutionLog параметры подключения SummitCloud_SCRUBS.rds.

Как мы видим на экране 1, отчет Scrub_Top Reports.rdl показывает краткую сводку 10 часто используемых отчетов. Щелчок мыши на имени любого отчета приведет к открытию подробного отчета Scrub_Report Summary Generation Statistics.rdl. Аналогично, отчет Scrub_Report Execution Log.rdl предоставляет краткую сводку по использованию отчетов в указанный период времени вместе с подробной информацией о выполнении отчетов. Щелчок мышью на имени отчета (или значке с песочными часами) приведет к открытию подробного отчета. Заметим, что, поскольку отчеты Power View сами являются частным случаем отчетов SSRS (.rdlx), использование Power View также отображается в этих отчетах.

Экран 1. Пример отчета, показывающего 10 часто используемых отчетов SSRS

Для случаев, когда необходим мониторинг в реальном времени, я включил три отчета, работающих непосредственно с базой данных сервера отчетов. Названия этих отчетов начинаются с префикса RealTime_.

Решение Лэндрума. В статье «Consolidate Data on Executed SSRS Reports for Easy Querying» (http://www.sqlmag.com/article/sql-server/consolidate-data-ssrs-report-easy-querying-144506) Родни Лэндрум изложил собственный подход к извлечению данных из журналов выполнения. Для извлечения данных в собственную схему Родни использует пакет SSIS.

Мониторинг использования PowerPivot для SharePoint

Так же, как отчеты SSRS могут применяться для мониторинга использования SSRS, отчеты PowerPivot могут применяться для мониторинга использования службы PowerPivot. Появившись впервые в SQL Server 2008 R2, служба PowerPivot для SharePoint позволяет пользователям публиковать и предоставлять совместный доступ к книгам Excel 2010, содержащим модели PowerPivot. Устанавливаемая в качестве совместно используемого серверного приложения, служба PowerPivot для SharePoint использует преимущества технологии ведения журналов, предоставляемой SharePoint. Реализованная в SharePoint единая служба ведения журнала Unified Logging Service предоставляет возможность накопления данных об использовании, а PowerPivot для SharePoint применяет эту возможность для загрузки данных об использовании в свою базу данных. Рабочая книга PowerPivot с именем «PowerPivot Management Data.xlsx» находится на верхнем уровне этой базы данных в качестве модели данных. Если быть более точным, книга «PowerPivot Management Data.xlsx» находится в библиотеке документов «PowerPivot Management» в семействе узлов центра администрирования.

Чтобы процесс сбора данных об использовании заработал, его надо включить для фермы SharePoint. Когда вы устанавливаете PowerPivot для SharePoint, используя вариант New Server, данная функция включается автоматически. В противном случае ее надо включить вручную. Пошаговые инструкции, как это сделать, приведены в документе «How to Manually Enable the Collection of SharePoint Usage Data» на странице http://www.windowsitpro.com/article/sharepoint/manually-enable-collection-sharepoint-usage-data-145045. По умолчанию данные об использовании PowerPivot хранятся 365 дней. Этот параметр можно изменить на странице управления службой PowerPivot.

Панель мониторинга управления PowerPivot Management Dashboard, доступная в корне сайта центра администрирования SharePoint, содержит несколько веб-частей, отображающих информацию о производительности и использовании, полученную из данных в рабочей книге «PowerPivot Management Data.xlsx». Например, такие метрики производительности, как время обработки запроса, использование памяти, использование процессора и уровни активности, доступны в веб-части «Infrastructure – Server Health», как показано на экране 2. Аналогично список опубликованных пользовательских рабочих книг (с информацией о количестве пользователей, просматривавших каждую рабочую книгу, размере рабочей книги и общем количестве запросов) доступен в веб-части «Workbook Activity – List».

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

Если необходимо, вы можете настроить панель мониторинга управления PowerPivot для отображения дополнительных представлений. Например, недавно один мой заказчик очень интересовался источниками данных, задействованными в рабочих книгах пользователей. Информация об источниках данных накапливается как часть данных об использовании, поэтому я сделал сводную таблицу Excel, используя в качестве источника данных книгу «PowerPivot Management Data.xlsx». Эта сводная таблица содержала списки источников данных, сгруппированные по поставщику данных. Затем я загрузил эту рабочую книгу на сайт центра администрирования, открыл страницу панели мониторинга, используя редактор страниц SharePoint, и добавил веб-часть на панель. Эту веб-часть, названную «Data Sources Used», можно увидеть на экране 2. Информацию о том, как настраивать панель мониторинга управления PowerPivot, можно найти в Интернете.

Есть один важный момент в понимании смысла данных об использовании. С точки зрения активности выполнения запросов эти данные вычисляются не на основе просмотров рабочей книги, а на основе запросов на подключение к данным PowerPivot в этой рабочей книге. Когда пользователь открывает рабочую книгу PowerPivot в библиотеке документов SharePoint, служба PowerPivot для SharePoint обычно не задействуется. И так до тех пор, пока пользователь не запросит срез данных, не выберет фильтр или не обновит подключение к рабочей книге, для которой задействована служба PowerPivot для SharePoint. Но, поскольку все эти действия могут сгенерировать множественные события подключений, число запросов в панели мониторинга управления PowerPivot будет, скорее всего, намного больше количества просмотров рабочей книги. А если рабочая книга используется как источник данных в какой-либо иной технологии (например, в отчете Power View), количество запросов будет еще больше.

Мониторинг использования служб PPS и служб Excel

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

Для автоматической идентификации страницы можно использовать программные интерфейсы API для SharePoint для перебора всех страниц в наборе сайтов и поиска ссылок на веб-часть PPS. В примере программного кода WSS_LoggingReports я использовал отчет с именем PPSUsageSummary2.rdl, который делает запрос к представлению RequestUsage в базе данных журнала использования. Этот запрос определяет веб-страницы SharePoint, которые также отображаются в строке запроса служебных вызовов PPS. Исходя из моего опыта, такой подход является достаточно точным с точки зрения определения и агрегирования данных об использовании служб PPS. На экране 3 показан пример такого отчета.

Экран 3. Пример отчета, показывающего данные о производительности и использовании служб PPS

Аналогично службам PPS, службы Excel также довольно сложны для мониторинга. И так же, как для служб PPS, я решил осуществлять мониторинг служб Excel на уровне страниц. Когда пользователь открывает рабочую книгу в сайте SharePoint, для которого включены службы Excel, книга открывается в веб-браузере. При этом для открытия рабочей книги используется одна и та же страница, которой в качестве параметра передается имя этой рабочей книги (например, http://MySite//_layouts/xlviewer.aspx?id=/MySite/Reports/VolumeAnalysis.xlsx&Source=http%3A%2F%MySite%2FReports%2FForms%2Fcurrent%2Easpx&DefaultItemOpen=1). Таким образом, при создании запроса к журнальной базе данных вы можете осуществлять поиск строк в представлении RequestUsage, в которых в пути к документу указана станица xlviewer.aspx, а затем просмотреть столбец QueryString и узнать, какая именно рабочая книга была просмотрена. В примере программного кода для данной статьи я использовал отчет ExcelServicesUsage.rdl, в котором выполняется данный запрос.

Заметим, что данный подход не определяет веб-страницы, использующие веб-часть Excel Web Access для встраивания содержимого рабочих книг Excel. Если вы используете эту веб-часть, вам необходимо определять и явно отслеживать такие типы страниц.

Практический подход к мониторингу

Я надеюсь, что теперь вы стали лучше понимать, как осуществлять мониторинг производительности и использования компонентов бизнес-аналитики, предоставляемых SQL Server 2012 и SharePoint 2010. Для мониторинга использования некоторых компонентов необходимо применять простой практический подход. Моей целью в долгосрочной перспективе является построение единого репозитория (и набора отчетов) для обеспечения унифицированного представления данных о производительности и использовании всех этих компонентов.