To the community -- Matrix and MDX

All,

I keep reading that the Matrix is the perfect tool to use with OLAP data,
but I am very confused about the efficacy of such a notion. As we know, OLAP
is about precalculated aggregations, and the noticeable performance
improvements that such an analytical database yields.

But RS answer to all of this is to calculate the aggregations at run-time,
they recommend to bring in the leaf level data from the cubes, and then let
RS aggregate the results at runtime.

This may an acceptable approach for simple MDX queries, but for advanced
analysis -- it just does not make good sense.

We desperately want to use RS for our Enterprise Reporting solution, but we
are hard pressed to justify the performance issues (Cellset flattening +
run-time aggregations) and the complexity of a MDX/ Matrix solution (if one
exists)

Any examples of a multiple group (rows and columns) Matrix using an MDX
datasource would be appreciated!!!

Thanks,

Jim

Hi Jim,

I can't pass along any examples like you requested, but I can verify that
we've discovered the same thing regarding RS. It just isn't ready for OLAP.
Microsoft keeps touting it as such of course, but when you get into real-life
examples we've found that RS isn't useful for anything other than basic SQL
reports.


______________________
Terry Smith
Senior Architect
Insight Ecosystems


"Jim_OLAP" wrote:

All,

I keep reading that the Matrix is the perfect tool to use with OLAP data,
but I am very confused about the efficacy of such a notion. As we know, OLAP
is about precalculated aggregations, and the noticeable performance
improvements that such an analytical database yields.

But RS answer to all of this is to calculate the aggregations at run-time,
they recommend to bring in the leaf level data from the cubes, and then let
RS aggregate the results at runtime.

This may an acceptable approach for simple MDX queries, but for advanced
analysis -- it just does not make good sense.

We desperately want to use RS for our Enterprise Reporting solution, but we
are hard pressed to justify the performance issues (Cellset flattening +
run-time aggregations) and the complexity of a MDX/ Matrix solution (if one
exists)

Any examples of a multiple group (rows and columns) Matrix using an MDX
datasource would be appreciated!!!

Thanks,

Jim

Hi Jim,

There are ways (though not pretty!), to use pre-calculated aggregations
from a cube in a Matrix or Table report. I heard that there are some
improvements in the works - don't know when. Meanwhile, this MSDN paper
explains a technique:

http://msdn.microsoft.com/library/de.../en-us/dnsql2k
/html/olapasandrs.asp?frame=true

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.
...
Using Grouping with Analysis Services Aggregates and Calculated Members
...
Instead of returning the data from just a single level for each
dimension, in this example we are going to return the data from every
level that is capable of being displayed as part of the drill-down and
then filter out the records for each group.
...
This query returns data from multiple levels along the store dimension
and accepts a parameter for the time dimension. If you examine the
dataset returned by the query, you'll see that the dataset contains
records at multiple aggregate levels, and that each level is represented
as a field in the dataset. Since each record in the dataset consists of
each field, if the field doesn't apply to that record, the field will
contain a NULL value. For example, you can identify country level data
by noticing that the state, city, and store fields are all left blank.
This characteristic of the dataset is what you can use in the definition
of the groups to filter out the appropriate rows for each group.

The next step uses the grouping capability of Reporting Services'
tables, and filters the rows for each drill-down so that the Report
Server only ends up grouping a single record.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***