The Ad-Hoc Reporting Myth

 

Introduction

 

Once the data is collected and the transactions are handled, reporting becomes the most important function in most computer business applications.  Reports provide the information and analysis needed to make decisions.  If the information is not available, or worse invalid, bad decisions can be made that wipe out the profits of 10,000 sales or cause a profit making division to be sold.  At a lower level, the invoice that was just mailed might under bill the customer and the customer certainly is not going to volunteer to correct it.  The data is usually in the computer, but most users can’t get it in the form that they need it without technical skills or the use of a programmer.  Ad-Hoc reporting tools that let the end-users pull whatever data they need have been available for more than 20 years but have not been the panacea that was expected.  That is not the fault of the tools or the people.  The end-users changed from a rigid reporting paradigm to unfathomable chaos.   Most are overwhelmed by the complexity and risks, so they do not use the tools.  This article presents a agile solution called “Core & Options Reporting” that is understandable, eliminates the risks of ad-hoc reporting, can have some interesting side benefits for the organization, and requires less than one hour’s of training for the end-user.  In the words of one accountant, it is “something that he has dreamed about”.

Text Box: Sales P&L Common Methods: 1. Sales, expenses & profit for each customer 2. Sales, expenses & profit for each product 3. Sales, expenses & profit for each area 4. Sales, expenses & profit for each season 5. Sales, expenses & profit by month Five custom report programs Core & Option Method: · Core: Sales, expenses & profit · Options: customer, product, area, season, month, in any combination of one or more One custom report program Development Costs: · Core & Option – One web page and one report program · Common (MS Access) – 120 report programs to match potential from Core & Options (5 factorial) · Add another option – Add one item to a list for the web page or write 600 more MS Access programs Box #1

Core & Options Reports

 

When people think of reports, they think of piles of paper coming off of a printer.  I am going to define a report as anything presenting data from the computer where the recipient cannot use it to directly change the underlying data in the database.  It can be in the form of lists or graphs displayed on a web page, saved to a PDF file, downloaded to a file or Excel, or it can be sent to a printer in the normal way.  Each report is usually created from one computer program.

 

Custom computer programs are expensive whether a professional programmer writes it or an end-user uses an ad-hoc reporting tool.  One may not be available and the other incurs additional risks.  Filters have been used to make reporting less rigid.  They limit the quantity of data, such as sales by one sales person or to one customer.  They can be hard-coded into the program or selected by the end-user when the report is created.  They have reduced somewhat the number of report programs needed.

 

However, there is still a tremendous pent-up demand for reports and frustration is high.  The Core & Options paradigm increases the number of reports and web pages available to the end-user while reducing the number of computer programs that need to be written, thus reducing development and support costs.  It is based on the fact that many reports are similar except for a couple of columns, sorts or totals (see box #1).  Using the Core & Options paradigm, the programmer writes the 90% of the program that contains the core and all of the formatting.  The user selects options when they request the report.  The program receives the options, finishes writing itself, and produces the report.  If specific reports are run overnight, the scheduler will send the options when it runs the report.

 

This sounds similar to OLAP cubes, and it is.  However, the OLAP methodology usually uses an extract from the live database and is targeted for strategic users (see box #2).  Tactical users need live data and do not have time to wait for the cube to be updated.  Also, cubes take a lot of programming effort to set up and the number of dimensions, which is equivalent to options, has a practical mathematical limit.  It takes a lot more effort to add a dimension after the cube design is set.  An option can be added to a list for the web page and it is propagated automatically to the report without any additional programming.  Finally, well-written Core & Option reports can pull data from cubes, reporting databases or transaction databases.

Text Box: Super User - has technical skills and understands their piece of the data, may be tactical or strategic Tactical User - needs up-to-minute detailed data to do their job, limited technical skills Strategic User – Executive or analyst who needs highly summarized and graphed snapshots to make strategic decisions, limited technical skills Box #2

Common Recommendations & Risks

 

Management has been well aware of the problem for years.  The technologists have said, “Reporting is easy.  Use Excel, Access, FOCUS, Business Objects, Crystal, SAS, Business Intelligence tools, etc and the users can create their own reports.  This will free up programmers to do more important things”.  Well, it hasn’t worked!  Often, users are expected to learn the tool by reading manuals or going through an on-line tutorial.  Training, if provided, usually covers the use of the tool, but not the data.  For most users, this introduces some very real and potentially damaging risks.

 

The most common risks fall into four categories:

 

  1. Complexity – Database structures have become so complex that they overwhelm the normal user.  They may be confronted with 500 tables, some with more than 200 fields.  And they can’t use just one, they may need to merge data from nine tables just to create one report.  Even with query tools, it is very easy to miss one or misinterpret their relationship.  Super-Users may be able to figure them out, but the average user frequently gives up, or worse, creates reports with wrong answers.
  2. Over Reporting – Assume that you are trading gas or oil based on your net quantity and dollar position.  The report program gets the quantity from the deal, gets the price from another source based on the Deal ID, computes the dollars, and presents the total quantity and dollars.  It works fine until a deal has a second, tiered price at a certain quantity.  The dollar total will be correct, but the volume will be added for each price, doubling the volume, unless one is very careful and aware that it can happen.  It would be easy to base the next trade on the wrong volume.
  3. Under Reporting – Suppose, in the scenario above, that a price was missing.  The volume could be excluded and again, it would be easy to base the next trade on the wrong volume.
  4. Server & Network Overload – The user may believe that they are producing a twenty-page report with about 1,000 lines.  Instead, they accidentally ask for a report containing 100,000,000 lines.  Something of this magnitude would slow down processing for everyone, both those generating reports and those doing transactions.  It could even bring a web site down.

 

Core & Option reports transfer the risks to the programmer while providing the power and flexibility to the end-user.  The programmer already understands the complexity of the data structure and its relationships.  The programmer can have indexes added to the databases to improve the load on the servers and do other things in the background to improve efficiency.  And finally, it is the programmer’s job to insure that the reports are correct.  However, a large number of options can make a report unwieldy and undermine the efficiency efforts.  The programmer and business champion need to work together to keep the list at a reasonable, but meaningful, level.

 

Organization Impact

 

The report development function itself can have a positive impact on an organization.  Most IT departments are organized in a hierarchal structure based on applications or technology.  Both structures promote silos.  DBA’s may understand the data structure behind the applications, but are unlikely to understand the impact of the data itself on the business.  An application specialist may understand everything about their specific piece of the application, but do not know how that piece fits with other parts of the business.  The Business Analyst function was created to span the gap, but it is frequently at either a more theoretical level or at a narrow, detailed level.  Reports, however, frequently combine data from more than one application.

 

The hierarchal staffing structure does not support combined reports very well.  When one application is driving the report, the other application may be changed without the first group being notified.  Also, the person supporting the report may also support transaction processing and the report may have a much lower priority when there are conflicting tasks.  Since reports have such a large potential impact on the business, IT management should consider creating a reporting team or department.

 

A reporting team can provide a lot of benefits to both IT and the business community – if new reports can be turned around quickly enough.  If assignments and production support are distributed uniformly throughout the team, the reporting team can provide continuity and breadth of knowledge about the business that may not be available in other specialties.  They would understand the databases, data flow, application relationships, processes, and the impact of the application on business.  With this much knowledge, the reporting team can become proactive.  They can suggest new reports instead of waiting until someone requests one.  This places the burden on management to recruit members who can understand many business situations and has the background to be proactive.  Also, team members must have empathy with their customers.

 

Tools

 

Core & Option reporting can be done with almost any tool.  However, some are more difficult to use than others, thus increasing development and production support costs.  In the opinion of the author, Java, ASP, VB and similar languages are not much different than COBOL on the mainframe, when creating reporting applications.  They cannot finish writing themselves without a lot of effort.  Self-building reports require an interpretive reporting product.

 

Text Box: Questions about Tools · Can options, or measures and dimensions, be added without a major programming effort · Can it pull data from transaction databases or must it have its own snapshot · Is it scalable – will it show down if the number of users triple · Is it commonly used? Does it require a lot of training for the developers and/or the end users? There are some products on the market that can provide nice reports with drilldown capabilities.  They have OLAP interfaces that can bounce around the data, if the data source is designed with them in mind.  However, programming is expensive and delay is even more expensive.  In the author’s experience, the average time for developing a report from concept to user acceptance testing, with a web front end to collect the options and filters, is about three workdays, assuming prior knowledge of the data source(s) and having the right tools.  Sometimes, it is much faster than that.  A good 4GL reporting tool with OLAP capabilities can make that possible.

 

With that in mind, the author requested a proof-of-concept (POC) from a vendor to prove the Core & Option capabilities.  They had to create three main reports and two graphs, ranging from simple to complex, small and large data sources, single and multiple tables, changes between yesterday’s and today’s dollars, and with drilldown capability to supporting detail reports.  A sample Core & Option web page, which was already tested, was provided for the front end.  When complete, the POC had to use the production databases for the demo to the VP’s.  The vendor’s consultant was allowed four days to complete the task, which he did.  The VP’s were all discussing “Core & Option” reporting as they left the demo.

 

Like everything else in life, a task is always easier with the right tool.  A programming language will suffice, but they take a lot of time and require a lot of code.  They are also hard to maintain.  OLAP tools using data cubes appear to provide the required flexibility, and they do as long as you do not want to make any changes to the source data cube.  The flexibility is in the user interface while the back-end data source is very rigid.  A lot of costly programming is required to change it.  However, some reporting products use virtual cubes.  They provide the OLAP capabilities while maintaining their flexibility.

 

Conclusion

 

Change is the driver of business today.  If an organization is not agile enough to adapt to the changing environment, they will lose customers and be out of business.  Having the right data, at the right time, in the right form is the key for making the right decisions.  Core & Option reporting is a simple solution for doing that.  End-users should be limited by the data that is available, not by the tools for pulling it out.