Manage Analysis Services Tabular Partitions (OnPrem / Azure) w/ PowerShell

One of the biggest challenges managing large analysis services databases is table partition maintenance. Either we can do it manually – witch eventually will lead us to an error prone, time consuming and boring task – or…we can AUTOMATE it, for example leveraging the PowerShell simplicity to deal with systems/servers administration. SSAS servers are not an exception to this PowerShell ability, so that the solution we are presenting today is the proof for this.

Describing the benefits for having large tables partitioned is not in the scope of this article so we will just leave a reference to an excellent source that can do it for us: https://msdn.microsoft.com/en-us/library/hh230803.aspx#bkmk_benefits.

In order to assemble the example shown below we’ve used

  • An Azure AS Analysis Service (AS) instance, thought on-prem is just fine;
  • A Windows machine with PowerShell V3.

That is all we will need to resemble our automated partition manager. Moreover, the sample scripts we are about to show depend on just one PowerShell module:

  • SSASHelper – A single lightweight PowerShell module with cmdlets to manage SSAS Instances (On-Prem. or Azure)

This one is indeed a new module developed to abstract some SSAS operations, e.g. the ones used in this blog post scope, hosted on GitHub – https://github.com/DevScope/ssas-powershell-modules.

Note that the examples provided do refer to the code of the modules as being in the folder on the scripts using it – presuming you’ve downloaded them, and so the Import-Module statements, thought the SSASHelper module is also available in the PowerShell Gallery, ready to be installed, as shown on GitHub.

Time for what matters, we will leave two scripts, the first for partition (re)creation – note that if the partition exists and there are no alteration to its definition everything is left untouched, data included:

# Clear the screen
cls

# On error stop the script
$ErrorActionPreference = "Stop"

# Get the current folder of the running script
$currentPath = (Split-Path $MyInvocation.MyCommand.Definition -Parent)

# Import helper modules
Import-Module "$currentPath\Modules\SSASHelper" -Force

# Connection strings (on prem & Azure)
# ex: "Data Source=.\sql2016tb;Initial Catalog=Adventure Works Internet Sales;"
$connStr = (Get-Content "$currentPath\SSASTestConnStr.txt" | Out-String)
 
$conn = Get-SSASConnection -connectionString $connStr -open

$ssasDatabase = "AW Internet Sales Tabular Model"

# Process only most recent facts
2009..2015|%{
 
 $year = $_

 if ($year -eq 2014) {
 $ssasTable = "Internet Sales"
 $partition = "Internet Sales $year"
 Invoke-SSASProcessCommand -connectionString $connStr -database $ssasDatabase -table $ssasTable -partition $partition -Verbose
 }
 
}

# Get all tables from AS database
$cmdText = 'select [Name] from $SYSTEM.tmschema_tables'
$result = Invoke-SSASCommand -connectionString $connStr -commandtext $cmdText -commandType "query"

foreach ($item in $result) {

 $tableName = $item.Values 

 # Process all the dimension tables (smaller tables)
 # If is not named like the fact tables: process
 if( !($tableName.Contains("Internet Sales")) ){
 Invoke-SSASProcessCommand -connectionString $connStr -database $ssasDatabase -table $tableName -Verbose
 }
}

Note that we are only creating partitions on the “Internet Sales” table, illustrating the need to do it only for the biggest tables.

The second script demonstrate partition, table or database processing. The example shown here leverages SSASHelper module partition and table processing for large and small tables, respectively.

# Clear the screen
cls

# On error stop the script
$ErrorActionPreference = "Stop"

# Get the current folder of the running script
$currentPath = (Split-Path $MyInvocation.MyCommand.Definition -Parent)

# Import helper modules
Import-Module "$currentPath\Modules\SSASHelper" -Force

# Connection strings (on prem & Azure)
# ex: "Data Source=.\sql2016tb;Initial Catalog=Adventure Works Internet Sales;"
$connStr = (Get-Content "$currentPath\SSASTestConnStr.txt" | Out-String)
 
$conn = Get-SSASConnection -connectionString $connStr -open

$ssasDatabase = "AW Internet Sales Tabular Model"

# Process only most recent facts
2009..2015|%{
 
 $year = $_

 if ($year -eq 2014) {
 $ssasTable = "Internet Sales"
 $partition = "Internet Sales $year"
 Invoke-SSASProcessCommand -connectionString $connStr -database $ssasDatabase -table $ssasTable -partition $partition -Verbose
 }
 
}

# Get all tables from AS database
$cmdText = 'select [Name] from $SYSTEM.tmschema_tables'
$result = Invoke-SSASCommand -connectionString $connStr -commandtext $cmdText -commandType "query"

foreach ($item in $result) {

 $tableName = $item.Values 

 # Process all the dimension tables (smaller tables)
 # If is not named like the fact tables: process
 if( !($tableName.Contains("Internet Sales")) ){
 Invoke-SSASProcessCommand -connectionString $connStr -database $ssasDatabase -table $tableName -Verbose
 }
}

The scripts are commented and self-explanatory and for that reason, we will not describe then much more. Note just the partition query definition – with variables – in the first script, and the use of SSAS DMV’s in the second so that we get to process entire tables for those wo have only one partition – this way we don’t have even to know the partitions name for the last ones.

…and the result – showing partitions created and one of them processed for “Internet Sales” table:

clip_image002

Some examples of use for this simple scripts include partition management to optimize database processing times, maintaining real-time partitions, etc.

Hope that this modules/sample scripts will help you achieve the same freedom! Feel free to give us your feedback.

Advertisements

One thought on “Manage Analysis Services Tabular Partitions (OnPrem / Azure) w/ 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