Using PowerShell for SSAS to check service status and database availability

By:   |   Comments (4)   |   Related: > Analysis Services Administration


In my first article about PowerShell we saw a brief introduction to PowerShell, learning how to use the basic commands, how to open PowerShell, how to Navigate in SSAS and how to process objects. In part 2, we learned how to work with variables and how to create SSAS backups using the current date and variables. Finally we learned to run MDX and XMLA scripts.

In part 3, there are tips to control the following:

  • PowerShell commands to verify the SSAS Service status
  • Send a message if the SSAS service is down
  • Verify the hard disk space for the SSAS database
  • Schedule a script to verify the database status every hour

In this article, we are going go run PowerShell commands to verify the SSAS status.


  1. The Adventureworks Multidimensional project.
  2. SQL Server 2008 or later (in this example I am using SQL Server 2012).

Getting started

  1. Let's start verifying the Windows Services Status with PowerShell:

    The command get-service, obtains the list of windows Services and their status:

    powershell get-service

  2. In this case we are interested in the Analysis Services status, so the command will be:
    get-service | select status, name | Where-Object {$_.Name -like  "*MSSQLServerOLAP*"}

    powershell MOLAP status

    The command shows the status of the service, the name of the object with a name similar to MSSQLServerOLAP. It will show if the service status is started or stopped.

  3. Now we are going to send a message if the SSAS Service is stopped.
    $servicestatus=get-service | select status,name | Where-Object {$_.Name -like  "*MSSQLServerOLAP*"}
    $Message="The SSAS Service is down"
    if($servicestatus.status -eq "Stopped"){
    echo $Message

    The command shows a message saying that the service is down if the Analysis Services Service is stopped.

  4. The following tip shows how to see the space available in the hard disks.
    $driveinformation=gwmi win32_volume -Filter 'drivetype = 3' | select driveletter, label, @{LABEL='GBfreespace';EXPRESSION={$_.freespace/1GB} }

    powershell disk space

    This command shows the disk volumes and the space free in GB. It is useful to see if we have enough disk space for our SSAS databases.

  5. In order to verify the SSAS status of a cube you can call a mdx script every 5 minutes. In this example we are going to create a simple mdx script with a simple query. Then we are going to call the query every hour to verify that the cube is online.
  6. In order to start,  open SSMS.
  7. Open the SSAS Server and browse the Adventureworks cube.

    ssas browse cube

  8. Now, drag and drop the Internet Gross Profit as shown below, so we can generate a MDX query.

    ssas create query

  9. The query would be the following:

    ssas see MDX

  10. Save the query in a file named Adventure.mdx in the scripts folder.
  11. Now we are going to call a PowerShell script to invoke the query. If there are no results or an error message related to the cube is not processed, a connection error message will be displayed.
    $result=Invoke-ASCmd  -Database "Adventureworks"  -InputFile:"c:\scripts\Adventure.mdx"
    $Message="Adventureworks had a connection error"
    if ((!$result) -or ($resultado -like  "*either does not exist or has not been processed*")){$message} 
  12. In order to run PowerShell commands on a schedule, in SSMS create a new job by right clicking in jobs:

    create job

  13. Give the job a name and click on the Step page. Click the New button in order to create a new step. 

    Click the New button in order to create a new step

  14. Specify a step name. In the Type select PowerShell and in Command copy and paste the command from Step 11 and then press OK.

    In the Type select PowerShell and in Command copy and paste the command of step 11

    Now select the Schedules page and press the New button.

    select the schedule page

    Enter the required schedule information. In this example we are creating a daily schedule that runs every hour to verify that the database is up.

    In this example we are creating a daily schedule every hour to verify that the database is up

That is all folks. You now have a script to verify the SSAS Database status every hour. I hope you enjoyed this article. If you have more questions about PowerShell, write in the comments below and we may create an article related to your question.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, January 5, 2017 - 10:04:39 AM - Daniel Calbimonte Back To Top (45143)


What is your problem when you execute the sp_send_dbmail?

Thursday, January 5, 2017 - 12:08:59 AM - Vivek Singh Back To Top (45129)


Thank you so much for sharing such a useful article.

The script will help us to veirify the status of the cube but how do we use EXEC msdb.dbo.sp_send_dbmail to notifiy users over the email.

Please help me. 



Vivek Singh

Monday, March 14, 2016 - 2:42:36 PM - Scott Back To Top (40933)

Update your script to work correctly I would recomment this.



Import-module sqlascmdlets

$result=Invoke-ASCmd  -Database:"BSIGCubes"  -InputFile:"D:\CubeValidation\ValidationScripts\PolicyInfoCubeCheck.mdx"

$Message="PolicyInfoCube had a connection error"

if((!$result) -or ($result -like '*either does not exist or has not been processed.*')){Throw $Message}

Sunday, July 14, 2013 - 1:18:28 PM - John R Back To Top (25827)

THX for the tip!


get free sql tips
agree to terms