Obtain SQL Server SSAS information through AMO

Analysis Management Objects (AMO) is the object model library of SQL Server SSAS. It allows you to conveniently access and control Objects in SSAS, including Cube, DataSource, performanceview, Partition, Measure, dimension, Assembly, Role, and DataMining objects. To use it, you must find the SSAS installation path \ MicrosoftSQL Server \ 90 \ SDK \ Assemblies on the machine, and set Microsoft. analysisServices. when the Dll file is loaded into the Reference list of the project, the AMO object is accessed through this Dll file.

First, add and reference using Microsoft. AnalysisServices;

The following is the source code used to obtain part of the information from SSAS: (the Code already contains a lot of explanations)

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 of the role Members

//{

// 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 role Members

//}

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 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)

{

List collectionRoles = new List ();

Foreach (CubePermission cp in cube. CubePermissions)

{

CollectionRoles. Add (cp. Role );

}

Return collectionRoles;

}

}

}

It should be noted that when I get the Roles value of the Database and the Roles value of the Cube, I made a comparison and their values are the same. This may be because there is only one Cube in the Database, or because the Cube belongs to the database, their Roles values are the same.

As follows: