当前位置:Gxlcms > mysql > 通过AMO获取SQLServerSSAS信息

通过AMO获取SQLServerSSAS信息

时间:2021-07-01 10:21:17 帮助过:35人阅读

Analysis Management Objects (AMO) 是SQL Server SSAS的对象模型库,通过它可以方便的对SSAS里的对象进行访问及控制,包括Cube,DataSource, DataSourceView, Partition, Measure, Dimension, Assembly, Role以及DataMining对象等。要使用它,必须在机器上找

  Analysis Management Objects (AMO) 是SQL Server SSAS的对象模型库,通过它可以方便的对SSAS里的对象进行访问及控制,包括Cube,DataSource, DataSourceView, Partition, Measure, Dimension, Assembly, Role以及DataMining对象等。要使用它,必须在机器上找到SSAS的安装路径MicrosoftSQL Server90SDKAssemblies,把目录中的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 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 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;

  }

  }

  }

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

  如下是运行完的截图:

通过AMO获取SQL Server SSAS信息 三联

人气教程排行