Cubes, MDX, Analysis Services and ADOMD in C#
4.20/5 (26 votes)
Mar 30, 2004
207166
4120
An article on Cubes, MDX, Analysis Services and ADOMD in .NET
Introduction
While searching through the net I came across many solutions but none of them worked properly. Then I thought about ADOMD.NET but it is in beta stage and so not advisable. Ultimately I had to take a step forward and decided to do it myself.
I used Interop for this to be done in .NET. Hope somebody finds it useful. This application takes in a MDX query as input and renders a HTML table in the web form as output. You have to use ADOMD 2.7 and ADODB 2.7 as reference and you have to include it for using it. Don’t forget write the following the two lines
using ADODB;
using ADOMD;
The code is very easy and is self explanatory.
What is MDX?
MDX is one of the key technologies you have to understand when working with OLAP. MDX can be used to describe multidimensional queries, define cube structures, and change data (in some cases). Let's start by outlining the basic form of an MDX statement:
SELECT {member selection} ON COLUMN FROM [cube name]
Sample Query:
SELECT {[Store Type].MEMBERS} ON COLUMNS,
{[Store].[Store City].MEMBERS} ON ROWS
FROM [Sales]
WHERE (MEASURES.[Sales Average])
Just remember it is to SQL analysis services as TSQL to MS Sql.
HTML code for webform1.aspx
<%@ 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# Code
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();
}
}
}
}