Working with SQL Server AMO (Analysis Management Objects) – Part 1

AMO provides us with a collection of classes with which we can manage objects of a running instance of SQL Server Analysis Services programmatically. These classes are available in namespace Microsoft.AnalysisServices. The assembly is available at the location <drive name>:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

With AMO we can create, modify, delete and view objects like dimensions, cubes, analysis services databases etc. We can also process the cubes, partitions as and when required.

One important thing to remember is you cannot query data with AMO; for this you will need ADOMD.NET.

AMO gives commands from client application and are converted to XMLA (XML for Analysis Services) and delivered to analysis services instance. AMO class library is equipped with various classes with the topmost class being the ‘Server.’' It follows the database, cubes, measure groups, measures, dimensions, perspectives.

The analysis services instance can have multiple databases, with AMO you can connect to one of them or there can be multiple instances of analysis services.

We need to connect to the server which has analysis services installed. Following code will list all existing analysis service databases from a server

//declare object
Server server;
try
{
       //connect to analysis server
       server = new Server();
       //if it is named instance server name should follow instance name
       server.Connect(@"data source=<server name>");
       //list all the databases from the server in listbox
       foreach (Database database in server.Databases)
       {
             listBox1.Items.Add(database.Name);
             //list all the data sources for each database in listbox
              foreach (DataSource  ds in database.DataSources)
             {
                  listBox2.Items.Add(ds.Name);
              }
        }
  }
  catch (Exception ex)
  {
         MessageBox.Show(ex.Message);
   }

AMO can be useful if we want to show users when were the cube or partitions last processed.

With the following code, we will choose one database from the analysis server and display the cubes, measure groups, measures, partitions and dimensions from it in a Listbox

//declare objects
Server server;
Database db=new Database();

try
{
      //connect to analysis server
      server = new Server();
      server.Connect(@"data source=<server name>");
      db = server.Databases["<database name>"];
       foreach (Cube cube in db.Cubes)
      {
           //will process full cube          
          cube.Process(ProcessType.ProcessFull);
          listBox3.Items.Add("Default measure: " + cube.DefaultMeasure);
          //list all cube names
          listBox3.Items.Add("Cube: " + cube.Name);
          //last processed is current time as we processed full
          listBox3.Items.Add(cube.LastProcessed);
          foreach (MeasureGroup measuregrp in cube.MeasureGroups)
          {
                //list all measure groups
                listBox3.Items.Add("Measure Group: " + measuregrp.Name);
                foreach (Partition partition in measuregrp.Partitions)
                {
                      //give partition name
                       listBox3.Items.Add("Partition: " + partition.Name);
                       //list data and time when partition was last processed
                       listBox3.Items.Add(partition.LastProcessed);
                 }
                 foreach (Measure measure in measuregrp.Measures)
                 {
                       //list measure for current measure group
                       listBox3.Items.Add("Measure: " + measure.Name);
                 }
            }
        }
        foreach (Dimension dim in db.Dimensions)
        {
             //list all dimensions
              listBox3.Items.Add("Dim: " + dim.Name);
         }
  }
 //capture specific exception
  catch (AmoException amoEx)
  {
          MessageBox.Show(amoEx.Message);
  }
  catch (Exception ex)
  {
          MessageBox.Show(ex.Message);
  }
}

This post showed how to connect to the analysis server and list all the databases. We then chose a database and displayed the cubes, measures, partitions and dimensions in a Listbox.

In the next part of this article, I will demonstrate the foolowing

- how to take backups of MultiDimensional Anaysis Service Databases,

- explore the security part of how to add a role, a member to role and how to provide the necessary permissions.

- how to create new databases, data sources, data source views and required tables .

- how AMO offers to wok without connecting to the analysis services instance.