Play PowerShell in SSAS

There are many ways to operate SSAS databases. Is there a way to perform these tasks automatically through scripts, such as processing partitions, creating backups, and monitoring the running status of SSAS. Source Address: www. mssqltips. comsqlservertip2937using-powershell-for-sql-server-analysis-services-tasks -- part-

There are many ways to operate SSAS databases. Is there a way to perform these tasks automatically through scripts, such as processing partitions, creating backups, and monitoring the running status of SSAS. Address: http://www.mssqltips.com/sqlservertip/2937/using-powershell-for-sql-server-analysis-services-tasks--part-

There are many ways to operate SSAS databases. Is there a way to perform these tasks automatically through scripts, such as processing partitions, creating backups, and monitoring the running status of SSAS.

Original article address:

Http://www.mssqltips.com/sqlservertip/2937/using-powershell-for-sql-server-analysis-services-tasks--part-1/

PowerShell, as a command line tool, is a good option to automate SSAS tasks. Because it is about SSAS processing tasks, reading this article requires some basic SSAS knowledge. Here we use SQL Server 2012.

This article introduces the following content:

Start PowerShell and navigate through PowerShell and view SSAS objects. Run some basic commands on PowerShell to process a partition of SSAS.

This article uses the Adventureworks Sample Database of SQL Server 2012. You can download the sample database here:

Http://msftdbprodsamples.codeplex.com/releases/view/55330

Start

Under the Start Menu, click the Run option. Or Windows key + r.

Enter sqlps in the running form to start PowerShell under SQL Server.

Enter the GCI command to view the Object List under PowerShell.

The GCI command is used to obtain all sub-objects, which is similar to the dir command in the DOS era. Therefore, you can use LS or DIR to obtain the same result. From the results, we can see that SQL Server PowerShell allows you to automatically process SQL Server Data Engine tasks, policies, and datasets, SQL Server integration service tasks and SQL Server Analysis Service tasks. This article focuses on SSAS tasks.

Run the following command to go to the analysis service directory.

CD sqlas

The CD command here is the same as that in DOS.

For more information about GCI, run the following command to get help:

Get-help gci-detailed

Partition operations

SSAS multi-dimensional databases contain Cubes, while Cubes also contain metric values and metric value partitions. Shows its hierarchy.

Next, run the CD command to move to the partition directory.

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

Servername is the name of Windows Server. Default is the name of the SSAS service instance. The AdventureWorksDW2012Multidimensional-EE is the database name. AdventureWork is the cube name. Internet Orders is the name of the measurement value group. Finally, we can get the partition information under it. When an Analysis Service database is too large, we will consider splitting it into different partitions:

By using partitions to split data, you can increase the processing speed through parallel methods and improve the query performance.

Next, run the GCI command to list all partition information.

We can see the partition name, estimated number of rows, and the processing mode. In the Adventureworks cube, partitions are split in years. By default, the number of rows in each partition is not calculated, so 0 is displayed. The processing mode is the processing method of partitions. The Regular mode means that both data and aggregation are processed. The other Lazy Mode means that data will be processed first and aggregated in subsequent background processes. This setting is very helpful for data and index creation, because index creation has additional performance overhead.

You can use the Get-member method to obtain all the members in the partition.

LS | Get-Member

The Get-Member command is used to list the attributes and methods supported by an object. Here, we will list the attributes and methods supported under the partition.

For example, you can see the partition name, LastProcessed Time, And EstimatedSize of the partition:

LS | select name, LASTPROCESSED, ESTIMATEDSIZE

To process a specified partition, run 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 Internet Orders metric value group named "Internet_Order_2006" partition under Adventureworks and process it in full mode.

In SQL Server 2012 SP1, when we run this command and then run "LS | SELECT NAME, LASTPROCESSED, ESTIMATEDSIZE", the default information is not updated. You need to disable PowerShell, re-open it, and run this command to view the updated information.

Back up an SSAS Database

Run the following command to back up Adventureworks:

Backup-asdatabase d: \ awdb-20110930.abf "AdventureWorksDW2012Multidimensional-EE"-AllowOverwrite-ApplyCompression

This command backs up the AdventureWorksDW2012Multidimensional-EE database, the backup file name is awdb-20110930.abf, and is stored under the D root directory. This command also overwrites an existing backup file with the same name.

This article demonstrates how to start PowerShell, how to display properties under an object, how to process a partition, and how to back up a database. The next article will introduce more PowerShell operations under SSAS.

:

The hierarchy of SSAS objects is complex. You need to have an understanding of the basic structure of the SSAS database to learn how to navigate or what can be done under some objects.

PowerShell also uses AMO to operate SSAS conveniently. net-based.