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.

Server

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.

Database

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.

Network

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.

Summary

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.

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