Discover Analysis Service’s Properties using Windows PowerShell and AMO

Part
I
of this
article series “Microsoft Windows PowerShell and SQL Server 2008 AMO”
illustrated how to install Windows PowerShell and connect to an Analysis
Service Servername. Part II of this article illustrates how to use Windows
PowerShell and AMO to get the various Server properties of SQL Server Analysis
Service.

Let’s
connect to Analysis Server using Windows PowerShell as shown below.


[Reflection.Assembly]::LoadWithPartialName
(“Microsoft.AnalysisServices”)
$servername=New-Object Microsoft.AnalysisServices.Server
$servername.connect(“PowerServer3\SQL2008”)

Note:
PowerServer3 is the Analysis Server with named instance SQL2008. Please replace
the server name with Analysis server name in your own environment.

Now let’s
query all the methods available for the Class ‘Microsoft AnalysisService.Server’.
This can be achieved by using the Windows PowerShell cmdlet “get-member” with
the parameter “ –membertype methods” as shown below.

$servername |get-member -membertype methods

This
would display a result similar to the results shown below. [Refer Figure 1.1]

Partial Result:


Attach
BeginTransaction
CancelCommand
CancelConnection
CancelSession
Clone
CommitTransaction
ConcatenateCaptureLog
Connect
CopyTo
CreateObjRef
Disconnect
Dispose
Drop
EndXmlaRequest
Equals
Execute
ExecuteCaptureLog
GetConnectionState
GetDependents
GetHashCode
GetLastSchemaUpdate
GetLifetimeService
GetReferences
GetType
GetUpdateOverwrites
InitializeLifetimeService
NotifyTableChange
Reconnect
Refresh
Restore
RollbackTransaction
SendXmlaRequest
StartXmlaRequest
Submit
ToString
Update
UpdateObjects
Validate

get-member with -membertype methods results

Figure 1.1

Now let’s
query all of the properties available for the Class ‘Microsoft AnalysisService.Server’.
This can be achieved using Windows Powershell cmdlet “get-member” with
parameter “ –membertype Properties”.

$servername |get-member -membertype properties

This displays
a result similar to the results shown below. [Refer Figure 1.2]

Partial Result:


Annotations
Assemblies
CaptureLog
CaptureXml
Connected
ConnectionInfo
ConnectionString
Container
CreatedTimestamp
Databases
Description
Edition
EditionID
ID
IsLoaded
LastSchemaUpdate
Name
OwningCollection
Parent
ProductLevel
ProductName
Roles
ServerProperties
SessionID
SessionTrace
Site
SiteID
Traces
Version



Figure 1.2

Now let’s
query some of the SQL Server Analysis Service properties by using the Windows
PowerShell cmdlets below.


$servername.ConnectionString
$servername.Edition
$servername.ProductLevel
$servername.databases | select Name

The output is similar to the results shown below.
Refer Figure 1.3


PS C:\> $servername.ConnectionString
PowerServer3\SQL2008
PS C:\> $servername.Edition
Evaluation
PS C:\> $servername.ProductLevel
RTM
PS C:\> $servername.databases | select Name
Name
—-
Adventure Works DW 2008
MyCube
Test

query SQL Server Analysis Service properties results

Figure 1.3

Let’s
create a Windows PowerShell script that displays some of the properties of any
SQL Server Analysis server that we pass as a parameter.

Create
the folder C:\AMOScripts using the following Windows PowerShell cmdlet as shown
below. [Refer Figure 1.4] We could store all the AMO related scripts under the
folder.

New-Item –Path C:\ -Name AMOScripts -Type directory

Create the folder C:\AMOScripts using the following Windows PowerShell cmdlet

Figure 1.4

Let’s
create a file C:\AMOScripts\get-SSASproperty.ps1 using notepad and copy and
paste the code below to that file and save it. [Refer Figure 1.5]


#Objective: To get properties of the given Analysis Server
#Created by: MAK
#Create Date: 9/9/2007
param
([string] $Servername)
## Add the AMO namespace
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
Write-Output (“Server ‘{0}’ not found” -f $ServerName)
break
}
else
{
Write-host “Connection String = ” $server.ConnectionString
Write-host “Edition = ” $server.Edition
Write-host “Version = ” $server.version
Write-host “SessionID = ” $server.SessionID
Write-host “Assemblies = ” $server.Assemblies
Write-host “CreatedTimestamp = ” $server.CreatedTimeStamp
Write-host “LastSchemaUpdate = ” $server.LastSchemaUpdate
Write-host “Roles = ” $server.Roles
Write-host “Connected = ” $server.Connected
Write-host “SessionID = ” $server.SessionID
Write-host “Databases = ” $server.databases

}

Please
copy the script from here.

create a file C:\AMOScripts\get-SSASproperty.ps1

Figure 1.5

Now let’s
try to execute the get-ssasproperty.ps1 by passing the SQL Server 2005 Analysis
server name as shown below. [Refer Figure 1.6]


set-location AMOScripts
./get-ssasproperty.ps1 “BUS-INTEL”

Note:
BUS-INTEL is the SQL Server 2005 Analysis Server with default Analysis Service
instance. Please replace the server name with the Analysis server name in your
environment.

Result


Connection String = BUS-INTEL
Edition = Enterprise
Version = 9.00.3042.00
SessionID = E2AE10B1-A163-43C8-BF41-40817643FB7B
Assemblies = System VBAMDXINTERNAL VBAMDX ExcelMDX
CreatedTimestamp = 11/29/2008 4:47:20 AM
LastSchemaUpdate = 11/29/2008 4:47:20 AM
Roles = Administrators
Connected = True
SessionID = E2AE10B1-A163-43C8-BF41-40817643FB7B
Databases = Adventure Works DW

execute the get-ssasproperty.ps1

Figure 1.6

Now let’s
try to connect to a SQL Server 2008 Analysis Server as shown below. [Refer
Figure 1.7]

./get-ssasproperty.ps1 "PowerServer3\SQL2008"

Note:
PowerServer3 is the Analysis Server with named instance SQL2008. Please replace
the server name with the Analysis server name in your environment.

Result


Connection String = PowerServer3\SQL2008
Edition = Evaluation
Version = 10.0.1600.22
SessionID = 03E59B7D-3A03-4989-99D3-48AA2DB4E1BF
Assemblies = System VBAMDXINTERNAL VBAMDX ExcelMDX
CreatedTimestamp = 10/28/2008 11:06:19 PM
LastSchemaUpdate = 10/28/2008 11:06:19 PM
Roles = Administrators
Connected = True
SessionID = 03E59B7D-3A03-4989-99D3-48AA2DB4E1BF
Databases = Adventure Works DW 2008 MyCube test

connect to a SQL Server 2008 Analysis Server

Figure 1.7

When we
execute the Windows PowerShell script by passing a server name that’s not
available on the network, you would get an error message similar to the results
below. [Refer Figure 1.8]

./get-ssasproperty.ps1 "ABC123"

Result


Exception calling “Connect” with “1” argument(s): “A connection cannot be made.
Ensure that the server is running.”
At C:\AMOScripts\get-ssasproperty.ps1:12 char:16
+ $server.connect <<<< ($ServerName)
Server ‘ABC123’ not found

error message

Figure 1.8

Conclusion

Part II of this
article has illustrated how to use Windows PowerShell and AMO to get the
various Server properties of SQL Server Analysis Service.

»


See All Articles by Columnist
MAK

Latest Articles