Problem
Discovering the status or size of multiple dimensions or measure groups for SQL Server Analysis Services (SSAS) in SQL Server Management Studio (SSMS) isn’t easy. Acting on that information can be equally problematic. So what options are available in PowerShell to help manage SSAS?
Solution
The SQLAS Provider allows you to see important information and act on it using PowerShell cmdlets. While critical information for common SSAS objects are available in SSMS if you right-click on each object and ask for its properties one at a time, they aren’t made available in SSMS’s Object Explorer Details. SQL PowerShell allows you to get to much of this information fairly easily.
I have worked with the SSAS Tools team, to make navigating the SQLAS Provider a little more user friendly for some common use cases. For items that I missed, this article will show you how to find that information for yourself.
SQLAS Provider for Processing SSAS
The SqlServer PowerShell module that is available in the PowerShell Gallery, contains a PowerShell Provider, made available via the SQLSERVER:\ PS Drive. PowerShell Providers have the ability to navigate ‘structures’ as if they were a storage drive on your machine. In this tip, we will navigate an instance of SSAS as if it were a drive on your machine. The great news is that if you use SSMS you’re already very familiar with how this structure is laid out. In essence, the SQLAS Provider is going to allow you to navigate an instance just the same as you would with Object Explorer in SSMS. The big difference here is that you will frequently have more information available to you in SQL PowerShell than you can easily find in SSMS.
To get started, you need to have the SqlServer module loaded. For simplicity, I will use the PowerShell ISE and the latest version of the SqlServer module from the PowerShell Gallery. To load the SqlServer module into your ISE session, simply run Import-Module SqlServer.
Import-Module SqlServer
Next, we will set our location to the SQLSERVER:\ PS Drive that has been made available by importing the SqlServer module. Note: The actual command being run here is a cmdlet called Set-Location however most people are more familiar with the “DOS-like” CD alias that also calls the Set-Location cmdlet. For simplicity, we will use CD in this tip.
CD SQLSERVER:
From here we can quickly take a look at the different resources you can access using the SQLSERVER PowerShell Provider by running the Dir command. Note: Again, we are using a more common alias, behind the scenes is actually running a cmdlet called Get-ChildItem when we run the Dir command.
Above, we see 10 different portions of the SQL Server suite of products that we can access; but today we will focus on the SQLAS portion. So, use the following command to change location.
CD SQLAS
Navigation from here is very straight-forward. The next thing that we need to tell PowerShell is which instance of SSAS we want to navigate to.
CD localhost\SQL2016
Once we have connected to our instance of SSAS, things should feel quite familiar. If you run a “Dir” command, you will see collections just like you would in object explorer and under Databases you will see however many databases you have on this instance.
CD Databases;
Dir
As you can see, SQL PowerShell is already providing you important information on the State of your SSAS databases. Let’s dive deeper!
Pick one of your SSAS databases to navigate to and then have a look under it, again, using the Dir command.
We will look at the Dimensions of this database to show you how I solved a common problem easier with SQL PowerShell. Looking at these dimension, we can see that they’re all in the “UNPROCESSED” State. Luckily, there are a few SQL PowerShell cmdlets to help us process certain SSAS objects.
PowerShell cmdlets for SSAS
The easiest way to get the list of cmdlets for SSAS is to look in the old SQLASCMDLETS module.
Get-Command -Module SQLAS*
If you don’t have the old SQLASCMDLETS module on your machine, you don’t need worry about it. As long as you have the latest version of the SqlServer module from the PowerShell Gallery, you are good to go. As of the writing of this tip, it contains 13 cmdlets for working with SSAS.
Next, we’re going to make use of a couple of those –Process* cmdlets.
Using the SSAS cmdlets with the SQLAS Provider
We’ve already demonstrated how to look at our Dimensions to see their current state, and now we’re going to use Invoke-ProcessDimension to process some of those dimensions.
CD SQLSERVER:\SQLAS\LOCALHOST\SQL2016\Databases\AdventureWorksDW2014Multidimensional-EE\Dimensions
dir;
First, we can just run a command like Invoke-ProcessDimension Account to process a single dimension.
Invoke-ProcessDimension -Name Account -Database AdventureWorksDW2014MD -ProcessType ProcessFull
In many cases, you’ll want to process multiple dimensions at once. PowerShell makes this easy with pipeline processing, however, the SSAS cmdlets do not yet support pipeline processing. Despite that, multiple dimensions can still be processed easily thanks to the ForEach-Object cmdlet. For instance, the following command is all that would be needed if you wanted to process every dimension of a cube.
dir |
ForEach-Object{
Invoke-ProcessDimension -Name $_.Name -Database $_.ParentDatabase -ProcessType ProcessFull
}
However, especially in development scenarios, I often find myself needing to process only a few of the dimensions. By simply inserting the Out-GridView cmdlet with the -PassThru parameter after the Dir, we are able to interrupt the pipeline and select only a few dimensions to be processed.
dir |
Out-GridView -PassThru |
ForEach-Object{
Invoke-ProcessDimension -Name $_.Name -Database $_.ParentDatabase -ProcessType ProcessFull
}
A basic example of how to use this functionality is to run the above command, and when the Out-GridView window pops up, select only the Account, Customer, and Date dimensions to be processed, and then click “OK”.
Assuming those run successfully, we would expect that running a Dir again would show us that our dimensions had now been processed and what time they had been processed. Unfortunately, the SQLAS Provider doesn’t do an automatic refresh of the \Dimensions node after running the Invoke-ProcessDimension cmdlet. It turns out that all that is needed to get the SQLAS Provider to update is to execute the .Refresh() method against a call to the Get-Item cmdlet.
(Get-Item . ).Refresh();
dir
After that, you should see your dimensions have a new State and that their Last Processed date-time has been updated.
While we’re at it, I wanted to call out another great feature of the Out-GridView cmdlet; you can filter the objects. In this basic example, I will filter the dimensions so that I only see the ones with a Storage mode of “Molap”. Using features like this can make finding the dimensions that you need to process a quicker task.
After you click “OK” you should see progress bars show up for each dimension as they get processed.
After refreshing the dimensions node, you should see that all of your remaining dimensions have now been processed.
Using the SSAS cmdlets by themselves
For completeness, you do not have to use the SQLAS Provider to utilize the PowerShell cmdlets for SSAS. In order to use them by themselves you simply need to pass in the requisite parameters manually. In addition to providing values for all of the parameters shown in the previous example, you will also need to supply a value for the -Server parameter.
Invoke-ProcessDimension -Server localhost\SQL2016 -Name Account -Database AdventureWorksDW2014MD -ProcessType ProcessFull
Processing Cubes with Invoke-ProcessCube
Finally, now that all of our dimensions have been processed, we can process all of our cubes with the Invoke-ProcessCube cmdlet.
dir 'SQLSERVER:\SQLAS\LOCALHOST\SQL2016\Databases\AdventureWorksDW2014MD\Cubes\'|
ForEach-Object{
Invoke-ProcessCube -Name $_.Name -Database $_.ParentDatabase -ProcessType ProcessFull
}
Again, we’ll need to use that Refresh() method in order to see the State and Last Processed On properties get updated.
Next Steps
- Read my tip on how to download the latest version of the SqlServer PowerShell module from the PowerShell Gallery.
- Learn how to deploy an entire folder containing SSRS objects with a single PowerShell command.
- See how you can use SQL PowerShell cmdlets to INSERT data into SQL Server [relational] tables.
