This month’s T-SQL Tuesday topic is about automating with PowerShell. I’ve been using PowerShell to help automate deployments for all sorts of SQL solutions; SSDT-based database projects, ISC-based SSIS projects, SQL Agent Jobs, multi-dimensional SSAS projects, to name a few. And when I worked as a DBA I wrote many ad-hoc scripts to automate many tedious processes I was required to do. And so when it came to pick a topic for today’s post I decided to go for something that not so much offers a complete, out-of-the-box, one-size-fits-all solution to everyone’s automation woes, but a bit of a left field example of how you can make use of .NET and PowerShell to automate a tedious process that would otherwise be time consuming and prone to error. It also means that I’ll be able to fit an example in one post, because automated deployments are anything but one post topics.

I knew I would be able to create a Powershell script that used AMO to check if a partition exists and drop it if it did. I also wanted the script to take into account any other partitions in other measure groups that may also need to be dropped. So I made sure the script uses PowerShell switches that can be included when calling the function, and if they are included then the pertaining partitions in that measure group will be deleted. So you can run the script for one, some or all of the measure groups in a cube.

You may have noticed that I said “Function”, and that is because within this script there are three functions; the Function that serves as the entry point into the script (remove-partitions), another function (set-partitions) that is called within the primary function to build up the name of the partitions, and finally the function that drops the partition (clear-partitions). So you will need to dot-source the script. Details of this are provided in the example below, and also the comments in the script. Oh, and while we’re on the subject of naming functions, please do everyone who is going to use your functions a favour and use approved verbs.

Because the script requires customising to a particular cube, I’ve created a demo version that uses the Adventure Works 2012 Multidimensional Model Enterprise Edition, which has several measure groups that are partitioned by year. All that needs to be altered for any other cube is the name of the SSAS database, the cube, measure group names and partition prefixes.

If extra measure groups need to be added then only the “remove-partitions” function needs to be updated: the other two functions will work irrespective of how many measure groups are added. A good option here might be to create an -all switch which includes all measure groups, because while 4 switches for all the measure groups seems manageable, any more might make it a chore to add the switches for all measure groups.

{