O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Microsoft® SQL Server™ 2005 Analysis Services Step by Step

Microsoft® SQL Server™ 2005 Analysis Services Step by Step

by Hitachi Consulting, Stacia Misner, Reed Jacobsen
Publisher: Microsoft Press
Release Date: March 2006
ISBN: 0735621993
View table of contents
Start reading

Book Description

Your hands-on guide to learning Microsoft SQL Server Analysis Services, Microsoft’s customizable analysis solution for business data modeling and interpretation. Analysis Services is a powerful tool for business intelligence, so understanding the essentials—how to create a cube, as well as how to deploy, customize, and extend the basic calculations—is critical to harnessing the full benefits of SQL Server. This step-by-step tutorial shows you how to get started, how to build scalable analytical applications, and how to use and administer advanced features. Interactivity features (enhanced in SQL Server 2005), data translation, and security are also covered in detail. With STEP BY STEP, you work at your own pace through hands-on, learn-by-doing exercises. Whether you’re a beginning programmer or new to this version of the technology, you’ll understand the fundamental techniques for using Analysis Services. A companion CD includes data sets and sample code.

Table of Contents

  1. Microsoft® SQL Server™ 2005 Analysis Services Step by Step
  2. A Note Regarding Supplemental Files
  3. Introduction
    1. Finding Your Best Starting Point
    2. About the Companion CD-ROM
    3. System Requirements
    4. Installing and Using the Sample Files
    5. Conventions and Features in This Book
  4. I. Getting Started with Analysis Services
    1. 1. Understanding Business Intelligence and Data Warehousing
      1. Introducing Business Intelligence
      2. Reviewing Data Warehousing Concepts
        1. The Purpose of a Data Warehouse
        2. The Structure of a Dimensional Database
        3. A Fact Table
        4. Dimension Tables
          1. Attributes That Change over Time
          2. Stars and Snowflakes
          3. Alternative Dimension Table Structures
      3. Chapter 1 Quick Reference
    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
          1. Finance Formulas
        4. Analysis Services Tools
      3. Chapter 2 Quick Reference
    3. 3. Building Your First Cube
      1. Exploring Business Intelligence Development Studio
        1. Examining the Contents of an Analysis Services Project
          1. Open the SSAS Step by Step solution
        2. Exploring Menu Commands
          1. Explore menu commands
      2. Preparing to Create a Cube
        1. Reviewing the Analysis Requirements
        2. Creating a New Analysis Services Project
          1. Start a new Analysis Services project
      3. Creating a Cube
        1. Using the Cube Wizard Without a Data Source
          1. Build a new cube
          2. Specify a data source
          3. Add measures
          4. Add dimensions
          5. Define time periods
          6. Add a special calendar
          7. Relate dimensions to measures
          8. Finish the Cube Wizard
        2. Reviewing the Cube Structure in the Cube Designer
          1. Review and modify the cube structure
      4. Generating a Schema
        1. Using the Schema Generation Wizard
          1. Create a relational schema
        2. Loading Data into the Relational Schema
          1. Loading the schema with data
      5. Processing and Browsing a Cube
        1. Deploying and Processing a Cube
          1. Deploy and process an Analysis Services project
        2. Browsing a Cube
          1. Browse a cube
      6. Chapter 3 Quick Reference
  5. II. Design Fundamentals
    1. 4. Designing Dimensions
      1. Reviewing the Data Warehouse Structure
      2. Building a Standard Dimension
        1. Adding a Data Source
          1. Add a data source to an Analysis Services project
        2. Creating a Data Source View
          1. Use the Data Source View Wizard
        3. Using the Dimension Wizard
          1. Create a standard dimension
        4. Deploying a Dimension
          1. Deploy a dimension to the Analysis Server
        5. Changing Attribute Properties
          1. Disable the AttributeHierarchyEnabled property
      3. Working with a Time Dimension
        1. Modifying a Data Source View
          1. Add tables to a data source view
        2. Creating a Time Dimension
          1. Build a time dimension
        3. Working with Role-Playing Dimensions
          1. Add an object to a project
          2. Add role-playing time dimensions to a cube
      4. Creating a Parent-Child Dimension
        1. Adding an Employee Dimension
          1. Create a parent-child dimension
        2. Totaling Data for Non–Leaf-Level Data Members
          1. Set the MembersWithData property
        3. Managing Levels within a Parent-Child Dimension
          1. Remove the All level from a hierarchy
          2. Create a level naming template for a parent-child hierarchy
      5. Chapter 4 Quick Reference
    2. 5. Designing Measure Groups and Measures
      1. Adding Measure Groups to a Cube
        1. Building a Cube
          1. Use the Cube Wizard
        2. Changing Properties for Measure Groups and Measures
          1. Rename measure groups
          2. Format measures
        3. Specifying Dimension Usage
          1. Review the relationship between cube dimensions and measure groups
        4. Browsing Multiple Measure Groups
          1. Examine the interaction between attributes and measure groups in a cube
      2. Aggregating Semiadditive Measures
        1. Adding a Measure Group to an Existing Cube
          1. Add a measure group to a cube
        2. Using a Semiadditive Aggregate Function
          1. Use the LastNonEmpty aggregate function
      3. Calculating Distinct Counts
        1. Adding a Distinct Count Measure
      4. Creating Simple Calculations
        1. Adding a Calculation to a Cube
          1. Add a weighted average calculation
        2. Applying Conditional Formatting
          1. Add a color expression to a calculation
      5. Chapter 5 Quick Reference
    3. 6. Working with a Finance Measure Group
      1. Designing an Account Dimension
        1. Working with Account Intelligence
          1. Create an Account dimension
        2. Using Unary Operators
          1. Browse the default aggregations of an Account dimension
          2. Set the UnaryOperatorColumn property
        3. Aggregating by Account
          1. Use the LastNonEmpty function with an Account dimension
          2. Use the ByAccount aggregate function
      2. Designing Nonadditive Financial Measures
        1. Creating a Nonadditive Measure
          1. Add an Operating Profit Per Employee measure
      3. Chapter 6 Quick Reference
    4. 7. Designing Aggregations and Hierarchies
      1. Understanding Aggregation Design
        1. Using the Aggregation Design Wizard
          1. Design aggregations for a cube partition
        2. Inspecting Aggregations
          1. Examine aggregations for a dimension in a cube partition
        3. Changing Partition Counts
          1. Edit fact table and granularity attribute counts
        4. Adding Attributes to the Aggregation Design
          1. Set the AggregationUsage property for an attribute
      2. Designing User Hierarchies
        1. Adding a User Hierarchy
          1. Add user hierarchies to a dimension
        2. Aggregating User Hierarchies
          1. Update the aggregation design with attributes from a user hierarchy
      3. Optimizing Aggregations
        1. Using the Query Log
          1. Populate the query log
        2. Viewing Usage Data
          1. Browse the query log
        3. Using the Usage-Based Optimization Wizard
          1. Design usage-based aggregations
        4. Maintaining the Query Log
          1. Delete records from the query log
      4. Chapter 7 Quick Reference
  6. III. Advanced Design
    1. 8. Using MDX
      1. Creating Tuple-Based Calculated Members
        1. Creating an MDX Calculation for Percent of Total
          1. Add a Percent of Total calculation to a cube
        2. Creating an MDX Calculation for Percent of Parent
          1. Add a Percent of Parent calculation to a cube
      2. Querying with MDX
        1. Executing MDX Queries
          1. Use SQL Server Management Studio to execute MDX queries
        2. Working with Basic MDX Queries
          1. Use sets and set functions in MDX queries
      3. Designing Custom Members
        1. Creating a Calculated Member Using a Set-Based Function
          1. Use the Avg function
        2. Creating Cumulative Calculations
          1. Compare the Sum and PeriodsToDate functions
      4. Working with MDX Scripts
        1. Managing the Sequence of Calculations
          1. Organize scripts
        2. Adding a Script Assignment
          1. Override cube values with a script assignment
      5. Developing Key Performance Indicators
        1. Comparing Cube Values to Goals
          1. Create a simple key performance indicator
        2. Using MDX Expressions with Key Performance Indicators
          1. Create a ratio key performance indicator
      6. Chapter 8 Quick Reference
    2. 9. Exploring Special Features
      1. Defining Dimension Relationships
        1. Using a Referenced Relationship Type
          1. Create a referenced dimension
        2. Using a Many-to-Many Relationship Type
          1. Build a many-to-many dimension relationship
      2. Supporting Currency Conversions
      3. Localizing Cubes
        1. Adding Translations
          1. Add translated captions and attribute values
        2. Browsing Translations
          1. Change language in the cube browser
      4. Organizing Information with Folders and Perspectives
        1. Organizing Measures
          1. Use display folders for measures and calculations
        2. Using Perspectives
          1. Create and browse a perspective
      5. Chapter 9 Quick Reference
    3. 10. Interacting with Cubes
      1. Implementing Actions
        1. Using Standard Actions
          1. Create a URL action
        2. Linking to Reports
          1. Create a reporting action
        3. Adding Drillthrough
          1. Create a drillthrough action
      2. Using Writeback
        1. Write-Enabling a Dimension
          1. Create a write-enabled dimension
        2. Dynamically Adding Members to a Dimension
          1. Write back a new dimension member
        3. Modifying the Cube Structure for Writeback
          1. Create a unit forecast measure group
        4. Writing Values Back to a Cube
          1. Write back values
      3. Chapter 10 Quick Reference
  7. IV. Production Management
    1. 11. Implementing Security
      1. Using Role-Based Security
        1. Creating Security Roles
          1. Create sample users and groups
          2. Create an Analysis Services role for all users
        2. Managing Roles
          1. Copy roles
      2. Applying Security to a Dimension
        1. Restricting Access to a Dimension
          1. Fully restrict a dimension
        2. Restricting Access to Specific Members of a Dimension
          1. Restrict the members of a dimension
        3. Controlling Visual Totals for a Dimension
          1. Enable visual totals
        4. Defining a Default Member for a Dimension
          1. Create a default dimension member for a role
      3. Securing Data at the Cell Level
        1. Preventing Values in Cells from Being Read
          1. Add an MDX expression to enable read permissions for specific cube cells
        2. Allowing Users to Write to Cells
          1. Enable user writeback
      4. Setting Administration Security
        1. Creating Security Roles for Processing
          1. Create a database role with cube processing permissions
      5. Chapter 11 Quick Reference
    2. 12. Managing Partitions and Database Processing
      1. Managing Very Large Databases
        1. Understanding Partition Strategies
        2. Creating Partitions
          1. Create partitions based on fact table filters
        3. Merging Partitions
          1. Merge two partitions
      2. Working with Storage
        1. Understanding Analysis Services Storage Modes
        2. Setting Storage Options
          1. Set storage options for sample cubes
        3. Changing Data in a Warehouse
          1. Browse data after updating the warehouse
      3. Managing OLAP Processing
        1. Processing a Dimension
          1. Update a dimension
        2. Processing a Cube
          1. Update data in a cube incrementally
        3. Configuring Proactive Caching
          1. Set caching and latency options
      4. Monitoring Cube Activity
        1. Profiling Analysis Services Queries
          1. Use SQL Server Profiler to monitor queries
        2. Using the Performance Monitor
          1. Monitor a performance counter
      5. Chapter 12 Quick Reference
    3. 13. Managing Deployment
      1. Reviewing Deployment Options
        1. Building a Database
          1. Use the Build command
        2. Deploying a Database
          1. Use the Analysis Services Deployment Wizard
        3. Processing a Database
          1. Use SQL Server Management Studio to process a database
      2. Managing Database Objects Programmatically
        1. Working with XMLA Scripts
          1. Create and execute a processing script
      3. Automating Database Processing
        1. Creating a SQL Server Integration Services Package
          1. Create a package
        2. Using the Analysis Services Processing Task
          1. Create an Analysis Services Processing Task
        3. Handling Task Failures
          1. Send an e-mail message if the task fails
        4. Scheduling a SQL Server Integration Services Package
          1. Create a package schedule
      4. Planning for Disaster and Recovery
        1. Backing Up an Analysis Services Database
          1. Back up a database
        2. Restoring an Analysis Services Database
          1. Restore a database
      5. Chapter 13 Quick Reference
  8. Glossary
  9. About the Authors
    1. Contributing Authors
  10. Index
  11. About the Authors
  12. Copyright