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

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

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

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

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.

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

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

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

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