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…



Author: Tim...

DBA, Developer, Author, Trainer.

7 thoughts on “The Ad Hoc Reporting Myth”

  1. Discoverer was a great tool, allowed anyone with a little common sense to write reports. Some people were worried they would be able to corrupt data and I would say no, but if their logic was flawed the generated SQL could (and very often did) affect performance. Then we needed to go back to you ‘experts’ to get us out the mess

  2. Tim,
    the data modelling and SQL problems arising from ad-hoc reporting are ugly. But I think an even bigger problem are the semantics of data: in old days handcrafted reports the numbers had (more or less) meaning – in (many) ad-hoc reports it is very simple to compare apples, oranges and all the other fruits.

  3. Debra: I think most of the tools are good if they are 1) configured properly, 2) used properly and 3) used for the right reason. It’s unfortunately, it seems like a lot of the time zero of these caveats seem to be true. 🙂

    Martin: Agreed. A lot of the subtlety can be lost unless the users really know the data well. When they do this stuff can be awesome. Unfortunately, the brain drain is happening in the user base as well as the IT staff… 🙁



  4. Tim,

    I agree that the phrase “empowering the users on reporting” or “empowering everyone to make smarter decisions from anywhere” is just a marketing term which lately seems to be working well because every IT software company is mentioning it.
    Now, going back to what Debra mentioned -Discoverer- it is also very true that the reporting tools have evolved radically making it easier for users to manipulate data just like “MS Excel” without having to dump the data into it.
    Of course, without a clear semantic layer it is impossible to have a user doing any reporting at all and you have to rely on IT for that and also for getting a “reasonable” performance.
    The key solution for a true empower to the end user would be a tool/something that could create a dynamic semantic that could be also contemplating the DB performance, sql, etc…


Comments are closed.