O'Reilly logo



Start Free Trial

No credit card required

Professional SQL Server™ Analysis Services 2005 with MDX

Book Description

  • Written by the lead developers of Analysis Services at Microsoft, this book begins with an overview showing how Analysis Services and MDX can be used to build data warehouses and multidimensional databases

  • The authors then describe the development processes for building dimensions and cubes from various data sources

  • Demonstrating a variety of techniques in real-world scenarios, the book shows how MDX can be used to query databases to provide sophisticated analysis of business problems

  • Finally, the book explains how Analysis Services can be used with other components of SQL Server, including DTS, data mining, and Report Services, to provide comprehensive, end-to-end solutions

Table of Contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Dedication
  5. Acknowledgments
  6. Introduction
  7. Introduction
    1. Introduction to Data Warehousing and Analysis Services 2005
      1. A Closer Look at Data Warehousing
      2. Key Elements of a Data Warehouse
      3. Inmon Versus Kimball Different Approaches
      4. Business Intelligence Is Data Analysis
      5. Analysis Services 2005
      6. The Unified Dimensional Model
      7. Summary
    2. First Look at Analysis Services 2005
      1. Differences between Analysis Services 2000 and Analysis Services 2005
      2. Upgrading to Analysis Services 2005
      3. Using the Business Intelligence Development Studio
      4. Using SQL Server Management Studio
      5. Querying Using the MDX Query Editor
      6. Summary
    3. Introduction to MDX
      1. What Is MDX?
      2. Fundamental Concepts
      3. MDX Query
      4. MDX Expressions
      5. Operators
      6. MDX Functions
      7. Summary
    4. Working with Data Sources and Data Source Views
      1. Data Source
      2. Data Source Views
      3. Data Source Views in Depth
      4. Multiple Data Sources within a DSV
      5. Summary
    5. Dimension Design
      1. Working with the Dimension Wizard
      2. Working with the Dimension Editor
      3. Browsing the Dimension
      4. Sorting Members of a Level
      5. Optimizing Attributes
      6. Defining Translations in Dimensions
      7. Creating a Snowflake Dimension
      8. Creating a Time Dimension
      9. Creating a Parent-Child Hierarchy
      10. Summary
    6. Cube Design
      1. The Unified Dimensional Model
      2. Creating a Cube using Cube Wizard
      3. Cube Dimensions
      4. Measures and Measure Groups
      5. Calculated Members
      6. Creating Perspectives
      7. Creating Translations
      8. Browsing Perspectives and Translations
      9. Summary
  8. Advanced Topics
    1. Advanced Topics in MDX
      1. Calculation Fundamentals
      2. Restricting Cube Space/Slicing Cube Data
      3. Removing Empty Cells
      4. Filtering Members on Axes
      5. Ranking and Sorting
      6. Parameterize Your Queries
      7. New MDX Functions
      8. Summary
    2. Advanced Dimension Design
      1. Custom Rollups
      2. Enhancements to Parent-Child Hierarchies
      3. Using Properties to Customize Dimensions
      4. Dimension Intelligence using the Business Intelligence Wizard
      5. Server Time Dimension
      6. Dimension Writeback
      7. Summary
    3. Advanced Cube Design
      1. Measure Groups and Measures
      2. Adding and Enhancing Dimensions
      3. Adding Calculations to Your Cube
      4. Key Performance Indicators (KPIs)
      5. Drill-Through
      6. Actions
      7. Adding Intelligence to the Cube
      8. Working with Partitions
      9. Defining Security
      10. Summary
    4. Extending MDX using External Functions
      1. COM User Defined Functions
      2. Built-in COM UDFs
      3. .NET User Defined Functions (Stored Procedures)
      4. COM UDFs vs .NET Assemblies
      5. Summary
  9. Administration, Performance Tuning Integration
    1. Updating Your UDM Data
      1. Updating Dimension Data in UDM
      2. Updating Your Cube Data
      3. Summary
    2. Administering Analysis Services
      1. Administration using SQL Server 2005 Tools
      2. Online Mode
      3. Administration via Programming
      4. HTTP Connectivity to Analysis Services
      5. Legacy DSO Applications
      6. Analysis Services and Fail-over Clustering
      7. Summary
    3. Performance Optimization
      1. Optimizing UDM Design
      2. Optimize for Processing
      3. Query Performance Improvement
      4. Scalability Optimizations
      5. Using Profiler to Analyze Performance
      6. Summary
    4. Data Mining
      1. The Data Mining Process
      2. Real-World Applications
      3. Data Mining Algorithms in Analysis Services 2005
      4. Working with Mining Models
      5. Summary
    5. Analyzing Cubes using Office Client Components
      1. Microsoft Excel Pivot Tables
      2. Creating Local Cubes from Excel
      3. Office Web Component Pivot Table
      4. Analyzing UDM Data using Microsoft Data Analyzer
      5. Summary
    6. Integration Services
      1. Creating an Integration Services Project
      2. Creating Integration Services Packages for Analysis Services Operations
      3. Automating SSIS Packages
      4. Summary
  10. Scenarios
    1. Reporting Services
      1. Report Designer
      2. Report Server
      3. Creating a Report on Relational Database
      4. Creating Reports Based on a UDM
      5. Managing Your Analysis Services Reports
      6. Ad-Hoc Reports using Report Builder
      7. Summary
    2. Designing Real-Time Cubes
      1. Proactive Caching
      2. Proactive Caching at Work
      3. Long Latency Scenario
      4. Average Latency Scenario
      5. No Latency Scenario
      6. Summary
    3. Securing Your Data in Analysis Services
      1. Securing Your Source Data
      2. Securing Your Dimension Data
      3. Securing your Cube Data
      4. Summary
    4. MDX Function and Operator Reference
      1. Index to Functions
      2. Basic Operators
      3. Function and Operator Reference
  11. Index