Home > Store

Microsoft SQL Server 2005 Analysis Services

Register your product to gain access to bonus material or receive a coupon.

Microsoft SQL Server 2005 Analysis Services

eBook (Watermarked)

  • Your Price: $41.59
  • List Price: $51.99
  • Includes EPUB, MOBI, and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    MOBI MOBI The eBook format compatible with the Amazon Kindle and Amazon Kindle applications.

    Adobe Reader PDF The popular standard, used most often with the free Adobe® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.


  • Copyright 2007
  • Edition: 1st
  • eBook (Watermarked)
  • ISBN-10: 0-13-265009-6
  • ISBN-13: 978-0-13-265009-0

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 www.informit.com/title/0672327821.

Sample Content

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




Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020