I’m just finishing off my presentation for PASS, in the course of which I’ve come up with a whole bunch of topics to blog about in the future, but now I’ve got some more spare time I’ve started to look at Reporting Services 2005 in more detail. Unfortunately this has also reminded me of something that has irritated me for a long time and which I need to get off my chest…
Now, before I get all steamed up I should say that I have done quite a bit of work with Reporting Services and like it a lot – its extensibility and programmability mean you can do a lot of cool stuff with it. However in my opinion it has one big design flaw, a flaw that Yukon doesn’t really seem to be doing much to correct, and which makes me roll my eyes and tear my hair whenever I think about it: Reporting Services just doesn’t support Analysis Services properly.
Before the comments start, yes, I have seen all the new features in RS2005 to do with AS support, but in my mind the problem is a fundamental one. And it’s that RS2005 still expects to receive a flattened rowset when you’re querying AS. Why is this a problem? Well, MDX gives you all this rich functionality to decide how you want to lay out your resultset, crossjoining as many dimensions you want on rows and columns, slicing by what you want, and RS then expects you to ignore all this and make MDX behave like SQL. You have to put measures on columns (what happens if you want to put measures in the WHERE clause then?) and all the rest of your dimensions on rows or in the WHERE clause, and then use the matrix control to recreate what you used to be able to do with a simple crossjoin. In RS2000 it was only recommended that you write your queries this way, and you could ignore this advice if you liked; in RS2005 this rule is actually enforced! What about backwards compatibility? Argh! It’s clumsy, it’s restrictive, and it’s a waste of time.
All I want is to be able to enter any MDX statement I want, assign it to a matrix-like control that understands multidimensional resultsets, and be able to display the results in a report. Not difficult, surely? I know it might break the purity of the design, but why should every data provider be forced to dumb itself down to the level of SQL? After all, isn’t one of the unique selling points of AS that MDX allows you to get round exactly this kind of inherent limitation in SQL? To me it’s unbelievable that the two Microsoft flagship BI products don’t understand each other on this basic level.
OK, rant over. Is anyone listening?
You are so right, RS is crappy
Chris, I agree, with you on this. Analysis Services having to "stoop" down to common table based record sets to utilize reporting services is alarming.
Chris – you can be sure that Microsoft is listening. RS2005 was not designed for the level of integration with AS that you were looking for, but we are very well aware of the issue you raised. Your blog had caused pretty extensive discussion in both AS and RS management teams, and while it won\’t cause any changes in RS2005 since we are so close to ship, it may influence future versions.
Chris,I haven\’t looked at RS2005 (yet) (been too busy on IS & AS) so I didn\’t know this. Quite frankly, I\’m flabbergasted. RS2000\’s support for AS was shoddy at best (no MDX builder???) but you\’d think that they\’d be able to supply something like this – its shouting out to be pretty standard functionality if you ask me!I note Mosh\’s point though (above) and hope that this appears in the future (lets hope it doesn\’t take another 5 years).Great blog by the way – I\’ve only just come across it after being refererred from Christian (http://blogs.conchango.com/christianwade) and I\’ve put you on my blog reader!-Jamie
Looks like you have more company joining this rant!http://www.tdwi.org/News/display.aspx?ID=7753>>Microsoft Fills in Missing BI Blanks11/2/2005By Stephen SwoyerWith its SQL Server 2005 launch less than a week away, Microsoft Corp. is starting to fill in some conspicuous BI blanks. …Mark Job, a SQL Server developer with Microsoft solution provider Immedient Corp., says he could talk for hours about what’s to like, BI-wise, in SQL Server 2005—starting first and foremost with the revamped SQL Server Integration Services. “[It] creates a lot of new functionality, along with the ability to bring along old DTS packages by hosting the old runtime, giving developers more time to convert old packages,” he says. “The visual debugging environment, separation of data and control layers, and elevation to transforms of a lot of what had to be done in script before are all good for the developer, but the big customer opportunity here is scalability, which will open up [Integration Services] use to a lot more needs.”Elsewhere, says Job, Microsoft’s OLAP and data mining technology is verging ever closer to best-of-breed. “Analysis Services has huge changes with attribute-based modeling and the [Universal Data Model],” he says. “ While it will be necessary to re-architect existing cubes to take full advantage of the new features, the additional capabilities that open up are well worth it.” Elsewhere, says Job, SQL Server 2005’s enhanced data mining component introduces “huge improvements in models and UIs. This will open up the potential benefits to a broader audience, and the exposure of all these tools for developers will make great opportunities to leverage DM in enterprise apps.”One disappointing aspect of the next-gen SQL Server release is the Reporting Services 2.0 component, says Job. “Reporting Services … doesn\’t introduce as much net new functionality as the other areas, with the exception of the new Report Builder feature,” he comments. “I still am frustrated by the ‘flattened rowset’ approach to MDX queries as RS inputs, which neuters a lot of the power of MOLAP by making it fit the relational mold. I hope Microsoft addresses this in a Service Pack or dot release soon.”…>>
Hi Chris,I agree entirely that it is a glaring ommission and when I attempted to put members other than Measures in the columns I was shocked and dismayed that you can\’t. Your statement about this got me thinking: "In RS2000 it was only recommended that you write your queries this way, and you could ignore this advice if you liked; in RS2005 this rule is actually enforced!" After a little tinkering I discovered that if you create a datasource in RS2005 of type OLE DB then choose the provider as "Microsoft OLE DB Provider for Analysis Services 9.0", the functionality from RS2000 is preserved.. Yay!I imagine you would have already worked this out but for all those who come across this blog in the future….
I just ran across this today for the first time. What a truly horrific and inexcusable limitation for RS 2005. Truly disgusting.
Chris,
Yes, I ran into this limitation when I was doing a demo for my customer. I think the reason for such a limitation for measures on columns is because so that the RS can create a well-defined data schema for the report. In the report, however, it is up to you where you want to put down as the column, so the measure does not have to be in the column in the report.
RS has its engine to pivot the data as you want. Yes, the integration between AS and RS is not as perfect as we would like to. The report builder is more SQL like than fully utilizing the features in AS. Hopefully this can be improved in the next release.
I feel compelled to second Chris\’ point here. Having worked in the past with the Office Web Components, it was my expectation that Reporting Services would be more aware of Analysis Services, and be able to offer the same, if not better, integration. The Matrix control is not at all well suited to displaying multi-dimensional data, for example if I want to show totals for groups, I have to create a SUM expression in the report, why I ask when these values are already supplied by Analysis Services?
Chris,
I know this is an old thread, but can you suggest a top-notch reporting tool that does have the AS integration you are looking for?
Mark Garner
http://mgarner.wordpress.com
Have there been any recent changes to SSRS 2012/2014 to address this problem?
No, unfortunately not.