SSAS – Find Cube Database Name, Size,Status and Total Size of SSAS Server

We are planning to take SSAS cube database backup since our backup tool does full file system backup in case of recovery, restoration is taking more time and hence as a first step we are started gathering the number of cubes,status and total size of cube databases .

SQLAS provider used for navigating the Analysis Management Object (AMO) hierarchy. You must import the SQLPS module before you can use the SQLAS provider and cmdlets. The SQLAS provider is an extension of the SQLServer provider. There are several ways to import the SQLPS module.

Please refer by earlier post to load SMO’s, if it’s not loaded.

http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2013/05/01/powersql-list-sql-server-instance-configuration-details-and-sending-an-automated-email/

Create a file SSAS1.PS1 and paste the below content

********************************************************
Param($ServerName=”localhost”)
$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
Write-Output (“Server ‘{0}’ not found” -f $ServerName)
break
}

$sum=0
foreach ($d in $server.Databases )
{
Write-Output ( “Database: {0}; Status: {1}; Size: {2}MB” -f $d.Name, $d.State, ($d.EstimatedSize/1024/1024).ToString(“#,##0″) )
$sum=$sum+$d.EstimatedSize/1024/1024
}

$SizeGB=$Sum/1024

write-host ‘Sum of Database = ‘$sum ‘ MB’
Write-host ‘Total Size of Cube Databases =’ $SizeGB ‘ GB’

***************************************************************

Open PowerShell – Type PowerShell in command prompt

PS C:\> .\SSAS1.PS1 <serverName>

Or Command Prompt
Powershell.exe c:\ssas1.ps1 <ServerName>

PS E:\> .\ssas.ps1 TEST
Database: Daily; Status: Processed; Size: 2,180MB
Database: Test; Status: Processed; Size: 49MB
Database: PP04; Status: Processed; Size: 57MB
Database: Trans; Status: Processed; Size: 1,613MB
Database: Complaint; Status: Processed; Size: 313MB
Database: PI; Status: Processed; Size: 8,687MB
Database: PI_Jey; Status: Processed; Size: 8,807MB
Database: PI_Trng; Status: Processed; Size: 8,474MB
Database: LG; Status: Processed; Size: 56MB
Database: VPP; Status: PartiallyProcessed; Size: 7MB
Database: Assign; Status: Processed; Size: 1,551MB
Database: Cubes; Status: Processed; Size: 3,044MB
Database: Cube; Status: Processed; Size: 3,048MB
Database: Tele; Status: Processed; Size: 2MB
Database: Pricing; Status: Processed; Size: 2,140MB
Database: DMDB; Status: Unprocessed; Size: 0MB
Database: LG cube; Status: Processed; Size: 62MB
Database: Manual; Status: PartiallyProcessed; Size: 2MB
Database: Tutorial; Status: Processed; Size: 10MB
Database: DW ; Status: Processed; Size: 32MB
Database: OLAP; Status: Processed; Size: 86MB
Database: SSAS; Status: Unprocessed; Size: 0MB
Sum of Database = 40220.1397724152 MB
Total Size of Cube Databases = 39.2774802464992 GB

About these ads

About Prashanth Jayaram

I’m a Database technologist having 7+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell. hobbies are playing sports and drawing.
This entry was posted in PowerShell, SSAS and tagged , , . Bookmark the permalink.

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