Ad hoc BI is killing us!

For Chinese people, 2003 was the ''Year of the Black Sheep.'' For business intelligence (BI) professionals, 2003 was the year of ''reporting.'' It's uncanny that in 2003 all major BI tools vendors made serious moves to add high-end reporting functionality to their suites. Last summer, Business Objects and Hyperion acquired production reporting vendors (Crystal Decisions and Brio Software, respectively). Last June, Cognos launched ReportNet, a three-year skunkworks project that company officials now say is the firm's flagship product. Last November, MicroStrategy launched a reporting module, and Microsoft is beta testing a reporting service that it will bundle into the next version of SQL Server.

Click your heels three times ...
Why is there such a flurry of interest in an allegedly tired and unappealing technology like reporting? Haven't we heard these same BI vendors ridicule reporting technology as out of date and out of touch with users' analytical needs?

That's simple: a majority of users want to analyze data using pre-defined reports. By ''reports,'' I mean a mixture of static production reports and structured interactive reports, which are exception reports with pre-defined drill paths to detailed data (like a dashboard, scorecard or parameterized report).

Starting in the 1990s, most BI vendors sold ad hoc query or OLAP tools, which, it turns out, have only been adopted by power users. After more than a decade, these vendors now realize that selling reporting tools to business users is the big market for BI.

At the same time, many user organizations are realizing that they've overspent on the current generation of BI tools. Vendors, consultants and third-party solutions providers pushed them into ad hoc solutions when what they really needed were structured reporting environments. The majority of users find the ad hoc BI products too difficult to use, which is one reason there are so many BI tools gathering dust on company shelves. (According to The OLAP Survey 2 [see ], the average shelfware rate of OLAP tools is 39%. Other factors contributing to shelfware are the way products are sold and priced.)

The ultimate time-waster
None of this is new or insightful, really. For years, many industry experts have decried the mismatch between BI tools and user analytical needs. However, most experts have failed to comment on another pernicious problem with current BI tools. That is, for every several users who find a BI tool difficult to use, one person develops a destructive, co-dependent relationship with the tool. Simply put, many users are wasting a lot of time doing ad hoc queries when they should be doing their real jobs. Ad hoc query tools may now have replaced computer games as the biggest productivity sinkhole inside corporate cubicles.

''Ad hoc BI still kills us,'' said Ken Kirchner, a data warehousing manager at Werner Enterprises Inc. in Omaha, Neb. ''In a worst-case scenario, a senior director who knows Access and has an ODBC connection starts creating ad hoc queries. Before you know it, they've spent 17 hours doing their own analysis outside of our formal reporting and analysis groups.''

And Kirchner isn't alone. ''We don't want the majority of our users doing ad hoc queries,'' said David Norton, a senior VP of marketing at Harrah's Entertainment, a Las Vegas casino management firm. ''We know what information our business users need to do their jobs. We create a standard set of static reports and then spend time educating them to use those reports effectively.''

It would be easy to discount Harrah's ''BI blasphemy'' -- its use of static reports -- as an example of poor practices employed by a ''late-adopter'' company. However, Harrah's is not a technology laggard. In fact, it is revolutionizing the casino industry by leveraging data warehousing and BI technologies to foster high levels of customer loyalty, gain market share and boost revenue.

The pendulum swings back
Surprisingly, Harrah's is not alone here. According to a recent report from TDWI, users at companies with successful BI solutions are more likely to analyze data via static online reports (47%) than any other method. The next highest methods -- navigate interactive online reports (39%), view paper reports (37%) and create reports from predefined selection criteria (34%) -- all fall within the reporting domain.

Despite all the hoopla about giving users unfettered data access, we need to recognize that static reports still offer significant benefits. Leading BI adopters like Harrah's are sending a signal that it's time for the BI pendulum to swing from exploratory BI to more structured interactivity with data.

The historical perspective
Originally, organizations justified the deployment of data warehouses and BI tools to offload the IT department from spending too much time and resources creating ad hoc reports for users. The rationale was that in a self-service BI environment, users could create their own reports faster and would not be held hostage by the IT department and its backlog of requests.

The good news is that this scenario worked well; but it worked too well. Many IT departments have drastically reduced the time they spend creating ad hoc reports. Unfortunately, this has simply shifted the burden to end users, who spend time and energy creating ad hoc reports instead of doing their real jobs.

This situation undermines another key rationale for deploying data warehouses and BI tools -- standardizing data and achieving a ''single version of the truth'' throughout an organization.

When business users create their own reports, they apply custom rules to custom snapshots of data, often portraying their activity as favorably as possible. This results in ''spreadmarts'' -- spreadsheets or custom reports that contain unique views of the business that can't be easily reconciled with each other. This creates confusion, which is yet another reason to rein in ad hoc BI.

Practical steps
Attentive business managers now recognize that it's time to rethink their BI deployments for the good of the business. Werner Enterprises' Kirchner, for example, is replacing a commercial BI tool with a custom analytic app built using .NET. The new analytic apps will customize and simplify the process of accessing, analyzing and acting on data warehouse data for its various user constituencies.

Other firms, like the Scotts Company, are splitting the middle between IT- and user-developed reports. They assign ''super users'' to write reports for their colleagues and evangelize best practices for interpreting and applying BI data. These super users are technically savvy business users within a department or functional area who receive training and support from IT, but are responsible for creating reports in their areas and supporting the analytical needs of their colleagues (see ''Facts up! Teach your team to use BI,'' ADT , November 2003).

A time and a place for ad hoc
As organizations add structure to their BI environments, they shouldn't eliminate ad hoc BI completely. Ad hoc BI plays an important, though limited, role in BI deployments. The only users who should engage in ad hoc exploration of data using query or OLAP tools are those who have been hired to analyze data for a living. Typically, these are ''business analysts'' who know spreadsheets and corporate databases inside and out. According to recent studies, such ''power users'' comprise only 5% of all users.

Given this puny market for ad hoc BI, it's no wonder that all the BI vendors have finally discovered reporting. Reporting has never been exciting, but it's effective for disseminating and maintaining a standard set of information to a broad base of users in an organization.

Recognizing the shifting winds, BI vendors who for years have touted the superiority of OLAP and ad hoc queries  are now adding reporting capabilities before they get sucked up by the advancing reporting tornado. The current flurry of BI vendor activity is a good sign -- it shows that the ultimate convergence of reporting and analysis to create both static and interactive exception-based reports is just around the corner.

About the Author

Wayne W. Eckerson is director of education and research for The Data Warehousing Institute, where he oversees TDWI's educational curriculum, member publications, and various research and consulting services. He has published and spoken extensively on data warehousing and business intelligence subjects since 1994.