通过AMO获取SQL Server SSAS信息（C#）

Analysis Management Objects (AMO) 是SQL Server SSAS的对象模型库，通过它可以方便的对SSAS里的对象进行访问及控制，包括Cube,DataSource, DataSourceView, Partition, Measure, Dimension, Assembly, Role以及DataMining对象等。要使用它，必须在机器上找到SSAS的安装路径..\MicrosoftSQL Server\90\SDK\Assemblies，把目录中的Microsoft.AnalysisServices.Dll文件加载到项目的Reference列表中，AMO对象就是通过这个Dll文件进行访问的。

首先要添加引用using Microsoft.AnalysisServices;

以下是测试用C#从SSAS中获取部分信息的源码：（代码中已经包含了较多的解释）

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<Database> 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<Cube> 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<Role> 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<Role> 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<Database> GetDatabaseCollection(Server server)
        {
            List<Database> collectdb = new List<Database>();
            foreach (Database db in server.Databases)
            {
                collectdb.Add(db);
            }
            return collectdb;
        }

        //get the list of a database cube name
        static public List<Cube> GetCubeCollection(Database db)
        {
            List<Cube> collectcube = new List<Cube>();
            foreach (Cube cube in db.Cubes)
            {
                collectcube.Add(cube);
            }
            return collectcube;
        }

        //get the list of the database roles
        static public List<Role> GetRolescollection(Database db)
        {
            List<Role> collectRoles = new List<Role>();
            foreach (Role cp in db.Roles)
            {
                collectRoles.Add(cp);
            }
            return collectRoles;
        }
        //get the list of the cube Roles
        static public List<Role> GetRolescollection2(Cube cube)
        {
            List<Role> collectionRoles = new List<Role>();
            foreach (CubePermission cp in cube.CubePermissions)
            {
                collectionRoles.Add(cp.Role);
            }
            return collectionRoles;
        }
    }
}

这里需要说明的是，当我获取Database的Roles和Cube的Roles值时，做了一个对比，他们的值是相同的。这可能的情况是，在Database中Cube只有一个，或者因为Cube隶属于database，他们的Roles值是相同的。

如下是运行完的截图：