By Richie Lee

Introduction

I’m still using my newly acquired string function skills to aid me in writing better scripts. This post I’m focusing on PowerShell. As an added bonus, I’m also providing a script that will get the total size of the dimensions within an analysis services database, although it can be altered quite easily. Unlike SQL Databases, it’s not possible to determine the size using built in DMV’s. Instead, you need to get the size of the dimension from the folder that resides within the database folder on the disk.

Q: How Long Is A Piece of String? A: It Can Be As Long As You Want It To Be

Using get-childitem, you’ll get back information about the objects in the current location, rather like “dir” in cmdline.

In the script we’ll set the location of the .db folder in a variable and run the get-childitem cmdlet on that. Then we can manipulate that as we please. Below I am recursing through the $startfolder including only items that begin with “.dim”, as the dimensions are stored in their own separate folders.

$startFolder = "I:\Cubes\AdventureWorks2012.0.db"

$colItems = (Get-ChildItem $startFolder -recurse -include *.dim | Where-Object {$_.PSIsContainer -eq $True} | Sort-Object)

Then for each separate folder I enter a loop to get the size of the folder (lines 8 to 24 in the full script at the bottom).

foreach ($i in $colItems)
{
$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
}
}

Before that though I’m manipulating the folder directory path ( line 7). Let’s look at the line where I am using string manipulation in detail:


$DimensionName = $i.ToString().Substring($i.ToString().LastIndexOf("\"), $i.ToString().Length - ($i.ToString().LastIndexOf("\"))).Trim("\0.dim")

What is happening here is that, rather than return the entire folder directory path (in lines 12, 17 and 22), so that the name of the dimension is clear I just want to return the name of the folder that the dimension data is located. I’m converting the variable $i, which is a single object of the file directory [System.IO.DirectoryInfo], into a string. Now we can have some fun with it. We want to return a substring, which requires a starting point and a length. Our starting point is the last “" as the rest of the string will be the folder/dimension name. The length is calculated by taking the total length of the string, and subtracting from this again the last index of “". So for the dimension “Account” the starting position is 9, and the length works out as 32. so that is

w/r/t finding Dimension sizes, I’ve added some if statements and colour coding as you may have wildly different sizes for your dimensions. I’ve also rounded up the results to 2dp.


$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

Summary

It’s easy to know that a substring has a start and length if that is always static, but if that changes dynamically then this requires the use of multiple functions together and also thinking a little differently. Throughout this mini series on string functions, what I have wanted to do has largely been the same, ie take a string and manipulate it so that dynamically what I pump into it finds the pattern and extracts the substring. It’s a case of trial and error, but at the end of it you have a basic pattern and then it’s just a case of understanding how a language works to manipulate strings.