Taille des modèles dans Power BI Report Server

Dans cet article je vais m’attarder sur l’une de ces problématiques de monitoring à savoir la taille des modèles. Je présenterai par la même occasion quelque prérequis à la compréhension de l’article sur l’architecture sous-jacente du PBIRS et rappellerai quelques bonnes pratiques.

1 – Introduction

La taille d’un fichier PBIX est différente de la taille de son modèle tabulaire lorsqu’il est en mémoire : En effet, il y a plusieurs étapes de compression comme on peut le voir sur le graphique ci-dessous :

Rappel de l’architecture PBIRS :

Derrière PBIRS se « cache » une instance SSAS tabulaire hébergeant les modèles Power BI : Lorsqu’un utilisateur consulte un rapport, le fichier PBIX est décompressé dans la mémoire et une copie du modèle de données « importé » est glissée dans une instance SSAS « cachée ». De plus l’instance SSAS a besoin lui aussi de mémoire pour s’exécuter et au moins du double de la taille du modèle pour le traiter. En effet durant le traitement une copie du modèle est créée à côté pour permettre au modèle existant de rester disponible. Sans oublier la mémoire également nécessaire pour répondre aux besoins des requêtes en cours d’exécution sur ces données. Et plus vous aurez d’utilisateurs qui interrogent un PBIX (modèle), plus vous aurez besoin d’espace RAM pour ces requêtes.

Vous devez donc vous assurer que votre serveur PBIRS a suffisamment de RAM pour répondre à ces différents besoins. Des paramètres au niveau de l’instance PBIRS permettent de recycler les modèles non utilisés :

Depuis la version d’octobre 2018, il est possible d’uploader des fichiers jusqu’à 2 Go : https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh-troubleshoot#configuration-settings

Pour planifier les ressources nécessaires de votre serveur PBIRS, Capacity Planning : https://docs.microsoft.com/en-us/power-bi/report-server/capacity-planning

 

2 – Supervision

Maintenant que les présentations sont faites, regardons comment superviser la taille des modèles dans PBIRS.

Depuis le portail il a possible de voir la taille de mes fichiers PBIX stockés dans notre base de données ReportServer :

Autre solution pour lister la taille de tous mes modèles depuis la base de données ReportServer :

SELECT
c.ItemID,
c.[Path],
c.[Name] AS ReportName,
ISNULL(cp.[Name], 'Root') AS ParentItem,
CASE c.[Type]
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'Resources'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
WHEN 6 THEN 'Report Model'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared dataset'
WHEN 11 THEN 'KPI Card'
WHEN 13 THEN 'PowerBI'
ELSE CAST(c.[Type] AS VARCHAR(10))
END AS ItemType,
c.Property,
c.[Description],
c.[Hidden],
cu.UserName AS CreatedBy,
c.CreationDate,
mu.UserName AS ModifiedBy,
c.ModifiedDate,
ISNULL(CAST(c.ContentSize AS FLOAT) / CAST((1024 * 1024) AS FLOAT), 0) AS ContentSizeMb
FROM dbo.[Catalog] c
LEFT OUTER JOIN dbo.[Catalog] cp ON c.ParentID = cp.ItemID
LEFT OUTER JOIN dbo.Users cu ON c.CreatedByID = cu.UserID
LEFT OUTER JOIN dbo.Users mu ON c.ModifiedByID = mu.UserID
WHERE LEFT(c.[Path], 14) <> '/Users Folders';

Source disponible sur mon GitHub : PBIRS_ModelSize.sql

Résultat :

Et pourquoi ne pas l’analyser depuis Power BI :

Ces modèles sauvegardés dans la base de données ReportServer peuvent n’occuper aucune place dans l’instance SSAS Tabulaire « caché ». Rappelez-vous du début de l’article, si un modèle n’est pas utilisé, ni traité il peut être évincé de l’instance SSAS.

Il serait donc pertinent de superviser quelles sont les modèles chargés dans l’instance SSAS tabulaire. Pour ce faire nous pouvons nous y connecter depuis différents outils à partir de l’adresse « localhost:5132 ».

J’ai donc créé le script PowerShell suivant qui exporte ces informations dans un fichier CSV (à exécuter en tant qu’Administrator) :


# Romain Casteres - Analyzing PBIRS Tabular Model Size
# SSAS Instance for PBIRS : "localhost:5132"
# Export to CSV

Param($ServerName="localhost:5132")
$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$outputfile = "C:\temp\PBIRS_ModelSize.csv"
if (Test-Path $outputfile) {
Remove-Item $outputfile
}

$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
Write-Output (“Server ‘{0}’ not found” -f $ServerName)
break
}

$sum=0
foreach ($d in $server.Databases ){
New-Object PSObject -Property @{
Name = $d.Name;
ItemID = $d.Name.SubString(0,36); # Mapping to the ItemID column from Catalog table
SizeMB = [math]::Round($d.EstimatedSize/1024/1024,2);
} | export-csv -Path $outputfile -NoTypeInformation -Append
}

Source disponible sur mon GitHub : SSAS_ModelSize_CSV.ps1

Résultat :

Et pourquoi ne pas l’analyser depuis Power BI en corrélant ces informations avec notre précédente requête :

Via SSMS (à exécuter en tant qu’Administrator) :

Via DAX Studio (à exécuter en tant qu’Administrator) :

Pour aller plus loin, ces mêmes outils peuvent être utilisés pour analyser le serveur PBIRS, pourquoi ne pas utiliser le Profiler ou encore mieux les XEvent sur l’instance SSAS « caché »…

À titre de bonnes pratiques, il faut éviter de programmer le rafraichissement des modèles aux mêmes horaires. Pour vérifier cela, vous pouvez analyser l’historique depuis la base de données ReportServer :

SELECT
SUB.SubscriptionID,
SUB.Report_OID,
CAT.Path,
CAT.Name,
USR.username,
SUB.Description AS SchedulName,
SUB.EventType,
HIST.SubscriptionHistoryID,
HIST.StartTime,
Hist.EndTime,
DATEDIFF(SECOND,HIST.StartTime,Hist.EndTime) AS Dure,
HIST.Status,
HIST.Message,
CASE
WHEN HIST.Status = 0 THEN 'Data refresh finished sucessfully'
WHEN HIST.Status = 0 THEN 'Data refresh is in progress'
ELSE 'Error during refresh'
END AS RESULT
FROM
dbo.Subscriptions SUB
INNER JOIN dbo.Users USR ON USR.UserID = SUB.OwnerID
INNER JOIN SubscriptionHistory HIST ON HIST.SubscriptionID = SUB.SubscriptionID
INNER JOIN dbo.Catalog CAT ON CAT.ItemID = SUB.Report_OID

Source disponible sur mon GitHub : ScheduleReport_History.sql

Ou encore à travers un rapport paginé (SSRS) sur la base de données MSDB contenant les informations sur les Job SQL Server Agent (créé lors de la création d’une planification côté PBIRS) :

Pensez a bien configurer le serveur PBIRS et de ses paramètres :
– Capacity Planning : https://docs.microsoft.com/en-us/power-bi/report-server/capacity-planning
– Limiter la taille des modèles « MaxFileSizeMb »
– Scale Out si necessaire : https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/configure-a-native-mode-report-server-scale-out-deployment?view=sql-server-ver15
– Noter que sur une architecture en Scale-Out un des serveurs peut être en charge du rafraichissement : “With this setting, you could define one or more servers to be the front end server to handle on demand reports, and have another set of servers to only be used for scheduled refresh.” https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh-troubleshoot#memory-pressure
– Troubleshoot scheduled refresh in Power BI Report Server : https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh-troubleshoot

Les bonnes pratiques relatives aux performances Power BI sont aussi importantes : https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance