OLAP Performance Tuning Tips

Blog Technology

In this post, we’ve collected a number of typical OLAP performance issues, with tips on how to perform OLAP performance tuning.

Typical OLAP Performance Issues

  • Problems at the database performance level:
    • Tables or index statistics are not up to date
    • Compression algorithms issues
    • Data is not distributed properly across files and drivers
    • Parallel processing is not configured properly
    • Server, CPU, or memory require upgrading
  • Problems at the application performance level:
    • Dimensional modeling is not implemented properly
    • Indexing and partitioning in database analysis is not optimized
    • De-normalization is not optimized
  • Problems at the report performance level:
    • Query function is not tuned properly
    • Use of derived reporting or indexed views
    • Temporary table rationalization use
    • Locking issues

Recommended Process for OLAP Performance Tuning

  1. Check the hardware and that all relevant patches have been applied (i.e. firmware, drivers and operating system patches).
  2. After you’ve ensured the environment is up to date and configured properly, you should determine the expected workload. Here you should consider if you want to apply parallel or serial processing. Parallel processing is a good way of improving performance; however, note that this means this requires cube partitioning and mapping across several partitions.
  3. Analyze the logical model and dimensions, followed by analysis of the cubes, including storage model, sparsity vs. density of stored data, partition model, aggregation model, and data quality.
  4. Lastly, you should analyze the queries schema and analyze the database as a whole, followed by the required views.

ActivePivot a real-time OLAP system that stores data in-memory. In a real time OLAP system, which can respond to queries in under a second, performance tuning involves optimizing the queries to best take advantage of the in memory analytics engine. ActivePivot is a Java object-based system, and users can customize the algorithms, data compression patterns, and data storage structure to squeeze more performance out of the engine.

Because our customers, who truly require accurate, real time information to support their decision-making, are extremely concerned with the performance of their OLAP systems, we are constantly working to improve the performance of our product.
We’ve come across several issues that we addressed and already incorporated our solutions in the system, to improve OLAP performance tuning capabilities, including:

  • Advanced compression patterns
  • Grouping of logical data columns
  • Improved algorithms
  • Parallel processing

All this provides smarter storage, reduces data access time, and enables quicker query retrieval.

To try out ActivePivot’s real time OLAP solution, see our live demo.