Saturday, February 14, 2009

Using AMO to interface with SQL Server Analysis Services (SSAS)

In SQL Server 2000, working programmatically with SSAS was a real challenge. Performing a simple operation such as cube processing was a non trivial process. One of the systems I have been working on recently was using the following technique to process a cube:

1. Client application calls a web service

2. The web service calls stored procedure

3. The stored procecure uses sp_oacreate() to invoke a DTS package

4. The DTS package uses an Analysis Services Processing Task to process the desired cube

Needless to say this chain of execution is more complicated that we would like, and there are many problems that can be encountered using this technique. For example, sp_oacreate has been known to cause memory fragmentation problems in the mem-to-leave area of SQL Server. This issue was resolved in SQL 2000 SP4, however I am still not convinced that the process of building up and tearing down objects is rock solid.

Wouldn’t it be nice to have an API straight into SSAS?

In SQL 2005 and 2008, we can use AMO (Analysis Management Objects)!

http://msdn.microsoft.com/en-us/library/ms345089(SQL.90).aspx

AMO is a straightforward, simple to use object model which allows you to do almost anything with SSAS programmatically. In fact, SQL Server Management Studio (SSMS) is nothing more than a well presented SMO/AMO client.

The point is that it is possible to build extremely powerful management applications by hooking into the AMO API. How do you get started with AMO?

Here is a simple starting point:

· Create a new Visual Studio 2008 Console Application

· Add a reference to Microsoft.AnalysisServices.dll ( I am using version 9.0.242.0)

· Add a using directive to the top of your code

using Microsoft.AnalysisServices;

· Now you are ready to start using the AMO object model. Instantiate a server object and connect to SSAS by providing a connection string

Server s = new Server();

s.Connect(“Provider=msolap.3;Datasource=localhost;Initial Catalog=FINANCIALS'”);

You can check the boolean value of s.IsConnected to ensure that you are ready to do some work, although I expect the Connect() method would throw an exception if it doesn’t find the designated server.

Once you have a reference to the server object, you can access other objects by traversing the hierarchy. The following two lines get a reference to a database by name and then process that database

Database d = s.Databases.GetByName("MY_FINANCIALS");

d.Process();

So there you have it – programmatically processing an SSAS cube in four lines of code. We’ve come a long way since SQL 2000!

No comments: