Obtaining SQL Server SSAS information via AMO

Source: Internet
Author: User
Tags foreach object model

Analysis Management Objects (AMO) is the object Model Library of SQL Server SSAS, which allows easy access to and control over objects in SSAS, including Cube,datasource, DataSourceView, Partition, Measure, Dimension, Assembly, role, and DataMining objects. To use it, you must locate the SSAS installation path MicrosoftSQL server90sdkassemblies on the machine, The Microsoft.AnalysisServices.Dll file in the directory is loaded into the project's reference list, and the AMO object is accessed through this DLL file.

First, you add a reference using Microsoft.analysisservices;

Here's the source code for testing part of the information from SSAS in C #: (There's a lot of explanations already in it)

Using System;

Using System.Collections.Generic;

Using System.Linq;

Using System.Text;

Using System.Threading.Tasks;

Using Microsoft.analysisservices;

Namespace Amotest

{

Class Program

{

static void Main (string[] args)

{

String connectestring = "Data Source =servername; Provider=msolap ";

Server Ssasserver = new server ();

Ssasserver.connect (connectestring);

List outdatabase = getdatabasecollection (ssasserver);

Show all database on the server

Console.WriteLine ("-------------databse-------------------------------");

for (int i = 0; i < Outdatabase.count; i++)

{

Console.WriteLine (Outdatabase[i]. Name.tostring ());

}

Show the Cube of a database

Console.WriteLine ("---------------Cube--------------------------------");

List Outcube = getcubecollection (outdatabase[0]);

for (int i = 0; i < Outcube.count; i++)

{

Console.WriteLine (Outcube[i]. Name.tostring ());

}

Show all Roles of one database

Console.WriteLine ("---------------roles_database-------------------------");

List outrole = getrolescollection (outdatabase[0]);

Console.WriteLine (Outrole[0]. Members.count);

for (int i = 0; i < outrole[0]. Members.count; i++)//to Show the Detial

//{

Console.WriteLine (Outrole[0]. Members[i]. Name);

//}

Show all Roles of One cube

Console.WriteLine ("---------------roles_cube----------------------------");

List OutRole2 = GetRolescollection2 (outcube[0]);

Console.WriteLine (Outrole2[0]. Members.count);

for (int i = 0; i < outrole2[0]. Members.count; i++)

//{

Console.WriteLine (Outrole2[0]. Members[i]. Name); To show the detial of the

//}

Console.read ();

}

Get the list of the database name

static public List getdatabasecollection (server server)

{

List collectdb = new list ();

foreach (Database db in Server. Databases)

{

Collectdb. ADD (DB);

}

return collectdb;

}

Get the list of a database cube name

static public List getcubecollection (Database db)

{

List collectcube = new list ();

foreach (Cube cube in db.) Cubes)

{

Collectcube. ADD (Cube);

}

return collectcube;

}

Get the list of the database roles

static public List getrolescollection (Database db)

{

List collectroles = new list ();

foreach (role CP in DB.) Roles)

{

Collectroles.add (CP);

}

return collectroles;

}

Get the list of the cube Roles

static public List GetRolescollection2 (Cube cube)

{

List collectionroles = new list ();

foreach (cubepermission cp in Cube. Cubepermissions)

{

Collectionroles.add (CP. role);

}

return collectionroles;

}

}

}

The point to note here is that when I get the roles of the database and the roles value of the cube, I make a comparison, and their values are the same. This may be the case where the cube is only one in the database, or because the cube is subordinate to the database, their roles values are the same.

The following screenshot is run:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.