Original post at jenunderwood.com
If you have done everything possible to tune your Analysis Services OLAP cube and it still is not delivering fast enough queries, you might want to evaluate an application acceleration utility in the realm of WAN optimization without the expensive price tags. Many enterprises spend hundreds of thousands of dollars on WAN accelerators. An accelerator for Analysis Services can be used for under $10,000.
Why Analysis Services Accelerator?
Even if Analysis Services performance is fine or you may be moving to cloud, accelerators can defer bandwidth upgrade costs, reduce network congestion and free up resources for other apps. These technologies leverage TCP acceleration and other techniques. They optimize how data is transferred between the SQL Server instance and the reporting client without modifying the end applications. They offer an alternative or a supplement to TCP tuning.
For Analysis Services administrators that need an easy, quick win to improve performance, these modern accelerator utilities are absolutely worth reviewing. They are simple to install and provide immediate results.
Having worked with Analysis Services since SQL Server 7.0 seventeen years ago, some things never change.
“Reporting users and data analysts always have a need for speed.
They demand faster queries.”
Meanwhile the Analysis Services server is usually increasingly deluged with new data sources, more data and additional users performing ad-hoc queries with self-service BI solutions. Over time it becomes more challenging for server administrators to satisfy those reporting users. No one wants to hear they need to invest in better hardware or upgrade a hosting plan. After cube and query design optimization activities have been done, and cache warmers applied, server admins then review other options.
Modern in-memory designs certainly have improved query performance tremendously. If you are still on a lower version of SQL Server and can afford to upgrade, you will gain performance improvements simply by upgrading versions.
In many cases you can’t quickly upgrade or you may be stuck with a poorly designed Analysis Services cube. Redesign is usually a significant amount of work with downstream dependencies to manage. I know there are a lot of not-so-great cubes out there in the wild! Analysis Services does have a steep learning curve.
Other Analysis Services cubes are wonderfully designed and may already be deployed on the latest and greatest Analysis Services 2016. However, reporting users still want faster queries. So, what can you do to speed it up quickly and easily?
“Modern, low-cost accelerator technologies can solve those pains.”
Accelerating Analysis Services
Nitrosphere develops products that accelerate, secure, and reduce costs of SQL Server applications over the WAN or cloud with no need for configuration. Recently Nitrosphere introduced SSAS support in NitroAccelerator Turbo acceleration technology at PASS Summit 2016. NitroAccelerator Turbo expedites the flow of data from the following:
- From source SQL Server databases to the Analysis Services server improving cube processing times
- From reporting client to Analysis Services server improving MDX and DAX query times
NitroAccelerator Turbo compresses and optimizes network traffic for SQL Server 2000, 2005, 2008, 2012, 2014 and 2016. By optimizing the Tabular Data Stream (TDS) protocol, NitroAccelerator allows for better data transfer performance while reducing network traffic. Several features improve performance for queries including:
- Adaptive packet compression dynamically adjusts TCP packet size based on result set size
- HyperCacheTM technology provides huge performance benefits by minimizing the transfer of data between endpoints
- Intelligent protocol detection enables compression of non-native TDS implementations like Java applications
- Encryption secures network traffic between endpoints
NitroAccelerator is designed to work seamlessly in the client-server model. Any application that is leveraging the TDS protocol using MDAC, SQL Native Client, Java thin or thick drivers or through any other implementation to connect to a SQL Server, such as SharePoint or SQL Management Studio, can use NitroAccelerator to compress and optimize the traffic. Note that all clients and servers are not required to have NitroAccelerator installed, but only connections that have NitroAccelerator on both ends will see a performance improvement.
According to the product launch press release, customers shared query wait times were reduced by 90%. That is quite impressive. I gave NitroAccelerator Turbo a quick test to see that for myself with my favorite free Analysis Service query performance tool, MDX Studio.
In my tests with the teeny tiny AdventureWorks OLAP cube, I was able to see an immediate 20% improvement in line item detail level MDX queries with SQL Server 2016. I tested before and after restarting from a cold cache each time. Analysis Services Tabular results were slightly better. I anticipate that if I had a much larger Analysis Services OLAP or Tabular cube generating large result sets on a bandwidth-constrained or congested network that I’d get much more compelling results.
“Thus I ask you to give it a try and let me know how it performs for you.”
For folks that are open to sharing your experience and results as an appendix to this blog, please send them to me and I’ll happily add them here. If anyone has less than stellar testing results, I’ll also share them with the product team for a reply or you can do that to help them continually improve.
NitroAccelerator also provides a Control Console accessible via the system tray or through the Start menu. It shows compression statistics for Nitro-enabled connections with an option to log them. Two graphs show the outbound and inbound traffic where the blue lines are the compressed traffic and the red lines are what the traffic would be if it were uncompressed. While these statistics are helpful in determining that NitroAccelerator is installed and running properly, you may want to track other performance measurements to see the full benefit of NitroAccelerator in your environment.
There is also a myriad of other performance monitoring tools out there that you can use to capture before and after results.
For More Information
If you’d like to learn more about Nitrosphere’s offerings and the Analysis Services NitroAccelerator Turbo, check out the following resources.
- NitroAccelerator Turbo video https://www.youtube.com/watch?v=djLKkbbxjRc
- Nitrosphere website http://nitrosphere.com/?ref=83022
- Nitrosphere case studies and white papers http://nitrosphere.com/resources/
- Nitrosphere FAQ http://nitrosphere.com/wp-content/uploads/2016/10/Nitro_WhyNitroFaq4.pdf
- Nitrosphere Installation Guide http://nitrosphere.com/wp-content/uploads/2016/05/Nitro_InstallGuide_2016.pdf