Fingerpops (RS and Flattened Rowsets)

We have gotten some negative feedback from customers about the RS/AS integration in SQL 2005. This is frequently communicated as a "flattened rowset versus cellset" issue. While RS does consume data from sources in a rowset format, flattened rowsets from AS provide identical functionality to cellsets. The exact same information is provided by AS to the client application -  it is merely provided in a different form. If RS were to retrieve data from AS using cellsets, it would not change RS functionality in any way.  Flattened rowsets are merely used internally for convenience of implementation.

The overall issue is that RS was designed as a reporting tool, not as a traditional OLAP tool. We realize that RS is the only built-in way to deliver AS data in SQL 2005 so people may expect it to work like OLAP clients such as PivotTables or partner products like Proclarity or Panorama.  This approach results in a couple of unexpected issues when integrating the two products.

First, RS currently only supports static schema from a query.  A traditional relational database query, once designed, always returns the same set of fields (with notable exceptions of “select *” and certain stored procedures.  This is not always true for Analysis Services.  In MDX, it is quite easy and natural to design a query which pivots the data, returning one field per member instance.  For example consider a query which is written to return this result:

This result set will work correctly with RS so long as there is always data returned for 2000, 2001 and 2002.  Omitted years (e.g. due to parameterization or data changes) will result in a blank field in the report since RS expects the column to be present in the data.  Added years (again due to parameterization or data changes) will simply not show up, since the known fields are fixed and will not be added to dynamically. 

If, however, the query is written so that data is returned in this form, everything works as expected:

In this case, the fields returned are known ahead of time and cannot vary.  The only thing that varies from execution to execution is the rows of data returned. If a user would like the years to be displayed across the top of their report, they can use a matrix control to pivot the fields.

It is worth reemphasizing that this has nothing to do with flattened rowsets versus cellsets.  With either mechanism of data transfer, the schema can be dynamic (as in the first example) or fixed (as in the second example).

Fortunately, nearly all cases MDX queries that generate dynamic schema can be rewritten to generate fixed schema instead.  The MDX query designer included as a part of the Report Designer automatically handles this on your behalf and can be a useful tool for learning how best to write your queries for use in RS.

We understand this is clearly a problem area for people who are already comfortable writing MDX by hand or are attempting to copy existing queries from AS client tools. Since most AS clients are designed solely for generic handling of dynamic schema (rather than high fidelity custom layout), people who have learned to write MDX for these clients have never had to be careful about constructing their queries to have fixed schema. There is a moderate learning curve required for such MDX experts and tool vendors to adapt to the style of MDX required within RS.

We also understand that people would like the ability to define high fidelity, custom report layouts which adapt to dynamic schema (from AS as well as other data sources). This is on our wishlist for a future release.

The second most common complaint about RS/AS integration is regarding handling of subtotal rows.  Again, this stems from confusion caused by trying to use RS the same way MDX experts traditionally use ad-hoc OLAP clients.  With such clients, subtotal rows are handled dynamically.  In RS, you may choose to include or not include the subtotals in whatever way you see fit.  Due to that flexibility, you must explicitly lay out your report to include the subtotal data using the Aggregate() function to retrieve the subtotals.  Users of traditional OLAP clients sometimes find this unnatural and expect the subtotal rows to be treated identically to any other detail row.  This is a case where added flexibility (of RS as opposed to traditional OLAP clients) necessarily results in added complexity.  There may be usability features that can be added to the Report Design tool in the future to ease this transition, but in the end, this is a user education issue.

Comments

• Anonymous
  December 16, 2005
  At the risk of sounding churlish (and I know I've been guilty of moaning about support for MDX in RS a bit too much), could I make two points on your post?
  
  First of all the problem that people have with flattened rowsets isn't actually the one you're describing. You're right that the static schema issue is the same regardless of whether you're retrieving cellsets or flattened rowsets; the real problem with using flattened rowsets is that in the AS2K era at least they had several irritating limitations (such as being unable to get at many cell properties, the way that column names were constructed when there were multiple dimensions crossjoined) and were a bit buggy compared to cellsets. In AS2005 I think some of these issues have been addressed, although I still see that when you include an All Member in your query it returns null instead of the All Member's name - something which I've been told is by design, but I don't understand the rationale behind. Anyway, the implementation of flattened rowsets isn't your responsibility.
  
  Secondly, the problem with only supporting static schemas for queries is, I accept, something that comes with the way that RS is designed. However what I didn't like, moving from RS2K to RS2005, was the way that the additional rule that you were only allowed to put measures on columns imposed. In RS2K you could write whatever query you wanted so long as you realised that you had to keep what was on columns static; this gave me a lot of room to work with. However, enforcing the 'measures only on columns' rule is very restrictive and leads to all sorts of other problems, such as being able to parameterise queries by Measures, and I don't see why it was really necessary. I'm pleased to hear, though, that you're going to look at support for dynamic schemas in future versions.
  
  
• Anonymous
  December 18, 2005
  The comment has been removed
• Anonymous
  December 19, 2005
  In my version SSRS, the measures only requirement is enforced in text-entry as well as in the designer. This limitation as well as the impossibility of referencing any dimension that includes calculated members makes SSRS a non-starter for me. I find this really disappointing since I was hoping to buy instead of build the next version of our olap report writer.
  
  
  
• Anonymous
  December 19, 2005
  Brian, re the graphical query designer vs text entry mode, I did mean that I was constructing the statement using an expression in RS2K. The point I was trying to make was that even in text entry mode RS imposes restrictions on the MDX you can use in a query. For example, if you want to create a report parameter with a list of available values which is a list of all the measures available on a cube, and take this list from a query, you have a bit of a problem: you need to write an MDX query which puts the measures dimension on rows and not columns, and RS doesn't allow you to do this. You have to go through the following process:
  http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!412.entry
  
  
• Anonymous
  December 20, 2005
  Hi, Chris
  
  I agree with you that there is much to be desired in the SSRS-SSAS integration area. However, by reading your blog, I am left with the impression that you are addressing the wrong team :-)
  
  My understanding is that the MDX Query Designer is implemented by the SSAS team. So is the Report Builder semantic wrapper. The SSRS team has provided the interfaces, the SSAS team has implemented them.
  
  As you, I expect to see much better integration across the BI stack in near future. I hope that Santa will give us a good SSAS 2005 OLAP browser at last. Will see...who's been a good boy.
• Anonymous
  March 24, 2006
  I am having a hard time finding out how to properly use AGGREGATE() in RS reports to cause the SSRS report to use the aggregates created by SSAS. I have successfully gotten it to work on one report but cannot get it to work on any others. I have no idea if this is just flakey and buggy or if I am missing something critical in my understanding of how to make it work. Do you have any info on it? The tutorials and samples don't seem to touch on it and BOL is pretty sparse on the subject. BTW I am using SQL 2005 SP1 CTP.
• Anonymous
  August 17, 2006
  Do you have any more information about the possibility of improving the RS/AS interface to allow (at least)
  
  
  1.  Something besides measures as columns
  2.  Dimensions with calculated members?
  
  We'd like to build our next generation of report writing around RS but without (at least) those featuers it won't work.
• Anonymous
  January 16, 2007
  Its not often that I link to other blog posts but I saw one this evening on Brian Welcker's (have I spelt
• Anonymous
  May 31, 2009
  PingBack from http://patiochairsite.info/story.php?id=154
• Anonymous
  June 14, 2009
  PingBack from http://adirondackchairshub.info/story.php?id=915
• Anonymous
  June 18, 2009
  PingBack from http://ebeanbagchair.info/story.php?id=315
• Anonymous
  June 18, 2009
  PingBack from http://patiocushionsource.info/story.php?id=319