MDX查询SSAS结果--通过adomd.net展示到客户端
SSAS多维模型建好之后，除了在excel客户端直接链接ssas源拖拽pivot分析使用外，还可以讲要展示的结果集通过MDX语句查询出来，嵌入到程序中，通过运行程序跑出完整的报表。如图所示：
MDX对于OLAP数据集就像Transact-SQL对于Microsoft SQL Server关系数据库。在SQL里mdxquery查询视窗，将SSAS的查询结果，显示在我们自定义的应用程序里面，和传统的ADO.net链接SQL数据库引擎相似。
例如如下MDX查询代码：
WITH MEMBER [Target] AS [Measures].[USD REV TARGET - Vw ST SUBREGION TGT]/1000 MEMBER [QTD] AS [Measures].[ST AMT USD]/1000 MEMBER [HitRate] AS IIF([Target]=NULL OR [Target]=0,NULL,[QTD]/[Target]) MEMBER [SO_Target] as [Measures].[USD REV TARGET]/1000 MEMBER [SO_QTD] AS [Measures].[SO AMT USD]/1000 MEMBER [SO_HitRate] as IIF([SO_Target]=NULL OR [SO_Target]=0,NULL,[SO_QTD]/[SO_Target]) SELECT CROSSJOIN([OverviewAllBrand-BU].[BU CODE], {[Target],[QTD],[HitRate],[SO_Target],[SO_QTD],[SO_HitRate]}) ON COLUMNS, CROSSJOIN({[OverviewAllBrand-Region].[REGION EN], [OverviewAllBrand-Region].[REGION EN].CHILDREN }, {[OverviewAllBrand-Channel].[CHANNEL TYPE].CHILDREN, [OverviewAllBrand-Channel].[CHANNEL TYPE] }) ON ROWS FROM [OverviewAllBrand] where ([OverviewAllBrand-Region].[AREA CN].&[CHINA])
原理：
1、客户端与SSAS的通讯，其实传递是XML/A指令。发起这种指令，可以通过标准的Web Request(封装为一个SOAP包即可），也可以通过客户端API。
2、SQL Server 自带了一个客户端API，Microsoft.AnalysisServices.AdomdClient.dll.这个程序集，一般是在 C:\Program Files (x86)\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll 这个路径下面。所以首先项目程序中需要先引用这个dll。
使用Adomd的方式与标准的ADO.NET还是有相似之处的，也需要有Connection以及Command之类的对象，只不过它的查询语法是所谓的MDX(多维表达式），而且它返回的结果不是一个平面的二维结构，可能会有多个轴（最多可以有128个轴），所以解析的时候会更加复杂些。
以下是代码：
public DataTable GetRegionOverview() { DataTable dt = null; string strSql = @"WITH MEMBER [Target] AS [Measures].[USD REV TARGET - Vw ST SUBREGION TGT]/1000 MEMBER [QTD] AS [Measures].[ST AMT USD]/1000 MEMBER [HitRate] AS IIF([Target]=NULL OR [Target]=0,NULL,[QTD]/[Target]) MEMBER [SO_Target] as [Measures].[USD REV TARGET]/1000 MEMBER [SO_QTD] AS [Measures].[SO AMT USD]/1000 MEMBER [SO_HitRate] as IIF([SO_Target]=NULL OR [SO_Target]=0,NULL,[SO_QTD]/[SO_Target]) SELECT CROSSJOIN([OverviewAllBrand-BU].[BU CODE], {[Target],[QTD],[HitRate],[SO_Target],[SO_QTD],[SO_HitRate]}) ON COLUMNS, CROSSJOIN({[OverviewAllBrand-Region].[REGION CN], [OverviewAllBrand-Region].[REGION CN].CHILDREN }, {[OverviewAllBrand-Channel].[CHANNEL TYPE].CHILDREN, [OverviewAllBrand-Channel].[CHANNEL TYPE] }) ON ROWS FROM [OverviewAllBrand] where ([OverviewAllBrand-Region].[AREA CN].&[CHINA])"; DataTable ssasResult = base.GetSsasResult(strSql); if (ssasResult.Rows.Count > 2) { int num; ssasResult.Rows[0][2] = " All Sell Thru Rev (K$) "; ssasResult.Rows[0][3] = " All Sell Thru Rev (K$) "; ssasResult.Rows[0][4] = " All Sell Thru Rev (K$) "; ssasResult.Rows[0][5] = " All Sell Out Rev (K$) "; ssasResult.Rows[0][6] = " All Sell Out Rev (K$) "; ssasResult.Rows[0][7] = " All Sell Out Rev (K$) "; ssasResult.Rows[1][5] = " Target "; ssasResult.Rows[1][6] = " QTD "; ssasResult.Rows[1][7] = " HitRate "; ssasResult.Rows[1][0] = " Region "; ssasResult.Rows[1][1] = " Channel "; for (num = 0; num < ssasResult.Rows.Count; num++) { if (ssasResult.Rows[num][0].ToString().ToLower() == "all") { ssasResult.Rows[num][0] = " ACCN "; } if (ssasResult.Rows[num][1].ToString().ToLower() == "all") { if (ssasResult.Rows[num][0].ToString().Trim().ToUpper() == "ACCN") { ssasResult.Rows[num][1] = " ACCN Total "; } else { ssasResult.Rows[num][1] = " Sub Total "; } } } return ssasResult; }
BaseDBAccess.cs
使用标准的方法：CellSet2DataTable(CellSet cs)返回一个特殊的CellSet对象，因为SSAS的结果集可能是有多维的 ，在方法中将CellSet转换为DataTable;
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.AnalysisServices.AdomdClient; using System.Data; namespace ReportExcel.DataAccess { public class BaseDBAccess { private AdomdConnection conn; private string connStr = string.Empty; protected BaseDBAccess() { } /// <summary> /// 设置连接的SSAS数据库 /// </summary> /// <param name="strConn"></param> protected void SetSSASDB(string strConn) { //connStr = "Provider=MSOLAP; DataSource=http://10.40.15.82/olap/msmdpump.dll; Initial Catalog=" + strConn; connStr = "Provider=MSOLAP; DataSource=http://10.40.15.87/olap/msmdpump.dll; Initial Catalog=" + strConn; } /// <summary> /// 选择连接的SSAS数据库 /// </summary> /// <param name="strDB"></param> private void CreateConn() { conn = new AdomdConnection(connStr); conn.Open(); } /// <summary> /// 到SSAS去查询数据,缺省连接SSAS1数据库 /// </summary> /// <param name="strSql"></param> /// <returns></returns> protected DataTable GetSsasResult(string strSql) { CreateConn(); AdomdCommand command = conn.CreateCommand(); command.CommandText = strSql; CellSet cs = command.ExecuteCellSet(); conn.Close(); return CellSet2DataTable(cs); } /// <summary> /// 将CellSet转换为DataTable /// </summary> /// <param name="cs"></param> /// <returns></returns> private DataTable CellSet2DataTable(CellSet cs) { DataTable dt = new DataTable(); DataColumn dc = new DataColumn(); DataRow dr = null; int rowDescCount = 0; int rowColLayersCount = 0; if (cs.Axes[1].Set.Tuples.Count > 0) { rowDescCount = cs.Axes[1].Set.Tuples[0].Members.Count; //得到行描述的列数 } if (cs.Axes[0].Set.Tuples.Count > 0) { rowColLayersCount = cs.Axes[0].Set.Tuples[0].Members.Count; //得到Axis[0]表头的层数 } //根据纵向表头的层数生成Columns for (int i = 0; i < rowDescCount; i++) { dt.Columns.Add(new DataColumn("c" + i.ToString())); } //生成剩下所有的Columns string name; for (int i = 0; i < cs.Axes[0].Set.Tuples.Count; i++) { dc = new DataColumn(); name = "a" + i.ToString(); dc.ColumnName = name; dc.DataType = typeof(object); dt.Columns.Add(dc); } //每层表头生成一行数据 for (int i = 0; i < rowColLayersCount; i++) { dr = dt.NewRow(); for (int j = 0; j < dt.Columns.Count - rowDescCount; j++) { dr[j + rowDescCount] = cs.Axes[0].Set.Tuples[j].Members[i].Caption; } dt.Rows.Add(dr); } //生成剩下的所有的数据行，以及纵向表头的描述 if (cs.Axes[1].Set.Tuples.Count > 0) { for (int i = 0; i < cs.Axes[1].Set.Tuples.Count; i++) { dr = dt.NewRow(); //维度描述列数据（行头） name = ""; for (int j = 0; j < cs.Axes[1].Set.Tuples[i].Members.Count; j++) { dr[j] = cs.Axes[1].Set.Tuples[i].Members[j].Caption; } dt.Rows.Add(dr); } //填充数据 for (int i = 0; i < dt.Rows.Count - rowColLayersCount; i++) { for (int j = rowDescCount; j < dt.Columns.Count; j++) { dt.Rows[i + rowColLayersCount][j] = cs.Cells[j - rowDescCount, i].Value; } } } dt.TableName = "SSAS Data"; return dt; } } }
如有兴趣可以参见另一篇其他博友bloger的文章，比较完整的一个winform小程序：
http://www.cnblogs.com/chenxizhang/archive/2011/05/28/2061073.html
