关于如何远程访问AnalysisServices以及Cell向DataTable转换的关键技术

下边简单的说一下我前段时间整理的从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;
       }
    }
}