SSRS performance from cube vs sql
Hi All, Please let me know the best approach in building SSRS(2008 version) reports for the below explained scenario. Working for a mid-size company on a pilot project to develop SSRS reports for user community. Our main goal is to facilitate users with ad-hoc reporting capabilities, so that in long term they would be able to build their own ad-hoc reports. So, is it a good option in terms of performance and user accessability to use an SSAS cube on top of tables and building reports through cube or to use report builder on top of cube to develop reports. Because the users are not much acquainted with MDX and other technical aspects,also are there any other tools which can be used on top SSRS for building dash boards or would SSRS be sufficient. So, what are the best optons? Secondly, is the performance of building reports through SSAS cube faster compared to that of using Tables(sql)? Thanks, Shiv
April 8th, 2011 12:02pm

A well-designed cube can be a much better source for ad hoc reporting than a relational database source. It should perform faster, it should organize information in a way that's familiar to users, and it should contain the logic for calculations that could never be stored in tables (as long as it is designed well). Users can use a drag-and-drop interface to build reports, but the caveat is that these are still relatively simple reports. For more advanced reports, drag-and-drop won't work and someone would have to write MDX queries which is not something the user can do. On the other hand, advanced SQL queries is not something the average user does either. So if your concern is primarily centered on performance and user-friendliness, then yes - a cube should provide better performance and it should be more user-friendly than SQL tables. The key to meeting these goals is in the design of the cube.
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2011 12:13pm

So, the option of using report builder is not necessary as cube itself can be user friendly?
April 8th, 2011 12:19pm

The cube is just a database, just like your SQL tables are stored in a database. It doesn't have a user interface by itself. You can use Report Builder to create ad hoc reports from a cube. You can also use Excel PivotTables to create ad hoc reports. And other tools, as well.
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2011 12:24pm

Okey, but i would like to know the better option between 1) using report builder or 2) using SSRS GUI itself both ontop on SSAS cube for ad-hoc reporting by users ( technically not good ). Thanks, Shiv
April 8th, 2011 12:29pm

For users, Report Builder is the better option. I assume by SSRS GUI that you mean Business Intelligence Development Studio (BIDS). The functionality between the two is the same as far as your users are concerned, but Report Builder is less intimidating and has wizards to make it easier to build reports. If they develop more advanced skills for fine-tuning the layout and adding expressions, etc., then Report Builder is still a perfectly good interface for them. The advantage with BIDS is the ability to work with multiple reports in the same session (so you can copy and paste between reports for example) and to work with multiple projects in the same solution, then deploy reports in bulk to the server. Usually users don't need this ability.
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2011 12:36pm

Thank you, for the reply. one clarification, For user friendliness and ad-hoc reporting, are there better tools like 1) Proclarity 2) Dundas 3) or using Report builder would suffice the needs. Thank you. Shiv
April 8th, 2011 12:57pm

I can't really answer that question for you. Ultimately, the users will have to determine what they consider to be user-friendly. I think Report Builder is a reasonable tool for most needs and you are not limited to using only cubes as a source. Proclarity requires a bit more skill as compared to Report Builder, is more limited with layout options, and can only be used with cubes. I only have limited experience with Dundas from several years ago, so I can't offer an opinion on any current offering they have. If you're using a cube as the back-end source, you don't have to limit your users to a single tool, however. You can start with one, like Report Builder, and offer other tools later if there are specific needs that aren't being met.
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2011 1:05pm

Thank you. That is really help ful for our team. Regards, Shiv
April 8th, 2011 1:07pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics