Best practices for SQL Server OLAP ...

This page is specific to

The 2010 product release

Other versions are also available for the following:

The 2007 product release

Best practices for SQL Server OLAP cube design and MDX querying (SharePoint Server 2010)

Published: May 12, 2010

When you are enabling Microsoft SQL Server 2005 or 2008 Analysis Services (SSAS) in a SharePoint Server 2010 business intelligence solution, proper cube design, efficient multidimensional expressions, and sufficient hardware resources are critical to optimal performance.

This article highlights best practices that help improve the performance of SSAS as a data source for Excel Services in Microsoft SharePoint Server 2010, PerformancePoint Services in Microsoft SharePoint Server 2010, and Visio Services in Microsoft SharePoint Server 2010. The first section lists articles that highlight Microsoft SQL Server 2008 enhancements that are specifically designed for business intelligence (BI) solutions, while the second section gives best practices that apply to SSAS 2005/2008 cube design, MDX query optimization, and more.

SQL Server 2008 enhancements for business intelligence

The following articles describe enhancements to SQL Server 2008 that relate to business intelligence.

Top 10 Performance and Productivity Reasons to Use SQL Server 2008 for Your Business Intelligence Solutions Provides a list, with some details, of the top performance reasons to use SQL Server 2008 for your new business intelligence solutions and to upgrade to SQL Server 2008 for your existing business intelligence solutions.
SQL Server 2008 Upgrade Technical Reference Guide Gives information about upgrading your existing Business Intelligence solutions to SQL Server 2008.
Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned Describes enhancements in performance and scalability to SQL Server Reporting Services 2008.
Performance Improvements for MDX in SQL Server 2008 Analysis Services Explains where issues can occur in your existing MDX code that will prevent you from experiencing the performance improvements in SQL Server Analysis Services 2008.
SQL Server 2008 White Paper: Analysis Services Performance Guide Describes how application developers can apply query and processing performance-tuning techniques to their SQL Server 2008 Analysis Services OLAP solutions.

Best practices for Analysis Services

Analysis Services Query Performance Top 10 Best Practices Covers ten best practices for optimizing Analysis Services query performance.
SQL Server Best Practices Article: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services Provides information about available MDX query troubleshooting tools. The article also demonstrates how to use the most common of these tools to identify and resolve MDX query performance bottlenecks with individual MDX queries.
Analysis Services Processing Best Practices Provides best practices for processing in SQL Server 2005 Analysis Services.
OLAP Design Best Practices for Analysis Services 2005 Outlines recommended best practices for designing OLAP databases in SQL Server 2005 Analysis Services to better meet the functional and performance needs of users.
Microsoft SQL Server 2005 Analysis Services Performance Guide Describes how application developers can apply performance-tuning techniques to their Microsoft SQL Server 2005 Analysis Services Online Analytical Processing (OLAP) solutions.
Scale-Out Querying with Analysis Services Describes how to set up a load-balanced scalable querying environment for Microsoft SQL Server 2005 Analysis Services so that you can handle a large number of concurrent queries to your Analysis Services servers. Load-balanced querying ensures that readers of OLAP cubes can consistently query for the latest aggregations throughout the day and distribute the load of all queries among the available servers. This scale-out querying architecture optimizes cube processing time, increases the frequency of cube update, and makes processing more robust because you can afford more frequent processing and transparent error recovery.

Change History

Date	Description	Reason
May 12, 2010	Initial publication