Obtain SQL Server SSAS information through AMO

Source: Internet
Author: User

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:

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.