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 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://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 : http://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 ('http://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 : http://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=http://localhost/reportserver set varReportFolder=ProjectName set varDatasetFolder=ProjectName set varDataSourceFolder=ProjectName set varDataSourcePath=ProjectName set varReportName= set varReportFilePath=C:\ProjectName
rs.exe -i
|
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