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.


6 comments:

Unknown said...

Hi Gouri Sohoni,

I get an error an row
db = server.Databases["database name"];

Error prompt:
"Database-Object is not listet"

AMO is definitely installed by SQL Server 2008.

You know maybe another error source ?

Thanks in advance

Anonymous said...

Hi Janosch,

Please check if the database name you are giving is available on Analysis services server (as this lists available databases). If still you get error please give complete error.

Sam Kane said...

Here are this and some other articles on SSAS AMO: http://ssas-wiki.com/w/Articles#Analysis_Management_Objects_.28AMO.29

Anonymous said...

Help. Where is the library "Server"?. Thank you very much.

//declare object
Server server;

Dr John said...

If you want to partition your cube or tabular project, check out the SSAS Partition Manager project on Codeplex which will dynamically add partitions with minimal configuration on your part. See https://ssaspartitionmanager.codeplex.com/

Anonymous said...

Great AMO overview, thanks for posting.