Reporting Services Architecture - Professional SQL Server 2005 Reporting Services - page 55

Analysis Services 2005 Provider
A welcome addition to Reporting Services 2005 is the Analysis Services 2005 provider. This provider
allows users to connect to the Analysis Services Unified Dimensional Model (UDM) and retrieve multi-
dimensional data. The UDM gives users a semantic model that understands data relationships and
aggregation. Access to the UDM is implemented using Multidimensional Expressions (MDX).

From a reporting perspective, MDX is an extremely powerful query language. MDX understands and
can work with data relationships. It can also perform coordinate-based calculations to build Excel-like
formulas.

Along with the power of MDX comes a bit of complexity. Most people have the perception that MDX is a
challenging language. It is my opinion that MDX is no more challenging than SQL; it just takes some
practice. Once you have a fundamental understanding of the language, common report tasks can
become trivial. In the meantime, you can use the MDX Query Builder provided by the Analysis Services
data extension. This query builder allows users to drag and drop measures and dimensions to form a
result set. It also allows users to specify parameterized dimensions that will then appear in the reports.

OLE DB Provider
The OLE DB provider gives report writers a great deal of flexibility. Using this provider, you can query a
number of different data sources. The following is a list of just a few:

❑ Microsoft Analysis Services 2000

❑ Microsoft Access

❑ Microsoft Excel

❑ Microsoft Directory Services

❑ OLEDB for ODBC

Data source extensions are the first key to the Reporting Services platform. They allow report writes to
access a large array of data sources, and if there is no provider present, a new data source extension can
be developed. In the next section, you will look at rendering extensions.

Rendering Extensions
Reporting Services supports a number of different rendering extensions. When creating a report in
Reporting Services, you are creating it in a neutral output format. In the report, you define the query, the
fields, and how they should be laid out on the page. It is the job of the rendering extension to take this
information and combine it with report data to create a formatted output. In this section, you look at
some of the supported extensions.

Excel
The Excel rendering extension takes report data and outputs it to a spreadsheet. This is an extremely
common format for many users. Excel is especially useful for those users wanting to perform further
analysis on the information.

Excel rendering in some reporting platforms can tend to cause formatting problems. This is due to the
report design methods used in other tools. Microsoft Access is an example of a banded report designer.
In this type of designer, users have detailed header and footer bands to deal with data set information.