674 Delivering Business Intelligence with Microsoft SQL Server 2005
If you want to make beautiful music, you must play the black and the white notes
Richard Milhous Nixon
eporting Services provides us with a capable tool for delivering Business
Intelligence (BI) to decision makers. In some situations, however, our
business intelligence solution must be more than a paper report; even more
than an interactive report available in a browser. At times, our business intelligence
must integrate tightly with other programs and solutions.
Once again, SQL Server 2005 provides us with the tools to fulfi ll this need. The
ADOMD.NET data provider enables us to execute MDX queries against OLAP
cubes on Analysis Services servers. Analysis Management Objects (AMO) provides
a programming interface for the management of an Analysis Services server and the
objects residing on that server. Reporting Services offers a number of methods for
integrating reports with applications.
With these features, we can provide our users with the complete package, the
integrated solution, the all-in-one application, the tightly coupled, well-oiled . . . well,
you get the picture.
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.
Chapter 16: Let’s Get TogetherIntegrating OLAP with Your Applications 675
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 fi rewall 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
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.

Get Delivering Business Intelligence with Microsoft SQL Server 2005 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.