Rétro documentation SSRS et analyse des liens morts entre les rapports

Le nombre de rapports SSRS ne cesse d’augmenter dans les différentes sociétés que je rencontre, cependant le nombre de projets documentés reste très limité.

L’objet de cet article est de vous fournir un certain nombre de requêtes vous permettant de générer une documentation sur vos projets Reporting Services.

L’idée de cet article m’est venue en analysant les N rapports d’un projet. Voici les quelques problématiques auxquelles j'ai été confrontées : Retrouver les liens morts entre les différents rapports, documenter la solution et simplifier les déploiements ainsi que la configuration des rapports.

 

1 - Introduction

A l’installation de Reporting Services, deux bases de données sont installées ReportServer et ReportServerTempDB.

La base de données ReportServer stocke le contenu suivant :

  • Les objets gérés par le serveur comme les rapports, les rapports liés, les sources de données, les modèles de rapports, les dossiers … ainsi que toutes les propriétés et les paramètres de sécurité associés à ces éléments.
  • Les définitions des souscriptions et leurs planifications.
  • Les Snapchots et l’historique des rapports
  • Les propriétés du système et les paramètres de sécurité
  • Les logs d’exécution
  • Les clés symétriques et connexions cryptées ainsi que les références des sources de données

La base de données ReportServerTempDB quant à elle gère les données temporaires liées aux sessions, au cache des données, les tables de travail temporaires. Un processus automatique s’occupe de supprimer les anciens éléments inutilisés de cette base.

Afin de documenter l’environnement SSRS, sa configuration, les différents rapports publiés ainsi que les souscriptions nous effectuerons des requiêtes sur les différentes tables de la base ReportServer.

 

2 - Informations sur l’instance SSRS

2.1 - Informations sur l’instance

SELECT

       MachineName, 

       InstanceName,

       Client

FROM dbo.Keys

WHERE MachineName IS NOT NULL

Remarque : La table Keys contient aussi les informations sur les clés de chiffrement et sur l’installation de l’instance.

2.2 - Informations sur la configuration de l’instance

SELECT

       Name,

       Value

FROM dbo.ConfigurationInfo

Résultat :

Voici la liste des différents paramètres : https://msdn.microsoft.com/en-us/library/bb934303.aspx 

2.3 - Informations sur les utilisateurs et leurs rôles

SELECT

       CAT.name  AS ReportName,

       USR.username  AS UserName,

       USR.authtype  AS AuthType,

       ROL.rolename  AS RoleName,

       ROL.Description AS Description

FROM dbo.users USR

       INNER JOIN dbo.policyuserrole PLU ON USR.userid = PLU.userid

       INNER JOIN dbo.policies POL ON POL.policyid = PLU.policyid

       INNER JOIN dbo.roles ROL ON ROL.roleid = PLU.roleid

       INNER JOIN dbo.catalog CAT ON CAT.policyid = POL.policyid

WHERE CAT.TYPE = 2

Résultat :

2.4 – Informations sur l’utilisation du cache

SELECT

       CAT.name AS ReportName,

       CAC.cacheexpiration AS CacheExpiration,

       CAC.expirationflags AS ExpirationFlags,

FROM 

       dbo.cachepolicy CAC

       INNER JOIN dbo.catalog CAT ON CAT.itemid = CAC.reportid

 

2.5 – Informations sur les souscriptions et leurs planifications

SELECT

       CAT.Name AS ReportName,

       SUB.Description AS SubscriptionName,

       SUB.EventType AS EventType,

       SUB.LastStatus AS LastStatus,

       SUB.LastRunTime AS LastRunTime,

       SUB.Parameters AS Parameters,

       SCH.Name AS ScheduleName,

       SCH.Type AS ScheduleType,

       SCH.EventType AS ScheduleEventType

FROM

       dbo.Subscriptions SUB

       INNER JOIN Catalog CAT ON SUB.Report_OID = CAT.ItemID

       INNER JOIN ReportSchedule RSC ON RSC.ReportID = CAT.ItemID AND RSC.SubscriptionID = SUB.SubscriptionID

       INNER JOIN Schedule SCH ON RSC.ScheduleID = SCH.ScheduleID

 

3 - Informations sur les rapports

3.1 - Liste des rapports et leurs paramètres

SELECT

       Name AS ReportName,

       Path AS ReportPath,

       CreationDate AS CreationDate,

       ModifiedDate AS ModifiedDate,

       Property AS Property,

       Hidden AS Hidden,

       Parameter AS Parameter,

       Convert(XML,(Convert(VARBINARY(MAX),Content))) AS XMLRdl

FROM dbo.Catalog

WHERE Type = 2

Résultat :

Remarques :

  • Le champ XMLRdl affiche l’ensemble du .RDL au format XML
  • Le champ Path détermine l’emplacement du rapport par rapport au dossier racine
  • Le champ Hidden détermine si le rapport sera caché ou non dans la liste des objets dans Report manager

En effet il est possible de cacher à l’utilisateur des objets dans Report manager :

3.2 – Liens morts entre les rapports

Une problématique récurrente à SSRS : « J’ai une centaine de rapports dans mon projet, certains rapports ont changés de noms, … Comment retrouver les liens morts entre les différents rapports ? »

Voici la solution :

;WITH XMLNAMESPACES (

       DEFAULT 'https://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',       

       'https://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd

), ReportInfo AS (

       SELECT

             CAT.Path AS ReportPath,

             CAT.name AS ReportName,

             TAB.value('data(ReportName)[1]', 'varchar(255)') AS Link,

             REPLACE(CAT.Path, CAT.name, '') + TAB.value('data(ReportName)[1]', 'varchar(255)') AS PathLink

       FROM 

             dbo.catalog CAT

             CROSS APPLY (SELECT CONVERT(XML, CONVERT(VARBINARY(MAX), content))) AS REP (reportxml)

             CROSS APPLY REP.reportxml.nodes('//*:Textbox/ActionInfo/Actions/Action/Drillthrough') NOD (TAB)

       WHERE CAT.Type = 2

)

SELECT

       ReportPath,

       ReportName,

       Link,

       PathLink,

       COUNT(1) AS NbLinks 

FROM ReportInfo

WHERE PathLink NOT IN (SELECT path FROM dbo.catalog)

GROUP BY

       reportpath,

       reportname,

       Link,

       PathLink

ORDER BY reportpath

Résultat :

Remarques : Ici le rapport « Product Line Sales » contient un lien (NbLinks) vers le rapport « Employee Sales Summary SQL2008R2 » situé dans le dossier « /AdventureWorks Sample Reports/ » qui n’existe pas ou plus.

L’Addin Excel NodeXL (Excel 2007 à 2016) n’est plus à présenter, il permet d’analyser sous forme de graph tout type de données. (Mon article de présentation de NodeXL : https://www.pulsweb.fr/nodexl/).

Voici le résultat d’une analyse sur une partie des rapports d’un projet SSRS :

Analyse :

  • Vertex 1 correspond au rapport contenant le lien vers le rapport Vertex 2
  • La grosseur du lien correspond au nombre de liens présents dans le rapport. Exemple le rapport « CM-Main » contient 8 liens vers le rapport « CM-Dashboard Hierarchy all Levels – Summary ».
  • La direction de la flèche indique le lien entre les rapports. Exemple le rapport « CM-main » contient un lien vers le rapport « CM-Dashboard SQL » et vice versa.
  • Le format de la flèche en continue ou en pointillé dépend de l’existence du rapport. Exemple le rapport « CM-Main » contient un lien vers le rapport « CM-Bad » or celui-ci n’existe pas.

 

Combiner le résultat de la précédente requête avec NodeXL permet d’avoir une vision complète des relations entre les différents rapports d’un projet, de vérifier qu’il n’y a pas de rapport isolé (sans aucun lien allant vers celui-ci), d’analyser les liens morts, …

 

3.3 – Informations sur les paramètres d’un rapport

WITH TMP AS (

       SELECT

             CAT.Name,

             CONVERT(XML,CAT.Parameter) AS XMLParameter

       FROM dbo.Catalog CAT

       WHERE CAT.Type  = 2 AND CAT.Name = 'Product Line Sales'

)

SELECT 

       VAL.value('Name[1]', 'VARCHAR(250)') AS ParameterName,

       VAL.value('Type[1]', 'VARCHAR(250)') AS ParameterType,

       VAL.value('Nullable[1]', 'VARCHAR(250)') AS ParameterNull,

       VAL.value('AllowBlank[1]', 'VARCHAR(250)') AS ParameterBlank,

       VAL.value('MultiValue[1]', 'VARCHAR(250)') AS ParameterMultiValue,

       VAL.value('UsedInQuery[1]', 'VARCHAR(250)') AS ParameterInQuery,

       VAL.value('Prompt[1]', 'VARCHAR(250)') AS ParameterPrompt,

       VAL.value('DynamicPrompt[1]', 'VARCHAR(250)') AS ParameterDynamic,

       VAL.value('PromptUser[1]', 'VARCHAR(250)') AS ParameterPromptUser,

       VAL.value('State[1]', 'VARCHAR(250)') AS ParameterState

FROM TMP

       CROSS APPLY XMLParameter.nodes('//Parameters/Parameter') PARAMXML (VAL)

Résultat :

 

3.4 - Liste des Data Sources et leurs rapports

SELECT

       DSR.Name AS DataSourceName,

       CASE

             WHEN DSR.Name IS NOT NULL THEN CAT.Name

             ELSE 'Shared Data Source'

       END AS DataSourceType,

       CLink.Name AS DataSource,

       CAT.Name AS ReportName

FROM

       dbo.DataSource DSR

       INNER JOIN dbo.Catalog CAT ON DSR.ItemID = CAT.ItemID

       LEFT JOIN dbo.Catalog CLink ON Clink.ItemID = DSR.Link

Résultat :

3.5 - Liste des sous rapports

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' as rdl),

TMP AS (

       SELECT

             CAT.Path,

             CONVERT(XML, CONVERT(VARBINARY(MAX), CAT.Content)) AS XMLColumn

       FROM dbo.Catalog CAT

       WHERE CAT.Type  = 2

)

SELECT

       Path as ReportPath,

       n.x.value('(//rdl:ReportName)[1]', 'nvarchar(256)') AS ReferencedReport

FROM TMP

       CROSS APPLY xmlColumn.nodes('//rdl:Subreport') n(x)

 

4 – Analyse des Logs

A partir de SQL Server 2008 R2, la vue ExecutionLog3 permet d’avoir des informations concernant l’utilisation des rapports SSRS.

SELECT

       ItemPath,

       UserName,

       Format,

       ItemAction,

       TimeStart,

       TimeEnd,

       Status,

       SUM(TimeDataRetrieval) AS TimeDataRetrieval,

       SUM(TimeProcessing) AS TimeProcessing,

       SUM(TimeRendering) AS TimeRendering,

       SUM(ByteCount) AS ByteCount,

       SUM([RowCount]) AS [RowCount]

FROM dbo.ExecutionLog3

GROUP BY

       ItemPath,

       UserName,

       Format,

       ItemAction,

       TimeStart,

       TimeEnd,

       Status

ORDER BY TimeStart DESC

Résultat :

Plus d’informations sur la vue ExecutionLog3 : https://msdn.microsoft.com/en-us/library/ms159110(v=sql.105).aspx

 

5 – Déploiement

Il existe plusieurs solutions pour déployer des rapports SSRS : SSDT (BIDS auparavant), Report Manager ou encore l’utilitaire RS.exe.

Lorsque plusieurs rapports sont à déployer, SSDT est bien pratique, mais celui-ci n’est pas toujours installé sur l’environnement cible ou un environnement ayant accès à l’environnement cible. Report Manager quant à lui ne permet pas l’Upload de plusieurs rapports en une fois ! La solution est alors d’utiliser RS.exe.

Le script RSS suivant permet d’uploader les rapports d’un dossier local : OneDrive

Après avoir téléchargé le .RSS, il faut créer et modifier le .Bat :

rem Source : https://www.sqlblogspot.com/2014/03/ssrs-deploymentcomplete-automation2012.html

rem 1 - Create a folder named “ProjectName” under this new folder

rem 2 - copy all the reports, datasource and dataset into the “ProjectName” folder

rem 3 - Modify 'ProjectName' Folder (Destination)

rem 4 - Modify 'C:\ProjectName' Reports path

set varServerPath=https://localhost/reportserver

set varReportFolder=ProjectName

set varDatasetFolder=ProjectName

set varDataSourceFolder=ProjectName

set varDataSourcePath=ProjectName

set varReportName=

set varReportFilePath=C:\ProjectName

rs.exe -i  Commonscript.rss -s %varServerPath% -v  ReportFolder="%varReportFolder%" -v DataSetFolder="%varDatasetFolder%"  -v DataSourceFolder="%varDataSourceFolder%" -v  DataSourcePath="%varDataSourcePath%" -v  ReportName="%varReportName%"   -v filePath="%varReportFilePath%" -e Mgmt2010

Le script RSS a été réalisé par Nishar, plus d’informations sur celui-ci à l’adresse suivante : SSRS Deployment–Complete Automation–2012 & 2008.

 

6 – Conclusion

Dans cet article nous avons vu quelques requêtes T-SQL permettant de documenter une instance Reporting Services, ses rapports et sa configuration.

Nous avons aussi vu une requête basique permettant d’auditer l’utilisation des rapports, leurs temps d’exécution …

Enfin le script de déploiement présenté permet de gagner du temps sur l’Upload d’une solution.

Sans oublier le fait qu’avec quelques requêtes T-SQL et NodeXL, il est possible d’avoir une vision complète sur les différents liens entre les rapports SSRS !

Romain Casteres

Premier Field Engineer – SQL & BI & Big Data