Using PowerShell for SSAS to check service status and database availability

Problem

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

Solution

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

Requirements

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:
    

   get-service 

   The command get-service, obtains the list of windows Services and their status:
   
   
   
   
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*"}

   
   
   
   
   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} }
   
   
   
   
   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. 
   
   
   
   
8. Now, drag and drop the Internet Gross Profit as shown below, so we can generate a MDX query. 
   
   
   
   
9. The query would be the following:
    
   SELECT NON EMPTY { [Measures].[Internet Gross Profit] } ON COLUMNS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
   
   
   
   
   
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:
    
    
    
    
13. Give the job a name and click on the Step page. 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.
    
    
    
    Now select the Schedules page and press the New button.
    
    
    
    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.
    
    

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.

Source Collected from MSSQLTIPS.COM