Chapter 54. Scripting SSAS databases – AMO and PowerShell, Better Together

 

Darren Gosbell

Have you ever found yourself clicking repetitively through an action in Business Intelligence Development Studio (BIDS)? Or struggling to compose XML for Analysis (XMLA) Create or Alter statements? Then read on to find out how to make your life a whole lot easier.

For designing and building Analysis Services databases, Microsoft has given you a great tool in the form of BIDS. For administrative tasks, you have SQL Server Management Studio (SSMS). These are both great GUI tools, but like all GUI tools, they fall short when you need to perform repetitive actions or when you have to automate a series of actions. Fortunately, both of these GUI tools have been implemented over the top of the Analysis Management Objects (AMO) library. The fact that both SSMS and BIDS use AMO internally is significant; it means that every single piece of functionality that these two GUI tools expose can be replicated programmatically. There’s no action that they perform that you can’t replicate in your own program or script.

AMO is a .NET library, so when it comes to working with AMO you have two choices:

  • Write a .NET program and compile it
  • Write a PowerShell script

Advantages of PowerShell

Using PowerShell presents a number of advantages.

Advantages of compiled code

Automating processing

Repetitive design changes

Summary

About the author