Microsoft® SQL Server® 2008 Analysis Services Step by Step

Microsoft® SQL Server® 2008 Analysis Services Step by Step

Released April 2009
Publisher(s): Microsoft Press
ISBN: 9780735626201

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

Teach yourself to use SQL Server 2008 Analysis Services for business intelligence one step at a time. You'll start by building your understanding of the business intelligence platform enabled by SQL Server and the Microsoft Office System, highlighting the role of Analysis Services. Then, you'll create a simple multidimensional OLAP cube and progressively add features to help improve, secure, deploy, and maintain an Analysis Services database. You'll explore core Analysis Services 2008 features and capabilities, including dimension, cube, and aggregation design wizards; a new attribute relationship designer; designer AMO warnings; and using dynamic management views to monitor resources. And as you complete each lesson, you can hone your skills using the practice exercises from the companion CD. Plus, you can review and download code samples illustrating the author's own, professional techniques direct from the companion Web site.

Table of contents

  1. Microsoft® SQL Server® 2008 Analysis Services Step by Step
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    2. A Note Regarding Supplemental Files
    3. Acknowledgments
    4. Introduction
      1. Finding Your Best Starting Point in This Book
      2. Conventions and Features in This Book
        1. Conventions
      3. Hardware and Software Requirements
      4. Practice Files and SQL Server 2008 Configuration
        1. Installing the Practice Files
        2. Configuring SQL Server 2008
        3. Uninstalling the Practice files
      5. Find Additional Content Online
      6. Support for This Book
        1. Questions and Comments
    5. I. Understanding Business Intelligence and Analysis Services
      1. 1. Business Intelligence: A Data Analysis Foundation
        1. Introducing Business Intelligence
        2. Multidimensional Data Analysis
          1. Attributes in Data Analysis
          2. Hierarchies in Data Analysis
          3. Dimensions in Data Analysis
        3. Understanding a Dimensional Data Warehouse
          1. A Fact Table
          2. Dimension Tables
          3. Surrogate Keys and Slowly Changing Dimensions
          4. Alternative Table Structures
        4. Multidimensional OLAP
      2. 2. Understanding OLAP and Analysis Services
        1. Understanding OLAP
          1. Consistently Fast Response
          2. Metadata-Based Queries
          3. Spreadsheet-Style Formulas
        2. Understanding Analysis Services
          1. Analysis Services and Speed
          2. Analysis Services and Metadata
            1. Dimension Metadata
            2. Cube Metadata
            3. Analysis Services Formulas
            4. Finance Formulas
        3. Analysis Services and the Microsoft Business Intelligence Platform
        4. Analysis Services Tools
      3. 3. Accessing Source Data
        1. Creating a Business Intelligence Solution
        2. Creating a Data Source
        3. Creating a Data Source View
          1. Modifying a Data Source View
    6. II. Design Fundamentals
      1. 4. Creating Dimensions
        1. Previewing Dimension Data
        2. Creating a Standard Dimension
          1. Deploying an Analysis Services Database
          2. Modifying a Dimension
        3. Creating a Time Dimension
        4. Creating a Parent-Child Dimension
      2. 5. Creating a Cube
        1. Previewing Cube Data
        2. Using the Wizard to Create a Cube
          1. Role-Playing Dimensions
        3. Deploying and Browsing a Cube
        4. Using the Cube Designer to Modify a Cube
          1. Adding User-Friendly Names to a Cube
          2. Formatting Measures
          3. Modifying the Interaction of Dimensions and Measure Groups
          4. Adding Measures and Measure Groups to a Cube
          5. Creating a Calculated Member
          6. Redeploying and Browsing a Cube
      3. 6. Creating Advanced Measures and Calculations
        1. Using Aggregate Functions
        2. Using MDX to Retrieve Values from a Cube
        3. Creating Calculated Members
          1. Applying Conditional Formatting
          2. Calculating Contribution
          3. Creating Calculated Members Outside of the Measures Dimension
        4. Calculation Scripting
        5. Creating KPIs
      4. 7. Advanced Dimension Design
        1. Dimension Usage
        2. Creating Reference Dimensions
        3. Creating a Fact Dimension
        4. Creating a Many-to-Many Dimension
    7. III. Advanced Design
      1. 8. Working with Account Intelligence
        1. Designing a Financial Analysis Cube
        2. Working with Account Intelligence
        3. Creating an Account Dimension
          1. Working with Non-Additive Financial Measures
          2. Creating a Non-Additive Calculated Member
          3. Working with a Scenario Dimension
      2. 9. Currency Conversion and Multiple Languages
        1. Supporting Foreign Currency Conversion
        2. Supporting Foreign Language Translation
      3. 10. Interacting with a Cube
        1. Implementing Actions
          1. Creating Standard Actions
          2. Creating Drillthrough Actions
          3. Linking to Reporting Services Reports
        2. Using Writeback to Modify Analysis Services Data
          1. Enabling Dimension Writeback
          2. Enabling Cube Writeback
      4. 11. Retrieving Data from Analysis Services
        1. Creating Perspectives
        2. Creating MDX Queries
        3. Accessing Analysis Services Using Excel 2007
        4. Creating Reporting Services Reports
      5. 12. Implementing Security
        1. Understanding Roles
        2. Securing Administrative Access
          1. Assigning Server-Level Administrative Access
          2. Assigning Database-Level Administrative Access
        3. Securing Data Access
          1. Granting Access to Cubes
          2. Restricting Access to Dimension Members
          3. Restricting Access to Cells
      6. 13. Designing Aggregations
        1. Understanding Aggregation Design
        2. Using the Aggregation Design Wizard
          1. Attribute Relationships, User-Defined Hierarchies, and Aggregation Design
          2. Designing an Aggregation
          3. Changing Partition Counts
        3. Using the Usage-Based Optimization Wizard
    8. IV. Production Management
      1. 14. Managing Partitions and Database Processing
        1. Working with Storage
          1. Understanding Dimension Storage Modes
          2. Understanding Partition Storage Modes
          3. Changing Data in a Warehouse
        2. Managing Analysis Services Processing
          1. Processing a Dimension
          2. Processing a Cube
          3. Proactive Caching
        3. Working with Partitions
          1. Understanding Partition Strategies
          2. Creating Partitions
      2. 15. Managing Deployment
        1. Deployment Overview
        2. Deployment Mechanics
        3. Deployment Using Business Intelligence Development Studio
        4. Deployment Using the Deployment Wizard
          1. Deployment Wizard UI
        5. Understanding Deployment Scripts
        6. Migrating Databases and Disaster Recovery
          1. Detaching and Attaching an Analysis Services Database
          2. Synchronizing Databases
          3. Backing Up and Restoring a Database
      3. 16. Advanced Monitoring and Management Tools
        1. Monitoring Analysis Services Using Windows Reliability And Performance Monitor
        2. Monitoring Analysis Services Using SQL Server Profiler
        3. Analysis Services Dynamic Management Views
    9. About the Author
      1. Contributing Authors
    10. Index
    11. SPECIAL OFFER: Upgrade this ebook with O’Reilly

Product information

  • Title: Microsoft® SQL Server® 2008 Analysis Services Step by Step
  • Author(s):
  • Release date: April 2009
  • Publisher(s): Microsoft Press
  • ISBN: 9780735626201