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

RESOURCE PORTALS
View all Portals

WEB SEMINARS
Scheduled Events

RESEARCH VAULT
White Paper Library
Research Papers

CAREERZONE
View Job Listings
Post a job

Advertisement

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

GENERAL RESOURCES
Bookstore
Buyer's Guide
Glossary
Industry Events Calendar
Monthly Product Guides
Software Demo Lab
Vendor Listings

DM REVIEW
About Us
Press Releases
Awards
Advertising/Media Kit
Reprints
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

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

online columnist Jonathan Wu     Column published in DMReview.com
December 22, 2000
 
  By Jonathan Wu

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.

...............................................................................

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

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

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!

Data Quality Tools, Affordable and Accurate
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.

Design Databases with ER/Studio: Free Trial
ER/Studio delivers next-generation data modeling. Multiple, distinct physical models based on a single logical model give you the tools you need to manage complex database environments and critical metadata in an intuitive user interface.

Email Regulatory Compliance
E-Trail Digital Archive is a feature rich, turnkey Electronic Communications Retention, Retrieval and Supervisory system.

Free EII Buyer's Guide
Understand EII - Trends. Tech. Apps. Calculate ROI. Download Now.

Click here to advertise in this space


E-mail This Column E-Mail This Column
Printer Friendly Version Printer-Friendly Version
Related Content Related Content
Request Reprints Request Reprints
Advertisement
advertisement
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.