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 > )
{
int num;
ssasResult.Rows[][] = " All Sell Thru Rev (K$) ";
ssasResult.Rows[][] = " All Sell Thru Rev (K$) ";
ssasResult.Rows[][] = " All Sell Thru Rev (K$) ";
ssasResult.Rows[][] = " All Sell Out Rev (K$) ";
ssasResult.Rows[][] = " All Sell Out Rev (K$) ";
ssasResult.Rows[][] = " All Sell Out Rev (K$) ";
ssasResult.Rows[][] = " Target ";
ssasResult.Rows[][] = " QTD ";
ssasResult.Rows[][] = " HitRate ";
ssasResult.Rows[][] = " Region ";
ssasResult.Rows[][] = " Channel ";
for (num = ; num < ssasResult.Rows.Count; num++)
{
if (ssasResult.Rows[num][].ToString().ToLower() == "all")
{
ssasResult.Rows[num][] = " ACCN ";
}
if (ssasResult.Rows[num][].ToString().ToLower() == "all")
{
if (ssasResult.Rows[num][].ToString().Trim().ToUpper() == "ACCN")
{
ssasResult.Rows[num][] = " ACCN Total ";
}
else
{
ssasResult.Rows[num][] = " 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 = ;
int rowColLayersCount = ;
if (cs.Axes[].Set.Tuples.Count > )
{
rowDescCount = cs.Axes[].Set.Tuples[].Members.Count; //得到行描述的列数
}
if (cs.Axes[].Set.Tuples.Count > )
{
rowColLayersCount = cs.Axes[].Set.Tuples[].Members.Count; //得到Axis[0]表头的层数
} //根据纵向表头的层数生成Columns
for (int i = ; i < rowDescCount; i++)
{
dt.Columns.Add(new DataColumn("c" + i.ToString()));
} //生成剩下所有的Columns
string name;
for (int i = ; i < cs.Axes[].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 = ; i < rowColLayersCount; i++)
{
dr = dt.NewRow();
for (int j = ; j < dt.Columns.Count - rowDescCount; j++)
{
dr[j + rowDescCount] = cs.Axes[].Set.Tuples[j].Members[i].Caption;
}
dt.Rows.Add(dr);
} //生成剩下的所有的数据行，以及纵向表头的描述
if (cs.Axes[].Set.Tuples.Count > )
{
for (int i = ; i < cs.Axes[].Set.Tuples.Count; i++)
{
dr = dt.NewRow();
//维度描述列数据（行头）
name = "";
for (int j = ; j < cs.Axes[].Set.Tuples[i].Members.Count; j++)
{
dr[j] = cs.Axes[].Set.Tuples[i].Members[j].Caption;
}
dt.Rows.Add(dr);
} //填充数据
for (int i = ; 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