Delivering Business Intelligence with Microsoft SQL Server 2005

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

together.

—Richard Milhous Nixon

R

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

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 Together—Integrating OLAP with Your Applications 675

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 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.)

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.