Related Links

Web Seminars

CATEGORY: Data Warehouse Design, Administration & Management

REVIEWERS: Jack Swearingen and Wayne Bellefeuille, data warehouse architects with Talent Software Services, and William Standke, director of information management for Medica.

BACKGROUND: Medica is a health plan organization serving the upper Midwest. Medica offers health plans with many unique features including easy access to healthcare, choice in selecting providers and innovative preventive care programs. Since 1987, Talent Software Services, Inc. has been a leader in information technology consulting in the Minneapolis/St. Paul area. Talent specializes in data management, providing expertise in various technologies, including data warehousing, database administration, data architecture and performance tuning.

PLATFORMS: Medica's data warehouse uses Oracle8.1.7 on an AIX UNIX server and is approximately 300GB in size. Users access the warehouse with a number of products, including SAS, Cognos and Siebel Analytics.

PROBLEM SOLVED: Medica wanted to proactively track user queries, improve overall performance and troubleshoot problems. Ambeo Usage Tracker's query capture and parsing functionality can determine who is accessing the warehouse and what applications they are using; when the warehouse is most used and if it is used over the weekend; the impact of adding a new index based on query history; which tables/columns would benefit from partitioning; common characteristics of long-running queries and if data mart possibilities exist; what happened the last time the user ran this same query that "used to work"; and what users/departments are impacted by a data-quality issue found in a column/table. These answers (and more) have provided significant returns on investment through reduction in design time, more focused resource utilization and extended life of existing hardware.

PRODUCT FUNCTIONALITY: Usage Tracker unobtrusively captures SQL text as it is passed to the database and tracks session and SQL statistics (e.g., user, rows processed, elapsed seconds). It also parses the SQL statement by table, column and clause (e.g., SELECT, WHERE, GROUP BY) and stores the data in tables (e.g., Oracle) for query by administrators. Further, Medica added an Oracle PL/SQL process that performs an EXPLAIN PLAN on all captured queries. This explains how Oracle processes a query at execution time and provides answers to questions such as: what columns are in the WHERE clause for queries that are doing a FULL SCAN on table X and what queries have column X in the WHERE clause, but are not using its index. With two years of captured data, Medica now plans to take advantage of a powerful Usage Tracker feature that identifies dormant columns/tables. This will help purge data and save disk space.

STRENGTHS: The main strength of Usage Tracker is its ability to parse the SQL it captures and store the data in relational tables. Administrators can then query this to analyze and manage the data warehouse.

WEAKNESSES: Architecturally, the Usage Tracker listener "sits" between the user and the database listener. This introduces a learning curve for DBAs in troubleshooting user disconnects, database server shutdowns and listener shutdowns.

SELECTION CRITERIA: Very few products compete with Usage Tracker directly. We selected Usage Tracker because usage data is stored in an open (non- proprietary) relational database; many standard reports come with the product, but we could easily add more; and the SQL is parsed into a low level of granularity allowing for detailed queries against the usage data.

DELIVERABLES: The main deliverable that Usage Tracker gives Medica is the usage data itself. The product also comes with a client interface that produces a number of parameter-driven standard reports.

VENDOR SUPPORT: Ambeo technical support has been very responsive and extremely knowledgeable on their product and the environments where Ambeo has been installed (e.g., UNIX, Oracle). In testing the product, we found some problems in their parsing algorithm (when using subqueries) and received a fix for the product in a short period of time.

DOCUMENTATION: The technical documentation provided is sufficient and easy to understand. The section that lists many of the standard reports along with the SQL used to generate the standard reports was especially useful. This allowed us to do ad hoc queries against the usage data using the same join and selection strategies that Usage Tracker uses for its own reports.

Jack Swearingen is a data warehouse architect with Talent Software Services.

Wayne Bellefeuille is a data warehouse architect with Talent Software Services.

William Standke is the director of information management for Medica.