Cubes, MDX, Analysis Services and ADOMD in C#

teknas

Rate me:

4.20/5 (31 votes)

29 Mar 2004

205.7K

4.1K

An article on Cubes, MDX, Analysis Services and ADOMD in .NET

Download source - 20.2 Kb

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:

SQL

SELECT {member selection} ON COLUMN FROM [cube name]

Sample Query:

SQL

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

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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;
<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>&nbsp;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();
        }

    }

}
}

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By

teknas

Web Developer

India

Presently Employed as an .Net Programmer in a company.He has done his Diploma in Advanced Computing From C-DAC.Is Indian by Birth.
More Intrested in touring , going out places and is a food freak.Its not that he is intrested in eating more but is rather interested in tasting different and new delicacies.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.