Integration between SSRS and SSAS

Any plans to improve the integration between Reporting Services and Analysis Services?

I‘ll try to be specific as possible. Below is a state of affairs for the SSRS and SSAS integration as of today (MS SQL Server 2005):

1. When querying Analysis Services cubes, the data can be returned in one of two ways: as a cellset or as a recordset. Reporting Services uses the recordset format, and as such, the data from Analysis Services goes through a flattening process to return the data in a two-dimensional grid.
   SSRS should accommodate SSAS native output format without flattening.
   This is obviously a very serious point of contention between SSRS and SSAS. This particular issue is affecting usefulness of the SSRS as reporting software for SSAS.
2. Quite often, depending on the MDX, SSRS produces the infamous error message “The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension”.
3. The error message above pushes to switch from the native "Microsoft SQL Server Analysis Services (AdomdClient)" to MS SQL Server 2005 Analysis Services 9.0 OLE DB Provider. However, OLEDB provider goes through the flattening process which negatively affects the received data overall format on the SSRS side.
   ADOMD native provider should support any kosher MDX statement. SSRS should be able to receive ADOMD output as is.
4. Graphical MDX Query Designer doesn’t accept manual changes tothe MDX.
   It should be 2-way communication.MDX Query Designer should analyze manually tweaked MDX and if it is legit accept it.

Hopefully, Tablix region as a UI control will be able to support native SSAS data outputs in MS SQL Server 2008.

I am talking here about straight reporting. I am not asking for the interactive OLAP client.

References:

1. Integrating Analysis Services with Reporting Services
   http://technet.microsoft.com/en-us/library/aa902647(sql.80).aspx
2. Rant: Reporting Services and Analysis Services
   http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
3. The state of Reporting Services UDM (Analysis Services) integration
   http://peterkol.spaces.live.com/blog/cns!68755AEAC31F9A6C!185.entry

Regards,

Yitzhak Khabinsky

Brian Welcker/ Robert Bruckner: I hope that one of you can elaborate. This is a VERY important subject matter. Thank you.a

|||

I agree....from the very beginning I thought that SSRS's ability to work with cubes was a VERY important feature because it would allow access to the amazing aggregation power that cubes have...without this you have to spend lots of time writing complicated and bad performing SQL in order to put aggregates in your reports (and the fact is that reports are mostly about aggregating data). I would have thought this to be a simple, natural solution and am AMAZED that it doesn't work.

I actually work for a company where we currently use Cognos and Reporting Services, and one of the things I've told them is to consider using Analysis Services instead because then the cubes would be accessible to SSRS. I guess I bought into the marketing hype a little too early :-).

SB

|||

Thanks for your feedback. The core scenario you seem to want to paste an arbitrary MDX statement into RS and have the RS engine format it. One question would be - where does your MDX come from? Are you crafting it by hand? We felt that most users are not MDX experts and would simply use the query builder (which will form the MDX in a conformant form).

Anyway, while supporting arbitrary MDX is certainly a valid scenario, it is simply not what RS was designed to do. In order to understand the data that is being returned from a source, SSRS must have a fixed schema. Without a schema, RS cannot compute subtotals or sort or filter the data. A cellset based tool relies fully on the back-end for all interactivity (which negates all of the RS caching layer). Tablix in 2008 does nothing to change this approach - in fact several of the Tablix functions (side by side groups, for example) are not even possible to express in MDX.

We do understand that people would like add this functionality to RS and we will consider supporting arbitrary schema and round trip the interactivity for post-2008 future version.