I recently attended a presentation by a business intelligence (BI) manager at a large insurance company that provides users direct access to a terabyte data warehouse but restricts query result sets to 25,000 rows. When asked the reason for the row set limitation, the manager — who until then had been the epitome of professional self-restraint and control — blurted out, “Because users are insane!”
It turns out that the company’s users weren’t adhering to proper protocol when submitting ad hoc queries. After years of downloading data sets into Excel or Access, many of the users found it hard to break the habit even after the firm provided a robust analytical environment with a scalable data warehouse. Many users employed the BI tool as a glorified ETL tool so they could analyze data in their favorite desktop applications.
Shortly thereafter, I attended a private briefing where another BI manager said their firm’s end users routinely use the BI tool to create and run parameterized reports consisting of 600,000 rows! Again, the users were dumping the report data into desktop productivity products to perform their “real” analysis.
Obviously, no matter how well a BI environment scales, users will find ways to “abuse” the system. This is not a case of myopic IT folks complaining about how good life would be without end users; rather the abuse is self-inflicted. Users are undermining their own productivity and wasting corporate resources by misusing the BI environment.
While BI scalability needs to improve across the board to prevent impatient users from trying to circumvent the system, it’s also clear that we must better educate users about how to optimize their queries and reports. First andforemost, users must understand the nature of their data. They need to know which tables are extremely large, how to leverage summary tables, and which queries or reports to run during off-peak times. In short, they need to be taught< (and constantly reminded) how to be good stewards of the BI resource.
The Big Brother Option?
Although education is the best medicine, there is a second way to manage the problem of runaway queries: impose limits. This usually backfires in the end. For example, some organizations—like the insurance company mentioned above—use query governors that prevent or restrict queries. However, query governors appear heavy-handed to end users and generate resentment. When asked what their users felt about the 25,000-row limit, the BI manager at the insurance firm said they were “not happy at all.”
A more subtle approach to controlling the BI environment is simply to restrict the users’ view of the environment at the outset. The theory goes that users won’t complain if they don’t know what they’re missing. Plus, you don’t overload them with too much data. Practically, this means that instead of giving users access to an enterprise data warehouse, you provide a narrower slice of the data using a data mart (physical or virtual) or static reports.
For example, one leading-edge BI implementer, Harrah’s Entertainment, only provides its casino managers with static reports because “we know what information our business users need to do their jobs,” said a top executive in a recent interview with TDWI. The executive said they would rather spend time educating users about how to use the reports than have managers waste time spinning and pivoting the data.
Balancing Freedom and Control
Of course, controlling access often disenfranchises knowledge workers. If users feel they need more data to perform their jobs properly, they will circumvent corporate policies and deploy shadow systems using their own resources. In the end, they’ll spend far too much time accessing and manipulating data, and create multiple versions of the truth. Here, the cure is worse than the disease.
The best strategy to ensure proper usage of the BI system is to provide continuous education. Unfortunately, most organizations only provide perfunctory training that shows users how to use the BI tool. Even well-meaning organizations often find it difficult to keep users up to date on changes in the data and techniques and recommendations for optimizing access.
One technique to continuously educate users is to apply a “passive” query governor. Rather than restrict or prevent queries, a passive governor educates users about the costs of the query and suggests alternatives. It gives users the freedom to make their own choices in the context of the greater good of the organization. In other words, it’s a real-time education tool rather than Big-Brother-enforced behavior modification.
Until the day when terabytes of data flow instantaneously through 64- and 128-bit-empowered analytical software, BI administrators will need to temper user enthusiasm for unfettered data access with education about how they can get the information they need while remaining a good BI citizen.
1 See Ad Hoc BI is Killing Us! TDWI Case Studies and Solutions, November, 2003.
This article appears courtesy of TDWI. To read more, please visit http://www.tdwi.org/index.aspx.