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.