Calculate Size of Dimensions for a SSAS Database
I posted this Powershell script in my previous post, however I feel it’s worth sharing on it’s own. For full details read my previous post.
Unlike SQL Server databases, there are no DMV’s or sys tables in SSAS that will provide us with size metrics. I have written about sys tables for SQL before here and here. Instead, you need to get the size of the dimension from the folder that resides within the database folder on the disk. This script will get the total size of the dimensions within an analysis services database.
If the use of WriteHost offends you read my defence on this practice.
$startFolder = "I:\Cubes\AdventureWorks2012.0.db"
Write-host "Searching" $startfolder", please wait..." -foregroundcolor yellow -backgroundcolor darkcyan
$colItems = (Get-ChildItem $startFolder -recurse -include *.dim | Where-Object {$_.PSIsContainer -eq $True} | Sort-Object)
Write-host "Calculating sizes of Dimensions, please wait..." -foregroundcolor yellow -backgroundcolor darkcyan
foreach ($i in $colItems)
{
$DimensionName = $i.ToString().Substring($i.ToString().LastIndexOf("\"), $i.ToString().Length - ($i.ToString().LastIndexOf("\"))).Trim("\0.dim")
$subFolderItems = (Get-ChildItem $i.FullName | Measure-Object -property length -sum)
$DimensionSize = ($subFolderItems.sum/ 1KB)
if ($DimensionSize -lt 1024)
{
$DimensionSizeInKb = $DimensionName + " -- " + "{0:N2}" -f $DimensionSize + " KB"
write-host $DimensionSizeInKb -foregroundcolor white -backgroundcolor darkgreen
}
elseif ($subFolderItems.sum / 1MB -lt 1024)
{
$DimensionSizeInMb = $DimensionName + " -- " + "{0:N2}" -f ($subFolderItems.sum / 1MB) + " MB"
write-host $DimensionSizeInMb -foregroundcolor white -backgroundcolor darkyellow
}
else
{
$DimensionSizeInGb = $DimensionName + " -- " + "{0:N2}" -f ($subFolderItems.sum / 1GB) + " GB"
write-host $DimensionSizeInGb -foregroundcolor white -backgroundcolor darkred
}
}
write-host "all sizes displayed in this format are in KB" -foregroundcolor white -backgroundcolor darkgreen
write-host "all sizes displayed in this format are in MB" -foregroundcolor white -backgroundcolor darkyellow
write-host "all sizes displayed in this format are in GB" -foregroundcolor white -backgroundcolor darkred