Expert: Todd Binenstock - 9/10/2006

Question
Hi,

I'm looking to find out about some of the best ad-hoc reporting tools out there.  I've used some inhouse built tools but I just dont get enough information out of them.

I want a tool that can take english queries and format adn give me the reports I want.  I want minimal effort in creating reports but the reports need to be robust enough to present to my management.  THe inhouse tool i have today requires pumping to excel and then working in excel after that, something which i dont like.

If you can help that would be great.  Thanks.

Answer
Hi Jay,

To give a full answer, I would need to know a lot about the size of your business, the number of users and what exactly you are trying to acoomplish.

Based on your general question I will give you a general response.

It sounds like you may have exceeded the capabilities of standard query and reporting tools, such as Access, Crystal or Qmf, etc.  I am not terribly familiar with these tools, other than to know that they are merely used to generate SQL to build relatively simple, highly structured queries and reports.

There is another category of software called OLAP, On-line Anlytical processing.  This name contrasts these systems with OLTP, which is traditional database design used for Transactional Processing.

OLAP systems are designed for Analytic needs such as you describe.  Quickly getting to accurate answers at any level and calculated with meaningful value added business logic, secure, multi-user and using analysis and business friendly concepts and constructs that make them relatively easy to populate, report from, secure, and maintain.

The key to OLAP reporting is the concept of dimensions... ie. breaking down your data into multiple aspects by which your data can be described in english.  Examples of dimensions might be accounts, time periods, stores, products, custormers, etc.  These dimensions are structured hierarchically so that reporting can be done at multiple levels.  Typically totals and derived values are predefined and/or precalculated.  Thus complex multi-level cross-tab type queries are available potentially in seconds.  Performance depends on the amount of data, the complexity and the technology chosen.  OLAP is available in  multi-dimensional (proprietary), relational and hybrid flavors. There are advantages to each type.

The software I work with is called Essbase, made by a company called Hyperion.  It is the top selling OLAP system, for many good reasons.  IT includes and excel interface, so that users can manipulate queries in a familiar environment and leaving open the possiblity of formatting and additional calculations if so desired.  Hyperion also supplies tools for "canned reporting" and "graphical analysis" if that is mroe what you are looking for.  While originally multidimensional for performance, Essbase is now hybrid, with the ability to integrate with relationally stored data.  It is truly enterprise class software, and I could go on and on about its robust capabilites.  Essbase requires no SQL database expertise for users or administrators, though help developing or at least optimizing and deploying applications is recommended.  Microsoft also provides its Analytic (OLAP) Services with each SQL Server it sells.  This is not top-tier software, but is adequate for reporting and the price is right.  There are others as well.

I hope this sets you in the right direction.  Feel free to reply with additional questions.