下边简单的说一下我前段时间整理的从Analysis Services读取数据,然后转换为DataTable的解决方法及思路。
从以下几个步骤说明:
一:配置AnalysisServices的远程http访问:http://blogs.msdn.com/b/sqldev/archive/2011/01/18/how-to-configure-http-access-to-sql-server-2008-analysis-services-on-iis7.aspx
配置完成以后就可以在程序中访问AnalysisServices服务了,下边是应用程序端的配置。
二: 在项目引用中引用Microsoft.AnalysisServices.AdomdClient 文件位置在
C:\Program Files(x86)\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll 没有的话需要去下载,一般都有。
三:配置AnalysisServices访问链接:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Collections;
namespaceBeisen.AnalysisServices.ServiceImp.Config
{
public sealed class AnalysisServicesConfig
{
public static readonly stringAnalysisServicesHttpConn4 = "Provider=MSOLAP;
DataSource=http://localhost/olap/msmdpump.dll;
Initial Catalog=BeiSenAnalysisServices;";
}
}
四:下边就可以访问数据了:
protected DataTableGetData()
{
string SQLQuery = @"select[Wd Highest Degree].[Name].[Name] on columns,([Wd Datetime].[DYear].children,
[Wd Datetime].[Dmonth].children,[Wd Datetime].[Ddate].children)on rows from [Beisen ASA];";
if (DataTable != null)return DataTable;
if (DBConnectionString == null || SQLQuery == null)
throw new Exception("Pleaseprovide either DataRows,
or both DBConnectionString and SQLQuery to getdata");
AdomdConnection conn = new AdomdConnection(DBConnectionString);
AdomdCommand comm = newAdomdCommand(SQLQuery, conn);
conn.Open();
CellSet cs = comm.ExecuteCellSet();
conn.Close();
return CellSetToDataTable.ToDataTable(cs,"table0");
}
五:到这就剩下需要找到ToDataTable方法了,下边是这个方法:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
usingMicrosoft.AnalysisServices.AdomdClient;
namespace AnalysisServices.Utility
{
public class CellSetToDataTable
{
/// <summary>
/// 将CellSet转化成DataTable(包括所有维度)
/// </summary>
/// <param name="cs">CellSet</param>
/// <returns></returns>
public static DataTable ToDataTable(CellSetcs,string tbname)
{
DataTable dt = newDataTable(tbname);
DataColumn dc = null;
DataRow dr = null;
//生成数据列对象
//多个维度转化成列
for (int col = 0; col< cs.Axes[1].Set.Hierarchies.Count; col++)
{
dc = new DataColumn();
//下面的代码会报错:"The connection is not open.” 获取层次结构的维度名时需要连接Cube才可以!
//dt.Columns.Add(new DataColumn(cs.Axes[1].Set.Hierarchies[col].ParentDimension.Name));
dt.Columns.Add(new DataColumn("Dimension" + col.ToString()));
}
int index = 0;
foreach (Positionp in cs.Axes[0].Positions)
{
dc = new DataColumn();
string name = "";
foreach (Memberm in p.Members)
{
name += m.Caption + "-";
}
if (name.Length > 0)
{
name = name.Substring(0, name.Length - 1);
}
//这里防止维度成员或度量值重名而需要容错处理
try
{
dc.ColumnName = name;
dt.Columns.Add(dc);
}
catch (System.Exceptionex)
{
dc.ColumnName = name + index.ToString();
dt.Columns.Add(dc);
}
index++;
}
//添加行数据
int pos = 0;
foreach (Positionpy in cs.Axes[1].Positions)
{
dr = dt.NewRow();
//维度描述列数据
intcols = 0;
foreach (Memberm in py.Members)
{
dr[cols] = m.Caption;
cols++;
}
//数据列
for (int x = 1;x <= cs.Axes[0].Positions.Count; x++)
{
dr[x + cols - 1] = cs[pos++].FormattedValue;
}
dt.Rows.Add(dr);
}
return dt;
}
}
}