Using PowerShell for SQL Server Analysis Services tasks - Part 1

By:   |   Comments   |   Related: > PowerShell


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.

    Windows Menu Run command

  2. In the Run windows type in "sqlps" to start SQL Server PowerShell.

    Run command

  3. To see a list of objects in PowerShell we are going use the gci command as shown below.

    gci

    GCI command

    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 

    get help

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.

SSAS parts

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:
cube partitions

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

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 

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 

ls and select command

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.

SSAS hierachies

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.

Run command

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.

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

















get free sql tips
agree to terms