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

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

3 thoughts on “Calculate Size of Dimensions for a SSAS Database”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s