Five Steps to Optimizing BI and Data Warehouse Performance

In recent years, business intelligence (BI) and data warehouse systems have evolved from their beginnings as batch-oriented systems for basic reporting, querying and analysis used by small, well-defined user communities. Today, many are enterprise systems that handle many kinds of queries and must be continuously available for diverse user communities.

Organizations in nearly all industries now view data warehouses as integral to financial reporting, customer relationship management, marketing and other functions. An increasing number see the ability to use data for BI, performance management and other forms of analysis as a competitive differentiator; they want users in nearly all business operations to have access to broad-based, timely, accurate data to use for both trend analysis and daily operational decision-making. This demand puts tremendous pressure on BI and data warehouse systems to perform well.

As requirements change, traditional approaches to managing and optimizing BI and data warehouse performance are becoming outmoded. Having been put together piece by piece, most BI and data warehouse systems are managed through tools designed to work only with a single database, storage system, operating system, server or BI system. This "silo" arrangement has made it difficult for IT managers to see performance from the user's perspective; individual components appear to be working fine, yet users report inadequate performance. Workload analysis and tuning tools can help IT managers to capture information about user activity, data flows and query performance, and to analyze the information for ways to improve BI and data warehouse performance. However, most organizations are just beginning to identify and access information sources for workload analysis and to deploy tools specifically intended for this purpose.

In light of these changes, Ventana Research recently conducted benchmark research to examine the tools used to identify queries that perform poorly or that put burdens on the system disproportionate to their business importance. We also sought to better understand how organizations are addressing performance problems through tuning and optimization of current systems as well as through deployment of new technologies, such as data warehouse appliances, specialized databases and query accelerators. Finally, we sought to discover organizations' plans for future deployments to address business information demands.

This article presents an executive summary of the resulting report, "Optimizing BI and Data Warehouse Performance," which examines the progress organizations are making toward gaining a more complete view of BI and data warehouse performance. As described in greater detail in the full, 63-page report, organizations face big challenges as they attempt to gain fuller analysis of data about customers, products and other key areas. In particular, firms have difficulty scaling warehouses as they attempt to analyze more data and handle ever more complex queries. What's more, performance lags even as they are faced with demands for lower data latency and continuous, seven-day-a-week, 24-hours-per-day availability. Presented below are the key findings of Ventana's recent research along with a five-step approach that will help you:

1. Identify key business drivers that should direct performance improvement efforts.
2. Improve information assets for analyzing and tuning performance.
3. Use performance demand to guide deployment of appliances, specialized databases and query accelerators.
4. Reduce the time it takes to remedy unsatisfactory performance and implement information change requests.
5. Assess your organization’s maturity and invest for improvement.

Maturity Matters

Ventana Research designed its survey to be answered by IT professionals and consultants who support BI and data warehouses, by business professionals who are concerned about the performance of these systems across finance and operations, and by business analysts who support these deployments. Of 1,066 participants who clicked through to the study, Ventana deemed 240 to be qualified participants. Roughly three quarters of respondents represent companies headquartered in North America, and there is a bias toward larger organizations: 41% of respondent firms have 10,000 or more employees, 33% have 1,000 to 9,999 employees, 18% have 100 to 999, and 8% have fewer than 100 employees.

The research shows that organizations are somewhat mature in their optimization of BI and data warehouse performance. Two-thirds (67%) of the organizations participating in the research were at the two middle levels of Ventana's four-tier Maturity Model; 29 percent were at the second-highest level, Strategic, and 38 percent at the third-highest level, Advanced (see "Organizational Maturity: Overall" chart above). Ventana also found symmetry at the extremes: Nearly the same percentages were at the lowest Tactical level (16%) and the highest Innovative level (17%).

Organizations at the Strategic level react to unsatisfactory performance within hours, which is somewhat faster than reported a year ago. In comparison, Advanced organizations respond within days and Tactical organizations within weeks; moreover, these less-mature organizations are not getting faster in their responses. Most organizations have not reached the Innovative level, where organizations have shortened the response rate over the previous year, in some cases to within minutes.

The quality and breadth of information available about BI and data warehouse performance is a key factor in whether organizations can increase their response rates and the overall maturity of their approach to optimization. Less than 10 percent said their information sources are excellent; the percentage describing them as good (38%) was only slightly larger than that describing them as fair (37%), and 15 percent said their sources are poor. Almost half (46%) indicated that their organizations have concerns about gathering performance data because the effort itself may degrade performance. Thus, some organizations need to develop less obtrusive processes for gathering information.

At the majority of organizations participating in the research, IT and business users collaborate in some fashion to exchange information about performance and take steps to improve it. More than half (56%) of all participants listed meetings with business users as an information source for analyzing and remedying unsatisfactory performance. Slightly more (58%) said that IT and business users collaborate to prioritize queries, reports and users; 55 percent said they collaborate to analyze data about performance, and 52 percent said their IT function works one-on-one with users to improve queries. While we regard this relatively high level of collaboration as an encouraging sign, organizations need to ensure that the information shared during these collaborations is of high quality. If it is not, then even in collaboration, IT and business users will not be able to analyze trends and develop repeatable steps for continuously improving performance.

Innovative organizations are, among other things, proactive: They are able to monitor workloads and queries and thus know when to deploy personnel and technology tools to address performance optimization issues. But the survey revealed that fewer than one-quarter (22%) of research participants' organizations are proactive. A larger 34 percent described their organizations as active, which means that they can monitor and tune some systems but have limited insight into future needs. The same percentage (34%) identified their organizations as reactive, and 10 percent said they are very reactive. The research finds some correlation between how active an organization is and whether it has deployed a BI or data warehouse appliance. The majority (60%) of organizations that have already deployed an appliance take an active or proactive approach to managing and tuning performance. Among organizations that are planning to deploy one within a year, 38 percent are proactive and 40 percent are active.

Reliance on the Appliance

Given that data warehouse appliances are still relatively new to the market, the rate of deployment among participants is significant. Overall, 19 percent of organizations have deployed a BI or data warehouse appliance, and the same percentage plan to within one year. Very large organizations with more than 10,000 employees have a slightly higher rate of deployment (23%). However, compared with other sizes of participating groups, small organizations of less than 100 employees most often plan to deploy an appliance within one year (32%). As for specialized databases and query accelerators, the research finds that fewer organizations have deployed them than have deployed appliances (10%, with 13% planning to within one year). This level of deployment is expected, as several column-oriented databases are just coming into the market and query accelerators are more specialized than mainstream technologies and, therefore, less well known.

The study reveals that organizations in need of improved performance for analytics show the strongest demand for appliances, specialized databases and query accelerators. Four of the top five business functions or information processes for which participants said they have already deployed or plan to deploy these systems were business planning and forecasting (46%), executive management reporting and analysis (44%), customer data analysis (43%) and operational reporting and analysis (43%). The finance function, tied for the second-highest percentage (44%) was the fifth; this function increasingly relies on BI analysis, including for business planning and forecasting. Interestingly, the study revealed that participants are dissatisfied with the performance of business planning and forecasting tools compared with enterprise BI and reporting tools, online analytical processing (OLAP) tools or servers, or other BI and analytic technologies. Thus, while analytic performance in general is the biggest driver for organizations' deployment of appliances, specialized databases and query accelerators, business planning and forecasting in the finance function is a specific area of demand.

Demand for Performance

When organizations attempt to scale their BI and data warehouse systems to meet increased demand, performance can suffer. The study finds that the number-one cause of unsatisfactory performance is the inability of systems to handle higher volumes of complex queries. Often necessary for customer data analysis, OLAP and other BI processes, these kinds of queries have multiple clauses and operations, and they group or join data from different tables, rows, columns or databases. More participants (58%) indicated complex queries as a cause of performance difficulty than other factors suggested, including scaling for more data (55%), more real-time data needs (54%) and more data loading (48%). Thus, Ventana concludes that organizations are under pressure from users to deliver analytical results faster. In an earlier, batch-oriented era, users placed less pressure on BI and data warehouse systems to return the results of complex queries quickly; now, however, to compete, organizations must be able to respond to more aggressive querying demands, including for real-time analytics. Therefore, the ability to deliver quality information and analysis of performance data will be critical to scaling up successfully; of participants who said that more complex queries were a cause of unsatisfactory performance, 45 percent regard their information sources for optimizing performance as only fair, and 18 percent said they are poor.

Organizations are least satisfied with the performance of their BI and data warehouse systems when used to address information demands resulting from changing plans and forecasts (20% are somewhat dissatisfied and 4% very dissatisfied). They are most satisfied with their performance in meeting the information demands associated with regulatory compliance (15% very satisfied and 42% somewhat satisfied) and business growth (13% very satisfied and 55% somewhat satisfied).

The bottom line, though, is that the user demand having the most impact on performance remains one of the most common requirements for BI and data warehouse systems: providing executives with access to data. Nearly half (45%) rated this as their number-one challenge. More than one-third (36%) rated operational data analysis as their top challenge, and the same percentage said it is spreadsheet-based data analysis. We believe that these results underscore the need to improve the performance of analytics, not only for executives but also for operational managers.

Providing better operational data analysis performance is critical to achieving the top business benefits that participants told us their organizations seek from BI and data warehouse deployments: optimizing business processes (81%) and identifying opportunities for improvement (78%). These high percentages show that many organizations are pursuing ambitious goals to establish competitive advantages. However, meeting these goals requires the ability to access and analyze more data faster and more effectively for a broader range of users than the designers of traditional systems contemplated.

Analytics As Catalyst

Ventana Research recommends that organizations let business drivers and benefits direct performance improvement efforts. Knowledge of the purpose of BI and data warehouse systems will help set priorities for tuning current systems and augmenting them with new technologies. Make it a goal to improve information about the performance of BI and data warehouse systems, especially as seen from the users' perspective. It's important for IT to meet with business users to understand performance needs and collaborate on how to remedy problems, but you must also supplement these meetings with data analysis. Only then can you develop independent analysis and metrics to measure IT's response to unsatisfactory performance, anticipate when demand will be at its highest and guide new technology deployments toward meeting strategic business objectives.

The survey revealed that organizations acknowledge that their information sources for optimizing performance fall short of excellence. Ventana believes that by improving sources and analysis, organizations can become more mature in their optimization of BI and data warehouse systems. More mature organizations are less reactive and more proactive; they can identify the most important queries and users and anticipate when performance demand will be greatest. Mature organizations can use their knowledge to determine where and when to deploy new technologies, including appliances, specialized databases and query accelerators.

Organizations are having the greatest difficulty meeting performance demand for analytics. The research also indicates that firms deploy appliances, specialized databases and query accelerators most often to optimize the performance of analytics. The current rates of deployment of these systems are significant given that many products are relatively new to the market. However, the majority of organizations continue to take traditional steps to improve performance in response to rising demand, including deploying more hardware and expanding the size of existing database systems.

Budgetary pressures can constrain how effectively an organization can respond to unsatisfactory performance; nearly two-thirds (65%) of research participants' organizations spend 25 percent or less of their IT budget on tuning systems and deploying technology to optimize BI and data warehouse performance. These pressures increase the importance of having information-based insight into how to optimize the performance of current systems and where to deploy new technology so it can have the most benefit for the business.

Five Steps Toward Success

Optimizing BI and data warehouse performance is vital to meeting business objectives. Based on the results of this research, and on knowledge of best practices involving people, processes, information and technology, Ventana Research recommends the five following steps toward BI and data warehouse performance improvement:

1. Let business drivers and benefits direct performance improvement efforts. Before taking steps to improve BI and data warehouse performance, make sure you understand the purpose of these systems, the objectives they serve and the benefits that your organization expects to derive from optimization. This knowledge will help you set priorities for tuning current systems and augmenting them with new systems. The study reveals that organizations are deploying BI and data warehouse systems primarily to optimize business processes (81%), identify opportunities for improvement (78%) and manage performance (68%). Reducing business costs and improving financial results are the next-most common objectives. Participants indicated that they are most satisfied with the performance of their BI and data warehouse systems for addressing the information demands presented by regulatory compliance (15%).

While the importance of regulatory compliance cannot be disputed, it's evident that organizations need to ensure that they focus performance improvements, including deployment of new technology, on achieving the business benefits they desire most. Apply your knowledge of the intended benefits and objectives to build the business case for investment in optimizing BI and data warehouse performance.

2. Improve information assets for analyzing and tuning performance. Determine early on what information sources your organization uses to understand performance. In most organizations, the sources are diverse and include both people and systems. Typically, it's not simple to access and integrate these sources. Overall, 38 percent rated their information sources good, while 37 percent said they were just fair. The largest percentage of research participants (56%) use meetings with business users as an information source for remedying unsatisfactory performance, but be careful not to depend entirely on meetings with users. Rather, supplement meetings with as many high-quality data sources as needed to support analysis of workloads and usage trends across the organization.

The study reveals that the most common source of systems data is DBMS utility statistics (47%), followed by usage monitoring systems (43%). Some organizations are concerned that the act of monitoring or gathering data to analyze BI and data warehouse usage could itself have a negative impact on performance (46% indicated such concern, including 73% of executive participants). Be aware of these concerns as you set strategy for gathering data. Accessing certain sources, such as log files and network traffic statistics, may have less of an impact on performance. Use better information sources not only to improve tuning of current systems but also to assess what new systems or tools are needed and where to deploy them.

3. Use performance demand to guide deployment of appliances, specialized databases and query accelerators. Determine your strategy for deploying appliances, specialized databases and query accelerators based on analysis of performance demand. A growing trend is use of BI and data warehouse appliances, which offer preconfigured combinations of software, hardware and storage systems. These tightly packaged systems have the potential to relieve IT of the extended configuration procedures typically involved in assembling the "stack" of technology required for BI and data warehousing. Nearly one-fifth (19%) of participants have deployed such an appliance, and 19 percent more plan to do so within one year. Specialized databases (including column-oriented systems that store data by column rather than by row) and query accelerators can address specific kinds of performance challenges. For example, the study found that the highest percentages of organizations currently using or planning to deploy these three technologies use or will use them for business planning and forecasting (46%), finance (44%) and executive management reporting and analysis (44%).

You are more likely to succeed with these technologies if you apply knowledge of workload and other measures of performance to determine where they can have the most impact. Ventana Research recommends that you improve access to and analysis of performance data, particularly about queries and workloads associated with high-priority processes or intended business benefits. Then apply this insight to the evaluation and deployment of appliances, specialized databases and query accelerators.

4. Reduce the time it takes to remedy unsatisfactory performance and implement information change requests. Make it a goal for your organization to improve your users' experience by addressing problems and implementing information change requests rapidly. The largest percentage (44%) of participants said it takes their organization about the same amount of time to remedy unsatisfactory performance as it did 12 months ago, and 5 percent said they are getting slower at doing this. Organizations at the two highest levels of maturity are reducing their response time; those at the Strategic level are faster (38%) and those at the top Innovative level are much faster (13%) than a year ago.

The majority of organizations are able to respond to unsatisfactory performance within hours (38%), but for 26 percent it takes days and for 15 percent weeks. In addition, most organizations (39%) take one to five days to implement information change requests, and 37 percent take six to 30 days. Slow implementation of these requests is not just a performance problem; it can become a data quality problem when users share wrong or outdated information through reports and applications.

A critical factor in improving response time is to know when performance demands will be at their highest so you can plan resources accordingly. Nearly three-quarters (73%) of participants said it is important to identify periods of peak performance demand; 59 percent said that peak periods come at the end of the month, and 42 percent said they occur at least weekly. Use information-based insight to anticipate spikes in performance so you can both address problems rapidly and analyze trends to guide deployment of new technology resources.

5. Assess your organization's maturity and invest for improvement. Ventana Research measures organizational maturity using a four-level scale. In this benchmark research, most participating organizations rank at the middle levels, with 38 percent at the third-highest Strategic level and 29 percent one step down at the Advanced level. Advanced organizations are reactive in their approach to remedying performance and are less confident than those at higher levels in the quality of their information sources for performance optimization (37% said they are fair). Strategic organizations are active in their approach to managing and tuning BI and data warehouse systems; they can monitor and tune some systems but have more limited insight into future needs than organizations at the Innovative level (17%).

Strategic and Innovative organizations are taking the most steps to collaborate with business users to analyze performance and determine what actions to take. At the Strategic level we find the largest percentage of organizations that have deployed BI or data warehouse appliances (38%). Innovative organizations have the most information sources for performance optimization, are the most confident in their data quality, and are leveraging them with workload analysis and tuning tools to gain insight. Use these results to assess your own maturity and to determine where you can apply improvements in terms of your people, processes, information and technology.

David Stodder is vice president and research director - information management and IT performance management at Ventana Research. He was previously editor-in-chief and editorial director of Intelligent Enterprise.

The complete "Optimizing BI and Data Warehouse Performance" report, which is available for purchase from Ventana Research, offers extensive detail on its benchmark research, including 15 charts and graphs, on the demographics and people, process, information and technology maturity of survey participants.