Автоматическое выполнение отчетов

Article
07/28/2009

Едва в 2005-м появились DMV (и DMF – dynamic management functions), с помощью которых можно получить массу подноготной правды о жизни и здоровье SQL Server, в SQL Server Manageability Product Unit, который выпускает Management Tools для SQL Server, немедленно возникла мысль скрестить выразительные средства Reporting Services с информативностью DMV и построить нечто сродни ситуационного центра администратора - https://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS-Reports-1.aspx. Был сделан набор стандартных отчетов, позволяющих мониторить различные аспекты деятельности SQL Server:

рис.1

Помимо основной задачи мониторинга, с помощью отчетов SSMS можно было решить также обратную задачу – подсмотреть, к каким DMV происходит обращение в датасете (запросе) отчета с тем, чтобы затем использовать их для своих собственных надобностей. Это элементарно сделать через профайлер (https://blogs.msdn.com/alexejs/archive/2009/07/25/1-2.aspx) при запуске отчета, либо скачав исходные RDLи стандартных отчетов - https://blogs.msdn.com/sqlrem/attachment/732910.ashx. Можно создать BIный проект типа Report Server Project, куда подцепить все rdl-файлы SSMS:

рис.2

Можно даже их подредактировать по своему желанию и засунуть обратно, потому что в SQL Server 2005 Service Pack 2 в дополнение к стандартным появилась возможность создавать Custom Reports - https://blogs.msdn.com/sqlrem/archive/2006/11/20/custom-reports-in-management-studio.aspx. Процедура создания и вызова пользовательских отчетов описана в BOL: https://msdn.microsoft.com/ru-ru/library/bb153684.aspx.

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

рис.3

которые распространялись в составе SQL Server 2005 Performance Dashboard, свободного дополнения к SQL Server 2005, доступного по адресу https://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en. С ней тоже можно развлекаться, создав в Visual Studio собственный Report Server Project и подцепив в него все rdlи в ее составе:

рис.4

Как утверждалось, the information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server. И это действительно, так. Стандартные отчеты, помимо DMV, прибегают к чтению SQL Serverной дефолтной трассы ::fn_trace_gettable(@base_tracefilename, default) в Backup_Restore_Events.rdl, Configuration_Changes_History.rdl, Database_Consistency_History.rdl, Disk_Usage.rdl, Login_Failures.rdl, Memory_Consumption.rdl, Schema_Changes_History.rdl, Schema_Changes_History_DB.rdl. Performance Dashboard основывается сугубо на DMV. Однако Performance Dashboard создает в msdb схему MS_PerfDashboard, а в ней свои процедуры и функции. Разумеется, исходный код созданных процедур и функций можно посмотреть в SSMS. В отличие от стандартных отчетов, Performance Dashboard не считалась штатной функциональностью SQL Server. Это означало, что если вы обнаружили какой-либо баг в стандартных отчетах, его будут исправлять, а Performance Dashboard предлагалась as is и поддержка на нее не распространялась. Впрочем, поскольку и то, и другое доступно в виде своих RDL-исходников, поправить выявленные баги не составляет труда - https://blog.hoegaerden.be/2008/12/21/a-performance-dashboard-issue/. Единственно, в случае стандартных отчетов пришлось бы довольствоваться custom report, пока разработчики SQL Server официально не внесут исправления в следующем сервис-паке.

Последовательность установки Performance Dashboard и что какой отчет в ней значит разбирается, например, здесь - https://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p2.aspx.

Проблема что стандартных отчетов, что Performance Dashboard состоит в том, что они нигде не накапливают собранную информацию, т.е. нельзя посмотреть изменение характеристик в динамике, чтобы понять, как мы дошли до жизни такой. Насколько я помню, в схеме MS_PerfDashboard не создается никаких табличек, токмо процедуры и функции, дабы уменьшить монструозность запросов. В 2008-м этой проблемы нет, в его состав штатно входит Performance Management Studio - дальнейшее развитие идей Performance Dashboard, где создается хранилище для накопления исторической информации. Но про Performance Management Datawarehouse мы поговорим в следующий раз, а сейчас я хочу остановиться на теме, вынесенной в название поста. Коль скоро в Performance Dashboard ретроспектива не поддерживается, а потребность в ней была, на одном проекте, имевшем место года 2 - 2.5 назад, возникла мысль иметь своего рода snapshot history, т.е. чтобы выбранные отчеты из списка Performance Dashboard или стандартных или комбинированного или еще какого-нибудь, неважно, выполнялись с заданной частотой в течение дня и результаты складировались. В данном посте мне показалось, будет полезным выделить фрагмент в виде более общей задачи. Имеется набор некоторых отчетов в виде rdl. Отчеты могут быть параметризованы, тогда предполагается, что к каждому такому отчету прилагается список значений его параметров. Требуется выполнить все эти отчеты и получить результаты в виде xls'ов. Я покажу, как бы стал решать эту задачу на примере отчетов Performance Dashboard. В SQL Server 2005 SP2 оболочка SSMS была доработана с тем, чтобы она могла отображать отчеты из RDL-файлов без необходимости установки Reporting Services - Reporting Services is not required to be installed to use the Performance Dashboard Reports. Разумеется, в данном случае нам понадобится честная установка Reporting Services. Существуют два способа автоматизировать действия на Reporting Service: при помощи VB-подобных скриптов утилиты командной строки rs.exe и обратившись к нему как к веб-сервису. Я выбрал второй вариант. Чтобы написать программу выполнения списка отчетов, вначале требуется этот список как либо составить. Можно просто читать rdlи из папки, но тогда непонятно, как быть с параметрами отчетов. Я остановился на XML, содержащем полные имена RDL и XLS как результата экспорта и полагающиеся каждому отчету значений параметров. Так это будет выглядеть в случае Performance Dashboard

DeployExecReports.xml:

<?xml version="1.0" encoding="utf-8"?>

<RDLFile>C:\Demo\DeployExecExportReports\PerformanceDashboard\SQLPerformanceDashboard\database_overview.rdl</RDLFile>

<ExportTo>C:\Demo\DeployExecExportReports\PerformanceDashboard\SQLPerformanceDashboard\database_overview.xls</ExportTo>

</Report>

...

<RDLFile>C:\Demo\DeployExecExportReports\PerformanceDashboard\SQLPerformanceDashboard\sessions_overview.rdl</RDLFile>

</Parameter>

</Parameters>

<ExportTo>C:\Demo\DeployExecExportReports\PerformanceDashboard\SQLPerformanceDashboard\sessions_overview.xls</ExportTo>

</Report>

</DeployExecExportReports>

Скрипт 1

Под это дело, я думаю, можно даже подогнать схему.

DeployExecReports.xsd:

<?xml version="1.0" encoding="utf-8"?>

<xs:schema xmlns:xs="https://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">

<xs:complexType name="ParameterType">

<xs:sequence>

<xs:element name="Value" type="xs:string" minOccurs="0" maxOccurs="unbounded"/>

</xs:sequence>

<xs:attribute name="Name" type="xs:string" use="required"/>

</xs:complexType>

<xs:complexType name="ReportType">

<xs:sequence>

<xs:element name="RDLFile" type="xs:string" minOccurs="1" maxOccurs="1"/>

<xs:element name="Parameters" minOccurs="0" maxOccurs="1">

<xs:complexType>

<xs:sequence>

<xs:element name="Parameter" type="ParameterType" minOccurs="1" maxOccurs="unbounded"/>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element name="ExportTo" type="xs:string" minOccurs ="1" maxOccurs ="1"/>

</xs:sequence>

</xs:complexType>

<xs:element name="DeployExecExportReports">

<xs:complexType>

<xs:sequence>

<xs:element name="Report" type="ReportType" minOccurs="0" maxOccurs="unbounded"/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

Скрипт 2

Это, собственно, код

/// Данная прога предназначена для автоматизации выполнения серии отчетов и экспорта их рез-тов в Excel.

/// Предполагается, что список отчетов (возможно, с параметрами) задается в файле DeployExecExportReports.xml,

/// построенном в соответствии со схемой DeployExecExportReports.xsd.

/// В коде происходит создание временного фолдера на Report Server, путь к которому указывается в rsTempFolder.

/// В ней создается источник данных, имя которого задается в tempDataSourceName. Источник имеет тип SQL Server.

/// Предполагается, что все отчеты выполняются против одного сервера и в одной и той же БД. строка соединения -

/// в connectionString.

/// Целью написания программы являлась автоматизация сбора стандартных отчетов SQL Server Management Studio.

/// Сами отчеты можно взять на https://go.microsoft.com/fwlink/?LinkId=81792.

/// Примечание 1. Некоторы отчеты стандартной серии нуждаются в правке - см. ..\Letter1.doc.

/// Примечание 2: В отчетах SQL Performance Dashboard (https://www.microsoft.com/downloads/details.aspx?familyid=1D3A4A0D-7E0C-4730-8204-E419218C1EFC\&displaylang=en

/// ставится в C:\Program Files\Microsoft SQL Server\90\Tools), предварительно нужно поправить параметр version_string как nullable,

/// чтобы их можно было запускать самостоятельно, а не только через центральный performance_dashboard_main.rdl.

/// Каждый rdl поочередно деплоится в этот фолдер, привязывается к этому источнику и рендерится в Excel, который затем

/// сохраняется в той же папке, где и rdlи.

/// Координаты RS задаются в Web-references. В данном проекте используется Web Reference на

/// https://localhost/ReportServer/ReportService2005.asmx для создания фолдера, источника данных и отчета на Report Servere

/// и https://localhost/ReportServer/ReportExecution2005.asmx для его выполнения.

/// Данные еndpointы справедливы для Reporting Services 2005 - 2008, установленных как native(не шаропойнт) -

/// см. https://msdn.microsoft.com/en-us/library/ms155398.aspx.

using System;

using System.IO;

using System.Xml;

using System.Xml.Schema;

namespace DeployExecExportReports

{

class Program

{

const string connectionString = "Data Source=(local);Initial Catalog=AdventureWorksDW";

static void Main(string[] args)

{

string rsTempFolder = "#DeployExecExportReports" + System.Guid.NewGuid(), parentFolderForTempFolder = "/";

string tempDataSourceName = "#DataSource";

string exeName = System.Reflection.Assembly.GetEntryAssembly().Location;

string currentPath = new DirectoryInfo(exeName).Parent.Parent.Parent.FullName;

exeName = Path.GetFileNameWithoutExtension(exeName);

RS.ReportingService2005 rs = new RS.ReportingService2005();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

RSExec.ReportExecutionService rsExec = new RSExec.ReportExecutionService();

rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;

Console.WriteLine("Работаем с Report Serverом {0}", rs.Url);

//Создаем на RS временный фолдер для работы

rs.CreateFolder(rsTempFolder, parentFolderForTempFolder, null);

Console.WriteLine("Создали на RS папку {0}", parentFolderForTempFolder + rsTempFolder);

//Создаем в нем источник данных

RS.DataSourceDefinition dsDef = new RS.DataSourceDefinition();

dsDef.CredentialRetrieval = RS.CredentialRetrievalEnum.Integrated;

//Это в случае логина на SQL через стандартную безопасность:

//dsDef.UserName = "sa"; dsDef.Password = "Password"; dsDef.CredentialRetrieval = RS.CredentialRetrievalEnum.Store;

dsDef.ConnectString = connectionString;

dsDef.Enabled = true;

dsDef.EnabledSpecified = true;

dsDef.Extension = "SQL";

dsDef.ImpersonateUser = false;

dsDef.ImpersonateUserSpecified = true;

dsDef.Prompt = null;

dsDef.WindowsCredentials = false;

rs.CreateDataSource(tempDataSourceName, parentFolderForTempFolder + rsTempFolder, true, dsDef, null);

Console.WriteLine("Создали в ней источник данных {0}", tempDataSourceName);

Console.WriteLine();

//Открываем xml-файл со списком отчетов и проверяем его на соответствие схеме

XmlDocument reports = new XmlDocument();

XmlSchemaSet sch = new XmlSchemaSet(); sch.Add("", currentPath + Path.DirectorySeparatorChar + exeName + ".xsd");

reports.Schemas = sch; bool schemaOK = true;

ValidationEventHandler validator =

delegate(object sender, ValidationEventArgs e)

{

schemaOK = false; Console.WriteLine(e.Message);

//throw new XmlException(e.Message);

};

reports.Load(currentPath + Path.DirectorySeparatorChar + exeName + ".xml");

reports.Validate(validator); if (!schemaOK) goto end;

//каждый из заданных файлов загружаем на Report Server и выполняем отчет в Excel

foreach (XmlElement report in reports.GetElementsByTagName("Report"))

{

//Прочитали содержимое rdl-файла

string reportName = report.SelectSingleNode("RDLFile").InnerText;

FileStream fs = File.OpenRead(reportName);

byte[] reportDefinition = new byte[fs.Length];

fs.Read(reportDefinition, 0, (int) fs.Length); fs.Close();

//Сделали upload

reportName = Path.GetFileNameWithoutExtension(reportName);

rs.CreateReport(reportName, parentFolderForTempFolder + rsTempFolder, true, reportDefinition, null);

reportName = parentFolderForTempFolder + rsTempFolder + '/' + reportName;

Console.WriteLine("Продеплоили отчет {0}", reportName);

//Присвоили отчету ранее созданный shared источник данных

foreach (RS.DataSource dataSource in rs.GetItemDataSources(reportName))

{

if (dataSource.Item.GetType() != typeof(RS.InvalidDataSourceReference)) continue;

RS.DataSource ds = new RS.DataSource();

RS.DataSourceReference dsRef = new RS.DataSourceReference();

dsRef.Reference = parentFolderForTempFolder + rsTempFolder + "/" + tempDataSourceName;

ds.Item = dsRef; ds.Name = dataSource.Name;

rs.SetItemDataSources(reportName, new RS.DataSource[] { ds });

}

Console.WriteLine("Поменяли у него источник данных");

//Выполняем отчет и экспортируем результат в Excel

rsExec.LoadReport(reportName, null);

string extension = String.Empty, mimeType = String.Empty, encoding = String.Empty;

RSExec.Warning[] warnings = null; string[] streamIDs = null;

//Параметры

XmlNodeList parameters = report.SelectNodes("Parameters/Parameter");

if (parameters.Count > 0)

{

Console.WriteLine("Параметры отчета:");

RSExec.ParameterValue[] reportParams = new RSExec.ParameterValue[parameters.Count];

for (int i = 0; i < parameters.Count; i++)

{

reportParams[i] = new RSExec.ParameterValue();

reportParams[i].Name = parameters[i].Attributes["Name"].Value;

reportParams[i].Value = parameters[i].SelectNodes("Value")[0].InnerText;

Console.WriteLine("{0} = {1}", reportParams[i].Name, reportParams[i].Value);

}

rsExec.SetExecutionParameters(reportParams, null);

}

byte[] results;

results = rsExec.Render("EXCEL", null, out extension, out mimeType, out encoding, out warnings, out streamIDs);

reportName = report.SelectSingleNode("ExportTo").InnerText;

fs = File.OpenWrite(reportName);

fs.Write(results, 0, results.Length); fs.Close();

Console.WriteLine("Выполнили его и сохранили результат в {0}", reportName);

Console.WriteLine();

}

end:

//Удаляем временный фолдер

rs.DeleteItem(parentFolderForTempFolder + rsTempFolder);

Console.WriteLine("Закончили. Прибрались за собой.");

Console.WriteLine("\nPress any key to continue or any other key to stop");

Console.ReadKey();

}

Скрипт 3

Результатом выполнения является набор указанных экселей

рис.5

с выполненными отчетами. Пример:

рис.6

Или вот, кстати, отчет, выводящий перечень всех запущенных трасс c их характеристиками (см. пред.пост «Программно сгенерить трассу профайлера»):

рис.7

Автоматическое выполнение отчетов

Additional resources