Improving Query Performance for Data WarehousesImproving Query Performance for Data Warehouses

VentanaView™

During the last quarter of 2004, Ventana Research conducted a study that surveyed 225 IT and line-of-business professionals about assuring and improving data warehouse/mart performance. We assessed satisfaction, intentions, issues, difficulties, and budgets related to assuring and improving query performance of data warehouses and data marts. We have found that organizations continually suffer from cultural, agenda, and understanding gaps between IT and business. While divergent agendas are sometimes hard to overcome, resolving misunderstandings can lead to improved relations. Establishing the value of a given level of performance is essential to overcoming misunderstandings. Performance value can only be done by relating query performance to business performance via translational frameworks. Ventana Research found that these frameworks relate reduced costs and improved revenues to improved business operations and decision making.

The report assessed the following issues and findings in the research:

Ad Hoc Query Performance Satisfaction
The majority (55%) of the respondents said their users were not satisfied or only somewhat satisfied with ad hoc query performance. 84% said their users would significantly benefit from a 10x improvement in query response times. However, 60% of the respondents could not establish a monetary value for a proposed 10x improvement in query performance. 20% of the respondents said it was worth $20k or less. Bottom line: While better ad hoc query performance improvement was clearly beneficial and desirable, IT practitioners had no understanding of the value of improved performance.

Resolution Intentions for Ad Hoc Query Performance
Despite the low level of ad hoc query satisfaction, only 31% of the respondents expected to buy more hardware within the next 12 to 18 months to improve data warehouse/data mart performance. Bottom line: IT realizes performance is an issue and is willing to spend money on improving performance. Yet, no actual dollar value has been established

Organizations were more likely to spend on hardware (31%) or software (18%) than on performance tuning training (17%), tuning consultants (12%), or hiring DBAs with tuning skills (8%). Bottom line: IT would rather solve the problem with infrastructure than with personnel.

Satisfaction with Dimensional Analysis Capabilities
71% of the respondents said their users would benefit from having significantly more OLAP dimensions or relational database query filters. However, when queried for a monetary value for this capability, study results showed 52% of the respondents did not know the value. 30% of the respondents said this capability was worth $100k or less. Bottom line: While IT clearly sees value in having more data, particularly more dimensional data, it can’t quantify the value.

Issues with Batch Report Generation Performance
45% of respondents stated their batch reporting windows would be exceeded within the next 12 months. Batch windows of 2-4 hours were most often cited by respondents (40%). Yet, as with ad hoc query performance, resolution to the problem was most often solved with additional hardware purchases (32% of respondents). Bottom line: Batch reporting windows are a chronic problem, most often handled by adding in more hardware.

Difficulty of Various Performance Assurance Tactics
Of an array of performance assurance solutions, including server upgrades, various tuning procedures, restriction of access, and creation of data marts or OLAP cubes, upgrading servers was cited as most difficult. Bottom line: Adding server hardware, while a popular performance band-aid, is a burdensome and difficult problem.

Data Warehouse Performance Assurance Personnel Budgets
Budgets for DW personnel varied significantly. 25% indicated their budgets were between 0 and $250,000, 16% indicated they were between $250,000 and $500,000, 8% indicated they were between $500,000 and $1,000,000, and 10% indicated they were between $1,000,000 and $2,000,000. 34% did not know their budget for DW personnel. Bottom line: While varying significantly, budgets for personnel are a significant cost for organizations. Costs for personnel should be factored into strategies for upgrading the performance of data warehouses. Trade-offs between additional infrastructure and personnel should be made after both costs are assessed.

Assessment
A significant contributor to the gap in understanding between IT and line-of-business (LOB) is the value placed on data warehouse query performance. Not knowing this value causes IT and LOB organizations to make seat-of-the-pants estimations of performance enhancement needs and costs. This approach is sub-optimal, as unmet expectations may occur no matter what level of expenditure IT makes to improve performance.

Part of the difficulty in estimating the value of performance is a lack of understanding as to what the performance actually is. Ventana Research recommends that organizations use performance-monitoring tools to establish baseline performance levels. These levels can then be related to user satisfaction. This then becomes the basis for planning for future performance levels.

Additionally, IT and LOB should work together to establish the value of improved query performance. The value of query performance should be related to speed of users’ analytic tasks. Analytic task speed should be related to opportunity costs. These can include:

Ventana Research recommends that organizations work to clearly identify the value of improved performance. If no clear value for improved performance can be identified, then none may exist, regardless of intuition to the contrary.

Eric Rogge is VP and research director for Business Intelligence & Performance Management at Ventana Research, a research and advisory services firm. Write to him at [email protected].