sql server reporting services: We've got a SQL Server db with tables and views than are the basis for
fact tables and dimensions in a group of Analysis Server cubes.

We need to build some reports based on this data. Should we use MDX and
have the cubes be the data source for the Reports, or our SQL Server
db? Pros and Cons?

Thanks,

Burt

Hi Burt,

The versions of Analysis Services and Reporting Services (2000 vs. 2005) are
also relevant here. But, assuming that you're using SQL Server 2005, here's
my initial list of points to consider (others may have many more to add):

Pros:
- (Assuming your cube is working well) cube is single source of
definitions/formulii.
- Cubes will typically have faster and more predictable query response times.

Cons:
- Analysis Services Provider (RS 2005) has limited flexibility, though easy
to use.
- Mapping date dimension to a report parameter with calendar control is
tricky.
- OLE DB for OLAP Provider (RS 2000/2005) is more flexible, but tedious to
use.

This MSDN paper addresses SQL Server 2000, but gives a good idea of the OLE
DB for OLAP option for developing cube-based reports:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp?frame=true
[quoted text, click to view]
Integrating Analysis Services with Reporting Services

Sean Boon
Microsoft Corporation

June 2004

Applies to:
Microsoft SQL Server 2000

Summary: Create a compelling solution for your customer that defines and
manages great-looking Analysis Services reports, and quickly answers
analytical questions to improve traditional reporting scenarios.
....
[quoted text, click to view]


[quoted text, click to view]