August 15, 2007 at 5:51 am
We're interested in an ad hoc solution for creating simple reports, most of which will be exported and subsequently munged around with in Excel. Having played with the SSRS ad hoc report builder, I must say I was slightly underwhelmed, but I'm open-minded. Has anyone used this as a production solution to allow manager-types to create some of their own reports? Would you say it was:
Enquiring minds want to know.
August 16, 2007 at 7:23 am
MSRS really isn't an ad-hoc tool, so shouldn't be approached as such. MSRS is really for creating quality, production ready reports. To take advantage of it's more advanced features and capabilities, you should have an IS background (hense why you develop reports in VS), so keep VS away from manager types. We could argue why manager types are doing ad-hoc reports, but that's another forum.
MSRS can achieve "some" of what you're looking for, due to the flexible nature of it's query engine. You haven't posted many details, such as what data source you're using. (Guys, let's make sure there's sufficient details to comment in posts!)
In general, here's the approach I've found that delivers limited ad-hoc:
Not an elegant solution, and certainly not recommended as a reporting strategy. But it does allow someone to get the number they were looking for.
Much better to focus on ad-hoc tools for ad-hoc and reporting tools for production. Different requirements and expectations.
Cheers.
August 16, 2007 at 7:47 am
Gosh, Dave, great pontification and lecture!
Anyway, I'd love to hear from anyone who is actually aware of the Report Builder that was added to ssrs 2005.
(Dave, go here:
and here:
http://msdn2.microsoft.com/en-us/library/aa569742.aspx
)
August 16, 2007 at 8:38 am
Ah, that's "Report Builder", which can publish into ReportServer. Companion tool. Didn't know you were refering to that.
August 16, 2007 at 9:55 am
You may want to look into using SSAS and Excel combo for ad hoc analysis of data. Excel is a great ad hoc analysis tool for viewing cubes built in SSAS on top of a data warehouse relational model. This is, of course, a much larger overhaul then just using report builder against the production data. The trick with ad hoc reporting is that the tool must have knowledge of the data schema. Cubes provide enough metadata to do true ad hoc analysis, and excel has the tools already baked in.
August 17, 2007 at 6:12 am
Ok, I actually have deployed the report builder in two different production environments.
First, after the first experience, I was willing to do it again. It was not horrible. The first time, I made the mistake of making the models too complicated. I have found that if you make models that have more than three or four tables in them, the users will quickly be lost.
My second deployment attempt at the first company went pretty well. The moster models I had built were cut up into task-oriented models that made it easier to get a good starting point.
At the second company I used this, I knew I needed to keep things easy to navigate so it went much smoother from the start.
Start with making sure you know who the users are and that they are capable of handling something complicated. They need to be able to use the tool (I have found that people that can create a pivot table in Excel seem to be able to handle report writer). They also need to have an understanding of the data. The biggest problem I have had is the users pulling garbage because they did not realize they needed to filter something out. This is also where the task-oriented models helped because I could pre-filter information rather well.
Do not underestimate what you are going to do. For a 10gb database with less than 100 tables and a user community of 5 people, I would guess setup, training, and deployment is at least a couple of months.
August 17, 2007 at 9:11 am
Thanks for the reply, Michael. It seems like a reasonable solution that with some planning, training, and use of the 80/20 rule could reduce report requests significantly. I'm a little surprised that I'm not finding more evidence of production useage. It's good to hear your experience has, on the whole, been positive.
Thanks again,
SteveW
August 23, 2007 at 2:24 pm
Politically and practically it's worth deploying Report Builder. Its hard to make a simple reporting tool that can do anything of value, but having it based on a model gets close. Some of the group and expression stuff probably over the heads of a lot of the 'average' users. But in practice users can do a certain amount of adhoc reporting and that empowers them and takes some of the heat off IT to keep providing data sliced 8 different ways. You might be surprised how the request volume drops when you can say 'hey, you can self serve'! Ideally those user developed reports may turn into full fledged report requests once they figure out what they need - on their dime. The only hard part is building the model and that isnt that hard, and I agree with the earlier comment about keeping it simple to start with (you can always deploy multiple models).
August 24, 2007 at 12:15 am
In my experience, Report Builder models are most successful if they are restricted to single subject areas (eg: Sales / Inventory etc) not the full blown relational model. Don't expect to just run the wizard and deploy the model...it ultimately won't succeed out in user land as is...you do need to tidy up and organise it. End users (not capable of working in BIDS) need simple solutions for their ad-hoc requirements. The best models I've seen work against a star schema with 1 fact table or alternatively a cube.
Having said that, I am quickly coming to the view that Excel 2007 on top of SSAS cube(s) is the killer solution for ad-hoc reporting, particularly now that its practical to add attributes to dimensions...business users know and love Excel and now we have the tools to enable them while the heavy lifting is already done for them back in SSAS.
John
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply