1、安装Microsoft AnalysisServies
2、创建一个Analysis 项目如下图所示
3、根据业务需求，数据源及多维度数据集，编译成功，进行布署。
4、发布成功后，通过SQLSERVER企业管理器进行连接，如何在IIS7下创建Analysis Services服务可以baidu一下具体配置，这里不在多说了。
5、创建一个客户端测试
创建一个AdomdHelper类
#region using System; using System.Data; using Microsoft.AnalysisServices.AdomdClient; using System.Collections; using System.Collections.Generic; #endregion namespace WMS.Utility { /// <summary> /// Analysis Services /// </summary> public class AdomdHelper { #region Enum public enum Versions { Server, Provider, Client } #endregion #region 属性 /// <summary> /// 多维数据库连接对象 /// </summary> public AdomdConnection AdomdConn { get; set; } /// <summary> /// 连接字符串 /// </summary> public string ConnString { get; set; } /// <summary> /// 连接状态 /// </summary> public bool IsConnected { get { return GetConnectedState(); } } #endregion #region AdomdHelper public AdomdHelper() { ConnString = “http://localhost/olap/msmdpump.dll”; } #endregion #region GetConnectedState /// <summary> /// 判断连接AdomdConnection对象是State是否处于Open状态。 /// </summary> /// <returns></returns> public bool GetConnectedState() { bool isConn = false; isConn = (!(AdomdConn == null)) && (AdomdConn.State != ConnectionState.Broken) && (AdomdConn.State != ConnectionState.Closed); return isConn; } #endregion #region Disconnect /// <summary> /// 断开连接 /// </summary> /// <param name="connection">AdomdConnection对象的实例</param> /// <param name="destroyConnection">是否销毁连接</param> public void Disconnect(bool destroyConnection) { try { if (!(AdomdConn == null)) { if (AdomdConn.State != ConnectionState.Closed) { AdomdConn.Close(); } if(destroyConnection == true) { AdomdConn.Dispose(); AdomdConn = null; } } } catch(Exception ex) { throw ex; } } /// <summary> /// 断开连接 /// </summary> public void Disconnect() { Disconnect(true); } #endregion #region Connect /// <summary> /// 建立连接 /// </summary> /// <param name="connection">AdomdConnection对象的实例</param> /// <param name="connectionString">连接字符串</param> public AdomdConnection Connect() { try { AdomdConn = new AdomdConnection(ConnString); AdomdConn.Open(); }catch (Exception ex) { string str = ex.Message; } return AdomdConn; } /// <summary> /// 获取OLAP数据库。 /// </summary> /// <param name="connection">AdomdConnection对象的实例</param> /// <param name="connectionString">连接字符串</param> /// <returns></returns> public DataTable GetSchemaDataSet_Catalogs() { DataTable objTable = new DataTable(); try { if(!IsConnected)Connect(); objTable = AdomdConn.GetSchemaDataSet(AdomdSchemaGuid.Catalogs,null).Tables[0]; Disconnect(false); } catch(Exception err) { throw err; } return objTable; } #endregion #region GetSchemaDataSet_Cubes /// <summary> /// 通过SchemaDataSet的方式获取立方体 /// </summary> /// <param name="connection">AdomdConnection对象的实例</param> /// <param name="connectionString">连接字符串</param> /// <returns></returns> public string[] GetSchemaDataSet_Cubes(ref AdomdConnection connection,string connectionString) { string[] strCubes = null; bool connected = true; //判断connection是否已与数据库连接 DataTable objTable = new DataTable(); if (!IsConnected) Connect(); string[] strRestriction = new string[] { null, null, null }; objTable = connection.GetSchemaDataSet(AdomdSchemaGuid.Cubes,strRestriction).Tables[0]; if(connected == false) { Disconnect(false); } strCubes = new string[objTable.Rows.Count]; int rowcount = 0; foreach(DataRow tempRow in objTable.Rows) { strCubes[rowcount] = tempRow["CUBE_NAME"].ToString(); rowcount++; } return strCubes; } #endregion #region GetSchemaDataSet_Dimensions /// <summary> /// 通过SchemaDataSet的方式获取制定立方体的维度 /// </summary> /// <param name="connection">AdomdConnection对象的实例</param> /// <param name="connectionString">连接字符串</param> /// <returns></returns> public string[] GetSchemaDataSet_Dimensions(string cubeName) { string[] strDimensions = null; DataTable objTable = new DataTable(); if (!IsConnected) Connect(); string[] strRestriction = new string[]{null,null,cubeName,null,null}; objTable = AdomdConn.GetSchemaDataSet(AdomdSchemaGuid.Dimensions,strRestriction).Tables[0]; Disconnect(false); strDimensions = new string[objTable.Rows.Count]; int rowcount = 0; foreach(DataRow tempRow in objTable.Rows) { strDimensions[rowcount] = tempRow["DIMENSION_NAME"].ToString(); rowcount++; } return strDimensions; } #endregion #region GetCubes /// <summary> /// 以connection的方式立方体 /// </summary> /// <param name="connection">AdomdConnection对象的实例</param> /// <param name="connectionString">连接字符串</param> /// <returns></returns> public string[] GetCubes(ref AdomdConnection connection,string connectionString) { string[] strCubesName = null; if (!IsConnected) Connect(); int rowcount = connection.Cubes.Count; strCubesName = new string[rowcount]; for(int i=0;i<rowcount;i++) { strCubesName[i] = connection.Cubes[i].Caption; } Disconnect(false); return strCubesName; } #endregion #region GetDimensions /// <summary> /// 获取维度 /// </summary> /// <param name="connection">AdomdConnection对象的实例</param> /// <param name="connectionString">连接字符串</param> /// <param name="CubeName">名称</param> /// <returns></returns> public string[] GetDimensions(ref AdomdConnection connection,string connectionString,string CubeName) { string[] strDimensions = null; if (!IsConnected) Connect(); int rowcount = connection.Cubes[CubeName].Dimensions.Count; strDimensions = new string[rowcount]; for(int i=0;i<rowcount;i++) { strDimensions[i] = connection.Cubes[CubeName].Dimensions[i].Caption.ToString(); } Disconnect(false); return strDimensions; } #endregion #region GetCellSet /// <summary> /// 根据mdx语句返回CellSet /// </summary> /// <param name="strMDX">MDX语句</param> /// <returns></returns> public CellSet GetCellSet(string strMDX) { if (!IsConnected) Connect(); AdomdCommand comm = new AdomdCommand(strMDX, AdomdConn); CellSet cs = comm.ExecuteCellSet(); return cs; } #endregion #region GetCellSet2DataTable /// <summary> /// 根据mdx语句获取CellSet，同时转换为DataTable /// </summary> /// <param name="strMDX">MDX语句</param> /// <returns></returns> public DataTable GetCellSet2DataTable(string strMDX) { if (!IsConnected) Connect(); AdomdCommand comm = new AdomdCommand(strMDX, AdomdConn); CellSet cs = comm.ExecuteCellSet(); DataTable dt = CellSetToTable(cs); return dt; } #endregion #region CellSetToTable /// <summary> /// 将CellSet转化成Table /// </summary> /// <param name="cellset">CellSet</param> /// <returns></returns> public DataTable CellSetToTable(CellSet cellset) { DataTable table = new DataTable("cellset"); int columnIndex = 0; Axis columns = cellset.Axes[0]; //获取列轴 Axis rows = cellset.Axes.Count > 1 ? cellset.Axes[1] : null;//获取行轴 CellCollection valuesCell = cellset.Cells;//获取度量值单元集合 //行轴的级别标题为表的列 for (int i = 0; rows != null && i < rows.Set.Hierarchies.Count; i++) { DataColumn col = new DataColumn(); col.ColumnName = "col_" + i; col.Caption = rows.Set.Hierarchies[i].Caption; table.Columns.Add(col); columnIndex++; } //行轴的各个成员的标题变成表的列 for (int i = 0; i < columns.Set.Tuples.Count; i++) { MemberCollection mc = columns.Set.Tuples[i].Members; DataColumn col = new DataColumn(); col.ColumnName = "col_" + columnIndex; col.Caption = mc[mc.Count - 1].Caption; table.Columns.Add(col); columnIndex++; } int valueIndex = 0; DataRow row = null; if (rows != null) { //向表中填充数据 for (int i = 0; i < rows.Set.Tuples.Count; i++) { row = table.NewRow(); //表所有行的层级的标题 MemberCollection mc = rows.Set.Tuples[i].Members; int j = 0; for (j = 0; j < mc.Count; j++) { row[j] = mc[j].Caption.Replace("All", "合计"); } for (int k = 0; k < columns.Set.Tuples.Count; k++) {//按顺序把度量值单元集合的值填充到表中 row[k + j] = valuesCell[valueIndex].Value; valueIndex++; } table.Rows.Add(row); } } else { //只有一个维度时 row = table.NewRow(); for (int i = 0; i < columns.Set.Tuples.Count; i++) { row[i] = valuesCell[valueIndex].Value; valueIndex++; } table.Rows.Add(row); } return table; } #endregion #region GetMeasureGroup /// <summary> /// 获得度量值组 /// </summary> /// <param name="cubeName">名称</param> /// <returns></returns> public IList<Dictionary<int, string>> GetMeasureGroup(string cubeName) { if (!IsConnected) Connect(); IList<Dictionary<int, string>> groupList = new List<Dictionary<int, string>>(); string mdx = "SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS Where CUBE_NAME = '" + cubeName + "';"; AdomdCommand comm = new AdomdCommand(mdx, AdomdConn); AdomdDataReader reader = comm.ExecuteReader(); int i=0; while (reader.Read()) { string name = reader["MEASUREGROUP_NAME"].ToString(); Dictionary<int, string> row = new Dictionary<int, string>(); row.Add(i, name); groupList.Add(row); i++; } reader.Close(); return groupList; } #endregion #region GetMeasures /// <summary> /// 获得度量值 /// </summary> /// <param name="cubeName">名称</param> /// <returns></returns> public IList<Dictionary<int, string>> GetMeasures(string cubeName, string measureGroupName) { if (!IsConnected) Connect(); IList<Dictionary<int, string>> groupList = new List<Dictionary<int, string>>(); string mdx = "SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES Where CUBE_NAME = '" + cubeName + "' And MEASUREGROUP_NAME='" + measureGroupName + "';"; AdomdCommand comm = new AdomdCommand(mdx, AdomdConn); AdomdDataReader reader = comm.ExecuteReader(); int i = 0; while (reader.Read()) { string name = reader["MEASURE_NAME"].ToString(); Dictionary<int, string> row = new Dictionary<int, string>(); row.Add(i, name); groupList.Add(row); i++; } reader.Close(); return groupList; } #endregion #region GetDimensions /// <summary> /// 获得维度 /// </summary> /// <param name="cubeName">名称</param> /// <returns></returns> public IList<Dictionary<int, string>> GetDimensions(string cubeName) { if (!IsConnected) Connect(); IList<Dictionary<int, string>> groupList = new List<Dictionary<int, string>>(); string mdx = "SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS Where CUBE_NAME = '" + cubeName + "' And DIMENSION_TYPE<>2;"; AdomdCommand comm = new AdomdCommand(mdx, AdomdConn); AdomdDataReader reader = comm.ExecuteReader(); int i = 0; while (reader.Read()) { string name = reader["DIMENSION_NAME"].ToString(); Dictionary<int, string> row = new Dictionary<int, string>(); row.Add(i, name); groupList.Add(row); i++; } reader.Close(); return groupList; } #endregion #region GetHierarchys /// <summary> /// 获得维度层级结构 /// </summary> /// <param name="cubeName">名称</param> /// <returns></returns> public IList<KeyCodeNameModel> GetHierarchys(string cubeName, string dimensionName) { if (!IsConnected) Connect(); IList<KeyCodeNameModel> groupList = new List<KeyCodeNameModel>(); string mdx = "SELECT [HIERARCHY_UNIQUE_NAME], HIERARCHY_CAPTION FROM $SYSTEM.MDSCHEMA_HIERARCHIES Where HIERARCHY_IS_VISIBLE And CUBE_NAME = '" + cubeName + "' And [DIMENSION_UNIQUE_NAME]='[" + dimensionName + "]';"; AdomdCommand comm = new AdomdCommand(mdx, AdomdConn); AdomdDataReader reader = comm.ExecuteReader(); int i = 0; while (reader.Read()) { string name = reader["HIERARCHY_UNIQUE_NAME"].ToString(); string caption = reader["HIERARCHY_CAPTION"].ToString(); KeyCodeNameModel row = new KeyCodeNameModel(); row.ID = i; row.Code = name; row.Name = caption; groupList.Add(row); i++; } reader.Close(); return groupList; } #endregion #region GetLevels /// <summary> /// 获得维度级别 /// </summary> /// <param name="cubeName">名称</param> /// <returns></returns> public IList<KeyCodeNameModel> GetLevels(string cubeName, string hierarchyName) { if (!IsConnected) Connect(); IList<KeyCodeNameModel> groupList = new List<KeyCodeNameModel>(); KeyCodeNameModel memberRow = new KeyCodeNameModel(); memberRow.ID = 0; memberRow.Code = "成员"; memberRow.Name = "成员"; groupList.Add(memberRow); string mdx = @"SELECT LEVEL_CAPTION, [LEVEL_UNIQUE_NAME] FROM $SYSTEM.MDSCHEMA_LEVELS Where LEVEL_IS_VISIBLE AND LEVEL_CAPTION<>'(ALL)' AND CUBE_NAME = '" + cubeName + "' And [HIERARCHY_UNIQUE_NAME]='" + hierarchyName + "';"; AdomdCommand comm = new AdomdCommand(mdx, AdomdConn); AdomdDataReader reader = comm.ExecuteReader(); int i = 1; while (reader.Read()) { string name = reader["LEVEL_UNIQUE_NAME"].ToString(); string caption = reader["LEVEL_CAPTION"].ToString(); KeyCodeNameModel row = new KeyCodeNameModel(); row.ID = i; row.Code = name; row.Name = caption; groupList.Add(row); i++; } reader.Close(); return groupList; } #endregion #region GetMembers /// <summary> /// 获得维度级别 /// </summary> /// <param name="cubeName">名称</param> /// <returns></returns> public IList<KeyCodeNameModel> GetMembers(string cubeName, string levelName) { if (!IsConnected) Connect(); IList<KeyCodeNameModel> groupList = new List<KeyCodeNameModel>(); string mdx = @" SELECT [MEMBER_UNIQUE_NAME], [MEMBER_CAPTION] FROM $SYSTEM.MDSCHEMA_MEMBERS Where CUBE_NAME = '" + cubeName + "' And [LEVEL_UNIQUE_NAME]='" + levelName + "';"; AdomdCommand comm = new AdomdCommand(mdx, AdomdConn); AdomdDataReader reader = comm.ExecuteReader(); int i = 1; while (reader.Read()) { string name = reader["MEMBER_UNIQUE_NAME"].ToString(); string caption = reader["MEMBER_CAPTION"].ToString(); KeyCodeNameModel row = new KeyCodeNameModel(); row.ID = i; row.Code = name; row.Name = caption; groupList.Add(row); i++; } reader.Close(); return groupList; } #endregion } }
6、需要求注意字符集
在 Microsoft.AnalysisServices.AdomdClient.HttpStream.GetResponseDataType()
在 Microsoft.AnalysisServices.AdomdClient.CompressedStream.GetResponseDataType()
在 Microsoft.AnalysisServices.AdomdClient.XmlaClient.EndRequest()
在 Microsoft.AnalysisServices.AdomdClient.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility)
在 Microsoft.AnalysisServices.AdomdClient.XmlaClient.Discover(String requestType, String requestNamespace, ListDictionary properties, IDictionary restrictions, Boolean sendNamespacesCompatibility)
在 Microsoft.AnalysisServices.AdomdClient.XmlaClient.SupportsProperty(String propName)
在 Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
在 Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
在 Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
在 Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
在 Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
在 Rattan.Core.Utility.AdomdHelper.Connect() 位置 d:\work\wms\ERPSource_WMS\Rattan.Core\Utility\AdomdHelper.cs:行号 130
