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.

Create a file SSAS1.PS1 and paste the below content

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

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”) )


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 Prashanth Jayaram

I’m a Database technologist having 10+ 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 You can connect me via jayaram/ My Articles are published in following sites
This entry was posted in PowerShell, SSAS and tagged , , . Bookmark the permalink.

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

  1. Bhargavi says:

    Nice article sir..
    i have some doubts
    What is the Maximum Size of cube in ssas?
    What is the limit of number of dimensions in a cube?

  2. Susanta says:

    Thank you very much really helpful

  3. David Farr says:

    The script file works great in Powershell and from a command prompt.
    However, I have a SQL Server Database Engine and SSAS instance with the same name.
    When executed from SSMS as follows;
    exec xp_cmdshell ‘powershell.exe -file c:\SSAS1.PS1 “localhost”‘ ..the result returns no databases.
    Sum of Database = 0 MB
    Total Size of Cube Databases = 0 GB
    Is there something I should be aware of in this case ?

    • Hi David Farr,

      I hope you have a valid localhost instance. I think its an issue with an instance name. Can you double check the instance name??

      I’m able to run the below SQL by giving a correct instance name. In the below example ABCD is an SSAS instance name.

      exec xp_cmdshell
      ‘powershell.exe -file F:\PowerSQL\SSAS.PS1
      -Servername ABCD’


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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