I’ve been wanting to demo cool-easy ways to do things in SSAS with PowerShell for a long time but haven’t gotten around to working on it much. Well, it’s time.
I use SSAS on a regular basis but I’m probably not going to come up with the lion’s share of ideas for improving PowerShell for SSAS, instead that will more likely come from hardcore SSAS users. However, since I don’t see their ideas flooding Connect or the Trello board just yet, I feel like they need a little inspiration to get the ball rolling. I think I have just the thing.
This weekend I chatted with several community members who use SSAS on a regular basis, a couple of them (Carlos Rodrigues & Shabnam Watson) have even done sessions on SSAS/MDX at our local UG & SQL Saturdays, so these people are the ‘sneezers‘ as Seth Godin calls them.
Frequently when developing updates to an SSAS cube I want to deploy my schema and process the dimension. Sometimes several of dimensions process successfully and then fails on one. At this point I go and correct the error, deploy the new schema, and then I only want to process all of my dimensions except the dimensions which did process successfully the first time. Sometimes this is really easy, but if you have a large number of dimensions this can become cumbersome since the only way to know which dimensions had been processed successfully or to right-click each dimension one at a time and find out, or to have memorized which dimensions had processed successfully on the earlier attempt. There can be a better way, and of course, PowerShell is one of those options. J The only problem is that as things currently stand, PowerShell is not as easy as it could be; the Invoke-ProcessDimension cmdlet doesn’t accept [direct] pipeline input. What is one to do when PowerShell isn’t as easy as it could be? File a Connect item of course!
At the end of the day we to be able to run a command like this:
CD SQLSERVER:SQLASLOCALHOSTSQL2016DatabasesAdventureWorksDW2014Multidimensional-EEDimensions;
DIR | Invoke-ProcessDimension-ProcessTypeProcessFull;
That way, if we want to process only some of the dimensions we will be able to run a command like this:
DIR |
Out-GridView -PassThru |
Invoke-ProcessDimension -ProcessType ProcessFull;
If you’ve seen one of my PowerShell sessions before, you’re bound to have seen me demonstrate the Out-GridView cmdlet and explain why I think the -PassThru parameter makes it one of the most compelling pieces of PowerShell as a whole. Well that applies to SSAS as well!
But here’s the thing, processing cube dimension a little more easily with SQL PowerShell is just the tip of the iceberg. Yes, it will make an easy to use, easy to demo case for SQL PowerShell + SSAS. But there’s got to be even better ideas out, and they’re probably in your head.
SSAS does have 11 SQL PowerShell cmdlets already, but do they cover everything you could ever need to make your job easier? My guess is the answer to that is “No”.
- Add-RoleMember
- Backup-ASDatabase
- Invoke-ASCmd
- Invoke-ProcessCube
- Invoke-ProcessDimension
- Invoke-ProcessPartition
- Merge-Partition
- New-RestoreFolder
- New-RestoreLocation
- Remove-RoleMember
- Restore-ASDatabase
When you think of it, please add that idea to the SSAS List on the SQL PowerShell Trello board so that others can Up-Vote it and we can get it into the product sooner than later.
I can’t wait to see what you come up with! J
