Datafly.pro

Reporting Services conserve les statistiques d'utilisation dans sa base de données pendant 60 jours par défaut. La première opération va être d'allonger cette durée de rétention en mettant à jour la table ConfigurationInfo, comme par exemple avec la requête suivante qui passe à 600 jours :
 

USE ReportServer -- sélectionner la base système de Reporting Services
SELECT * FROM [dbo].[ConfigurationInfo] WHERE Name = 'ExecutionLogDaysKept'
UPDATE dbo.ConfigurationInfo SET value = '600' WHERE Name = 'ExecutionLogDaysKept'
SELECT * FROM [dbo].[ConfigurationInfo] WHERE Name = 'ExecutionLogDaysKept'

Constituons maintenant notre tableau de bord avec Excel et PowerPivot (pour les paresseux, vous trouverez le lien vers le fichier Excel à la fin du post) :
Dans Excel, lançons l'interface de PowerPivot,

et connectons nous à la base ReportServer de notre instance SQL Server.

Nous devons interroger les statistiques d'exécution avec une requête.

SQL 2008 R2 et les versions ultérieures nous offrent la vue ExecutionLog3 que nous utilisons dans cet exemple. Pour des versions plus anciennes, vous pouvez utiliser ExecutionLog2 (disponible depuis 2008) ou ExecutionLog (disponible depuis 2000).

La requête est la suivante :

SELECT  CAST(CONVERT(VARCHAR(8), TimeStart, 112)
+ LEFT(CONVERT(VARCHAR(8), TimeStart, 108), 2) AS INT) AS TimeKey
, CASE WHEN LEN(ItemPath) - CHARINDEX('/', REVERSE(ItemPath)) = 0
THEN ''
ELSE SUBSTRING(ItemPath, 2,
LEN(ItemPath) - CHARINDEX('/', REVERSE(ItemPath))
- 1)END AS Folder
      , SUBSTRING(ItemPath,
LEN(ItemPath) - CHARINDEX('/', REVERSE(ItemPath)) + 2,
LEN(ItemPath)) AS Report
, ItemPath AS Path
, [UserName] AS UserName
, [Parameters] AS Parameters
, [TimeStart] AS StartTime
, [TimeEnd] AS EndTime
, DATEDIFF(ms, TimeStart, TimeEnd) AS TotalDuration_ms
, [TimeDataRetrieval] AS DataRetrievalTime_ms
, [TimeProcessing] AS ProcessingTime_ms
, [TimeRendering] AS RenderingTime_ms
, [ByteCount] AS Bytes
, [RowCount] AS NbLines
FROM    ExecutionLog3 WITH ( NOLOCK )
WHERE   ItemAction = 'Render'
        AND LEN(RTRIM(itempath)) > 0

Au tableau qui nous est renvoyé dans l'interface PowerPivot, nous allons ajouter quelques calculs :

• Sous la colonne TimeKey, le nombre d'exécutions avec la formule : Execution Count:=COUNTA([TimeKey])
• Sous la colonne UserName, le nombre d'utilisateurs avec la formule : Users Count:=DISTINCTCOUNT([UserName])
• Sous la colonne TotalDuration_ms, la durée d'exécution moyenne : Average Duration ms:=AVERAGE([TotalDuration_ms])
• Sous la colonne DataRetrievalTime_ms, la durée de requête moyenne : Average Data Retrieval Time ms:=AVERAGE([DataRetrievalTime_ms])
• Sous la colonne ProcessingTime_ms, la durée de traitement moyen : Average Processing Time ms:=AVERAGE([ProcessingTime_ms])
• Sous la colonne RenderingTime_ms, la durée de rendu moyen : Average Rendering Time ms:=AVERAGE([RenderingTime_ms])

Nous allons maintenant ajouter une table de dates que nous pourrons joindre au journal d'exécution. Au point où nous en sommes, autant demander à SQL Server de la générer. Pour cela, sélectionnez la connexion existante à SQL Server, et nommez la nouvelle requête "TimeCalendar"

Voici la requête qui nous génère une table de temps (dates et heures, du 1er janvier de l'année en cours jusqu'au 31/12 de l'année suivante) :

set datefirst 1
set dateformat 'ymd';
WITH CTETemps AS
  (
    SELECT cast('2014-01-01' AS datetime) DateHeure
    UNION ALL
    SELECT DATEADD(hh,1,DateHeure) 
    FROM CTETemps
    WHERE DATEADD(hh,1,DateHeure) < 
    DATEADD(yy,1,
    DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 
    )
  )
 SELECT
  CAST(CONVERT(VARCHAR(10),DateHeure,112)+RIGHT('0'+CAST(DATEPART(hh,DateHeure) AS VARCHAR(2)),2) AS INT) AS TimeKey,
  RIGHT('0'+CAST(DATEPART(hh,DateHeure) AS VARCHAR(2)),2) AS Hour,
  CONVERT(VARCHAR(10),DateHeure,120) AS Date,
  CONVERT(VARCHAR(7),DateHeure,120) AS Month,
  LEFT(cast(year(DateHeure) as char(4))+' '+'Q'+ DATENAME(quarter,DateHeure),7)  AS Quarter,
  cast(year(DateHeure) as nchar(4))+' '+ case when month(DateHeure) < 7 then 'S1 ' else 'S2 ' end as Semestrer,
  cast(year(DateHeure) as nchar(4)) as Year,
  cast(year(DateHeure) as nchar(4))+' W'+ CAST(datepart(wk,DateHeure) AS NVARCHAR(2)) AS WeekOfYear,
  datepart(dw,DateHeure) as DayOfWeek_Num,
  CASE datepart(dw,DateHeure) WHEN 1 THEN 'monday' WHEN 2 THEN 'tuesday' WHEN 3 THEN 'wednesday' 
  WHEN 4 THEN 'thursday' WHEN 5 THEN 'friday' WHEN 6 THEN 'saturday' WHEN 7 THEN 'sunday' END
  AS DayOfWeek,
  CASE datepart(dw,DateHeure) WHEN 1 THEN 'lundi' WHEN 2 THEN 'mardi' WHEN 3 THEN 'mercredi' 
  WHEN 4 THEN 'jeudi' WHEN 5 THEN 'vendredi' WHEN 6 THEN 'samedi' WHEN 7 THEN 'dimanche' END
  AS DayOfWeek_French,
  CASE WHEN DATEPART(hh,DateHeure) BETWEEN 8 AND 19 THEN 'Y' ELSE 'N' END AS OpeningHours,
  case when (datepart(dw,DateHeure) > 5) then 'N' else 'Y' end as OpeningDay --,
FROM
    CTETemps  
OPTION (MAXRECURSION 0)

Une fois la table renvoyée, nous pouvons trier certaines colonnes pour certains des rapports : sélectionner la colonne "DayOfWeek", et cliquez sur le bouton "Trier par colonne". Sélectionnez la colonne "DayOfWeek_num" comme colonne de tri.

Faites la même chose pour la colonne "DayOfWeek_French".

Il ne nous reste plus qu'a établir la relation entre nos deux tables dans PowerPivot. Pour cela afficher le diagramme en cliquant sur le bouton "Vue de Diagramme", puis établissez la relation en cliquant sur la colonne TimeKey de ExecutionLog et en la faisant glisser sur la colonne TimeKey de TimeCalendar.

Notre modèle PowerPivot est prêt. Nous pouvons maintenant créer un tableau croisé dynamique (bouton "Tableau Croisé Dynamique") et/ou quitter PowerPivot et insérer de nouvelles feuilles PowerView dans notre classeur Excel.

Quelques exemples de rapports PowerView à partir de ce modèle (avec des données d'exemple) :

Le lien vers le classeur Excel finalisé est ici : Reporting_Services_execution_analysis.xlsx. Pour l'utiliser, changer la source de données dans PowerPivot pour l'adapter à votre environnement : Ouvrir l'interface PowerPivot, puis cliquer sur le bouton "Connexions Existantes", sélectionner la connexion, puis cliquer sur "Modifier".

Bonne analyse de votre serveur Reporting Services...