BACKGROUND: First Union National Bank implemented an Informix-based data warehouse and data mart to support an enterprise-wide initiative in customer-centered marketing. First Union Corporation is a leading provider of financial services to more than 16 million retail and corporate customers nationwide, with the majority of its customer base located on the East Coast. It is the nation's sixth largest bank holding company with assets of $223 billion as of March 31, 1999.

PLATFORMS: First Union uses the Informix monitoring tool I-Spy in its SIGMA data mart. SIGMA, which is five terabytes, runs on a multinodal parallel processing IBM SP2, UNIX AIX, Informix Dynamic Server with the Extended Parallel Option and the Advanced Decision Support Option.

PROBLEM SOLVED: First Union was searching for a strong monitoring tool to determine who was using its SIGMA data mart. The company wanted a tool that could pinpoint the users accessing SIGMA, the number of queries run, the number of tables accessed, the average query execution time and the length of time a query remained in the system.

PRODUCT FUNCTIONALITY: First Union has been using Informix I-Spy in production for over a month with minimal problems. Informix I-Spy has met all of First Union's needs and significantly increased the company's data warehouse monitoring capabilities. More than 100 First Union analysts use the SIGMA data mart, accessing the information with two DSS tools and SAS. These tools access more than 250 aggregate and fact tables. First Union's goal is to find out how many of the 250 aggregates and/or fact tables are actually used.

STRENGTHS: Informix I-Spy's Web-based mechanism is a core strength of this product. This mechanism allows First Union to obtain simplified, easy-to-understand information on data mart (SIGMA) usage. In addition, First Union can customize its reports to meet its particular needs. For example, First Union customized a report detailing, in graph-form, how the database is utilized on a given day. Informix's I-Spy reporting mechanism has given First Union, especially analysts and upper management, a more complete and accurate picture of data mart utilization than was previously possible. An additional strength is Informix I-Spy's rule-based system. Using Informix I- Spy's increased control over client connections, such as PDQPRIORITY, First Union's rule system is now much more efficient. First Union has configured Informix I-Spy to automatically assign PDQPRIORITY based on the running application. For example, Informix I-Spy will automatically set PDQPRIORITY 35 for a SAS query and PDQPRIORITY 5 for a DSS query running against pre- aggregated tables.

WEAKNESSES: Informix I-Spy does not provide a reporting mechanism to create bills based on resource usage of a particular database by user or group of users. In addition, Informix I-Spy is unable to perform query "scheduling" (i.e., allowing specific queries to be run at a later date/time).

SELECTION CRITERIA: First Union compared a variety of different tools on the market to find the best one to meet its needs. Informix I-Spy's major advantage over other products in the marketplace is its rule system and Web-based reporting mechanism which simplifies and customizes complex data warehouse information.

DELIVERABLES: Informix I-Spy has already provided First Union with 20 different Web-based reports to review. Some of the most impressive reports are "number of queries run by application" and "number of queries run by user by application." These two reports give First Union the ability to find out what tool the analysts are using most frequently and which analysts are most frequently using Sigma. First Union also has the following reports: longest running queries in SIGMA, average query execution time, users with the most SQL errors and tables accessed by users. These reports quickly point out inefficient queries and the users who write them. We collect the "bad" queries, rewrite them and present them on our intranet Web site which helps all First Union analysts learn to write queries more efficiently.

VENDOR SUPPORT: Vendor support has been fantastic. Informix has responded quickly to any issues raised by First Union. All issues were resolved in a timely manner. The best part of working with Informix has been the great support and rapid response.

DOCUMENTATION: The documentation is fairly comprehensive and is easy to understand. Running the product with only the documentation would present no problems whatsoever.

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