Microsoft SQL Server 2005 Analysis Services

Microsoft SQL Server 2005 Analysis Services

by Edward Melomed, Irina Gorbach, Alexander Berger, Py Bateman
Released December 2006
Publisher(s): Sams
ISBN: 0672327821

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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.

    Table of contents

    1. Table of Contents (1/4)
    2. Table of Contents (2/4)
    3. Table of Contents (3/4)
    4. Table of Contents (4/4)
    5. Foreword
    6. Introduction
    7. PART I: Introduction to Analysis Services
      1. 1 What's New in Analysis Services 2005
        1. Modeling Capabilities of Analysis Services 2005
        2. Advanced Analytics in Analysis Services 2005
        3. New Client-Server Architecture
        4. Improvements in Scalability
        5. Development and Management Tools
        6. Manageability of Analysis Services
        7. Sample Project
        8. Summary
      2. 2 Multidimensional Databases
        1. The Multidimensional Data Model
        2. Multidimensional Space (1/2)
        3. Multidimensional Space (2/2)
        4. Summary
      3. 3 UDM: Linking Relational and Multidimensional Databases
        1. Summary
      4. 4 Client/Server Architecture and Multidimensional Databases: An Overview
        1. Two-Tier Architecture
        2. One-Tier Architecture
        3. Three-Tier Architecture
        4. Four-Tier Architecture
        5. Distributed Systems
        6. Summary
    8. PART II: Creating Multidimensional Models
      1. 5 Conceptual Data Model
        1. Data Definition Language
        2. Summary
      2. 6 Dimensions in the Conceptual Model
        1. Dimension Attributes (1/3)
        2. Dimension Attributes (2/3)
        3. Dimension Attributes (3/3)
        4. Dimension Hierarchies
        5. Attribute Hierarchies
        6. Summary
      3. 7 Cubes and Multidimensional Analysis
        1. Cube Dimensions (1/2)
        2. Cube Dimensions (2/2)
        3. The Dimension Cube
        4. Perspectives
        5. Summary
      4. 8 Measures and Multidimensional Analysis
        1. Measures in Multidimensional Cubes
        2. Measure Groups
        3. Measure Group Dimensions (1/4)
        4. Measure Group Dimensions (2/4)
        5. Measure Group Dimensions (3/4)
        6. Measure Group Dimensions (4/4)
        7. Linked Measure Groups
        8. Summary
      5. 9 Multidimensional Models and Business Intelligence Development Studio
        1. Creating a Data Source
        2. Designing a Data Source View
        3. Designing a Dimension (1/2)
        4. Designing a Dimension (2/2)
        5. Designing a Cube (1/3)
        6. Designing a Cube (2/3)
        7. Designing a Cube (3/3)
        8. Configuring and Deploying a Project So That You Can Browse the Cube
        9. Summary
    9. PART III: Using MDX to Analyze Data
      1. 10 MDX Concepts
        1. The SELECT Statement (1/2)
        2. The SELECT Statement (2/2)
        3. Query Execution Context
        4. Set Algebra and Basic Set Operations
        5. MDX Functions (1/2)
        6. MDX Functions (2/2)
        7. Referencing Objects in MDX and Using Unique Names
        8. Summary
      2. 11 Advanced MDX
        1. Using Member and Cell Properties in MDX Queries
        2. Dealing with Nulls (1/2)
        3. Dealing with Nulls (2/2)
        4. Type Conversions Between MDX Objects
        5. Strong Relationships
        6. Sets in a WHERE Clause
        7. SubSelect and Subcubes (1/2)
        8. SubSelect and Subcubes (2/2)
        9. Summary
      3. 12 Cube-Based MDX Calculations
        1. MDX Scripts
        2. Calculated Members (1/2)
        3. Calculated Members (2/2)
        4. Assignments (1/2)
        5. Assignments (2/2)
        6. Named Sets
        7. Order of Execution for Cube Calculations (1/2)
        8. Order of Execution for Cube Calculations (2/2)
        9. Summary
      4. 13 Dimension-Based MDX Calculations
        1. Unary Operators
        2. Custom Member Formulas
        3. Semiadditive Measures (1/2)
        4. Semiadditive Measures (2/2)
        5. Order of Execution for Dimension Calculations
        6. Summary
      5. 14 Extending MDX with Stored Procedures
        1. Creating Stored Procedures (1/2)
        2. Creating Stored Procedures (2/2)
        3. Calling Stored Procedures from MDX
        4. Security Model
        5. Server Object Model (1/2)
        6. Server Object Model (2/2)
        7. Using Default Libraries
        8. Summary
      6. 15 Key Performance Indicators, Actions, and the DRILLTHROUGH Statement
        1. Key Performance Indicators (1/2)
        2. Key Performance Indicators (2/2)
        3. Actions (1/3)
        4. Actions (2/3)
        5. Actions (3/3)
        6. Drillthrough (1/2)
        7. Drillthrough (2/2)
        8. Summary
      7. 16 Writing Data into Analysis Services
        1. Using the UPDATE CUBE Statement to Write Data into Cube Cells
        2. Updatable and Nonupdatable Cells
        3. Lifetime of the Update
        4. Enabling Writeback
        5. Converting a Writeback Partition to a Regular Partition
        6. Other Ways to Perform Writeback
        7. Summary
    10. PART IV: Creating a Data Warehouse
      1. 17 Loading Data from a Relational Database
        1. Loading Data
        2. Data Source Objects
        3. Connection Timeouts
        4. Summary
      2. 18 DSVs and Object Bindings
        1. Data Source View
        2. Object Bindings
        3. Summary
      3. 19 Multidimensional Models and Relational Database Schemas
        1. Relational Schemas for Data Warehouses
        2. Building Relational Schemas from the Multidimensional Model (1/2)
        3. Building Relational Schemas from the Multidimensional Model (2/2)
        4. Summary
    11. PART V: Bringing Data into Analysis Services
      1. 20 The Physical Data Model
        1. Internal Components for Storing Data
        2. Data Structure of a Dimension (1/3)
        3. Data Structure of a Dimension (2/3)
        4. Data Structure of a Dimension (3/3)
        5. Physical Model of the Cube (1/3)
        6. Physical Model of the Cube (2/3)
        7. Physical Model of the Cube (3/3)
        8. Summary
      2. 21 Dimension and Partition Processing
        1. Dimension Processing
        2. Cube Processing (1/2)
        3. Cube Processing (2/2)
        4. Progress Reporting and Error Configuration
        5. Summary
      3. 22 Using SQL Server Integration Services to Load Data
        1. Using Direct Load ETL (1/2)
        2. Using Direct Load ETL (2/2)
        3. Summary
      4. 23 Aggregation Design and Usage-Based Optimization
        1. Designing Aggregations (1/2)
        2. Designing Aggregations (2/2)
        3. Query Usage Statistics
        4. Summary
      5. 24 Proactive Caching and Real-Time Updates
        1. Data Latency and Proactive Caching
        2. Timings and Proactive Caching
        3. Proactive Caching Scenarios
        4. Change Notifications and Object Processing During Proactive Caching
        5. General Considerations for Proactive Caching
        6. Monitoring Proactive Caching Activity
        7. Summary
      6. 25 Building Scalable Analysis Services Applications
        1. Approaches to Scalability
        2. OLAP Farm
        3. Linked Dimensions and Measure Groups (1/2)
        4. Linked Dimensions and Measure Groups (2/2)
        5. Remote Partitions
        6. Using Business Intelligence Development Studio to Create Linked Dimensions
        7. Summary
    12. PART VI: Analysis Server Architecture
      1. 26 Server Architecture and Command Execution
        1. Command Execution
        2. Session Management
        3. Server State Management
        4. Executing Commands That Change Analysis Services Objects
        5. Canceling a Command Execution
        6. Batch Command (1/2)
        7. Batch Command (2/2)
        8. Summary
      2. 27 Memory Management
        1. Economic Memory Management Model
        2. Server Performance and Memory Manager
        3. Managing Memory of Different Subsystems
        4. Memory Allocators
        5. Effective Memory Distribution with Memory Governor (1/2)
        6. Effective Memory Distribution with Memory Governor (2/2)
        7. Summary
      3. 28 Architecture of Query Execution—Calculating MDX Expressions
        1. Query Execution Stages
        2. Parsing an MDX Request
        3. Creation of Calculation Scopes (1/2)
        4. Creation of Calculation Scopes (2/2)
        5. Building a Virtual Set Operation Tree
        6. Optimizing Multidimensional Space by Removing Empty Tuples
        7. Calculating Cell Values
        8. Cache Subsystem
        9. Summary
      4. 29 Architecture of Query Execution—Retrieving Data from Storage
        1. Query Execution Stages
        2. Querying Different Types of Measure Groups (1/2)
        3. Querying Different Types of Measure Groups (2/2)
        4. Summary
    13. PART VII: Accessing Data in Analysis Services
      1. 30 Client/Server Architecture and Data Access
        1. Using TCP/IP for Data Access
        2. Using HTTP for Data Access
        3. Offline Access to Data
        4. Summary
      2. 31 Client Components Shipped with Analysis Services
        1. Using XML for Analysis to Build Your Application
        2. Using Analysis Services Libraries to Build Your Application
        3. Summary
      3. 32 XML for Analysis
        1. State Management
        2. XML/A Methods (1/2)
        3. XML/A Methods (2/2)
        4. Handling Errors and Warnings (1/2)
        5. Handling Errors and Warnings (2/2)
        6. Summary
      4. 33 ADOMD.NET
        1. Creating an ADOMD.NET Project
        2. Writing Analytical Applications
        3. ADOMD.NET Connections (1/2)
        4. ADOMD.NET Connections (2/2)
        5. Working with Metadata Objects (1/4)
        6. Working with Metadata Objects (2/4)
        7. Working with Metadata Objects (3/4)
        8. Working with Metadata Objects (4/4)
        9. AdomdCommand (1/2)
        10. AdomdCommand (2/2)
        11. Using the CellSet Object to Work with Multidimensional Data (1/2)
        12. Using the CellSet Object to Work with Multidimensional Data (2/2)
        13. Working with Data in Tabular Format (1/2)
        14. Working with Data in Tabular Format (2/2)
        15. Which Should You Use: AdomdDataReader or CellSet?
        16. Using Parameters in MDX Requests
        17. Asynchronous Execution and Cancellation of Commands
        18. Error Handling
        19. Summary
      5. 34 Analysis Management Objects
        1. AMO Object Model
        2. Creating a Visual Studio Project That Uses AMO
        3. Connecting to the Server
        4. Canceling Long-Running Operations
        5. AMO Object Loading
        6. Working with AMO in Disconnected Mode
        7. Using the Scripter Object
        8. Using Traces (1/2)
        9. Using Traces (2/2)
        10. Error Handling
        11. Summary
    14. PART VIII: Security
      1. 35 Security Model for Analysis Services
        1. Connection Security (1/2)
        2. Connection Security (2/2)
        3. External Data Access Security
        4. Summary
      2. 36 Object Security Model for Analysis Services
        1. Server Administrator Security
        2. Database Roles and the Hierarchy of Permission Objects (1/2)
        3. Database Roles and the Hierarchy of Permission Objects (2/2)
        4. Managing Database Roles
        5. Summary
      3. 37 Securing Dimension Data
        1. Defining Dimension Security (1/2)
        2. Defining Dimension Security (2/2)
        3. Testing Dimension Security
        4. Dynamic Security
        5. Dimension Security Architecture
        6. Dimension Security, Cell Security, and MDX Scripts
        7. Summary
      4. 38 Securing Cell Values
        1. Defining Cell Security
        2. Testing Cell Security
        3. Contingent Cell Security
        4. Dynamic Security
        5. Summary
    15. PART IX: Management
      1. 39 Using Trace to Monitor and Audit Analysis Services
        1. Trace Architecture
        2. Types of Trace Objects
        3. Creating Trace Command Options
        4. SQL Server Profiler (1/2)
        5. SQL Server Profiler (2/2)
        6. Flight Recorder
        7. Tracing Processing Activity
        8. Query Execution Time Events (1/2)
        9. Query Execution Time Events (2/2)
        10. Summary
      2. 40 Backup and Restore Operations
        1. Backing Up Data
        2. Backing Up Related Files
        3. Backup Strategies
        4. Automating Backup Operations
        5. Restoring Lost or Damaged Data
        6. MDX Extensions for Browsing Your File System
        7. Summary
      3. 41 Deployment Strategies
        1. Using the Deployment Wizard
        2. Synchronizing Your Databases
        3. Synchronization and Remote Partitions
        4. Synchronization and Failover Clusters
        5. Summary
    16. Index
      1. A
      2. B
      3. C
      4. D
      5. E
      6. F
      7. G
      8. H
      9. I
      10. J
      11. K
      12. L
      13. M
      14. N
      15. O
      16. P
      17. Q
      18. R
      19. S
      20. T
      21. U
      22. V
      23. W
      24. X

    Product information

    • Title: Microsoft SQL Server 2005 Analysis Services
    • Author(s): Edward Melomed, Irina Gorbach, Alexander Berger, Py Bateman
    • Release date: December 2006
    • Publisher(s): Sams
    • ISBN: 0672327821