Building a Custom Application with ADOMD.NET

SQL programmers have been using ADO.NET for a relational view of data for some time. ADOMD.NET extends ADO.NET by incorporating multidimensional data functionality. You can learn about ADOMD.NET by going through the process of building a custom application. First you’ll want to examine how ADOMD.NET works with cubes and see how to use a sample cube in coding a custom application. The custom application requires the use of an MDX query on the cube. Building the custom application shows how the ADOMD.NET object model allows you to draw upon your familiarity with ADO.NET as you query data stored in a SQL Server Analysis Services (SSAS) database. Because the following example is based on the sample SQL Server AdventureWorksDW database, which doesn’t ship with any active cubes, I’m using the cube created in“Creating Dimensions in SSAS, Part I,” Instant- Doc ID 98510 and “Creating Dimensions in SSAS, Part II,” InstantDoc ID 98699 as a prerequisite to the code used in this article. You can download the script to produce the cube; the AdventureWorksDW.xmla file is in the 99908.zip file. To download this file, click the 99908.zip hotlink at the top of this page. For more information about working with ADOMD .NET, see the web Learning Path that goes with this article.

MDX Queries Against Cubes

You can use ADOMD.NET to create custom multidimensional queries against an SSAS data cube within your application. ADOMD.NET can be used as part of an application that you design and build from the ground up. This article will focus on the requirements of building such an application. Before working with ADOMD.NET, you need to have an understanding of the MDX query syntax. For more information, see the sidebar “MDX Queries.”

In addition to understanding MDX query syntax, you’ll also need the following MDX query created especially for use in this custom application.

Select [Ship Date].[Calendar Year].    MEMBERS on Columns,           [Product].[Dim Product].CHILDREN    on Rows From [AdventureWorksDW] Where [Measures].[Sales Amount]

To learn how this query was constructed, see the sidebar, “Using SQL Server Management Studio 2005 to Build MDX Queries.”

Coding ADOMD.NET Data Access

To create your custom application, open Microsoft Visual Studio 2008 and create a new Windows Forms project. This example I created uses a Visual Basic project called ADOMD_NET. This project, shown in Figure 1, will host your application code. In Figure 1, the Toolbox is open and the DataGridView is selected. You’ll be using them next.

My example loads a grid with the query results when the application is started in order to minimize coding not related to the ADOMD_NET classes. To implement the grid, use a DataGridViewControl. To add the grid to the display area, in the Toolbox click Data, and then drag DataGridView onto the Form. Next, modify the properties of the grid to dock it to the form. Double-click the Form’s title bar to generate an event handler for the Form’s load event.

You’ve triggered the generation of a new event handler where you’ll place your custom code. Visual Studio has also updated your environment so that the newly generated method appears in the text editor. But before using the ADOMD.NET classes you need to reference the SSAS ADOMD.NET Client library. The typical project templates don’t default with a reference to this class library so you need to add it manually. To reference the library, right-click your project in the Solution Explorer window and select Add Reference. This opens the Add Reference dialog box, which is shown in Figure 2 with the Microsoft. AnalysisServices.AdomdClient library selected. Double-click this selection or select the library and click the OK button to add the reference. Note that the correct version of the library for SQL Server 2005 is 9.0.242.

With the addition of the SSAS ADOMD.NET Client library, you’re ready to start coding. You should be able to draw on your experience coding with ADO.NET when you code with ADOMD.NET. The similarities between the two outweigh the differences. Both ADO.NET and ADOMD.NET have Connection, Command, and DataAdapter objects and can reference DataSet, DataTable, and DataReader objects.

However, the Connection, Command, Data- Adapter, and DataReader objects in ADOMD.NET are slightly different from the equivalent objects in ADO.NET because the ADOMD.NET objects encapsulate a different connection. Instead of creating a SQL Connection object, as you would in ADO.NET, you define your connection with an AdoMdConnection object in ADOMD.NET. But the behavior of these objects is similar. The behavior and the interfaces for DataTable and DataReader objects are the same in ADO.NET and ADOMD.NET.

One object in ADOMD.NET isn’t present in ADO.NET—the CellSet. With CellSet you can create a multi-dimensional MDX query and maintain the relationships between the various data elements within the results. CellSet maintains the same hierarchical relationships that exist within the cube within the results that you have retrieved. CellSet does this by containing a copy of the data, plus a copy of the metadata that defines the MDX relationships.

Working with CellSet lets you create a UI that exposes the metadata so that the displayed results can be customized by the end users, just as they would manipulate data in a pivot table or other business intelligence (BI) application.

Now you’re ready to use ADOMD.NET to open a connection to SSAS, retrieve your cube’s data, and display that data. First, you’ll add the Ado Md Connection object to your code

Dim advwrksConn As New AdomdConnection( _     "Data Source=  localhost;Catalog=  AdventureWorksDW") 

This line of code creates a new AdoMdConnection object. The connection string is simple. Because it doesn’t define a specific authentication method, integrated authentication with your running account will be used. You can customize the connection string to use a specific account or to use other settings that are defined for any common ADO.NET connection.

Continue to page 2

When you first type this line into your code, you should receive an error because it doesn’t fully qualify the AdomdConnection object as coming from the Microsoft.AnalysisServices.AdomdClient namespace. You can go to the top of your file in Visual Studio and add an Imports statement for this library. You can also use a drop-down box by clicking the red square at the lower- right corner of your object. The drop-down box , shown in Figure 3, offers to automatically add the necessary imports statement for you. Selecting this option will update your code and resolve the error.

When you have a connection, you can use an MDX query within the following code to create a data adapter that references this connection:

Dim dataAdapter As New  AdomdDataAdapter( _    "Select [Ship Date].
   [Calendar  Year].MEMBERS on Columns," & _    " [Product].[Dim Product].  CHILDREN on Rows" & _    " 
   From [AdventureWorksDW]" & _    " Where [Measures].[SalesAmount]", _  advwrksConn)

This code takes the MDX query defined for use in this custom application and passes it as a string to a newly created AdomdDataAdapter. Along with the query, the connection information is passed to the data adapter. Now this data adapter can be used to fill a data set. So the next step is to create and fill a data set, using the following code:

Dim ds As New DataSet()dataAdapter.Fill(ds)

Now you’re ready for the last step, which is displaying the data in the data grid. To do so, run the following code:

    DataGridView1.DataSource =ds.Tables(0)

At this point you’re ready to run your new application code and review the results. Figure 4 shows the application after startup. It’s certainly not in a production-ready state, but you’ll note that the data is retrieved and displayed within your custom application. Any further manipulation of the data, such as formatting the column names, would be up to you.

Deployment

When you’re deploying applications that reference the ADOMD.NET library, keep in mind that the .NET Framework doesn’t install the ADOM.NET library by default. This means that if you reference ADOMD.NET you need to also include the redistributable package for it with your application. The correct version of ADOMD.NET for SSAS 2005 is part of the SQL Server 2005 November 2005 Feature Pack or later, with February 2007 being the most recent release. SQL Server 2008 has a similar feature pack which contains the ADOMD.NET libraries. You can download the February 2007 Feature Pack with the ADOMD.NET redistributable at: www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en. Add the associated package SQLServerADO_MD.msi (or one of the 64-bit versions of this package) as a prerequisite to your installation’s Setup.exe file. Note that MSI files don’t install prerequisites—only Setup .exe files that can be generated with your MSI package will reference prerequisite packages.

Extending ADO.NET

I hope this exercise building a custom application has shown you how to get started using ADOMD.NET to handle complex queries as well as retrieve multidimensional data. The fact that ADOMD.NET objects and ADO.NET objects share a lot of common syntax means you’ll spend less time learning ADOMD.NET when you already know ADO.NET. This should free you up to focus on the details of the new MDX query syntax and quickly integrate these queries into your custom applications.