Cubes、MDX、Analysis Services 和 ADOMD in C#
4.20/5 (26投票s)
2004 年 3 月 30 日
208213
4120
一篇关于 .NET 中 Cubes、MDX、Analysis Services 和 ADOMD 的文章
引言
在网上搜索时，我发现了很多解决方案，但没有一个能正常工作。然后我考虑了 ADOMD.NET，但它仍处于 Beta 阶段，因此不建议使用。最终，我不得不主动出击，决定自己动手实现。
我使用了 Interop 来在 .NET 中完成这项工作。希望对某人有所帮助。这个应用程序将 MDX 查询作为输入，并在 Web 表单中呈现 HTML 表格作为输出。您需要将 ADOMD 2.7 和 ADODB 2.7 作为引用，并且需要包含它们才能使用。不要忘记写入以下两行
using ADODB;
using ADOMD;
代码非常简单，易于理解。
什么是 MDX？
MDX 是您在使用 OLAP 时必须了解的关键技术之一。MDX 可用于描述多维查询、定义立方体结构以及更改数据（在某些情况下）。让我们从概述 MDX 语句的基本形式开始
SELECT {member selection} ON COLUMN FROM [cube name]
示例查询
SELECT {[Store Type].MEMBERS} ON COLUMNS,
{[Store].[Store City].MEMBERS} ON ROWS
FROM [Sales]
WHERE (MEASURES.[Sales Average])
请记住，它与 SQL 分析服务的关系就像 TSQL 与 MS Sql 的关系一样。
webform1.aspx 的 HTML 代码
<%@ Page language="c#" Codebehind="WebForm1.aspx.cs"
AutoEventWireup="false" Inherits="Test2.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio 7.0" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5"
name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<TABLE id="Table1" style="Z-INDEX: 101; LEFT: 16px; WIDTH: 681px;
POSITION: absolute; TOP: 40px; HEIGHT: 128px"
cellSpacing="1" cellPadding="1" width="681" border="1">
<TR>
<TD>
<asp:Button id="btnFire" runat="server" Text=">>>"
Width="85px" ToolTip="Press to Execute Query"
Font-Bold="True"></asp:Button></TD>
</TR>
<TR>
<TD>
<asp:TextBox id="txtQuery" runat="server"
TextMode="MultiLine" Width="671px" Height="94px"
ForeColor="Black"></asp:TextBox></TD>
</TR>
</TABLE>
<TABLE id="Table3" style="Z-INDEX: 103; LEFT: 338px;
WIDTH: 359px; POSITION: absolute; TOP: 168px; HEIGHT: 30px"
cellSpacing="1" cellPadding="1" width="359" border="1">
<TR>
<TD style="WIDTH: 78px"><STRONG> Dimensions</STRONG></TD>
<TD>
<asp:DropDownList id="cboDimns" runat="server" Width="261px"
AutoPostBack="True"></asp:DropDownList></TD>
</TR>
</TABLE>
<TABLE id="Table2" style="Z-INDEX: 102; LEFT: 15px; WIDTH: 297px;
POSITION: absolute; TOP: 168px; HEIGHT: 30px" cellSpacing="1"
cellPadding="1" width="297" border="1">
<TR>
<TD style="WIDTH: 323px"><STRONG>Cubes</STRONG></TD>
<TD>
<asp:DropDownList id="cboCubeList" runat="server" Width="268px"
AutoPostBack="True"></asp:DropDownList></TD>
</TR>
</TABLE>
</form>
</body>
</HTML>
C# 代码
using System;
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using ADOMD;
using ADODB;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace Test2
{
/// <summary>
/// Summary description for WebForm1
/// Developed By Sanket Naik Emp Code 1550.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button btnFire;
protected ADODB.Connection con;
protected System.Web.UI.WebControls.TextBox txtQuery;
protected ADOMD.Cellset cset;
protected System.Web.UI.WebControls.DropDownList cboCubeList;
protected System.Web.UI.WebControls.DropDownList cboDimns;
protected string constr =
"Datasource=localhost; Provider=msolap; Initial Catalog=FoodMart 2000;";
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnFire.Click += new System.EventHandler(this.btnFire_Click);
this.cboCubeList.SelectedIndexChanged +=
new System.EventHandler(this.cboCubeList_SelectedIndexChanged);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void btnFire_Click(object sender, System.EventArgs e)
{
try
{
string query = txtQuery.Text.ToString();
con = new ConnectionClass();
con.Open(constr,"sa","",0);
cset = new CellsetClass();
cset.ActiveConnection = con;
cset.Open(query,con);
int totColsLvl = 0;
//total rows within column header (since cross joins may occur)
totColsLvl = cset.Axes[0].Positions[0].Members.Count;
//Axes[0] represent column headers. Axes[1] represent row headers.
//Positions[0] represents the first position within the columns
Response.Write("<Table id='Table1' style='Z-INDEX: 103;
LEFT: 120px; POSITION: absolute; TOP: 238px' cellSpacing='0'
cellPadding='2' width='600' border='1' bordercolor = '#000000'
bgcolor = 'WhiteSmoke' >");
for (int i = 0;i < totColsLvl;i++) //For each row in the column header
{
Response.Write(
"<TR bgcolor = 'Lightblue'> <TH > Rows </TH>"); //Start a row
//Get all the header of the current row
foreach (ADOMD.Position pos in cset.Axes[0].Positions)
{
Response.Write("<TH>" + pos.Members[i].Caption + "</TH>");
//Member[] holds the actual column data
//Caption returns the header caption to be displayed
}
Response.Write("</TR>"); //Close the row
}
// int totRowsLvl = 0;
//total rows within Rows header (since cross joins may occur)
//
// totRowsLvl = cset.Axes[1].Positions.Count;
// //Axes[1] is for Rows
// //Response.Write("<TD>"+ totColsLvl+"</TD>");
// for (int i = 0;i < totRowsLvl ;i++)
//For each row in the rows header
// {
// Response.Write("<TD>"); //Start a row
// //Get all the header of the current row
// Response.Write(
cset.Axes[1].Positions[i].Members[0].Caption);
// Response.Write("</TD>"); //Close the row
// }
//Response.Write("</Table></TD>");
int totCols =0;
int totRows = 0;
totCols = cset.Axes[0].Positions.Count;
//Total columns in the result
totRows = cset.Axes[1].Positions.Count;
//Total rows in the result
object[] coords = new object[2];
//Objects to hold the coordinates of the data being accessed
for (int i = 0;i < totRows;i++) //For each row in the result
{
Response.Write("<TR>"); //Start table row
Response.Write("<TD bgcolor = 'LightBlue'><b>" +
cset.Axes[1].Positions[i].Members[0].Caption+ "</b></TD>");
for ( int j = 0;j < totCols; j++)
//In each row, for each column
{
//Initialize the coordinates of the data to be fetched
coords[0] = j; //column
coords[1] = i; //row
//get_Item fetches the data.
ADOMD.Cell newcell;
newcell = cset.get_Item(ref coords);
object dispvalue;
dispvalue = newcell.FormattedValue;
if ( newcell.FormattedValue != null)
{
//display data
Response.Write("<TD>" + dispvalue + "</TD>");
}
else
{
Response.Write("<TD> 0 </TD>");
}
}
Response.Write("</TR>"); //Close the row
}
Response.Write("</Table>");
}
catch(Exception ex)
{
txtQuery.Text = ex.Message;
}
}
private void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack)
{
try
{
con = new ConnectionClass();
con.Open(constr,"sa","",0);
ADOMD.Catalog ct = new Catalog();
ct.ActiveConnection = con;
int totalcubes = 0;
totalcubes = ct.CubeDefs.Count;
for(int i = 0; i < totalcubes; i++)
{
cboCubeList.Items.Add(ct.CubeDefs[i].Name);
}
}
catch(Exception ex)
{
txtQuery.Text = ex.Message;
}
finally
{
con.Close();
}
}
}
private void cboCubeList_SelectedIndexChanged(
object sender, System.EventArgs e)
{
try
{
con = new ConnectionClass();
con.Open(constr,"sa","",0);
ADOMD.Catalog ct = new Catalog();
ct.ActiveConnection = con;
int totalcubes = 0;
totalcubes = ct.CubeDefs.Count;
for(int i = 0; i < totalcubes; i++)
{
if (cboCubeList.SelectedItem.Text == ct.CubeDefs[i].Name)
{
cboDimns.Items.Clear();
int cntDim = 0;
cntDim = ct.CubeDefs[i].Dimensions.Count;
for(int j = 0; j <cntDim ; j++)
{
cboDimns.Items.Add( ct.CubeDefs[i].Dimensions[j].Name);
}
}
}
}
catch(Exception ex)
{
txtQuery.Text = ex.Message;
}
finally
{
con.Close();
}
}
}
}