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.
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.
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:
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.)
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.
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.
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
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.
The Prospect Analyzer sample application can be downloaded from the website for this book.
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:
Right-click the project entry in the Solution Explorer window and select Add Reference from the Context menu. The Add Reference dialog box appears.
Scroll down and select the entry for Microsoft.AnalysisServices.AdomdClient as shown in Figure 16-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:
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.)
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.
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