Microsoft® SQL Server® 2008 MDX Step by Step

Book description

Teach yourself the Multidimensional Expressions (MDX) query language—one step at a time. With this practical, learn-by-doing tutorial, you’ll build the core techniques for using MDX with Analysis Services to deliver high-performance business intelligence solutions.

Discover how to:

  • Construct and execute MDX queries

  • Work with tuples, sets, and expressions

  • Build complex sets to retrieve the exact data users need

  • Perform aggregation functions and navigate data hierarchies

  • Assemble time-based business metrics

  • Customize an Analysis Services cube through the MDX script

  • Implement dynamic security to control data access

  • Develop MDX-driven reports in SQL Server Reporting Services

  • CD features:

  • Practice exercises

  • Database and code samples

  • Fully searchable eBook

  • Table of contents

    1. List of Figures
    2. List of Tables
    3. Acknowledgements
    4. Introduction
      1. Who This Book Is For
      2. What This Book Is About
      3. Conventions and Features in This Book
        1. Conventions
        2. Other Features
      4. System Requirements
      5. Samples
        1. Installing the Samples
        2. Uninstalling the Samples
        3. Find Additional Content Online
      6. Support for This Book
        1. Questions and Comments
    5. I. MDX Fundamentals
      1. 1. Welcome to MDX
        1. The Business Intelligence Landscape
        2. The Dimensional Model
        3. Implementing the Dimensional Model
          1. The Relational Data Warehouse
          2. The Multidimensional Data Warehouse
        4. The MDX Language
        5. Chapter 1 Quick Reference
      2. 2. Using the MDX Query Editor
        1. SQL Server Management Studio
        2. The MDX Query Editor
        3. Building a Simple MDX Query
        4. Exploring the Step-by-Step Cube
        5. Building a More Complex Query
        6. Chapter 2 Quick Reference
      3. 3. Understanding Tuples
        1. N-dimensional Space
        2. Cube Space
        3. Accessing Data with Tuples
        4. Understanding Cells
        5. Working with Partial Tuples
        6. Building Tuples with User-Hierarchies
          1. Understanding User-Hierarchy Translation
          2. Avoiding Reference Conflicts
          3. Member Reference Shortcuts
        7. Chapter 3 Quick Reference
      4. 4. Working with Sets
        1. Set Basics
        2. Understanding the SELECT Statement
        3. Building Sets with Functions
          1. The Members Function
          2. The Crossjoin Function
        4. Limiting Sets
          1. Working with Auto-Exists
          2. The Exists Function
        5. Chapter 4 Quick Reference
      5. 5. Working with Expressions
        1. Expression Basics
        2. Calculated Members
        3. Building Dynamic Expressions
        4. Resolving Contextual Conflicts
          1. Avoiding Infinite Recursion
          2. Controlling Solve Order
        5. Building Complex Expressions
          1. Working with the Current Member
          2. Working with Sets in Expressions
        6. Chapter 5 Quick Reference
    6. II. MDX Functions
      1. 6. Building Complex Sets
        1. Assembling Ordered Sets
        2. Retrieving the First or Last Tuples of a Set
        3. Filtering Sets
        4. Combining Sets
        5. Performing Advanced Set Construction
          1. Assembling Sets with the Generate Function
          2. Assembling Sets with the Extract Function
        6. Chapter 6 Quick Reference
      2. 7. Performing Aggregation
        1. Performing Summation
        2. Calculating Averages
          1. Calculating Averages with the Avg Function
          2. Calculating Averages with Expressions
        3. Identifying Minimum and Maximum Values
        4. Counting Tuples in Sets
        5. Chapter 7 Quick Reference
      3. 8. Navigating Hierarchies
        1. Accessing Immediate Relatives
        2. Accessing Extended Relatives
        3. Navigating within a Level
        4. Chapter 8 Quick Reference
      4. 9. Working with Time
        1. Understanding the Time Dimension
        2. Calculating an Accumulating Total
          1. Calculating Rolling Averages
        3. Performing Period-over-Period Analysis
        4. Combining Time-Based Metrics
        5. Chapter 9 Quick Reference
    7. III. MDX Applications
      1. 10. Enhancing the Cube
        1. Understanding the MDX Script
        2. Constructing Calculated Members
          1. Assembling a Basic Calculated Member
          2. Setting Calculated Member Properties
        3. Assembling Named Sets
        4. Chapter 10 Quick Reference
      2. 11. Implementing Dynamic Security
        1. Understanding Dynamic Security
        2. Implementing Attribute-Hierarchy Restrictions
          1. Restricting Standard Attribute-Hierarchies
          2. Restricting Parent-Child Hierarchies
        3. Implementing Cell-Level Restrictions
        4. Chapter 11 Quick Reference
      3. 12. Building Reports
        1. Getting Started
        2. Connecting to Analysis Services
        3. Designing the Dataset
        4. Adding Parameters to the Dataset
        5. Presenting the Data in the Report
        6. Chapter 12 Quick Reference
    8. Appendix
      1. Bryan C. Smith
      2. C. Ryan Clay
      3. Hitachi Consulting
    9. Index
    10. About the Authors
    11. Copyright

    Product information

    • Title: Microsoft® SQL Server® 2008 MDX Step by Step
    • Author(s): C Ryan Clay Bryan C Smith and Hitachi Consulting
    • Release date: February 2009
    • Publisher(s): Microsoft Press
    • ISBN: 9780735626188