Monitorando a utilização dos relatórios pelo log do Reporting Services

É comum encontrarmos nas empresas uma infinidade de relatórios. Porém, você já parou para pensar quantos realmente são utilizados? E quais demoram mais tempo para carregar, ou seja, tem uma performance pior?

No Reporting Services, ferramenta Microsoft de construção de relatórios, é possível ter acesso a este tipo de informação que pode ser bastante útil para entendermos quais são os relatórios mais importantes e quais são aqueles que podem ser excluídos ou modificados. Para isso, vamos utilizar as informações do log de execução que ficam registradas em algumas tabelas do banco de dados do Reporting Services. A ideia deste post é mostrar basicamente como criar um relatório de monitoramento de utilização, semelhante ao da imagem abaixo:

relatorio_utilizacao_relatorios

Banco de dados do Reporting Services

Até a versão do SQL Server 2016 (última lançada quando este post foi escrito), o Reporting Services possui duas opções de instalação: modo nativo e modo integrado ao Sharepoint. Para ambas as opções, após a instalação/ configuração, o Reporting Services gera um banco de dados (que pode ter algumas variações, mas normalmente é chamado de “ReportServer” ou “ReportingService”). É este banco de dados que armazena os logs de uso dos objetos do Reporting Services em geral (data sources, datasets e relatórios).

Por padrão, este banco de dados armazena um histórico de 60 dias de dados de utilização nas suas tabelas. Porém, esta propriedade pode ser alterada através da instância do Reporting Services, conforme descrito neste link.

Consultas SQL para retornar as informações

Para facilitar o acesso aos dados, o banco de dados do Reporting Services possui algumas views que já retornam um conjunto de informações relevantes. A Microsoft recomenda a utilização da view ExecutionLog3, que é a mais recente e possui mais campos. Porém, por questões de compatibilidade, ainda estão disponíveis as views ExecutionLog2 e ExecutionLog, que possuem dados bem semelhantes.

No nosso exemplo, como queremos retornar informações específicas dos relatórios e a view ExecutionLog possui o campo ReportID, vamos utilizá-la para simplificar a consulta.

Abaixo geramos duas consultas que retornam os dados que precisamos para gerar o nosso relatório:

  • Query 1: quantidade de execuções de cada relatório
SELECT
  c.Name,
  c.[Path],
  COUNT(*) AS TimesRun
FROM [dbo].[ExecutionLog] AS l
INNER JOIN [dbo].[Catalog] AS c
  ON l.ReportID = C.ItemID
WHERE c.Type = 2
GROUP BY l.ReportId,
         c.Name,
         c.[Path]
ORDER BY TimesRun DESC
  • Query 2: tempo médio e data da última execução de cada relatório
SELECT
  ReportID,
  C.Name,
  CAST(AVG(
  (TimeDataRetrieval + TimeProcessing + TimeRendering) / 1000.0)
  AS decimal(10, 2)) AS TotalRenderingTime,
  CAST(MAX(l.TimeStart) AS date) AS [LastRun]
FROM dbo.ExecutionLog AS l
INNER JOIN [dbo].[Catalog] AS c
  ON l.ReportID = C.ItemID
WHERE c.Type = 2
GROUP BY l.ReportId,
         C.Name
ORDER BY TotalRenderingTime DESC

 

Criação do relatório

Para gerar o nosso relatório, iremos utilizar o SQL Data Tools, ferramenta que é um template do Visual Studio para a criação de projetos de banco de dados e BI (Integration Services, Analysis Services e Reporting Services). Vamos selecionar um novo projeto do tipo “Report Server Project” (imagem abaixo):

sqldatatools_ssrs

Em seguida vamos adicionar os seguintes objetos ao nosso projeto:

Data source: conexão com o banco de dados do Reporting Services (SSRS_db.rds);

Datasets: criaremos dois datasets (“DataSet_ExecutionTime.rsd” e “DataSet_TimesRun.rsd”) que terão as consultas “Query 1” e “Query 2”;

Relatório: criaremos um relatório chamado “Report Usage.rdl”, e adicionaremos os dois “shared datasets” ao nosso relatório. Assim, poderemos gerar os seguintes objetos:

  • Gráfico de barras contabilizando a quantidade de execuções de cada relatório;
  • Tabela com tempo médio de renderização de cada relatório e a última vez que ele foi executado.

A imagem abaixo mostra como ficou o projeto após a criação destes objetos:

ssrs_project

 

Por fim, vamos realizar o deploy do nosso projeto para o servidor de relatórios (imagem a seguir), definindo nas propriedades do projeto as URL’s do site do Reporting Services ou do Sharepoint onde ficarão armazenados os objetos do projeto. Assim conseguiremos acompanhar em tempo real (acessando o site onde se encontra o relatório) a frequência de uso dos relatórios e a duração média da cada execução.

ssrs_deploy

A grande vantagem dessa solução é a possibilidade de criar outras formas de visualização personalizadas, modificando as queries para utilizar outras views/ tabelas trazendo outros dados mais relevantes para o tipo de monitoramento que você deseja realizar.

Referências:

SSRS Report Usage Queries

How to Display Report Execution Time in SQL Server Reporting Services (SSRS)

Report Server ExecutionLog and the ExecutionLog3 View

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s