There are many ways to turn data into information. The challenge for companies is to choose the right method to get simple yet flexible access to information for the broadest range of users. Although it may not be apparent, the words simple and flexible are diametrically opposed. Usually the simpler something is, the less flexible it is, and the more flexible it is, the more complex it becomes.

In the spectrum of business intelligence (BI) functions and tools, an information gap exists between the simple and the flexible. Although there are many different ways of getting information from a data warehouse and other data sources, the three most common information access methods - scheduled or push reporting, parameter-driven reporting, and ad hoc query and reporting and online analytical processing (OLAP) - exhibit varying degrees of simplicity and flexibility. A fourth and newer alternative called structured ad hoc may offer the best hope of filling in the information gap, providing a broad range of users simple and flexible in one package.

Batch or scheduled reports are created by IT people or technically astute business unit power users using report development tools. The information in a scheduled report is generally fixed or static and meant to serve a large audience of users with similar information requirements. The user usually retrieves the report daily, weekly or monthly from a known location on the corporate intranet or receives it automatically via email.

Users who receive reports in this fashion generally prefer Microsoft Excel as the output format. Why? In any other format the report is what it is, but in an Excel spreadsheet format, the user has some flexibility to change how it looks and focus on the data they want. Unfortunately, as soon as the user does extra work on the report, the original meaning and authenticity of the report becomes suspect, because in Excel anything is possible. In addition, a majority of users will not take the time, or have the skills, to properly use Excel to focus in on the numbers they want.

Online parameter-driven reporting, an alternative to static scheduled reports, provides users some options, usually through a Web-based form, to customize the content of a report by filtering the records they need. This option has served as the cornerstone of BI for many years. The parameters give users simple flexibility to get what they want without needing to manipulate the output. And, in most cases, a parameter-driven Web form is no more complex than browsing the Internet, making this type of information access easily available to many users.

The drawback to most parameter-driven reporting environments is that the report typically has fixed columns and measures. Only the filters that define the records to be delivered to the report are variable. As a result, many - sometimes hundreds - of parameter-driven reports are needed to cover the reporting requirements of a broad range of users. This means more work and maintenance for IT. In addition, users must spend extra time shifting through all the reports to find exactly what they need.

In the parameter-driven ranking report shown Figure 1, the user can rank the top-10 selling products, within any year, quarter or month, sold at any retailer and made in any manufacturing plant. While this is somewhat flexible, if a user wanted to rank the sales reps with the highest expenses or the retailers with the most returns, they would need to run a different report. While parameterizing filters are helpful, more flexibility is still needed by most users to get what they want.

Figure 1: Sample Parameter-Driven Report

The third option - more like a quantum leap forward in information access - for users is to familiarize themselves with a BI tool, either ad hoc query and reporting or OLAP analysis. These tools provide complete flexibility for them to select and choose what they want. But this complete flexibility comes at a cost in more ways than one, chiefly because the minute a tool is chosen as a method for providing users with information access, 80 to 90 percent of the potential audience for the information is eliminated. Tools require training, technical skills, and time and effort to master. Most users lack the skill and the time and most companies lack the funds to purchase the tools and training for a large number of their users.

Figure 2: The Information Gap of Reports

At first glance, structured ad hoc, a new method for delivering information, looks identical to parameter-driven reporting, but one subtle difference makes it tremendously more flexible - the ability to make selections that change the actual columns on the report. The regular parameter-driven ranking report shown in Figure 1 tells a user about one thing, the top-10 selling products. A structured ad hoc report, on the other hand, can potentially result in hundreds or even thousands of different reports. They are all created and accessed with great simplicity, yet the users still have a good amount of flexibility.

This is where the word structured comes into play. Someone has created the basic structure of the report and is passing that structure on to the user, giving them the option of filling in the contents of the structure. In a sense, the complex work is done by the developer who created the structured template, leaving a simple, flexible form for the end users to get what they need.

While it may seem like a trivial thing, most BI tools do not have column prompting, the ability to parameterize the report columns, which significantly changes the range of what can be provided in a report. For example, a report may have 10 options for the major report sort (Table Of Contents in the example), 10 for the secondary sort, 10 for the cross tab (across sort) sort and 10 for the report measure. That makes the total number of combinations of report outputs from this one report 10 times 10 times 10 times 10, or 10,000 unique reports - available from one location. The only other BI option that offers such flexibility is complete ad hoc query and OLAP analysis - tools only a few select power users can access.

From an IT perspective, the real benefit of structured ad hoc is that just a few structured ad hoc reports can replace the creation and maintenance of thousands of scheduled or parameter-driven reports.

Simplicity and flexibility is the key to getting a broad range of users what they want. I have seen too many companies concentrate on the features of power-user tools (ad hoc query and OLAP) when selecting a BI software product. These tools provide only flexibility, which results in shallow deployments and impedes information independence.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access