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

Comments

• Anonymous
  October 05, 2015
  Merci beaucoup!

• Anonymous
  October 05, 2015
  Merci de ton retour Sonia :-)

• Anonymous
  February 24, 2016
  Bonjour, je découvre ton article, et je te remercie du partage. Très intéressant tout ça.

• Anonymous
  April 29, 2016
  Merci beaucoup