Microsoft SQL Server 2005 Analysis Services

Microsoft SQL Server 2005 Analysis Services

NOOK Book(eBook)

$30.49 $51.99 Save 41% Current price is $30.49, Original price is $51.99. You Save 41%.
View All Available Formats & Editions

Available on Compatible NOOK Devices and the free NOOK Apps.
WANT A NOOK?  Explore Now

Product Details

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

About 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 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.

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


    MAX and MIN



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


    DRILLTHROUGH Statement

    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




Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews

Microsoft SQL Server 2005 Analysis Services 3.5 out of 5 based on 0 ratings. 2 reviews.
Anonymous More than 1 year ago
Guest More than 1 year ago
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.