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.

<identityimpersonate="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.

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 libraryusing Microsoft.AnalysisServices.AdomdClient;

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

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.

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.

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.

privatevoid BuildGrid(CellSet cst)
{
//check if any axes were returned else throw error.int axes_count = cst.Axes.Count;
if (axes_count == 0)
thrownew Exception("No data returned for the selection");
//if axes count is not 2if (axes_count != 2)
thrownew Exception("The sample code support only queries with two axes");
//if no position on either row or column throw errorif (!(cst.Axes[0].Positions.Count >0) && !(cst.Axes[1].Positions.Count >0))
thrownew 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 rowif (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.

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 headerif (cur_row < col_dim_count)
{
//check if we are writing to a cell having row headerif (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 headerif (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.

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.

Comments and Discussions

I have seen your article on “Displaying a grid using ADOMD.NET and MDX” but my question is, can we do this by binding to chart? and how do i pass dynamic parameters to query using check boxes like giving state wise and city wise dimensions?

You could use the AdomdDataAdapter to fill a DataTable and bind it to a GridView in fewer lines of code, but it doesn't deliver the nicely formatted measure values and column headings as appears in this article without adding about the same amount of labor to generate the GridView columns. The measure cells are of type object so the GridView cannot autogenerate them.