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.
XMLA – A Less Flexible Language Than T-SQL
One such an example of ad-hoc DBA tasks was when I had to delete about 600 partitions from a measure group that had thousands of partitions. Doing this manually would be ridiculous, so at the time I created a SQL script that used some dynamic T-SQL to create the delete commands in XMLA. XMLA has no “delete if exist” type syntax, so if I needed to run this again, this dynamic SQL output wouldn’t work. And so I decided that if I had to run the same task a gain I would write a PowerShell script that would run DSC-style and drop the partitions that were no longer required. And funnily enough, that is exactly what I had to do.
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 used 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.
Below the script is an example of the script in action against the Adventure Works cube. I’ve also put comments in the script. The example is proper noddy example, so, as ever, if something is not clear feel free to add a comment to this post.
Ok, let’s dot-source the script. So copy and save the script somewhere as “dropPartitions.ps1”. Then right click and copy as path the file. Open a Powershell window and type a dot then a space, and paste the location of the file into the console.
Now the primary function “remove-partitions” will be available to you (the other functions will also be available, but outside of the context of this script there’s no value in trying to use them.) I’m running locally so instances are “.”, but you can run remotely.
The function runs, finds two partitions per measure group to delete and drops them.
Refresh the view in the object explorer; the partitions for just Reseller Orders and Reseller Sales will be gone because those were the switches included, and neither of the “Internet*” switches were included.
If we run again then the partitions will not be found and the script does nothing.
So we can be confident that if we had to change the “BETWEEN” values that included partitions already dropped, the script will just ignore them.
..and this is the progress bar.
I’ve really developed a jones for using PowerShell to make painful, repetitive, time consuming tasks much quicker. PowerShell’s great for automation, but it’s also great for running tasks on an ad-hoc basis. There’s probably a formula for when a task ought to be automated, and when I worked as a DBA that formula was “whenever I found something tedious.” Probably not very scientific but it seemed to work well enough…