Gift Guide

Microsoft SQL Server 2005 Analysis Services [NOOK Book]


Microsoft SQL Server Analysis Services provides fast access to data by means of multidimensional data structures and the multidimensional query languag MDX. Analysis Services provides the capability to design, create, and manage multidimensional cubes based on data warehouse tables, and it serves as the foundation for the Microsoft Business Intelligence strategy.

Microsoft SQL Server 2005 Analysis Services gives the reader insight into the way Analysis Services functions. It not...

See more details below
Microsoft SQL Server 2005 Analysis Services

Available on NOOK devices and apps  
  • NOOK Devices
  • Samsung Galaxy Tab 4 NOOK 7.0
  • Samsung Galaxy Tab 4 NOOK 10.1
  • NOOK HD Tablet
  • NOOK HD+ Tablet
  • NOOK eReaders
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac

Want a NOOK? Explore Now

NOOK Book (eBook)
$29.99 price
(Save 42%)$51.99 List Price


Microsoft SQL Server Analysis Services provides fast access to data by means of multidimensional data structures and the multidimensional query languag MDX. Analysis Services provides the capability to design, create, and manage multidimensional cubes based on data warehouse tables, and it serves as the foundation for the Microsoft Business Intelligence strategy.

Microsoft SQL Server 2005 Analysis Services gives the reader insight into the way Analysis Services functions. It not only explains ways to design and create multidimensional objects, databases, dimensions, and cubes, but also provides invaluable information about the reasons behind design decisions made by the development team.

Here's what you will find inside:

  • Understand the key concepts of multidimensional modeling
  • Explore the multidimensional object model and its definition language
  • Learn the main concepts of the MDX language and gain an in-depth understanding of advanced MDX concepts
  • Understand the mechanisms of integrating multidimensional and relational databases
  • Learn how to build client applications to access data in Analysis Services
  • Examine server architecture, including main data structures, data processing, and query resolution algorithms
  • Gain a deep understanding of the internal and external protocols for data transfer, including the XML/A protocol
  • Explore how Analysis Services manages memory
  • Explore the security model, including role-based security, code-access security, and data security
  • Discover how to monitor and manage Analysis Services

All the code for the sample database used in the book can be found at

Read More Show Less

Product Details

  • ISBN-13: 9780768689990
  • Publisher: Pearson Education
  • Publication date: 12/29/2006
  • Series: SQL Server Series
  • Sold by: Barnes & Noble
  • Format: eBook
  • Edition number: 1
  • Pages: 864
  • File size: 27 MB
  • Note: This product may take a few minutes to download.

Meet the Author

Edward Melomed is one of the original members of the Microsoft SQL Server Analysis Services team. He arrived in Redmond as a part of Microsoft's acquisition of Panorama Software Systems, Inc., which led to the technology that gave rise to Analysis Services 2005. He works as a program manager and plays a major role in the infrastructure design for the Analysis Services engine.

Irina Gorbach is a senior software designer on the Analysis Services team, which she joined soon after its creation nine years ago. During her time at Microsoft, Irina has designed and developed many features, was responsible for client subsystems OLEDB and ADOMD.NET, and was in the original group of architects that designed the XML for Analysis specification. Recently she has been working on the architecture and design of calculation algorithms.

Alexander Berger was one of the first developers to work on OLAP systems at Panorama. After it was acquired by Microsoft, he led the development of Microsoft OLAP Server through all its major releases. He is one of the architects of OLEDB for the OLAP standard and MDX language, and holds more than 30 patents in the area of multidimensional databases.

Py Bateman is a technical writer at Microsoft. She originally hails from Texas, which was considered a separate country on the multinational Analysis Services team.

Read More Show Less

Read an Excerpt


It was a pleasure to be asked to write the foreword to this new book, which is remarkable for two reasons:

  • People who have spent five years developing a product are normally more than ready to move on to the next release once the product is finally ready for release. Indeed, long before a new version gets into customers' hands, the developers are normally already working on the next release. So, for the actual developers to spend the considerable time that this book must have taken to write a lengthy, detailed book on it is very rare.
  • In my years as an industry analyst with The OLAP Report, and much earlier as a product manager, I have rarely come across developers who are prepared to provide such chapter and verse information on exactly how a product works. Even under NDA, few software vendors are prepared to volunteer this level of inside information.

But why should this be of interest to anyone who isn't an OLAP server developer? Why should a mere user or even an application developer care about what exactly happens under the hood, any more than ordinary car drivers needs to know the details of exactly how their car's engine management system works?

There are some good reasons why this is relevant. Analysis Services is now by far the most widely used OLAP server, which inevitably means that most of its users are new to OLAP. The OLAP Surveys have consistently found that the main reason for the choice is price and the fact that it is bundled with SQL Server, rather than performance, scalability, ease of use, or functionality.

This is not to say that Analysis Services lacks these capabilities; just thattypical Analysis Services buyers are less concerned about them than are the buyers of other products. But when they come to build applications, they certainly will need to take these factors into account, and this book will help them succeed. Just because Analysis Services is perceived as being a low-cost, bundled product does not mean that it is a small, simple add-on: particularly in the 2005 release, it is an ambitious, complex, sophisticated product. How it works is far from obvious, and how to make the most of it requires more than guesswork.

Many of the new Analysis Services users will have used relational databases previously, and will assume that OLAP databases are similar. They are not, despite the superficial similarities between MDX and SQL. You really need to think multidimensionally, and understand how Analysis Services cubes work.

Even users with experience of other OLAP servers will find that they differ from each other much more than do relational databases. If you start using Analysis Services without understanding the differences and without knowing how Analysis Services really works, you will surely store up problems for the future. Even if you manage to get the right results now, you may well compromise the performance and future maintainability of the application.

The OLAP Surveys have consistently found that if there is one thing that really matters with OLAP, it is a fast query response. Slow performance is the biggest single product-related complaint from OLAP users in general, and Analysis Services users are no different. Slow query performance was also the biggest technical deterrent to wider deployment.

Many people hope that ever improving hardware performance will let them off the hook: If the application is too slow, just rely on the next generation of faster hardware to solve the problem. But results from The OLAP Surveys show that this will not work—the rate of performance complaints has gone up every year, whether actual query performance has improved or not. In an era when everyone expects free sub-second Web searches of billions of documents, books, and newsgroup postings, they are no longer willing to wait five or ten seconds for a simple management report from a modest internal database. It is not enough for an OLAP application to be faster than the spreadsheet or relational application it replaced—it must be as fast as other systems that we all use every day.

The good news is that fast query performance is possible if you take full advantage of the OLAP server's capabilities: The OLAP Survey 6 found that 57% of Analysis Services 2005 users reported that their typical query response was less than five seconds. This was the traditional benchmark target query time, but in the new era of instant Web searches, I think the new target should be reduced to one second. This is a tough target, and will require application developers to really know what they are doing, and to take the time to optimize their systems.

This is where this book comes in. The authors—who have been involved with Analysis Services from its earliest days, long before it was called Analysis Services—have documented, in detail, what really happens inside Analysis Services 2005, right down to the bit structure of data records. Along the way, numerous controllable parameters are described, with helpful information about how they cause memory or other computer resources to be used.

This book is not intended to teach new users how to use Analysis Services 2005; it is for technically competent implementers who want to make the most of Analysis Services by understanding how it really works, as described by those who really know, unlike other books written by external authors who sometimes have to speculate. If you are new to Analysis Services, you probably need to start with a "how do I?" book or course, rather than a "what happens inside?" book like this one.

Nigel Pendse

Editor of The OLAP Report
Author of The OLAP Survey

© Copyright Pearson Education. All rights reserved.

Read More Show Less

Table of Contents



PART I: Introduction to Analysis Services

1 What's New in Analysis Services 2005

Modeling Capabilities of Analysis Services 2005

Advanced Analytics in Analysis Services 2005

New Client-Server Architecture

Improvements in Scalability

Development and Management Tools

Manageability of Analysis Services

Sample Project

Customer Data

Store Data

Product and Warehouse Data

Time Data

Account Data

Currency Data

Employee Data

The Warehouse and Sales Cube

The HR Cube

The Budget Cube

The Sales and Employees Cube


2 Multidimensional Databases

The Multidimensional Data Model

The Conceptual Data Model.

The Physical Data Model

The Application Data Model

Multidimensional Space

Describing Multidimensional Space


3 UDM: Linking Relational and Multidimensional Databases


4 Client/Server Architecture and Multidimensional Databases: An Overview

Two-Tier Architecture

One-Tier Architecture

Three-Tier Architecture

Four-Tier Architecture

Distributed Systems

Distributed Storage

Thin Client/Thick Client


PART II: Creating Multidimensional Models

5 Conceptual Data Model

Data Definition Language

Objects in DDL


6 Dimensions in the Conceptual Model

Dimension Attributes

Attribute Properties and Values

Relationships Between Attributes

Attribute Member Keys

Attribute Member Names

Relationships Between Attributes

Attribute Discretization

Parent Attributes

Dimension Hierarchies

Types of Hierarchies

Attribute Hierarchies

Parent-Child Hierarchies


7 Cubes and Multidimensional Analysis

Cube Dimensions

Cube Dimension Attributes

Cube Dimension Hierarchies

Role-Playing Dimensions

The Dimension Cube



8 Measures and Multidimensional Analysis

Measures in Multidimensional Cubes





Measure Groups

Measure Group Dimensions

Granularity of a Fact

Indirect Dimensions

Measure Expressions

Linked Measure Groups


9 Multidimensional Models and Business Intelligence Development Studio

Creating a Data Source

Creating a New Data Source

Modifying an Existing Data Source

Modifying a DDL File

Designing a Data Source View

Creating a New Data Source View

Modifying a Data Source View

Designing a Dimension

Creating a Dimension

Modifying an Existing Dimension

Designing a Cube

Creating a Cube

Modify a Cube

Build a Cube Perspective

Defining Cube Translations

Configuring and Deploying a Project So That You Can Browse the Cube

Configuring a Project

Deploying a Project

Browsing a Cube


PART III: Using MDX to Analyze Data

10 MDX Concepts

The SELECT Statement

The SELECT Clause

Defining Coordinates in Multidimensional Space

Default Members and the WHERE Clause

Query Execution Context

Set Algebra and Basic Set Operations






MDX Functions

Functions for Navigating Hierarchies

The Function for Filtering Sets

Functions for Ordering Data

Referencing Objects in MDX and Using Unique Names

By Name

By Qualified Name

By Unique Name


11 Advanced MDX

Using Member and Cell Properties in MDX Queries

Member Properties

Cell Properties

Dealing with Nulls

Null Members, Null Tuples, and Empty Sets

Nulls and Empty Cells

Type Conversions Between MDX Objects

Strong Relationships

Sets in a WHERE Clause

SubSelect and Subcubes


12 Cube-Based MDX Calculations

MDX Scripts

Calculated Members

Defining Calculated Members


Assignment Operator

Specifying a Calculation Property

Scope Statements

Root and Leaves Functions

Calculated Cells

Named Sets

Order of Execution for Cube Calculations

The Highest Pass Wins

Recursion Resolution


13 Dimension-Based MDX Calculations

Unary Operators

Custom Member Formulas

Semiadditive Measures

ByAccount Aggregation Function

Order of Execution for Dimension Calculations

The Closest Wins


14 Extending MDX with Stored Procedures

Creating Stored Procedures

Creating Common Language Runtime Assemblies

Using Application Domains to Send-Box Common Language Runtime Assemblies

Creating COM Assemblies

Calling Stored Procedures from MDX

Security Model

Role-Based Security

Code Access Security

User-Based Security

Server Object Model

Operations on Metadata Objects

Operations on MDX Objects

Using Default Libraries


15 Key Performance Indicators, Actions, and the DRILLTHROUGH Statement

Key Performance Indicators

Defining KPIs

Discovering and Querying KPIs


Defining Actions

Discovering Actions



Defining DRILLTHROUGH Columns in a Cube


16 Writing Data into Analysis Services

Using the UPDATE CUBE Statement to Write Data into Cube Cells

Updatable and Nonupdatable Cells

Lifetime of the Update

Enabling Writeback

Converting a Writeback Partition to a Regular Partition

Other Ways to Perform Writeback


PART IV: Creating a Data Warehouse

17 Loading Data from a Relational Database

Loading Data

Data Source Objects

Data Source Object Properties

Data Source Security

Connection Timeouts


18 DSVs and Object Bindings

Data Source View

Named Queries

Named Calculations

Object Bindings

Column Bindings

Table Bindings

Query Bindings


19 Multidimensional Models and Relational Database Schemas

Relational Schemas for Data Warehouses

Building Relational Schemas from the Multidimensional Model

Using Wizards to Create Relational Schemas

Using Templates to Create Relational Schemas


PART V: Bringing Data into Analysis Services

20 The Physical Data Model

Internal Components for Storing Data

Data Store Structure

File Store Structure

Bit Store Structure

String Store Structure

Compressed Store Structure

Hash Index of a Store

Data Structure of a Dimension

Data Structures of the Attributes

Attribute Relationships

Data Structures of Hierarchies

Physical Model of the Cube

Defining a Partition Using Data Definition Language

Physical Model of the Partition

Overview of Cube Data Structures


21 Dimension and Partition Processing

Dimension Processing

Attribute Processing

Hierarchy Processing

Building Decoding Tables

Building Indexes

Schema of Dimension Processing

Dimension Processing Options

Processing ROLAP Dimensions

Processing Parent-Child Dimensions

Cube Processing

Data Processing

Building Aggregations and Indexes

Cube Processing Options

Progress Reporting and Error Configuration

ErrorConfiguration Properties

Processing Error Handling


22 Using SQL Server Integration Services to Load Data

Using Direct Load ETL

Creating an SSIS Dimension-Loading Package

Creating an SSIS Partition-Loading Package


23 Aggregation Design and Usage-Based Optimization

Designing Aggregations

Relational Reporting-Style Dimensions

Flexible Versus Rigid Aggregations

Aggregation Objects and Aggregation Design Objects

The Aggregation Design Algorithm

Query Usage Statistics

Setting Up a Query Log

Monitoring Aggregation Usage


24 Proactive Caching and Real-Time Updates

Data Latency and Proactive Caching

Timings and Proactive Caching

Frequency of Updates

Long-Running MOLAP Cache Processing

Proactive Caching Scenarios

MOLAP Scenario

Scheduled MOLAP Scenario

Automatic MOLAP Scenario

Medium-Latency MOLAP Scenario

Low-Latency MOLAP Scenario

Real-time HOLAP Scenario

Real-time ROLAP Scenario

Change Notifications and Object Processing During Proactive Caching

Scheduling Processing and Updates

Change Notification Types

Incremental Updates Versus Full Updates

General Considerations for Proactive Caching

Monitoring Proactive Caching Activity


25 Building Scalable Analysis Services Applications

Approaches to Scalability

The Scale-Up Approach

The Scale-Out Approach


Data Storage

Network Load Balancing

Linked Dimensions and Measure Groups

Updates to the Source of a Linked Object

Linked Dimensions

Linked Measure Groups

Remote Partitions

Processing Remote Partitions

Using Business Intelligence Development Studio to Create Linked Dimensions


PART VI: Analysis Server Architecture

26 Server Architecture and Command Execution

Command Execution

Session Management

Server State Management

Executing Commands That Change Analysis Services Objects

Creating Objects

Editing Objects

Deleting Objects

Processing Objects

Commands That Control Transactions

Managing Concurrency

Using a Commit Lock for Transaction Synchronization

Canceling a Command Execution

Batch Command


27 Memory Management

Economic Memory Management Model

Server Performance and Memory Manager

Memory Holders

Memory Cleanup

Managing Memory of Different Subsystems

Cache System Memory Model

Managing Memory of File Stores

Managing Memory Used by User Sessions

Other Memory Holders

Memory Allocators

Effective Memory Distribution with Memory Governor

Model of Attribute and Partition Processing

Model of Building Aggregations

Model of Building Indexes


28 Architecture of Query Execution—Calculating MDX Expressions

Query Execution Stages

Parsing an MDX Request

Creation of Calculation Scopes

Global Scope and Global Scope Cache

Session Scope and Session Scope Cache

Global and Session Scope Lifetime

Building a Virtual Set Operation Tree

Optimizing Multidimensional Space by Removing Empty Tuples

Calculating Cell Values

Calculation Execution Plan Construction

Evaluation of Calculation Execution Plan

Execution of the Calculation Execution Plan

Cache Subsystem

Dimension and Measure Group Caches

Formula Caches


29 Architecture of Query Execution—Retrieving Data from Storage

Query Execution Stages

Querying Different Types of Measure Groups

Querying Regular Measure Groups

Querying ROLAP Partitions

Querying Measure Groups with DISTINCT_COUNT Measures

Querying Remote Partitions and Linked Measure Groups

Querying Measure Groups with Indirect Dimensions


PART VII: Accessing Data in Analysis Services

30 Client/Server Architecture and Data Access

Using TCP/IP for Data Access

Using Binary XML and Compression for Data Access

Using HTTP for Data Access

Offline Access to Data


31 Client Components Shipped with Analysis Services

Using XML for Analysis to Build Your Application

Using Analysis Services Libraries to Build Your Application

Query Management for Applications Written in Native Code

Query Management for Applications Written in Managed Code

Using DSO and AMO for Administrative Applications


32 XML for Analysis

State Management

XML/A Methods

The Discover Method

The Execute Method

Handling Errors and Warnings

Errors That Result in the Failure of the Whole Method

Errors That Occur After Serialization of the Response Has Started

Errors That Occur During Cell Calculation




Creating an ADOMD.NET Project

Writing Analytical Applications

ADOMD.NET Connections

Working with Metadata Objects

Operations on Collections

Caching Metadata on the Client

Working with a Collection of Members (MemberCollection)

Working with Metadata That Is Not Presented in the Form of Objects




Using the CellSet Object to Work with Multidimensional Data

Handling Object Symmetry

Working with Data in Tabular Format


Using Visual Studio User Interface Elements to Work with OLAP Data

Which Should You Use: AdomdDataReader or CellSet?

Using Parameters in MDX Requests

Asynchronous Execution and Cancellation of Commands

Error Handling






34 Analysis Management Objects

AMO Object Model

Types of AMO Objects

Dependent and Referenced Objects

Creating a Visual Studio Project That Uses AMO

Connecting to the Server

Canceling Long-Running Operations

AMO Object Loading

Working with AMO in Disconnected Mode

Using the Scripter Object

Using Traces

Error Handling






PART VIII: Security

35 Security Model for Analysis Services

Connection Security

TCP/IP Connection Security

HTTP Security

External Data Access Security

Choosing a Service Logon Account

Changing a Service Logon Account

Security for Running Named Instances (SQL Server Browser)

Security for Running on a Failover Cluster


36 Object Security Model for Analysis Services

Server Administrator Security

Database Roles and the Hierarchy of Permission Objects

Permission Objects

Managing Database Roles


37 Securing Dimension Data

Defining Dimension Security

The AllowedSet and DeniedSet Properties

The VisualTotals Property

Defining Dimension Security Using the User Interface

Testing Dimension Security

Dynamic Security

Dimension Security Architecture

Dimension Security, Cell Security, and MDX Scripts


38 Securing Cell Values

Defining Cell Security

Testing Cell Security

Contingent Cell Security

Dynamic Security


PART IX: Management

39 Using Trace to Monitor and Audit Analysis Services

Trace Architecture

Types of Trace Objects

Administrative Trace

Session Trace

Flight Recorder Trace

Creating Trace Command Options

SQL Server Profiler

Defining a Trace

Running a Trace

Flight Recorder

How Flight Recorder Works

Configuring Flight Recorder Behavior

Discovering Server State

Tracing Processing Activity

Reporting the Progress of Dimension Processing

Reporting the Progress of Partition Processing

Query Execution Time Events

Running a Simple Query

Changing the Simple Query

Running a More Complex Query

Changing the Complex Query

Changing Your Query Just a Little More


40 Backup and Restore Operations

Backing Up Data

Planning Your Backup Operation

Benefits of Analysis Server 2005 Backup Functionality

Using the Backup Database Dialog Box to Back Up Your Database

Using a DDL Command to Back Up Your Database

Backing Up Related Files

Backing Up the Configuration File

Backing Up the Query Log Database

Backing Up Writeback Tables

Backup Strategies

Typical Backup Scenario

High Availability System Backup Scenario

Automating Backup Operations

SQL Server Agent

SQL Server Integration Services

AMO Application

Restoring Lost or Damaged Data

Using the Restore Database Dialog Box

Using the DDL Command to Restore Your Database

Using DISCOVER_LOCATIONS to Specify Alternative Locations for Partitions

MDX Extensions for Browsing Your File System

The MDX Extensions


41 Deployment Strategies

Using the Deployment Wizard

Synchronizing Your Databases

Using the Synchronize Database Wizard

Using a DDL Command to Synchronize Databases

Similarities Between the Synchronization and Restore Commands

Synchronization and Remote Partitions

Synchronization and Failover Clusters



Read More Show Less

Customer Reviews

Average Rating 3.5
( 2 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 2 Customer Reviews
  • Anonymous

    Posted January 25, 2007

    different way of analysing SQL data

    The book teaches a means of analysing massive data sets, that is different from SQL, but which can be applied to SQL databases. Called MDX, it lets you, the analyst, define a hyperspace of several dimensions, where the number of dimensions can be greater than 3. Along each axis, there is a discrete set of values. Unlike engineering or physics analysis, where the spaces often take on continuum values. The authors show how MDX comes with a rich set of built in functions. But you can also easily write your own, that use these, or start from scratch. The Analysis Service version 2005 encompasses MDX, along with a user interface. The MDX syntax borrows deliberately in part from SQL, since as a practical matter, many of its users will come from a SQL background. But for analysis, it is often superior, offering a more flexible and intuitive syntax geared towards analysis. One potential 'problem', which is not mentioned, is that if you get used to the MDX syntax, going back to writing code for a strict SQL application might now seem so constricting. Of course, this is scarcely MDX's fault. The book's chapters are often quite short ('bite-sized') and are easy to follow.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted August 11, 2011

    No text was provided for this review.

Sort by: Showing all of 2 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)