PowerShell

Scripts, Providers, Cmdlets and comments relating to PowerShell
How to build your own SSAS Resource Governor with PowerShell
A few weeks ago I posted a way to manually find and kill long running SSAS queries. In this post I’ll take it a step further and show you an automated solution. The idea behind this technique is inspired by a blog post Chris Webb did some years ago. Chris implemented his solution using SSIS while this version uses PowerShell. You might ask - why create a Powershell version? Well it does a little bit more in that it logs the cancelled queries and sends the user an email. It also uses membership in ......

Posted On Monday, March 31, 2014 6:04 AM | Comments (0)

SSAS – Listing Active Queries with PowerShell
Recently one of our production Tabular servers was being hit with a number of extremely large queries that were causing excessive load and impacting the performance for other users. These queries were pretty much attempts at extracting detail level information. Some of these were due to the fact that some of the users had their own alternate hierarchy which we had not been informed of, so we were able to extend the cube design to rectify this. Other users were trying to build 10+ page reports in ......

Posted On Monday, March 3, 2014 7:20 AM | Comments (2)

PowerSSAS v0.3.1.0 released
Today I put out a new release for PowerSSAS. This release adds the following new cmdlets: backup-ASDatabase clear-ASCache get-ASConnection get-ASRole restore-ASDatabase It also includes a Powershell v2 .psd1 module file and includes the option to just download a zip file which can be copied into a folder your modules folder. So you can now install PowerSSAS without needing to have admin privileges. There are instructions on installing PowerSSAS as a v2 module here. I have also done some preliminary ......

Posted On Sunday, July 18, 2010 10:34 PM | Comments (0)

PowerShell One Liner: Duplicating a folder structure in a Sharepoint document library
I was asked by someone at work the other day, if it was possible in Sharepoint to create a set of top level folders in one document library based on the set of folders in another library. One document library has a set of top level folders that is basically a client list and we needed to create the same top level folders in another library. I knew that it was possible to open a Sharepoint document library in explorer using a UNC style path and that you could map a drive using a technique like this ......

Posted On Monday, May 24, 2010 5:33 PM | Comments (2)

SSAS: Automating the Scripting of an SSAS database
I've been meaning to post this for a little while, and a recent post on the SSAS forum at ssas-info.com prompted me to finally get around to it. Basically the small Powershell script below will attach to the specified SSAS server and script all of the databases out to an XMLA file. In this example I also add a timestamp in the form of YYYYMMDD to the end of the file. $serverName = "localhost\sql08" $outputFolder = "C:\data\" ## load the AMO and XML assemblies into the current runspace [System.Reflection.Assembly... ......

Posted On Monday, February 22, 2010 11:43 AM | Comments (1)

SSAS: Powershell to replace a group member in a role
There was a question in the SSAS forum recently on how to replace one group name with another within the membership of a number of SSAS roles in a number of databases. While you could possibly do this with XMLA it would be tricky as you have to re-submit the whole membership list, you can't just add/remove single members. The easiest way to do this is to write something using the AMO library and in my opinion the easiest way to write a script for AMO is using Powershell. Below is my short script ......

Posted On Thursday, June 11, 2009 8:41 AM | Comments (1)

PowerShell: List all the senders from an Outlook folder
A friend of mine was looking for a tool today to extract a list of names and email addresses from a folder in outlook. I know that Outlook has a comprehensive COM based object model which I figured that I should be able to access from Powershell. I quick search turned up articles from both James Manning and Lee Holmes on automating Outlook from Powershell (which I think I have come across before). A bit of poking around using the get-member helped me locate the properties I needed and resulted in ......

Posted On Wednesday, April 8, 2009 9:17 PM | Comments (8)

SSAS: Are my Aggregations processed?
You have designed Aggregations for your cube, but how do you know that they are currently processed? Hopefully you have your processing routines setup in production so that your indexes are always kept processed. But maybe you are working in a development environment or you are performance tuning that you want to double check that your aggregations are currently processed. It is not immediately obvious how you can figure if the indexes for a partition or a set of partitions are processed as this ......

Posted On Tuesday, December 2, 2008 10:17 PM | Comments (6)

Teaser: SSAS Cache Warming with PowerShell
You may be thinking oh-no, not another one. Allan Mitchell recently posted an example of an SSIS package that would warm the SSAS cache which is an updated version with a slightly simpler SSIS data flow from something Chris Webb originally blogged. Being a PowerShell fan as I read Allan's post I realised that most of the tasks mapped to native PowerShell cmdlets and I already had PowerShell code to execute an MDX command, so all I was missing was someway of reading in the trace data. What I ended ......

Posted On Sunday, November 16, 2008 11:41 PM | Comments (5)

powerSSAS: Listing Role information
After my last post on how to list the aggregations for a given attribute using Powershell and PowerSSAS, miltruenos commented, asked if it was possible to do a similar script to show roles and allowed/denied sets, so here is just such a script. add-PSSnapin powerSSASnew-PSDrive ssas powerssas localhost $roles = gi "\Databases\Adventure Works DW\" ` | % {$_.Dimensions} ` | % {$_.DimensionPermissions} ` | % {$_.AttributePermissions} ` | add-Member ScriptProperty DimensionName {$this.Parent.Parent.Name}-... ......

Posted On Thursday, May 1, 2008 10:29 PM | Comments (3)

Full PowerShell Archive