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.