In my last post about BI teams executing faster, Faster BI Execution: Four Practical Approaches to Improving BI System Quality, I looked at how quality impacts BI team performance, in this fourth post in the series I examine how slow BI systems can slow the performance of BI teams.
Do users complain about how slow the system is? Do you have to create a summary table for many new reports? Do you test on sample data or only run tests once or twice because things are slow? Do developers spend a lot of time clicking refresh waiting for reports and ETL to finish?
Investing in system performance can make your business intelligence team faster. Faster systems will help BI teams deliver on shorter deadlines, adapt to changing business requirements faster, and build more functionality with few resources.
Slow systems have a big impact on development and test productivity. Tests run longer or get skipped. Unnecessary summary tables and extracts take effort that could be used on new features and make batch run times longer. Highly skilled people twiddle their thumbs and surf the web waiting for jobs to finish.
BI systems have a performance problem when system speed impacts productivity. Here are some rules of thumb for understanding when your slow system is slowing down your team:
Batch and ETL Performance Problems:
- Developers check their email or surf the web while waiting for query results.
- Batch runs during business hours.
- Developers work in production because development or test environments are too slow.
Front end performance:
- You limit who can do as-hoc or analytic reporting because of performance concerns
- Reports take more than 5 seconds to open
- Filter/drill takes longer than 2 seconds
- No one outside the BI team and full-time analysts uses ad hoc
- Write new reports routinely require new summary tables or cubes
Getting to good performance
Every environment has its own challenges, but here are some common fixes. When the time comes to invest in system performance, we suggest splitting investments into quick wins (under $10,000 and less than two weeks), and larger efforts, which can take scale up to months and hundreds of thousands of dollars.
BI Performance Quick wins:
- Increase database server RAM. Database server RAM speeds up nearly all queries. If your cache hit rate dips below 98%, this is a likely source of improvement.
- Increase network bandwidth and add adapters. Network bandwidth between database, ETL, and reporting servers is a frequent bottleneck, and cheap to add. If your peak actual used bandwidth between servers is less than 70 MB/s, you have an opportunity to gain performance.
- Tune the 10 most resource intensive SQL statements. Tuning can be labor intensive, but the biggest wins usually occur in the top few statements. Consider only statements that run longer than 10 minutes for tuning.
- Move from a general purpose database to an analytic database. Columnar, cube, and appliance databases generate dramatic improvements over general purpose databases. Typical results are 10-100 times improvements, but the cost in rewritten ETL and front end is high.
- Switch from full refresh to change-only refreshes. Changing ETL to loading changes enables big performance gains, and lays the basis for more frequent refreshes and near-real-time.
- Add Flash storage. Flash storage is affordable and integrates well within SAN environments. Storage access performance gains can be in the 10x range.
System performance is not just an annoyance. High performing systems help your BI team deliver higher productivity and business value.
by Tom Victory, Principal Consultant
© DecisionPath Consulting, 2012