Analysis Services Performance Guide for SQL Server 2012 and SQL Server 2014 - Apply OLAP Query and Processing Tuning Techniques

Sep 1, 2026

This white paper explains how business intelligence developers can tune query execution and OLAP processing performance in Microsoft SQL Server Analysis Services. Updated from the prior 2008 R2 performance guide, it validates performance improvements for SQL Server 2012 (including SP1) and SQL Server 2014. It covers scalable cube design patterns, including dimension modeling, partitioning, relational source choices, and calculation script practices, then moves into query processor architecture and internal behaviors.

LanguageEnglish

CategoryPresentations

FormatDOCX

Pages112

Reading Time39 min

AccessFree to download

Introduction

Overview of Performance Goals

Design Patterns for Scalable Cubes

Building Optimal Dimensions

Partitioning a Cube

Relational Data Source Design

Calculation Scripts

Tuning Query Performance

Query Processor Architecture

SQL Server Technical Article Analysis Services Performance Guide for SQL Server 2012 and SQL Server 2014 Summary: This white paper describes how business intelligence developers can apply query and processing performance-tuning techniques to their OLAP solutions running on Microsoft SQL Server Analysis Services. This paper is based on the performance guide for 2008 R2 and has been reviewed and updated to validate performance on SQL Server 2012 and SQL Server 2014. Writers: Karan Gulati and Jon Burchel Editor: Jeannine Takaki Contributors and Technical Reviewers: Akshai Mirchandani, Siva Harinath, Lisa Liu Published: May 2014 Applies to: SQL Server 2012 (including 2012 SP1), SQL Server 2014 Copyright The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in, or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property. © 2014 Microsoft Corporation. All rights reserved. Microsoft, Excel, SQL Server, Visual Basic, Visual Studio, and Windows are trademarks of the Microsoft group of companies. All other trademarks are property of their respective owners. Contents TOC \o "1-3" \h \z \u HYPERLINK \l "_Toc387860792" 1 Introduction PAGEREF _Toc387860792 \h 7 HYPERLINK \l "_Toc387860793" 1.1 Overview of Performance Goals PAGEREF _Toc387860793 \h 7 HYPERLINK \l "_Toc387860794" 2 Design Patterns for Scalable Cubes PAGEREF _Toc387860794 \h 7 HYPERLINK \l "_Toc387860795" 2.1 Building Optimal Dimensions PAGEREF _Toc387860795 \h 8 HYPERLINK \l "_Toc387860796" 2.1.1 Using the KeyColumns, ValueColumn, and NameColumn Properties Effectively PAGEREF _Toc387860796 \h 8 HYPERLINK \l "_Toc387860797" 2.1.2 Hiding Attribute Hierarchies PAGEREF _Toc387860797 \h 9 HYPERLINK \l "_Toc387860798" 2.1.3 Setting or Disabling Ordering of Attributes PAGEREF _Toc387860798 \h 9 HYPERLINK \l "_Toc387860799" 2.1.4 Setting Default Attribute Members PAGEREF _Toc387860799 \h 10 HYPERLINK \l "_Toc387860800" 2.1.5 Removing the All Level PAGEREF _Toc387860800 \h 10 HYPERLINK \l "_Toc387860801" 2.1.6 Identifying Attribute Relationships PAGEREF _Toc387860801 \h 10 HYPERLINK \l "_Toc387860802" 2.1.7 Using Hierarchies Effectively PAGEREF _Toc387860802 \h 12 HYPERLINK \l "_Toc387860803" 2.1.8 Turning Off the Attribute Hierarchy PAGEREF _Toc387860803 \h 13 HYPERLINK \l "_Toc387860804" 2.1.9 Reference Dimensions PAGEREF _Toc387860804 \h 14 HYPERLINK \l "_Toc387860805" 2.1.10 Fast-Changing Attributes PAGEREF _Toc387860805 \h 15 HYPERLINK \l "_Toc387860806" 2.1.11 Large Dimensions PAGEREF _Toc387860806 \h 18 HYPERLINK \l "_Toc387860807" 2.2 Partitioning a Cube PAGEREF _Toc387860807 \h 20 HYPERLINK \l "_Toc387860808" 2.2.1 Partition Slicing PAGEREF _Toc387860808

FAQs

What is the purpose of this performance guide for Analysis Services?

It provides business intelligence techniques for tuning query and processing performance in OLAP solutions running on SQL Server Analysis Services.

Which SQL Server versions does the guide apply to?

It applies to SQL Server 2012 (including 2012 SP1) and SQL Server 2014.

What major topics are covered before query tuning?

The guide covers scalable cube design patterns, including optimal dimension construction, cube partitioning, relational data source design, and calculation scripting practices.

Sep 1, 2026

Upload by วิน