MDX, Matrix and Aggregate()

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

Are you asking about RS 2000 or RS 2005?

MDX queries designed in graphical query designer of RS 2005 can retrieve AS server aggregates directly within the query. You would then use the =Aggregate(...) function within the matrix cells instead of using regular aggregations. Based on the matrix cells' scopes, the Aggregate(...) function will determine the correct aggregate row from the flattened rowset. However, this means that the aggregate rows must be present and retrieved by the MDX query in the first place. Adjusting the MDX query based on the usage of the Aggregate function within the report is taken care of automatically by report designer if you designed the MDX query with the graphical MDX query designer of RS 2005.

-- Robert

|||

Robert,

Thanks for getting back to me -- I'm using RS 2000, so the good news is that it sounds like RS 2005 has addressed the issue of the MDX / Matrix complexity.

Any ideas on RS 2000? We won't be going to 2005 until Q1 2007.

Thanks,

Jim

|||

Technically, you could implement a full custom data extension for RS 2000. That data extension would need to implement the IDataReaderExtension interface (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_8je0.asp) among other interfaces and identify aggregate rows within the flattened rowset returned from a AS 2000 server.

However, this is a non-trivial task and requires a lot of effort to get everything working. Btw, RS 2000 already supports the Aggregate function - so you would then use it as described in my previous posting. Anyway, the Aggregate function is only useful if with a data extension that implements the IDataReaderExtension interface. None of the RS 2000 data extensions implement that functionality however.

Specific support for AS server aggregates is a feature that was added in RS 2005 in combination with a new RS 2005 Analysis Services data extension that works on top of the new AS 2005 AdoMd data provider.

-- Robert

|||

When I use the Aggregate() function instead of the default Sum() function in subtotals RS2005 returns nothing, i.e. it doesn't work.

The problem in all its simplicity: I have two measures in my MDX query:

Sales in dollars (can use Sum for subtotals)