Pro SQL Server 2008 Analysis Services

Pro SQL Server 2008 Analysis Services

Released June 2010
Publisher(s): Apress
ISBN: 9781430219958

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

Every business has a reams of business data locked away in databases, business systems, and spreadsheets. While you may be able to build some reports by pulling a few of these repositories together, actually performing any kind of analysis on the data that runs your business can range from problematic to impossible. Pro SQL Server 2008 Analysis Services will show you how to pull that data together and present it for reporting and analysis in a way that makes the data accessible to business users, instead of needing to rely on the IT department every time someone needs a different report.

  • Accessible-With a single author's voice, this book conducts a guided tour through the technology that makes it easy to dive into.

  • Solution-oriented-While technically deep, the goal is to focus on practical application of the technologies instead of acting as a technical manual.

  • ComprehensiveThis book covers every aspect of Analysis Services and ancillary technologies to enable you to make the most of SQL Server.

Table of contents

  1. Copyright
  2. About the Authors
  3. About the Technical Reviewers
  4. Acknowledgments
  5. Introduction
  6. 1. Introduction to OLAP
    1. 1.1. From Pivot Tables to Dimensional Processing
    2. 1.2. Data Warehousing
    3. 1.3. Applications of OLAP
    4. 1.4. History of OLAP
    5. 1.5. SQL Server Analysis Services
    6. 1.6. Data Mining
    7. 1.7. Summary
  7. 2. Cubes, Dimensions, and Measures
    1. 2.1. Cubes and Their Components
    2. 2.2. Defining Measures and Dimensions
      1. 2.2.1. Schemas
      2. 2.2.2. Dimensions in Depth
        1. 2.2.2.1. Attributes
        2. 2.2.2.2. Slowly Changing Dimensions (SCD)
        3. 2.2.2.3. Hierarchies
        4. 2.2.2.4. Time Dimensions
      3. 2.2.3. Measures
      4. 2.2.4. Types of Aggregation
        1. 2.2.4.1. Additive
        2. 2.2.4.2. Semiadditive
        3. 2.2.4.3. Nonadditive
      5. 2.2.5. Writeback
      6. 2.2.6. Calculated Measures
    3. 2.3. Actions
    4. 2.4. XMLA
    5. 2.5. Multidimensional Expressions (MDX)
    6. 2.6. Data Warehouses
    7. 2.7. Storage
      1. 2.7.1. Staging Databases
      2. 2.7.2. Storage Modes
        1. 2.7.2.1. MOLAP
        2. 2.7.2.2. ROLAP
        3. 2.7.2.3. HOLAP
    8. 2.8. Summary
  8. 3. SQL Server Analysis Services
    1. 3.1. Requirements
      1. 3.1.1. Hardware
      2. 3.1.2. Virtualization
      3. 3.1.3. Software
      4. 3.1.4. Upgrading
      5. 3.1.5. Standard or Enterprise Edition?
    2. 3.2. Architecture
      1. 3.2.1. The Unified Dimensional Model
      2. 3.2.2. Logical Architecture
      3. 3.2.3. Physical Architecture
      4. 3.2.4. Storage
    3. 3.3. Cube Structures in SSAS
      1. 3.3.1. Data Sources
      2. 3.3.2. Data Source View
      3. 3.3.3. The Cube Structure Itself
      4. 3.3.4. Dimensions
      5. 3.3.5. Mining Structures
    4. 3.4. What's New in SQL Server 2008
      1. 3.4.1. Performance
        1. 3.4.1.1. Management Data Warehouse
        2. 3.4.1.2. Reliable Query Cancel
        3. 3.4.1.3. Dynamic Management Views
        4. 3.4.1.4. Block Computing—Optimization
        5. 3.4.1.5. Writeback Performance Improvements
        6. 3.4.1.6. Change Data Capture
      2. 3.4.2. Tools
        1. 3.4.2.1. Dimension Design
        2. 3.4.2.2. Aggregation / UBO Designer
        3. 3.4.2.3. AMO Design Warnings
    5. 3.5. Summary
  9. 4. SSAS Developer and Admin Interfaces
    1. 4.1. Business Intelligence Development Studio
      1. 4.1.1. BIDS Is Visual Studio?
      2. 4.1.2. Panes
      3. 4.1.3. Solution Explorer
      4. 4.1.4. Properties Pane
      5. 4.1.5. Creating or Editing a Database Solution
        1. 4.1.5.1. Create a New Analysis Services Project
        2. 4.1.5.2. Open an Existing SSAS Database
        3. 4.1.5.3. Open an Existing SSAS Database as Part of a Project
    2. 4.2. SQL Server Management Studio
      1. 4.2.1. Managing Analysis Services
        1. 4.2.1.1. Data Sources
        2. 4.2.1.2. Data Source Views
        3. 4.2.1.3. Cubes
        4. 4.2.1.4. Dimensions
        5. 4.2.1.5. Mining Structures
        6. 4.2.1.6. Roles
      2. 4.2.2. Executing MDX Queries
    3. 4.3. PowerShell
      1. 4.3.1. A Convincing Example
      2. 4.3.2. PowerShell for SQL Server
      3. 4.3.3. PowerShell with SSAS
    4. 4.4. Summary
  10. 5. Creating a Data Source View
    1. 5.1. Cubes Need Data
    2. 5.2. Data Sources
    3. 5.3. Data Source Views
      1. 5.3.1. Designer Tour
        1. 5.3.1.1. Finding Tables
        2. 5.3.1.2. Replacing a Table
      2. 5.3.2. Named Calculations and Queries
        1. 5.3.2.1. Named Calculations
        2. 5.3.2.2. Named Queries
    4. 5.4. Summary
  11. 6. Creating Dimensions
    1. 6.1. Dimensional Analysis
      1. 6.1.1. Review of the Dimension Concept
      2. 6.1.2. Star or Snowflake?
        1. 6.1.2.1. The Advantage of Simplicity
    2. 6.2. Dimensions in SSAS
      1. 6.2.1. Creating a Dimension
      2. 6.2.2. Analysis Management Objects (AMO) Warnings
      3. 6.2.3. Dimension Properties
        1. 6.2.3.1. Dimension Type
        2. 6.2.3.2. ErrorConfiguration
        3. 6.2.3.3. MdxMissingMemberMode
        4. 6.2.3.4. Processing
        5. 6.2.3.5. Storage
        6. 6.2.3.6. UnknownMember
        7. 6.2.3.7. WriteEnabled
    3. 6.3. Attributes
      1. 6.3.1. Attribute Relationships
      2. 6.3.2. Attribute Properties
    4. 6.4. Parent-Child Dimensions
    5. 6.5. The Time Dimension
    6. 6.6. Summary
  12. 7. Building a Cube
    1. 7.1. Dimensions and Cubes
      1. 7.1.1. Creating Cubes
      2. 7.1.2. Using Measure Group Tables
      3. 7.1.3. Selecting Dimensions
      4. 7.1.4. Defining Dimension Usage
    2. 7.2. Measures and Measure Groups
      1. 7.2.1. Measures
      2. 7.2.2. Measure Groups
      3. 7.2.3. Calculated Measures
    3. 7.3. Summary
  13. 8. Deploying and Processing
    1. 8.1. Deploying a Project
      1. 8.1.1. Project Properties
      2. 8.1.2. Deployment Methods
    2. 8.2. Using the Deployment Wizard
      1. 8.2.1. Running the Wizard
      2. 8.2.2. Input Files
      3. 8.2.3. Deployment Scripts
    3. 8.3. Synchronizing SSAS Databases
    4. 8.4. Processing
      1. 8.4.1. What Processing Does for Us
      2. 8.4.2. How to Initiate Processing from BIDS
      3. 8.4.3. Processing from SQL Server Management Studio
      4. 8.4.4. Processing via XMLA
      5. 8.4.5. Processing with Analysis Management Objects (AMO)
    5. 8.5. Scheduling OLAP Maintenance
    6. 8.6. Summary
  14. 9. MDX
    1. 9.1. Why the Need?
    2. 9.2. Tuples and Sets
      1. 9.2.1. Notation
      2. 9.2.2. Tuples
      3. 9.2.3. Sets
    3. 9.3. MDX Queries
      1. 9.3.1. SELECT
      2. 9.3.2. WHERE
      3. 9.3.3. MDX Functions
      4. 9.3.4. Categories of Functions
        1. 9.3.4.1. Tree Functions
        2. 9.3.4.2. Aggregate Functions
        3. 9.3.4.3. Time Functions
    4. 9.4. Summary
  15. 10. Cube Features
    1. 10.1. Business Intelligence
      1. 10.1.1. Time Intelligence
      2. 10.1.2. Account Intelligence
      3. 10.1.3. Dimension Intelligence
    2. 10.2. Operators, Functions, and More
      1. 10.2.1. Unary Operators
      2. 10.2.2. Custom Member Formulas
      3. 10.2.3. Attribute Ordering
      4. 10.2.4. Currency Conversion
    3. 10.3. Calculations Tab
      1. 10.3.1. Calculated Measures
      2. 10.3.2. Named Sets
    4. 10.4. Other Cube Features
      1. 10.4.1. Key Performance Indicators
      2. 10.4.2. Actions
      3. 10.4.3. Perspectives
      4. 10.4.4. Translations
    5. 10.5. Summary
  16. 11. Data Mining
    1. 11.1. Why Mine Data?
    2. 11.2. Using Data-Mining Algorithms
      1. 11.2.1. Microsoft Naïve Bayes
      2. 11.2.2. Microsoft Clustering
      3. 11.2.3. Microsoft Decision Trees
    3. 11.3. Creating the Accessory Buyers Marketing Campaign
    4. 11.4. Preparing the Data Warehouse
      1. 11.4.1. Creating the Accessory Buyers Views in AdventureWorks
      2. 11.4.2. Creating the Accessory Campaign Data Source View
    5. 11.5. Finding Accessory Buyers by Using the AdventureWorks EDW
    6. 11.6. Using the Data Mining Model Designer
      1. 11.6.1. The Mining Structure View
      2. 11.6.2. The Mining Models View
      3. 11.6.3. The Mining Model Viewer View
        1. 11.6.3.1. Exploring the Decision Tree
        2. 11.6.3.2. Using Drill-Through to View a Model's Training Cases
        3. 11.6.3.3. Using the Dependency Network
      4. 11.6.4. The Mining Accuracy Chart View
      5. 11.6.5. The Mining Model Prediction View
    7. 11.7. Finding Accessory Buyers by Using Data Mining Extensions (DMX)
      1. 11.7.1. Use the DMX Development Environment
      2. 11.7.2. Create the Accessory Buyers Mining Structure
      3. 11.7.3. Add a Naïve Bayes Mining Model to the Accessory Buyers Campaign
      4. 11.7.4. Process the Accessory Buyers Campaign
      5. 11.7.5. View the Accessory Buyers Mining Model
      6. 11.7.6. Predict Our Accessory Buyers
    8. 11.8. Summary
  17. 12. PowerPivot
    1. 12.1. PowerPivot Support in SQL Server 2008 R2
      1. 12.1.1. Master Data Services
      2. 12.1.2. Excel Writeback
    2. 12.2. PowerPivot from Excel
    3. 12.3. PowerPivot with SharePoint Server 2010
    4. 12.4. Summary
  18. 13. Administration
    1. 13.1. DBA Tasks
      1. 13.1.1. Processing a Cube
      2. 13.1.2. Processing Options
      3. 13.1.3. Processing Architecture
      4. 13.1.4. Profiler
      5. 13.1.5. Performance Monitor
      6. 13.1.6. Automation
      7. 13.1.7. XML for Analysis
      8. 13.1.8. Analysis Management Objects
      9. 13.1.9. PowerShell
      10. 13.1.10. Scheduling
        1. 13.1.10.1. SQL Server Agent
      11. 13.1.11. SQL Server Integration Services
    2. 13.2. Security
      1. 13.2.1. Authentication
      2. 13.2.2. Authorization
    3. 13.3. Performance
      1. 13.3.1. Design
        1. 13.3.1.1. Dimensions
        2. 13.3.1.2. Attribute Design
        3. 13.3.1.3. Partitions
      2. 13.3.2. Aggregations
        1. 13.3.2.1. Understanding Aggregations
        2. 13.3.2.2. Creating an Aggregation
        3. 13.3.2.3. Preparing an Aggregation for Use
      3. 13.3.3. Scaling
        1. 13.3.3.1. Large Data Size
        2. 13.3.3.2. Large Number of Users
      4. 13.3.4. Virtualization
      5. 13.3.5. SharePoint Server 2010
    4. 13.4. Summary
  19. 14. User Interfaces
    1. 14.1. Excel 2007
      1. 14.1.1. Data Source Connections
      2. 14.1.2. Pivot Tables
        1. 14.1.2.1. Pivot Table Task Pane
        2. 14.1.2.2. Pivot Table Ribbons
        3. 14.1.2.3. Formatting a Pivot Table
        4. 14.1.2.4. Formulas Using Pivot Table Values
      3. 14.1.3. Pivot Charts
    2. 14.2. Visio 2007
    3. 14.3. SQL Server Reporting Services
      1. 14.3.1. Reports
      2. 14.3.2. Tablix
      3. 14.3.3. Charts
      4. 14.3.4. Report Builder 2.0
    4. 14.4. MOSS 2007
      1. 14.4.1. KPI Lists
      2. 14.4.2. Excel Services
    5. 14.5. PerformancePoint
    6. 14.6. Summary
  20. A. Setting Up Adventure Works
  21. B. Data-Mining Resources
    1. B.1. Books
    2. B.2. Web Sites

Product information

  • Title: Pro SQL Server 2008 Analysis Services
  • Author(s):
  • Release date: June 2010
  • Publisher(s): Apress
  • ISBN: 9781430219958