Home > Store > Data > SQL Server

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
  • Pages: 864
  • 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

Unlimited one-month access with your purchase
Free Safari Membership