Using PowerShell to update the case of Attribute names

I had a question recently about how to quickly convert all the attributes in a given database from UPPER CASE to Title Case. Now you could certainly write a C# or VB.Net based program, but I feel that a script is the way to go for a once off requirement like this and what better scripting environment could we ask for than PowerShell. So here is a short PowerShell script that does the job.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
[Microsoft.AnalysisServices.Server]$svr = new-Object([Microsoft.AnalysisServices.Server])
$DatabaseID = "Adventure Works DW"
$db = $svr.Databases.Item($DatabaseID)

# This small function is primarily extracted to improve readability
function ToProperCase([String]$in)
( Get-Culture ).TextInfo.ToTitleCase($in.ToLower())
} # Foreach dimension, loop through each attribute and call the ToProperCase function $db.Dimensions | % {$_.Attributes | % {$_.Name = ToProperCase $_.Name}; $_.Update()}
# Print out all the Attributes for each dimension
$db.Dimensions | % {"Dimension: " + $_.Name; $_.Attributes | % {"   " + $_.Name }}


The key to the whole routine is the following line, which probably looks a bit cryptic at first.

$db.Dimensions| % {$_.Attributes| % {$_.Name = ToProperCase $_.Name}; $_.Update()}

There are 4 PowerShell features in operation here
|  - the pipe character, which passes the output of the previous command as input to the next one
%  - the foreach operator which iterates over all the objects in a collection
$_ - the "current object" variable which returns the current object during each iteration of the foreach loop
;  - is the statement terminator

So in English the line of PowerShell above reads as follows: "Get the collection of dimensions and pass it to a for-each loop. For each dimension get the collection of Attributes and pass it to a for-each loop. For each Attribute, set the Name property equal to the Proper Case version of the Name. After processing all the attributes call the Update() method of the dimension."

In C# the single line of PowerShell would look like the follow:

foreach (Microsoft.AnalysisServices.Dimension dim in db.Dimensions)
  foreach (Microsoft.AnalysisServices.Attribute att in dbdim.Attributes)
      att.Name = ToProperCase(att.Name);

It's not hard to modify the main line to alter the name of all the dimensions in the database by using something like the following:

$db.Dimensions | % {$_.Name = ToProperCase $_.Name; $_.Update()}

Changing all the measure names can be done with the same technique, but I leave that as a exercise for the reader.

Update 17 Mar 08: Thanks to /\/\o\/\/ for a much simpler way to get the current culture.
Update 26 Mar 08: Corrected a slight typo in the C# translation (thanks Richard)

Technorati Tags: ,

Print | posted on Monday, March 17, 2008 12:01 AM