Find Unprocessed Analysis Services Objects Using PowerShell

“PowerShell is great because I am flawed “- Jeffrey Snover

I’m a big fan of PowerShell: every-time there’s a problem I need to fix, I think “how can PowerShell help me here?” Following on from last weeks post on getting the size of dimensions, here is another PowerShell script for SSAS. This script looks for any objects that are unprocessed and writes them to the console. The script has a pause between SSAS databases so that if there is a lot of unprocessed objects you have a chance to go through them. I posted a similar but simpler script back in February that looks at the cube level for the status.


[CmdletBinding()]
	param(
        [Parameter(Position=0,mandatory=$true)]
        [string] $ssasInstance)
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
	$svr = new-Object Microsoft.AnalysisServices.Server
	$svr.Connect($ssasInstance)
	foreach ($db in $svr.Databases)
		{
    		if ($db.State -ne "Processed")
			{
			write-host "-------------------------------------------------------------------------------------------------------------------------" -foregroundcolor yellow -backgroundcolor darkcyan
        		write-host
        		write-host "Database" $svr $db "is" $db.State
        		write-host
			$Cub=New-object Microsoft.AnalysisServices.Cube
        		foreach ($cub in $db.Cubes)
				{
            			if ($cub.State -ne "Processed")
					{
                			write-host $db "Cube" $cub "is" $Cub.State
					write-host
                			foreach ($mg in $cub.MeasureGroups)
						{
                    				if ($mg.State -ne "Processed")
							{
                        				write-host $db $Cub "Measure Group"  $mg "is" $mg.State
							write-host
                        				foreach ($p in $mg.Partitions)
								{
                            					if ($p.State -ne "Processed")
									{
                                					write-host $db $Cub $mg "Partition"  $p "is" $p.State
									write-host
                            						}
                        					}
                    					}
                				}
            				}

        			foreach ($dim in $db.Dimensions)
					{
            				if ($Dim.State -ne "Processed")
						{
                				write-host $db "Dimension" $Dim "is" $Dim.State
						write-host
            					}
        				}
    				}
				Write-host "End of checking database "$db
				write-host "-------------------------------------------------------------------------------------------------------------------------" -foregroundcolor yellow -backgroundcolor darkcyan
				Write-Host "Press any key to continue ..."
				$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
				write-host
			}

		}
	Write-host "End of checking Instance" $svr
	Write-Host
	$svr.disconnect

Once you have saved this to a .ps1 file you open up a PowerShell console and drag and drop the file there (or copy as path, whichever way you like) and press enter. It’ll ask you for the ssasInstance, and then hit enter and off it goes. The thing I like about checking cube status this way is that it is so much quicker than going through the UI.

It’s interesting to note that you can check the state of the dimensions within the first foreach loop of the cubes, however you cannot get the state. Why I have no idea, but I hazard a guess that it’s because a dimension is not strictly speaking part of a cube.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

2 thoughts on “Find Unprocessed Analysis Services Objects Using PowerShell”

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