ADOMD.NET

ADOMD.NET, the multidimensional counterpart to ADO.NET, is our means to programmatically access the wealth of business intelligence we have been creating on the Analysis Services server. With ADOMD.NET, our client applications can query databases on an Analysis Services server. ADOMD.NET also allows these applications to programmatically view and manipulate the structures residing in Analysis Services databases.

ADOMD.NET uses XML for Analysis (XML/A) to interact with the Analysis Services server.

ADOMD.NET Structure

The structure of ADOMD.NET is similar to the structure of ADO.NET. Both use a connection object to manage the connection string and set up access to the server. And both use a command object to execute queries against a database. And both provide structures for connected and disconnected access to data.

AdomdConnection

The AdomdConnection manages the connection between the client application and the multidimensional data server. A connection string similar to the following is used to initiate the connection to a server:

 Data Source=serverName;Catalog=AnalSvcsDB;Provider=msolap; 

The connection to the server is made using either TCP/IP or HTTP protocols. This enables connections to be made with a minimum of firewall and network interference.

Once the connection is open, an AdomdCommand object can be used to interact with the Analysis Services database. When the session has concluded, the connection must be explicitly closed with the Close method. An AdomdCommand object does not automatically close the connection when it goes out-of-scope in your code. (This is done to facilitate connection sharing.)

AdomdCommand

The AdomdCommand manages the execution of queries against a multidimensional server. The query is set using either the CommandText property or CommandStream property. The query itself must be an MDX command or an XML/A-compliant command that is valid on the target server.

AdomdCommand offers the following methods for executing the query:

• Execute The Execute method returns the result of the command either as a CellSet or as an AdomdDataReader, depending on the format of the results themselves.

• ExecuteCellSet The ExecuteCellSet method returns the result of the command as a CellSet.

• ExecuteNonQuery The ExecuteNonQuery method executes a command that does not return a result.

• ExecuteReader The ExecuteReader method returns the result of the command as an AdomdDataReader.

• ExecuteXMLReader The ExecuteXMLReader method returns the result of the command in the native XML/A format, using an XMLReader.

AdomdDataReader

The AdomdDataReader provides a means for reading a forward-only result set from a query. While this result set is being read, the connection to the data source remains busy. This is a connected result set. Connected access requires more server overhead to maintain the active connection.

CellSet

Unlike the AdomdDataReader, the CellSet facilitates a disconnected result. set. The CellSet contains the entire structure of the multidimensional result set. Therefore, an application can interact with this result set without having to maintain a connection to the server.

A portion of the CellSet structure is shown in Figure 16-1. The CellSet contains one or more axis objects. These represent the query dimensions: Column, Row, Page, and so on. Review Chapter 11, if you need a refresher on query dimensions in an MDX query. Each axis contains a set of tuples. A set can have zero, one, or many tuples. As we discussed in Chapter 10, tuples are made up of dimension and hierarchy members.

Figure 16-1: A partial view of the CellSet structure

In addition to the items shown in Figure 16-1, the CellSet also contains a collection of cells called Cells. These cells contain the measures that are included in the MDX query. The cell collection contains one index for each dimension in the MDX query. For example, if the MDX query contains Column, Row, and Page dimensions, the cell collection will have three dimensions and an individual cell would be addressed as shown:

 CellSet.Cells(x, y, z) 

The measure calculated for an individual cell is accessed through the Value property, as shown:

 CellSet.Cells(x, y, z).Value 

ADOMD.NET Example

Let's look at an example using ADOMD.NET to incorporate an MDX query result into an application. A Maximum Miniatures developer has created a Windows program for analyzing prospective customers called Prospect Analyzer. The method of analysis being used by this program is to take a prospective customer's city of residence and display the buying habits of other residents of that city for the past three years. Many of Max Min's products have regional appeal, so this type of analysis can be helpful when planning a sales approach to a prospective customer.

The program displays the number of product sales by store by year. Because we have three dimensions in the result set, a bit of creativity was required. Two of the three dimensions are represented by the rows and columns of a data grid. Multiple data grids are placed on a set of tab pages to represent the third dimension. The user interface for the Prospect Analyzer is shown in Figure 16-2.

Figure 16-2: The Prospect Analyzer sample application with a three-dimensional MDX query result viewer

Recall the MDX Query window in SQL Server Management Studio only allows two dimensions in query results. Here, we have an MDX query result viewer that allows three. Your homework is to design your own MDX query result viewer that will display four dimensions.

Note 

The Prospect Analyzer sample application can be downloaded from the website for this book.

Setting a Reference

We can use ADOMD.NET in a number of different .NET project types. But, before we can use ADOMD.NET in the project, we must first set a reference to the assembly that implements it. That reference is set by doing the following:

1. Right-click the project entry in the Solution Explorer window and select Add Reference from the Context menu. The Add Reference dialog box appears.

2. Scroll down and select the entry for Microsoft.AnalysisServices.AdomdClient as shown in Figure 16-3.

3. Click OK to exit the Add Reference dialog box and add a reference to this assembly in your project.

Figure 16-3: The Add Reference dialog box with the entry for the ADOMD.NET client highlighted

If you need to view the references currently set for a project to determine if the ADOMD.NET client has already been referenced, use the following steps:

1. Double-click the MyProject entry in the Solution Explorer window. The Project Properties tab appears. (This tab will have the name of the project you are working with.)

2. Select the References page of the Project Properties tab. This page appears as shown in Figure 16-4. Notice the entry for Microsoft.AnalysisServices.AdomdClient at the top of the list of References.

Figure 16-4: The References page of the Project Properties tab

We also need to import AdomdClient. The following line of code must be at or near the beginning of each program.

 Imports Microsoft.AnalysisServices.AdomdClient 

Retrieving a CellSet

The code to retrieve the CellSet is shown here. This code should look familiar to anyone who has used ADO.NET because it has the same format. We connect to the database server, we create and execute the query on this server, and then we pick up the query result and pass it back.

 Imports Microsoft.AnalysisServices.AdomdClient Public Class ADOMDWrapper     Public Function BuyingByProductByStoreByYearMDX( _                       ByRef CityName As String) As CellSet         Dim conn As AdomdConnection         Dim cmd As AdomdCommand         Dim cst As CellSet         Dim strServer As String = "BLARSONXP2\BMLSQL2005"         Dim strDatabase As String = "MaxMinSalesDM"         Dim strMDXQuery As String         strMDXQuery = "SELECT [Time].[Year].members ON PAGES, " & _                       "[Store].[Store Name].members ON ROWS,  " & _                       "[Product].[Brand Name].members ON COLUMNS " & _                       "FROM [MaxMinSales] " & _                      "WHERE ([Measures].[Unit Sales], "  & _                              " [Customer].[City].[" & CityName & " ])"         ' Open the connection to the Analysis Services server         Try             ' Create a new AdomdConnection object, providing the             ' connection string.             conn = New AdomdConnection("Data Source=" & strServer & _             ";Catalog=" & strDatabase & ";Provider=msolap;")             ' Open the connection.             conn.Open()         Catch ex As Exception             Throw New ApplicationException( _                 "An error occurred while connecting.")         End Try         ' Execute the MDX Query         Try             ' Create a new AdomdCommand object,             ' providing the MDX query string.             cmd = New AdomdCommand(strMDXQuery, conn)             ' Run the command and return a CellSet object.             cst = cmd.ExecuteCellSet()             ' Return the CellSet object             Return cst         Catch ex As Exception             Throw New ApplicationException( _                 "An error occurred while opening the cellset.")         End Try         ' Release resources.         Try             conn.Close()         Catch ex As Exception             ' Ignore errors         Finally             cst = Nothing             cmd = Nothing             conn = Nothing         End Try     End Function End Class 

The next code listing shows how to parse the information into our fancy three-dimensional viewer. First, we loop through the content of Axis(2), the Pages dimension, to create a tab page for each one. Each tab page has a DataGridView control for displaying columns and rows on that tab. Second, we loop through the content of Axis(0), the Column dimension, to create columns in the DataGridView. Third, we loop through the content of Axis(1), the Row dimension, to create row labels in the DataGridView. Finally, we loop through the cells in the CellSet to populate the content of the DataGridView on each tab page.

 Imports Microsoft.AnalysisServices.AdomdClient Public Class ProspectAnalyzer     Private Sub cmdAnalyze_Click(ByVal sender As System.Object, _             ByVal e As System.EventArgs) Handles cmdAnalyze.Click         Dim ADOMDBWpr As New ADOMDWrapper         Dim CSet As CellSet         Dim i, j, k As Integer         Dim DataGridView As System.Windows.Forms.DataGridView         Dim DataGridViewColumn As _            System.Windows.Forms.DataGridViewTextBoxColumn         ' Don't do any analysis if there is no city selected         If String.IsNullOrEmpty(cboCity.Text) Then             Return         End If         ' Set the label on the buying analysis group box         gbxBuyingAnalysis.Text = "Buying Analysis for " & cboCity.Text         ' Call the function to get a CellSet         ' with the results for the specified city         CSet = ADOMDBWpr.BuyingByProductByStoreByYearMDX(cboCity.Text)         ' Create a tab for each Page in the resulting CellSet         TabForPages.TabPages.Clear()         For i = 0 To CSet.Axes(2).Set.Tuples.Count - 1             ' Label the tab using the caption for             ' each Page in the CellSet            TabForPages.TabPages.Add( _                 CSet.Axes(2).Set.Tuples(i).Members(0).Caption)             ' Place a DataGridView on the new tab             DataGridView = New System.Windows.Forms.DataGridView             DataGridView.ReadOnly = True             DataGridView.Width = TabForPages.TabPages(i).Width             DataGridView.Height = TabForPages.TabPages(i).Height             DataGridView.RowHeadersVisible = True             DataGridView.RowHeadersWidth = 123             ' Create a column in the DataGridView for             ' each Column in the CellSet             For j = 0 To CSet.Axes(0).Set.Tuples.Count - 1                 DataGridViewColumn = New _                    System.Windows.Forms.DataGridViewTextBoxColumn                 ' The headers for the column is the caption                 ' for each Column in the CellSet                 DataGridViewColumn.HeaderText = _                   CSet.Axes(0).Set.Tuples(j).Members(0).Caption                 DataGridViewColumn.Width = 150                 DataGridView.Columns.Add(DataGridViewColumn)             Next             ' Create an empty row in the DataGridView             'for each Row in the CellSet             DataGridView.RowCount = CSet.Axes(1).Set.Tuples.Count             ' The headers for each row is the caption             'for each Row in the CellSet             For k = 0 To CSet.Axes(1).Set.Tuples.Count - 1                 DataGridView.Rows(k).HeaderCell.Value = _                     CSet.Axes(1).Set.Tuples(k).Members(0).Caption             Next             ' Place the values from the CellSet in the DataGridView             ' cells             For j = 0 To CSet.Axes(0).Set.Tuples.Count - 1                 For k = 0 To CSet.Axes(1).Set.Tuples.Count - 1                     DataGridView(j, k).Value = CSet.Cells(j, k, i).Value                 Next             Next             ' Place the DataGridView on the tab page             TabForPages.TabPages(i).Controls.Add(DataGridView)         Next         ' Set the Buying Analysis group box visible         gbxBuyingAnalysis.Visible = True     End Sub End Class