Microsoft Analysis Services 2005: Displaying a grid using ADOMD.NET and MDX

• Download source code - 5.79 KB

Introduction

This article is targeted at all who have worked on ASP.NET web applications, Microsoft SQL Server Analysis Services, and MDX in some manner, and wish to add the power of analytics to their web applications.

The concept of Data Warehousing is not new anymore. With the rise in the scale, size, and complexity of businesses in today's world, the need for business analytics has become almost inevitable. This has lead to the growth of a breed of tools and technologies termed as "Business Intelligence" (BI). Microsoft SQL Server Analysis Services is one such product from Microsoft that helps in building business analytic applications. The querying language MDX is a very powerful means of fetching multidimensional data from the Microsoft SQL Server Analysis Services Cube.

However, the challenge that remains here is how to present this analytical data to business users so that it can aid them in their decision making process. There is an abundance of reporting tools and products available in the market today that can help you achieve this. Microsoft SQL Server Reporting Services is one example of such a tool.

This article describes yet another means of presenting analytical data to the user. It explains how to execute MDX query using Microsoft ADOMD.NET client components to fetch multidimensional data from the Cube and present it to the user in the form of a grid.

Even though the examples shown here are limited to presentation of analytical data, the functionality can be extended to do a lot more things such as Drill Down, Drill Up, Sorting, Filtering, etc., depending on the business needs. You can even create reports and graphs with the data.

Prerequisites

Fair knowledge of ASP.NET, Microsoft SQL Server Analysis Services, and MDX is required to work with the example in the article.

The following software will be needed to run the source code in the article:

• .NET Framework 2.0
• Microsoft Visual Studio .NET 2005
• Microsoft IIS 6 or above
• Microsoft SQL Server Analysis Services 2005

I have used the AdventureWorks sample database from Microsoft to execute the example MDX queries. You can use your own database to run the example. If you wish to use the AdventureWorks sample database, this link will guide you on how to install it.

Background

As we will be using ADOMD.NET client components to fetch data from the Microsoft SQL Server Analysis Services Cube, it is important to know the ADOMD.NET client object model.

The main three objects that we are going to use in our example are AdomdConnection, AdomdCommand, and Cellset. The AdomdConnection and AdomdCommand objects are similar to their counterparts in ADODB.NET. We will be using the ExecuteCellSet method of AdomdCommand to retrieve the CellSet.

Here is the partial object model of CellSet showing only those properties that are of interest in our example.

The CellSet contains the results of MDX query executions. As MDX allows fetching dimension members on different Axis, the CellSet contains a collection of Axis. Our example restricts the user to querying two Axis:

• Axis 0 – Columns Axis
• Axis 1 – Rows Axis

Axis contains a collection of Positions. Position represents a tuple on the Axis, and in turn contains one or more Members. The Cells collection contains a cell for each combination of Positions on all Axis.

Here is how you access member details from a CellSet:

CellSet.Axes[n].Positions[n1].Members[n2].PropertyName;

Here:

• n is the index of the axis. This will be 0 for column axis and 1 for row axis.
• n1 is the index of the position. This would be the index of the column in the case of the column axis and row in the case of the row axis.
• n2 is the index of the member. A position (tuple) may contain multiple members.

Here is how you access cell data from a CellSet:

CellSet[n, n1, n2,…nn].PropertyName

Here n, n1, n2 … nn are axis co-ordinates and depend on the number of axis in the CellSet.

You can find more information on the ADOMD.NET client object model from MSDN.

To show the output in the form of a grid, we will use the ASP.NET Table server object.

Setup and Run the Example Code

You can simply unzip the example web application code provided here to any folder on your local drive and create a virtual directory in IIS to point to the folder containing the code. You can now open the website from Visual Studio .NET 2005 IDE.

Since we will be using Windows authentication to connect to Microsoft Analysis Services 2005, you will have to modify the web.config file to impersonate a user having access to the Analysis Services.

<identity impersonate ="true" userName="user" password="password"/>

Once done, you can browse the newly created website, and it should look like this:

As I mentioned earlier, I will be using the AdventureWorks database to execute the MDX queries. You can modify the connection string and the default MDX query as needed on the page while running it, or in the Page_Load method in the code-behind.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        //setting default connection string value
        txtConnStr.Text = "Your Connection String";
        txtMDX.Text = "    Your default MDX query";
    }
    
    //clearing any error message
    lblErr.Text = "";
}

Click the “Go” button, and you should see the output grid like this:

Understanding the Code

Let’s go a bit deeper and look at the source code of our web application. First, we will look at the markup of our web-form.

<form id="form1" runat="server">
<table width="800" border="0">
    <tr>
        <td>Connection String:</td>
        <td>
          <asp:TextBox ID="txtConnStr" runat="server" Width="668px">
          </asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>MDX</td>
        <td><asp:TextBox ID="txtMDX" runat="server" Height="100px" 
 Rows="10" Width="650px" TextMode="MultiLine"> </asp:TextBox></td>
    </tr>
    <tr>
        <td></td>
        <td>
          <asp:Button ID="btnGo" runat="server" Text=" Go " OnClick="btnGo_Click" />
        </td>
    </tr>
    <tr>
        <td colspan="2">
          <asp:Label ID="lblErr" runat="server" ForeColor="Red">
          </asp:Label>
        </td>
    </tr>
</table>
<asp:Panel ID="gridPanel" runat="server"  CssClass="gridPanel">
</asp:Panel>
</form>

The web form has two textboxes, txtConnStr and txtMDX, to accept the connection string and the MDX query. It has a button btnGo, on the click of which we execute the MDX and create the grid. An event handler btnGo_Click is tied to the OnClick event of the button. A label lblErr is used to display any errors. Finally, gridPanel is the panel within which we are going to create the output grid.

Now, let’s examine the code-behind of our web-form. Since we are going to use ADOMD.NET client components, we have added a reference to it in our web application. This can be done using the menu – Website > Add Reference.

The using directive is added for Microsoft.AnalysisServices.AdomdClient so we can access objects without having to use the fully qualified name.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//Following is included to use ADOMD library
using Microsoft.AnalysisServices.AdomdClient;

Now, let’s see the code for the btnGo_Click event handler which gets called when the user clicks the button.

protected void btnGo_Click(object sender, EventArgs e)
{
    try
    {
        CellSet cst = GetCellset();
        BuildGrid(cst);
    }
    catch (System.Exception ex)
    {
        lblErr.Text = ex.Message;
    }
}

For ease of understanding, I have created two methods. GetCellSet executes the MDX query and returns a CellSet object, and BuildGrid accepts a CellSet and creates the grid. The event handler btnGo_Click calls these two methods in a try…catch block. If any error occurs, it displays it in the label lblErr.

Here is the MDX query that we have used in our example. Note, we have a state-province on the column axis and a cross-join of all months in 2003 with two measures Internet Sales Amount and Internet Order Quantity on the row axis.

select  
[Customer].[Customer Geography].[State-Province].Members on columns,

Descendants([Date].[Calendar].[Calendar Year].&[2003],[Date].[Calendar].[Month])*
{[Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity]} on rows

from [adventure works]

The GetCellSet method executes the MDX query and returns a disconnected CellSet object. It reads the connection string and the MDX query from the textboxes and establishes a connection with Microsoft Analysis Services using the AdomdConnection object. It then executes the MDX using the ExecuteCellSet method of the AdomdCommand object. Before returning the CellSet, the connection is closed.

private CellSet GetCellset()
{
    //Lets store the connection string and MDX query to local variables
    string strConn = txtConnStr.Text;
    string strMDX = txtMDX.Text;

    //create and open adomd connection with connection string
    AdomdConnection conn = new AdomdConnection(strConn);
    conn.Open();

    //create adomd command using connection and MDX query
    AdomdCommand cmd = new AdomdCommand(strMDX, conn);

    //The ExecuteCellSet method of adomd command will 
    //execute the MDX query and return CellSet object
    CellSet cst = cmd.ExecuteCellSet();

    //close connection
    conn.Close();

    //return cellset
    return cst;
}

The BuildGrid method accepts a CellSet (parameter name cst) and creates the output grid within the gridPanel panel that we have added to our web-form.

It checks the number of axis in the CellSet and restricts it to two. Also, it checks if there are no positions (tuples) returned on any axis, and it throws an error.

private void BuildGrid(CellSet cst)
{
    //check if any axes were returned else throw error.
    int axes_count = cst.Axes.Count;
    if (axes_count == 0)
        throw new Exception("No data returned for the selection");

    //if axes count is not 2
    if (axes_count != 2)
        throw new Exception("The sample code support only queries with two axes");

   //if no position on either row or column throw error
    if (!(cst.Axes[0].Positions.Count > 0) && !(cst.Axes[1].Positions.Count > 0))
        throw new Exception("No data returned for the selection");

It counts the number of dimensions (or should I say hierarchy) on each axis. In the case of the MDX that we are running, it would be 1 (state-province) for column and 2 (month and measure) for rows.

//Number of dimensions on the column
col_dim_count = cst.Axes[0].Positions[0].Members.Count;

//Number of dimensions on the row
if (cst.Axes[1].Positions[0].Members.Count > 0)
    row_dim_count = cst.Axes[1].Positions[0].Members.Count;

The total rows that we will need on the output grid would be the number of dimensions on the columns plus the number of positions on the rows. This is because we want to show the column headers for each dimension in the column axis. For columns, this would be the other way round.

//Total rows and columns
//number of rows + rows for column headers
row_count = cst.Axes[1].Positions.Count + col_dim_count;
//number of columns + columns for row headers
col_count = cst.Axes[0].Positions.Count + row_dim_count;

Now that we know the number of rows and columns on our grid, let’s create it. First, we clear any content under the panel gridPanel and create a new Table control and add it to the gridPanel.

//lets clear any controls under the grid panel
gridPanel.Controls.Clear();

//Add new server side table control to gridPanel
Table tblGrid = new Table();
tblGrid.CellSpacing = 0;
tblGrid.Width = col_count * 100;
gridPanel.Controls.Add(tblGrid);

Next, we create nested loops for adding rows and columns (rather, cells for each row). To show the headers and data, we will use a Label control.

//We will use label control to add text to the table cell
Label lbl;

for (cur_row = 0; cur_row < row_count; cur_row++)
{
    //add new row to table
    TableRow tr = new TableRow();
    tblGrid.Rows.Add(tr);

    for (cur_col = 0; cur_col < col_count; cur_col++)
    {
        //create new cell and instance of label
        TableCell td = new TableCell();
        lbl = new Label();

Based on the current row (cur_row) and current column (cur_col) coordinates, we decide which part (or cell) of the grid we are about to create.

If current row (cur_row) is less than (<) column dimension count (col_dim_count), it means we are creating a row containing column headers.

While writing the column header row, if the current column (cur_col) is less than (<) the row dimension count (row_dim_count), it means we are creating empty cells that are at the top-left of the grid. In this case, we create a label control with a blank space. Otherwise, if the current column (cur_col) is not less than (<) the row dimension count (row_dim_count), it means we are creating a column header cell. In this case, we create a Label control with column member caption.

//check if we are writing to a ROW having column header
if (cur_row < col_dim_count)
{
    //check if we are writing to a cell having row header
    if (cur_col < row_dim_count)
    {
        //this should be empty cell -- it's on top left of the grid.
        lbl.Text = " ";
        td.CssClass = "titleAllLockedCell";
        //this locks the cell so it doesn't scroll upwards nor leftwards
    }
    else
    {
        //this is a column header cell -- use member caption for header
        lbl.Text = 
          cst.Axes[0].Positions[cur_col - row_dim_count].Members[cur_row].Caption;
        td.CssClass = "titleTopLockedCell";
        // this lockeders the cell so it doesn't scroll upwards
    }
}

Similarly, when the current row (cur_row) is more than (>) the column dimension count (col_dim_count), it means we are creating a row containing data.

While writing the data row, if the current column (cur_col) is less than (<) the row dimension count (row_dim_count), it means we are creating a row header cell of the grid. In this case, we create a Label control with a row member caption. Otherwise, if the current column (cur_col) is not less than (<) the row dimension count (row_dim_count), it means we are creating a value cell. In this case, we create a Label control with data.

We turn the wrapping off for data row cells, so it doesn’t wrap and look weird.

else
{
    //We are here.. so we are writing a row having data (not column headers)
    //check if we are writing to a cell having row header
    if (cur_col < row_dim_count)
    {
        //this is a row header cell -- use member caption for header
        lbl.Text = 
          cst.Axes[1].Positions[cur_row - col_dim_count].Members[cur_col].Caption;
        td.CssClass = "titleLeftLockedCell";
        // this lockeders the cell so it doesn't scroll leftwards
    }
    else
    {
        //this is data cell.. so we write the Formatted value of the cell.
        lbl.Text = cst[cur_col - row_dim_count, 
                       cur_row - col_dim_count].FormattedValue + " ";
        td.CssClass = "valueCell";
        //this  right aligns the values in the column
    }

    //turn the wrapping off for row header and data cells.
    td.Wrap = false;
}

Finally, we add the Label control to the table cell and add the cell to the row.

            //add cell to the row.
            td.Controls.Add(lbl);
            tr.Cells.Add(td);
        }
    }
}

How the Freeze Pane Works

You must have noticed that the grid that we created has row and column headers frozen, something similar to Excel’s freeze pane feature.

This works because of the styles that are applied to the different types of cells. We achieved this with four CSS properties: top, left, position, and z-index. You can look at the styles below. I have removed other CSS properties, so comparing them is easier.

Column header cells use the titleTopLockedCell style. Notice that "left" is not specified here.

.titleTopLockedCell
{
    top<span class="code-none">: expression(parentNode.parentNode.parentNode.parentNode.scrollTop)<span class="code-none">; 
    position<span class="code-none">:relative<span class="code-none">;
    z-index<span class="code-none">: 10<span class="code-none">;
<span class="code-none">}</span></span></span></span></span></span></span>

Row header cells use the titleLeftLockedCell style. Notice that “top” is not specified here.

.titleLeftLockedCell
{
    left<span class="code-none">: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft)<span class="code-none">; 
    position<span class="code-none">:relative<span class="code-none">;
    z-index<span class="code-none">: 10<span class="code-none">;
<span class="code-none">}</span></span></span></span></span></span></span>

Empty cells (top-left of grid) use the titleAllLockedCell style. Notice that both “left” and “top” are specified here.

.titleAllLockedCell
{
    top<span class="code-none">: expression(parentNode.parentNode.parentNode.parentNode.scrollTop)<span class="code-none">; 
    left<span class="code-none">: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft)<span class="code-none">; 
    position<span class="code-none">:relative<span class="code-none">;
    z-index<span class="code-none">: 20<span class="code-none">;
<span class="code-none">}</span></span></span></span></span></span></span></span></span>

Conclusion

There are various options available to present business analytics data. ADOMD.NET client components help retrieve data easily from Microsoft Analysis Services, and it can be presented in any form such as report, UI, graph etc. This article is just a step towards explaining how you can use the power of ADOMD.NET and MDX to create your own UI, and trust me, the possibilities are endless.

If you liked or didn’t like this article, or if you have any feedback on the article, please feel free to email me. I would love to hear your valuable opinions.

History

• 01-Aug-2008

Added the article.