Business Intelligence Performance

Business Intelligence (BI) has become an indispensable tool for critical areas of your business such as customer relationship management, market analysis, and financial reporting. BI projects frequently have high visibility and the budgets to match.

One of the leading causes of failure in these projects is poor performance. When performance drops and response times grow longer, users quickly become disenchanted as their productivity is impacted. Since BI is a competitive differentiator for many companies, poor performance can impact the ability to compete when time to market is a critical success factor but the business can no longer react to fast-moving trends.

Identifying BI performance problems requires a holistic approach, since performance problems can arise from multiple sources. These include:

  • Scaling beyond the design limit. It’s a fact of life that BI systems will be called upon to handle more data and execute more complex queries by more users over time. This can frequently happen when companies try to add more lines of business into the system that weren’t originally included in the design. When this happens, performance can suffer if a sufficient capacity margin isn’t available. With Big Data sourced from the Internet becoming more popular, the size of the data set could grow to hundreds of Terabytes.
  • Using a Data Mart as a Data Warehouse. A Data Mart is typically designed around a dimensional star-schema model to facilitate analysis for one subject area, such as sales or inventory. Companies can get into trouble when they start to add more kinds of data, as the differences between summarized data in a data mark and the more complete information needed in a data warehouse result in design compromises that impact performance.
  • Unplanned Information Security Requirements. When security is added to an existing BI system, rather than designed-in from the start, the results are always less than optimal, and the new security can cause slow performance. This is frequently driven by compliance audits that result in mandatory new security features.
  • Real-time BI. New applications such as customer behavior analysis can help companies bring new revenue sources to market quickly and make changes to existing offerings to match fast-changing consumer trends. This kind of analysis requires real-time performance that your BI system may not be designed to deliver – you may find that your system for weekly reports is being asked to function more like an operational data warehouse.
  • Self-service BI. A growing trend is to empower BI users to define their own analysis and visualization, which can deliver business-critical results faster than involving skilled programmers. However, this inevitably leads to more users taking advantage of direct access to data and increased load on the system, which is frequently exacerbated by queries that are optimized more for ease of use than for performance.
  • Inefficiencies in the Extract-Transform-Load (ETL) process. The process could be occurring in a sub-optimal time window or users could be accessing the system during the ETL. The amount of data to be loaded could be excessive given the available time and processing power; in many cases the amount of data involved isn’t known until ETL begins. Other causes include indexing during the data load, performing integrity checking and transformations in the database instead of at the ETL layer, and lack of parallelism in the process.
  • Misunderstood usage patterns. There could be too many concurrent users at peak times, such as end-of-quarter, which may coincide with more complex queries being attempted.
  • Inefficient queries. Many times, the SQL queries used in the BI system will be developed with the business results in mind, but without regard to performance implications. There are many factors that affect SQL performance, such as the order of the table names in FROM or JOIN clauses, queries that result in full-table scans (because of missing WHERE clauses) causing a huge amount of unnecessary I/O, excessive sorting of large result sets, and many more.
  • Misuse of Indexes. SQL performance can be slowed by creating indexes on frequently updated columns, or by simply having too many indexes in one table
  • I/O Limitations. Most of the I/O load could be concentrated in a small number of storage devices that turn into performance bottlenecks.
  • Inefficient Table Design. The choice of data types in tables can have an impact on performance; e.g., using different data types for primary and foreign key columns results in less efficient join operations
  • Reduced IT Staff. With reduced budgets comes a reduction in the skilled staff that can tune the BI system for optimum performance.

 

GRT is ready to provide solutions to your BI performance problems. Areas we can help include:

  • Evaluation of your current BI performance state. This may include implementation of workload analysis tools that capture information about user activity, data flows and query performance.
  • Tuning of queries, database parameters, and caching to minimize response time.
  • Design of storage infrastructures that spread out the I/O load to avoid bottlenecks.
  • Use of techniques such partitions, aggregate tables, stored procedures, materialized views and indexing to achieve faster access.
  • Reorganization of the ETL process to better match the size and attributes of the data set and better integrate ETL scheduling with related processes.

Poor BI performance can have a significant impact on your ability to analyze trends and make daily operational decisions.

 

To learn more about how GRT can help improve your BI performance, contact us.