Portals eNewsletters Web Seminars dataWarehouse.com DM Review Magazine
DM Review | Covering Business Intelligence, Integration & Analytics
   Covering Business Intelligence, Integration & Analytics Advanced Search

View all Portals

Scheduled Events

White Paper Library
Research Papers

View Job Listings
Post a job


DM Review Home
Current Magazine Issue
Magazine Archives
Online Columnists
Ask the Experts
Industry News
Search DM Review

Buyer's Guide
Industry Events Calendar
Monthly Product Guides
Software Demo Lab
Vendor Listings

About Us
Press Releases
Advertising/Media Kit
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

Business Intelligence:
Components of an Optimal BI/Ad Hoc Query Environment

  Article published in DM Direct Newsletter
December 22, 2000 Issue
  By Jonathan Wu

Editor's Note: Jonathan Wu is one of DMReview.com's regular online columnists. We are proud to publish his December installment in this issue of DM Direct. To read Wu's previous columns, please visit our Online Columnist feature at www.dmreview.com/onlinecolumnis ts/.

We live in an age where expectations about technology are high. With the Internet, information is readily available. If a Web site is slow to load, we move on to another one; with so many sites to choose from, there is no need to wait for the poor performers. This impatient attitude also applies to accessing information within an organization. Individuals searching for information have a variety of sources to choose from, and they will pick the sources that can provide reliable, quick results. Generally, business intelligence (BI)/ad hoc query applications can provide users with information on demand. However, when a BI/ad hoc query application's performance deteriorates, users grow impatient and seek other means to access the information they need. In this month's column, I will discuss several components that can affect the BI/ad hoc query application's performance.

Two primary technical architectures currently exist for BI/ad hoc query applications: full client (non-Web BI applications) and thin client ( Web- enabled BI applications). (Please refer to Differences between Web and Non-Web Applications, March 2000.)

Full Client or Non-Web BI/Ad Hoc Query Architecture

In this architecture, four main components can impact performance: client workstations, servers, databases and networks.

Client Workstation

This hardware component provides users with the ability to execute the BI/ad hoc query application. Three main factors impact the reporting performance of a client workstation:

  • Random access memory (RAM) - Inside every computer is a component called random access memory (RAM) which is usually referred to simply as memory. RAM is a determining factor of a computer's speed and power. For a computer to operate, it must read and follow a set of programming instructions. If a computer were to read instructions directly from its hard drive, every activity (from typing to opening a window) would be painfully slow. With RAM, computers read instructions once from the hard drive and temporarily place them into memory. The hard drive is available for other activities and the computer is faster.

    Every program loaded onto a computer also loads more information into RAM. The more RAM a computer has, the more programs it can run. What happens when a computer runs out of memory? Most systems are smart enough to juggle one set of instructions with another, but performance will noticeably suffer. In some cases, the computer system will drop everything and crash.

  • Speed and type of processor - The central processing unit (CPU) interprets and executes program instructions. The CPU microprocessor contains:
    1. An arithmetic/logic unit that performs calculations, performs comparisons and makes logical decisions.
    2. Registers that temporarily store information.
    3. A control unit that interprets and performs instructions.

    The faster the processor, the greater the number of instructions the CPU can process and the faster the performance of the software applications.

  • Hard drive - The hard drive is the internal storage device that contains the user's software applications. The greater the capacity of the hard drive, the more information it can store.

    When users execute the BI /ad hoc query application, the result set of their query is returned to the client workstation. A sufficient amount of hard disk space is necessary to store the result set of an ad hoc query. Sometimes businesses must upgrade hard drives to handle the vast amount of information that the users store.


The server hosts the database that users need for analysis and reporting. The main factors impacting the reporting performance of a server are:

  • RAM - Please refer to previous discussion on RAM in the client workstation.
  • Speed and number of processors - Please refer to previous discussion on speed and number of processors in the client workstation. The more processors a server has, the greater the number of calculations it can perform.
  • Number of concurrent users - Concurrent users refers to users executing instructions on the server at the same time. To properly configure the server to support concurrent users, anticipated usage must be determined and the server must be sized. If the number of current users exceeds the anticipated usage, server performance deteriorates significantly.


The three main components impacting the database performance are:

  • SQL statements - The complexity and number of joins with a SQL statement can impact date extraction. A complex join such as "purchase.vendor_name != 'BASE Consulting Group, Inc.'" will disable an index on the vendor_name attribute, causing the system to read the entire contents of the vendor_name attribute within the purchase entity. The number of joins impacts the performance of a query. It is faster to read from one entity than it is to read from several.
  • Indexes - Table indexing can directly impact data retrieval. Indexes are the defined sort order and access path to attribute values within an entity. If an entity does not have an index, the system has to read every attribute value within the entity. It is faster to read the data that has been sorted by an index than it is to read all of the data in an entity.
  • Schema - Entity relationships within a database can significantly impact query performance. The greater the complexity of the schema, the slower the query performance. The complicated joins within the retrieving SQL statement and the increased number of required passes of the database impede the reporting process.


Networks connect client workstations to the server and other devices. The components that impact network performance include bandwidth, hardware devices and configuration. If any performance problems are attributed to the network, the network administrator must be notified.

Thin Client or Web-Enabled BI/Ad Hoc Query Architecture

In this architecture, as in the full client BI /ad hoc query, four main components can impact performance. The components are identical to those of the full client architecture, except that the client workstation is replaced by the Web/application server.


An optimal reporting environment is one in which users are provided with the information that they need in an expedient manner. Incorrect data excluded, nothing frustrates a user of a BI/ad hoc query environment more than waiting for results to be processed by the application. Understanding the components that can impact performance of the BI/ad hoc query environment will help you address potential performance problems before they arise.


For more information on related topics visit the following related portals...
Query & Reporting and Business Intelligence (BI).

Jonathan Wu is a senior principal with Knightsbridge Solutions. He has extensive experience designing, developing and implementing information solutions for reporting, analysis and decision-making purposes. Serving Fortune 500 organizations, Knightsbridge delivers actionable and measurable business results that inform decision making, optimize IT efficiency and improve business performance. Focusing exclusively on the information management disciplines of data warehousing, data integration, information quality and business intelligence, Knightsbridge delivers practical solutions that reduce time, reduce cost and reduce risk. Wu may be reached at jwu@knightsbridge.com.

Solutions Marketplace
Provided by IndustryBrains

Best Practices in BI: Webcast featuring Gartner
View this free Webcast featuring Gartner and Information Builders and hear leading experts share their vision for the future of enterprise business intelligence, including how to maximize the success and ROI of BI applications through best practices.

Autotask: The IT Business Solution
Run your tech support, IT projects and more with our web-based business management. Optimizes resources and tracks billable project and service work. Get a demo via the web, then try it free with sample data. Click here for your FREE WHITE PAPER!

See Enterprise Business Intelligence in Action
See how business intelligence can be used to solve real business problems with this live demo from Information Builders

Increase Your Existing Data Center Capabilities
Robert Frances Group believes that the enterprise data center is undergoing significant and sustained transformation?and that Azul Systems has delivered on a new approach to delivering processing and memory resources to enterprise applications...

Verify Addresses Before You Ship or Mail.
Protect against fraud, waste and excess marketing costs by cleaning your customer database of inaccurate, incomplete or undeliverable addresses. Add on phone check, name parsing and geo-coding as needed. FREE trial of Data Quality dev tools here.

Click here to advertise in this space

E-mail This Article E-Mail This Article
Printer Friendly Version Printer-Friendly Version
Related Content Related Content
Request Reprints Request Reprints
Site Map Terms of Use Privacy Policy
SourceMedia (c) 2006 DM Review and SourceMedia, Inc. All rights reserved.
SourceMedia is an Investcorp company.
Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.