Into the Multi-dimensional Space

Lately, I’ve run into a few “multi-dimensional” posts in the public SSRS newsgroup. It seems like more and more people target SSAS 2005 cubes as a reporting source. It looks like I am not the only one who have noticed this trend. Brian Welcker has recently commented about the SSRS-SSAS integration in his Fingerpops blog. Here are some notes based on my experiments with the MDX Query Designer.

Limitations
The promise of the MDX Query Designer (implemented by the SSAS team) is giving the report author a tool to produce easily SSAS-based reports without requiring MDX knowledge. Unfortunately, the MDX Query Designer and SSRS integration has left a substantial ground for improvement. For example, it should allow dropping levels of a parent-child dimension on rows to produce the report similar to this one.

I can produce this report in the SSAS cube browser (or another OLAP browser) in seconds. However, the MDX Query Designer doesn’t allow me to request dimension levels side-by-side. I understand that the SSRS can consume only flattened datasets but this doesn’t explain why the MDX Query Designer lacks behind the cube browser which uses OWC to display the results in a two-dimensional format as well. So, my SSAS-SSRS wish #1 is to see the MDX Query Designer support the same functionality as the SSAS cube browser.

Aggregates
There are two ways to request data from an SSAS cube – cellset (hierarchical) and rowset (two-dimensional). SSRS uses the second format which is, of course, more natural for relational reporting. SSRS is not an OLAP browser and it should be viewed as such. Unfortunately, as a result of flattening the data, the SSAS aggregates (All and level aggregates) are lost. You may wonder why on earth you may need the SSAS aggregates instead of re-calculating them using the SSRS Sum(), Avg(), etc. functions. Well, the short answer is that SSAS may go far beyond the standard aggregate functions. For example, the Account dimension in the report above uses custom operators to roll up account categories. For example, income categories add up while expenses subtract. This is why the Balance Sheet Total in the report is $0. Using account charts is a cornerstone of financial reporting. If you can’t bring the SSAS precious aggregates, you have no other choice but to re-invent SSAS with relational reporting which is something you should avoid.
The good news is that SSRS can ask SSAS for the “lost” aggregates. If this is the case, the Aggregates element of the MdxQuery element in the report RDL file won’t be empty, e.g.:
<Aggregates><Aggregate><Levels /></Aggregate></Aggregates>

Here is a trick to make SSRS ask for aggregates:

1. Use the graphical MDX Query Designer to produce interactively the report dataset.
   
2. Switch to the Layout tab and author your report.
   
3. In a texbox inside the report Group, enter the expression =Aggregate(Fields!<FieldName>.Value), where FieldName is the field that you need to aggregate.
   
4. Save the report.

If all is well, the Aggregates element will be populated and the Aggregate function will return the SSAS aggregate value. However, the problem in the aggregate story is that:

• Sometimes SSRS refuses to ask for these aggregates.
  
• It is not clear what you could do on your part to help it out.
  
• The Aggregates RDL syntax is not documented and you don’t know how to force the MDX query to bring the aggregates.

For example, I attempted to produce the above report by manually creating the MDX statement. While I succeeded to some degree, I wasn’t able bring the aggregates. Neither I was able to produce the sample report with the groups I wanted.

In conclusion, SSAS and SSRS could be a winning combination for OLAP and relational reporting. I hope that future service packs and releases will improve the SSRS and SSAS integration and preserve the fidelity of the cube data.