Powershell

There are 28 entries for the tag Powershell
SQL PASS Summit & MVP Deepdives Volume 2
I'm currently in Seattle enjoying the start of some of the activities around the SQL PASS Summit. One of the activities that I'm looking forward to is the book signing session for the MVP Deepdives Volume 2 at lunchtime on Wednesday. I was fortunate to be one of the 60 or so authors this time around with a chapter on using Powershell to manipulate Analysis Services databases. All of the proceeds from this book go to support Operation Smile. You'll find the book's website here: http://www.manning.com/dela... ......

Posted On Tuesday, October 11, 2011 9:24 AM | Comments (1)

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)

Which Edition is my Analysis Services Server?
There was a question the other day on the MSDN SSAS forum asking how to find out the Edition of SSAS which is running on a given server. The answer to this question could be either Standard, Enterprise or Developer. The Object Explorer in Management Studio displays the current version next to the server name, but not the Edition. Now at a basic level I know that this is stored as a property of the server, so you could run a DISCOVER_XML_METADATA command like the following. <Discover xmlns="urn:schemas-microsof... ......

Posted On Wednesday, October 14, 2009 8:20 PM | Comments (0)

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)

BIDS Helper release 1.4.1
I am happy to announce that we recently put out a new release of BIDS Helper. I have copied the release notes out below, but one important "feature" that is not listed is that we now have a build script which is a modified version of the psake Powershell build script written by James Kovacs. It had gotten to the point where it was a bit of an effort to do a build. You had to start by making sure you had the latest version of the source code, then version number had to be updated in a number of spots, ......

Posted On Thursday, May 14, 2009 6:40 AM | Comments (0)

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)

TechEd: BI Power Tools
For those of you who attended my talk or for any other interested parties, I have uploaded my slide deck from my MVP Theatre presentation at Tech.Ed Australia 2008 to my sky drive here: There is not too much to this deck as the presentation was mainly demos. If you are just after the links to the tools I demonstrated I have included them below: MDX Studio BIDS Helper Analysis Services Stored Procedure (ASSP) project PowerSSAS (which unfortunately only got a brief mention) And here are the links to ......

Posted On Saturday, September 6, 2008 2:10 PM | Comments (1)

PowerShell and SSIS
Up until now, most of my use of PowerShell has been against SSAS, but I recently had a need to setup a number of SSIS packages for some relatively large CSV files. My problem was that the CSV files had about 125 columns and 120 of them (10 years of monthly figures) need to be inserted into Numeric(18,5) columns in SQL Server. However the connection manager auto-detected these columns on my flat file connection manager as DT_FLOAT. Changing these columns to DT_NUMERIC and setting the scale to 5 quickly ......

Posted On Monday, July 14, 2008 12:21 AM | Comments (1)

SSAS: Reporting on Metadata
I had a comment recently on one of my older posts that I did about XMLA which asked about listing cubes in a database and whether the cube is processed and it's size. I have posted about a number of different ways of doing this over the last few years, but I thought I would use this opportunity to consolidate a list all the various ways of doing this that I could think of. Under the covers all these techniques are ultimately issuing some sort of XMLA discover against the SSAS database and returning ......

Posted On Tuesday, May 6, 2008 10:08 AM | Comments (4)

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)

powerSSAS: Getting Aggregations for a particular Attribute
If you have ever manually edited aggregation designs, you may have wondered what aggregation currently exist for a particular attribute. Well the following powerSSAS script will give you just that. (you could do this with straight PowerShell, but powerSSAS just makes it slightly easier to address a given measure group) Technically this script is just one single statement, with a couple of parameters to make it more re-usable. I have broken it up in order to make more readable by using the PowerShell ......

Posted On Sunday, April 27, 2008 10:46 PM | Comments (1)

Using PowerShell to update the case of Attribute names
I had a question recently about how to quickly convert all the attributes in a given database from UPPER CASE to Title Case. Now you could certainly write a C# or VB.Net based program, but I feel that a script is the way to go for a once off requirement like this and what better scripting environment could we ask for than PowerShell. So here is a short PowerShell script that does the job. [System.Reflection.Assembly... > $null [Microsoft.AnalysisServices... ......

Posted On Monday, March 17, 2008 12:01 AM | Comments (3)

PowerSSAS - Exporting information to Excel
There was a comment recently on the post where I annouced the release of powerSSAS, asking if you could export the information from the PowerShell Analyzer Provider Explorer. While I don't think you can do it from the Provider Explorer, but you can use the Export-Csv cmdlet that is built-in to PowerShell. Something like the following would export a list of dimensions to a .csv file. add-PSSnapin powerssas new-PSDrive ssas powerssas localhost cd ssas: cd "Databases\Adventure Works DW\Dimensions" # ......

Posted On Monday, January 7, 2008 2:56 PM | Comments (0)

powerSSAS: DMV Equivalents
I was reading Vidas Matelis' recent post on the metadata rowsets in SSAS 2008 and this got me to thinking about a discussion that Chris Webb and I had recently about the new "DMV" functionality is SSAS 2008. And that it basically that they are simply gives you an easier way to access information that is available through XMLA discover commands. And that you could already get to this information relatively easily through the Discover() function in the Analysis Services Stored Procedure (ASSP) project. ......

Posted On Monday, December 10, 2007 10:37 PM | Comments (0)

Announcing the release of PowerSSAS: a PowerShell provider for Analysis Services
Anyone who has been reading this blog for a while will know that I was quite taken with PowerShell when it was released and was even playing with it back when it was only known by it's code name of "Monad". Well, back when it was still known as Monad, I started experimenting with writing a provider for Analysis Services. It was one of those side projects that I was toying with in my spare time and I kept telling myself that I just need to add one or two more features and then it would be ready for ......

Posted On Monday, November 19, 2007 11:24 PM | Comments (9)

SSAS: AMO Powershell Provider
Following up from my recent post on Powershell, I have updated the Powershell provider for Analysis Services that I wrote back when Powershell was still known by its code name of "Monad", so that it works with the released version of Powershell. I have a few more features that I would like to add to it, but it is quite functional in its current state and I am thinking of putting it up on codeplex in its current form. My main issue now is what to call the thing. The project is currently called AmoPsProvider ......

Posted On Sunday, August 26, 2007 10:20 PM | Comments (8)

SSAS: Manipulating Cube Partitions with PowerShell
Jesse Orosz posted some interesting sample code recently on how to remove partitions with a certain prefix using AMO. I thought it would be an interesting comparison to see what similar code would look like in PowerShell. There is definitely not anything wrong with Jesse's code, it's obviously part of a much larger framework. It's more that I think PowerShell is a really good tool for doing these sort of admin tasks and it's an interesting exercise to take someone else's code from .net and re-implement ......

Posted On Sunday, August 26, 2007 10:13 PM | Comments (2)

SSAS: Detecting the version of SSAS on a given server
To detect the version of AS, use ADOMD and connect using a connection string like "Provider=MSOLAP;Data Source=<server>;" where <server> is the name of your server. If you have the 2000 and 2005 providers on your system, the 2005 provider will attempt to connect to first and will then fall through to using the 2000 (v8) provider if the server is an AS2000 server. Once you have an open ADOMD connection you can check the value of the ServerVersion property of the connection. A value with ......

Posted On Saturday, July 14, 2007 8:08 PM | Comments (1)

SSAS: New ASSP Function - ListFunctions()
I just added another sample to the Analysis Services Stored Procedure project. The following text is copied from the Wiki documentation which I just updated. This function is not currently available as part of a compiled release, but it is available from the source code repository, so you can download the code and compile it yourself. I also have a Powershell based variation of this proc which was actually how I prototyped the technique. I plan to polish up the rough edges of that script and post ......

Posted On Monday, January 8, 2007 9:16 PM | Comments (0)

Generating XMLA - referencing Objects by name
Jamie Thomson has an interesting post here http://blogs.conchango.com/... about some of his recent experiences with Analysis Services. One point that particularly stuck out for me an that was his criticism of having to access objects using their ID instead of their name in XMLA. This affected me recent while I was working on the PowerShell provider for AMO (which is still a work in progress). Most of the collections in AMO can be accessed using the object's ID, ......

Posted On Wednesday, June 21, 2006 8:52 PM | Comments (6)

Powershell IDE's
As you may have figured out, I am a bit of a PowerShell fan - and it just keeps getting better. As Scott Hanselman posted last week (http://www.hanselman.com/b... there are now 2 high quality IDE's for PowerShell available for free download. PowerShell Analyzer http://karlprosser.edify.us... PowerShell IDE http://powershell.com/ I don't know if PowerShell IDE's claim to be the “first PowerShell IDE“ is entirely true or not. From my perspective, I thought that ......

Posted On Thursday, June 15, 2006 9:14 PM | Comments (0)

Running XMLA Queries the Powershell way
I have been experimenting a bit more recently to see what I can do with Powershell and Analysis Services. The following small script executes an MDX query using an XMLA connection. I have borrowed the xsl files from one of Chris Harrington's excellent ThinOlap samples. 1 [System.Reflection.Assembly... 2 [Microsoft.AnalysisServices... = new-Object Microsoft.AnalysisServices.... 3 $xmlac.Connect("localhost\s... ......

Posted On Wednesday, June 14, 2006 8:25 PM | Comments (2)

A PowerShell provider for Analysis Management Objects
The more I play with PowerShell the more I like it. And now that I have a provider working that allows me to link in an Analysis Services server and navigate through the object hierarchy. Here is an early picture of the Shell (developed while PowerShell was still know by its code name of Monad) The screenshot above shows a PowerShell console that is connected to a drive called “amo“ which is linked to the SQL05 instance of Analysis Services 2005 on my localhost. One of the biggest differences ......

Posted On Sunday, May 7, 2006 8:14 PM | Comments (1)

Monad/MSH/Powershell What's in a name
I think I'm with Mitch on this one. I'm not overly keen on “PowerShell“, the new name for Monad. The product team have already renamed their blog to PowerShell, so it could be pretty much a done deal, but there seems to be a fair bit of criticism on Scobles post about the new name. While there are some people who point out some worse possible names, there are not many people showering the new name in praises. I quite liked Monad and even MSH was acceptable (although I don't know if Microsoft ......

Posted On Wednesday, April 26, 2006 9:38 PM | Comments (0)