The Ad Hoc Reporting Myth

Empowering users! Giving users access to the information they need, when they need it! Allowing users to decide what they need! These are all great ideas and there are plenty of products out there that can be used to achieve this. The question must be, is it really necessary?

There will always be some users that need this functionality. They will need up-to-the-second ad hoc reporting and will invest their time into getting the most from the tools they are given. There is also a large portion of the user base that will quite happily use what they are given and will *never* invest in the tool set. They don’t see it as part of their job and basically just don’t care.

Back when I started IT, most projects had some concept of a reporting function. A group of people that would discuss with the user base the type of reporting that was needed and identify what was *really needed* and what were just the never ending wish list of things that would never really be used. They would build these reports and they would go through user acceptance and be signed off. It sounds like the bad old days, but what you were left with were a bunch of well defined reports, written by people who were “relatively speaking” skilled at reporting. What’s more, the reporting function could influence the application design. The quickest way to notice that “One True Lookup Table” is a bad design is to try and do some reporting queries. You will soon change your approach.

With the advent of ad hoc reporting, the skills base gradually eroded. We don’t need a reporting function any more! The users are in charge! All we need is this semantic layer and the users can do it all for themselves! Then the people building the semantic layers got lazy and just generated what amounts to a direct copy of the schema. Look at any database that sits behind one of these abominations and I can pretty much guarantee the most horrendous SQL in the system is generated by ad hoc reporting tools! You can blame the users for not investing more time in becoming an expert in the tool. You can blame the people who built the semantic layer for doing a poor job. You can blame the tools. What it really comes down to is the people who used ad hoc reporting as a “quick and easy” substitute for doing the right thing.

There will always be a concept of “standard reports” in any project. Stuff that is known from day one that the business relies on. These should be developed by experts who do it using efficient SQL. If they are not time-critical, they can be scheduled to spread out the load on the system, yet still be present when they are needed. This would relieve some of the sh*t-storm of badly formed queries hitting the database from ad hoc reporting.

I’m going to file this under #ThoseWereTheDays, #GrumpyOldMen and #ItProbablyWasntAsGoodAsIRemember…

Cheers

Tim…

Dbvisit Reporting…

I try to stick close to core Oracle technologies and steer clear of product endorsements, but over the years I’ve bumped into the ladies and gents from Dbvisit a number of times and they have been consistently cool and consistently on the money with their products. At Oracle OpenWorld 2012 I bumped into Arjen Visser who told me about their new product called Dbvisit Reporting, so I asked him to keep nagging me until I tried it out. When we met at UKOUG 2012 I still hadn’t tried it, but he had been too polite to bug me about it. This weekend it made it to the top of my list. 🙂

Dbvisit Reporting is essentially a cut-down version of Dbvisit Replicate, targeted specifically at creating real-time offload reporting databases. I’ve been having a play with it today, which resulted in this.

You can see my previous articles on their other products here.

Like all their products, Dbvisit Reporting delivers exactly what is says it will. The install is quick, so you don’t need to be a rocket scientist to get it working.

Keep up the good work ladies and gents! 🙂

Cheers

Tim…

PS. You can now test-drive Dbvisit Standby in the Cloud.