Using PowerShell for SQL Server Analysis Services tasks - Part 1

Problem

There are several repetitive SSAS tasks that I perform on a regular basis and I want to know what options there are to automate some of these tasks such as processing partitions, creating backups and monitoring SSAS. Is there a scripting tool to automate these types of SQL Server Analysis Services (SSAS) tasks?

Solution

PowerShell is a nice command line tool that can be used to automate SSAS tasks.  This tutorial requires SQL Server Analysis Services (SSAS) knowledge because we are going to learn how to automate SSAS tasks you already may be performing. We will be using SQL Server 2012 for this tutorial.

In this tip we are going to show how to:

• Start PowerShell
• Navigate and watch SSAS objects with PowerShell
• See how to perform some basic commands in PowerShell
• Process a SSAS partition
• Backup the a SSAS database with PowerShell

Requirements

We are going to use the Adventureworks database for SQL Server 2012. You can download it here:http://msftdbprodsamples.codeplex.com/releases/view/55330

Getting started

1. To start PowerShell go to start and click the Run... option.
   
   
   
   
2. In the Run windows type in "sqlps" to start SQL Server PowerShell.
   
   
   
   
3. To see a list of objects in PowerShell we are going use the gci command as shown below.
   

   gci

   
   
   
   
   The gci command means get child items. It is like the DOS "dir" command to show a list of objects in the current path. You can also use "ls" or "dir" and get the same results. As you can see above, SQL Server PowerShell can help you automate SQL Server Database Engine Tasks, Policies, Data Collection, SQL Server Integration Services (SSIS) Tasks and SQL Server Analysis Services (SSAS) tasks. In this tip we are going to focus on SSAS tasks.
   
4. Let's move to the SQL Server Analysis Service directory using the below command:
    

   cd sqlas 

   
   The "cd" command changes directories just like the DOS command.
   
5. For more information about the gci command, you can run the following command:
    

   get-help gci -detailed 

   
   
   
   
   

Partitions

As you know, a SSAS multidimensional Database contains Cubes, the Cubes contain Measures and the measures contain Partitions. The picture below shows the hierarchy.

Now let’s use the "cd" command to move to the partitions in PowerShell:

cd servername\DEFAULT\databases\AdventureWorksDW2012Multidimensional-EE\cubes\Adventure Works\MeasureGroups\Internet Orders\Partitions

• Servername is the name of the Windows Server
• Default is the name of the SSAS instance
• AdventureWorksDW2012Multidimensional-EE is the name of the database
• AdventureWorks is the name of the cube (a cube is like a multidimensional table)
• Internet Orders is the name of the measure (a measure group is a group of elements that we want to measure in a company like Revenue, Sales, number of customers).
• Finally we get to the partitions. When a database is too large we divide the cube into different partitions as shown below:

The partitions help you to divide data in order to process the partitions in parallel so you can process the information faster which will increase the speed of queries if the partition and the query are related.

What we are going to do now is to list the partition information:

gci

We can see the partition Name, the Estimated Rows and the Processing Mode. In the Adventureworks cube the partitions are per year (2005, 2006, etc.). The number of rows per partition by default is not calculated and the value defaults to zero. The Processing Mode is the way the partitions are processed. Regular means that the Data and then the Aggregations will be processed. Lazy aggregations means that the Data will be processed first, but the aggregations will be created later as a background process. This option is useful to have the data ready for use and reduce the impact of creating indexes because the index creation is resource intensive.

To list the members of the partitions you can use the get-member command:

ls | Get-Member 

The Get-Member command is used to get a list of properties and methods of an object. In this case the properties and methods of the partition.

For example you can see the partition Name, LastProcessed date and the EstimatedSize of the partition with this query:

ls | select name,lastprocessed,estimatedsize 

If you want to process a specific partition you can do it with the following command:

Invoke-ProcessPartition –Name “Internet_Orders_2006” –MeasureGroupname “Internet Orders” –CubeName “Adventure Works” –database “AdventureWorksDW2012Multidimensional-EE” –ProcessType “ProcessFull”

This command will process the partition named "Internet_Order_2006" which is in the group Internet Orders in the cube Adventureworks and the database AdventureWorksDW2012Multidimensional. A complete process is a ProcessFull.

In the current version (SQL 2012 SP1), when we run the command and then we run "ls | select name,lastprocessed,estimatedsize", the information is not updated by default and it is necessary to close PowerShell and open it again.

Backup a SSAS Database

In order to backup the Adventureworks database you can use the following command:

backup-asdatabase d:\awdb-20110930.abf “AdventureWorksDW2012Multidimensional-EE” -AllowOverwrite -ApplyCompression

This will create a backup of the AdventureWorksDW2012Multidimensional-EE. The backup name is awdb-20110930.abf stored on the D: drive. This command will overwrite the file if it exists and compress the backup as well.

In this tutorial we learned how to start PowerShell, how to show component properties, how to process a partition and finally how to backup a database. In future tips we will show more SSAS options in PowerShell.

Source collected from mssqltips.com