SSAS :PowerShell scripts for SSAS Objects Maintenance

will explain how to maintain Sql server analysis Objects Using windows Power Shell Scripts. The PowerShell scripts are very useful for Operational teams for processing a partition or for processing a dimension or for deleting partition etc.

 

Intended Audience

           

SSAS developers and support team.

Introduction

           

In this Paper I will cover some of the operational tasks we are doing on SSAS objects using Power shell. The most important task that every operational team gets is Delete Old partitions from the Cube, Process new Partitions etc. We will store the PowerShell script in notepad with the extension .ps1.

Task 1: How to delete old partitions from Cube

           

  When we have more measure groups and each measure group have more number of partitions then we can write a script to delete all the required partitions, by writing script we can avoid deleting each required partition manually.  We need to write the power shell script as fallows to delete all the 2011 year partitions. We have daily partitions so there are many partitions we need to delete.

Actual Script :

$svrName = "<Analysis server name >>"

$sourceDB = "<<SSAS Database Name>>"

$sourceCube = "<<SSAS Cube Name>>"

# load the AMO library (redirect to null to 'eat' the output from assembly loading process)

[System.Reflection.Assembly]::LoadwithpartialName("Microsoft.AnalysisServices")> $null

# connect to the AS Server

write-Host "Connecting to $svrName"

$svr = New-Object Microsoft.AnalysisServices.Server

$svr.Connect($svrName)

# get a reference to the database

$cub = $svr.Databases.Item($sourceDB).Cubes.FindByName($sourceCube)

$i = 0

foreach ($mgroup in $cub.MeasureGroups)

{

    $i = $i + 1

    $parts = $mgroup.Partitions|Where-Object {$_.name -like "*_2011_*"}

    foreach ($p in $parts)

    {  $p.drop()

       write-Host "Partition name : " $p.name

    }

}

write-Host $i measure groups scanned

Code walk through   : Declare variables for to hold the values of Analysis Server name, Database name and Cube name.

$svrName = "<Analysis server name >>"

$sourceDB = "<<SSAS Database Name>>"

$sourceCube = "<<SSAS Cube Name>>"

 Load AMO library this is very important task when we want to interact with analysis server objects. 

[System.Reflection.Assembly]::LoadwithpartialName("Microsoft.AnalysisServices")> $null

Then connect to Analysis server

$svr = New-Object Microsoft.AnalysisServices.Server

$svr.Connect($svrName)

Get the Reference to the Cube

$cub = $svr.Databases.Item($sourceDB).Cubes.FindByName($sourceCube)

In each measure group in a cube get all the partition names which like *_2011_*

And store it $Parts variable. Now delete all the partitions which are in this variable.

$i = 0

foreach ($mgroup in $cub.MeasureGroups)

{

    $i = $i + 1

    $parts = $mgroup.Partitions|Where-Object {$_.name -like "*_2011_*"}

    foreach ($p in $parts)

    {  $p.drop()

       write-Host "Partition name : " $p.name

    }

}

Finally we are showing how many measure groups we scanned.

Task 2: How to process a particular partition in a cube

There is a scenario where we need to process some old date partitions. If we have more measure group and each measure group is having many partitions. In that case we can write script to process that particular date partitions.

NOTE: all these scripts are created when we creating our partitions with some naming convention. i.e.  For day portion   MeasureGroup_D_YYYY_MM_DD

We will show how to process all the Partitions on 21/06/2013 in Cube.

Actually Script :

$svrName = "<Analysis server name >>"

$sourceDB = "<<SSAS Database Name>>"

$sourceCube = "<<SSAS Cube Name>>"

# load the AMO library (redirect to null to 'eat' the output from assembly loading process)

[System.Reflection.Assembly]::LoadwithpartialName("Microsoft.AnalysisServices")> $null

# connect to the AS Server

write-Host "Connecting to $svrName"

$svr = New-Object Microsoft.AnalysisServices.Server

$svr.Connect($svrName)

# get a reference to the database

$cub = $svr.Databases.Item($sourceDB).Cubes.FindByName($sourceCube)

$i = 0

foreach ($mgroup in $cub.MeasureGroups)

{

    $i = $i + 1

    $parts = $mgroup.Partitions|Where-Object {$_.name -like "*_D_2013_06_21"}

    foreach ($p in $parts)

    {  $p.Process("ProcessFull")

       write-Host "Partition name : " $p.name

    }

}

write-Host $i measure groups scanned

Code walk through   : Declare variables for  to hold the values of Analysis Servername  , Database name and Cube name .

$svrName = "<Analysis server name >>"

$sourceDB = "<<SSAS Database Name>>"

$sourceCube = "<<SSAS Cube Name>>"

 Load AMO library this is very important task when we want to interact with analysis server objects . 

[System.Reflection.Assembly]::LoadwithpartialName("Microsoft.AnalysisServices")> $null

Then connect to Analysis server

$svr = New-Object Microsoft.AnalysisServices.Server

$svr.Connect($svrName)

Get the Reference to the Cube

$cub = $svr.Databases.Item($sourceDB).Cubes.FindByName($sourceCube)

In each measure group in a cube  get all the partition names which  like *_D_2013_06_21

And store it $Parts variable . now Process all the partitions which are in this variable .

$i = 0

foreach ($mgroup in $cub.MeasureGroups)

{

    $i = $i + 1

    $parts = $mgroup.Partitions|Where-Object {$_.name -like "*_D_2013_06_21"}

    foreach ($p in $parts)

    {  $p.Process("ProcessFull")

       write-Host "Partition name : " $p.name

    }

}

Finally we are showing how many measure groups we scanned.

Task 3: How to run PowerShell scripts without starting PowerShell

            First of all I just want to tell how to Run PowerShell script without starting windows power shell, I will explain how to write the power shell script later in this paper.  We can’t start a PowerShell script by double-clicking a .PS1 file. So apparently that means that you do have to start PowerShell before you can run a PowerShell script. In a somewhat roundabout way, that’s technically true. However, that doesn’t mean that you can’t start a PowerShell script from a shortcut or from the Run dialog box; likewise you can run a PowerShell script as a scheduled task. The secret? Instead of calling the script you need to call the PowerShell executable file, and then pass the script path as an argument to PowerShell.exe. For example, in the Run dialog box you might type a command like powershell.exe -noexit c:\scripts\test.ps1

             

  There are actually three parts to this command:

·         Powershell.exe, the Windows PowerShell executable.

·         -noexit, an optional parameter that tells the PowerShell console to remain open after the script finishes. Like we said, this is optional: if we leave it out the script will still run. However, the console window will close the moment the script finishes, meaning we won’t have the chance to view any data that gets displayed to the screen.

Incidentally, the -noexit parameter must immediately follow the call to the PowerShell executable. Otherwise the parameter will be ignored and the window will close anyway.

·         C:\Scripts\Test.ps1, the path to the script file.

What if the path to the script file contains blank spaces? In that case you need to do the ampersand trick we showed you earlier; in addition, you need to enclose the script path in single quote marks, like so

Like 

powershell.exe -noexit &'c:\my scripts\test.ps1'

Conclusion

We have many other methods available in databases class in “Microsoft Analysis Service .server’. We can do all the below operations on analysis database using power shell script. It is very useful to Support team. Here are list of methods we can do.

Name

AddToContainer

AfterInsert

AfterMove

AfterRemove

Backup(String)

Backup(BackupInfo)

Backup(String, Boolean)

Backup(String, Boolean, Boolean)

Backup(String, Boolean, Boolean, BackupLocation[])

Backup(String, Boolean, Boolean, BackupLocation[], Boolean)

Backup(String, Boolean, Boolean, BackupLocation[], Boolean, String)

BeforeRemove

CanProcess

Clone()

Clone(Boolean)

CopyTo(Database)

CopyTo(ModelComponent)

CopyTo(NamedComponent)

CopyTo(MajorObject, Boolean)

CreateObjRef

Detach()

Detach(String)

Dispose()

Dispose(Boolean)

Drop()

Drop(DropOptions)

Drop(DropOptions, XmlaWarningCollection)

Drop(DropOptions, XmlaWarningCollection, ImpactDetailCollection)

Drop(DropOptions, XmlaWarningCollection, ImpactDetailCollection, Boolean)

Equals

Finalize

GetCreateReferences

GetDependents

GetDropDependents

GetHashCode

GetLifetimeService

GetReferences

GetService

GetType

GetUpdateOverwrites

InitializeLifetimeService

LinkDimension(Dimension, String)

LinkDimension(Dimension, String, String)

MemberwiseClone()

MemberwiseClone(Boolean)

Process()

Process(ProcessType)

Process(ProcessType, Binding)

Process(ProcessType, ErrorConfiguration)

Process(ProcessType, WriteBackTableCreation)

Process(ProcessType, ErrorConfiguration, XmlaWarningCollection)

Process(ProcessType, ErrorConfiguration, XmlaWarningCollection, ImpactDetailCollection)

Process(ProcessType, ErrorConfiguration, XmlaWarningCollection, ImpactDetailCollection, Boolean)

Refresh()

Refresh(Boolean)

Refresh(Boolean, RefreshType)

RemoveFromContainer

Reset

Submit()

Submit(Boolean)

ToString

Update()

Update(UpdateOptions)

Update(UpdateOptions, UpdateMode)

Update(UpdateOptions, UpdateMode, XmlaWarningCollection)

Update(UpdateOptions, UpdateMode, XmlaWarningCollection, ImpactDetailCollection)

Update(UpdateOptions, UpdateMode, XmlaWarningCollection, ImpactDetailCollection, Boolean)

Validate(ValidationErrorCollection)

Validate(ValidationResultCollection)

Validate(ValidationErrorCollection, Boolean)

Validate(ValidationResultCollection, ValidationOptions)

Validate(ValidationErrorCollection, Boolean, ServerEdition)

Validate(ValidationResultCollection, ValidationOptions, ServerEdition)