The Microsoft® Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft® Business Intelligence Toolset, Second Edition

The Microsoft® Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft® Business Intelligence Toolset, Second Edition

by Joy Mundy, Warren Thornthwaite, Ralph Kimball
Released March 2011
Publisher(s): Wiley
ISBN: 9780470640388

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

Best practices and invaluable advice from world-renowned data warehouse experts

In this book, leading data warehouse experts from the Kimball Group share best practices for using the upcoming "Business Intelligence release" of SQL Server, referred to as SQL Server 2008 R2. In this new edition, the authors explain how SQL Server 2008 R2 provides a collection of powerful new tools that extend the power of its BI toolset to Excel and SharePoint users and they show how to use SQL Server to build a successful data warehouse that supports the business intelligence requirements that are common to most organizations. Covering the complete suite of data warehousing and BI tools that are part of SQL Server 2008 R2, as well as Microsoft Office, the authors walk you through a full project lifecycle, including design, development, deployment and maintenance.

  • Features more than 50 percent new and revised material that covers the rich new feature set of the SQL Server 2008 R2 release, as well as the Office 2010 release

  • Includes brand new content that focuses on PowerPivot for Excel and SharePoint, Master Data Services, and discusses updated capabilities of SQL Server Analysis, Integration, and Reporting Services

  • Shares detailed case examples that clearly illustrate how to best apply the techniques described in the book

  • The accompanying Web site contains all code samples as well as the sample database used throughout the case studies

The Microsoft Data Warehouse Toolkit, Second Edition provides you with the knowledge of how and when to use BI tools such as Analysis Services and Integration Services to accomplish your most essential data warehousing tasks.

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. Foreword
  6. Introduction
    1. The Data Warehouse and Business Intelligence System
      1. The Kimball Group
      2. Why We Wrote This Book
      3. Who Should Read This Book
    2. The Kimball Lifecycle
      1. Lifecycle Tracks and Task Areas
      2. Key Terminology and the Microsoft Toolset
      3. Roles and Responsibilities
    3. How This Book Is Organized
      1. Part 1: Requirements, Realities, and Architecture
        1. Chapter 1: Defining Business Requirements
        2. Chapter 2: Designing the Business Process Dimensional Model
        3. Chapter 3: The Toolset
        4. Chapter 4: System Setup
      2. Part 2: Building and Populating the Databases
        1. Chapter 5: Creating the Relational Data Warehouse
        2. Chapter 6: Master Data Management
        3. Chapter 7: Designing and Developing the ETL System
        4. Chapter 8: The Core Analysis Services OLAP Database
        5. Chapter 9: Design Requirements for Real-Time BI
      3. Part 3: Developing the BI Applications
        1. Chapter 10: Building BI Applications in Reporting Services
        2. Chapter 11: PowerPivot and Excel
        3. Chapter 12: The BI Portal and SharePoint
        4. Chapter 13: Incorporating Data Mining
      4. Part 4: Deploying and Managing the DW/BI System
        1. Chapter 14: Designing and Implementing Security
        2. Chapter 15: Metadata Plan
        3. Chapter 16: Deployment
        4. Chapter 17: Operations and Maintenance
        5. Chapter 18: Present Imperatives and Future Outlook
    4. Additional Information
    5. On the Website
  7. 1. Requirements, Realities, and Architecture
    1. 1. Defining Business Requirements: Building the foundation.
      1. 1.1. The Most Important Determinant of Long-Term Success
      2. 1.2. Adventure Works Cycles Introduction
      3. 1.3. Uncovering Business Value
        1. 1.3.1. Obtaining Sponsorship
        2. 1.3.2. Defining Enterprise-Level Business Requirements
          1. 1.3.2.1. Establishing Initial Enterprise Requirements Project Scope
          2. 1.3.2.2. Gathering and Documenting Enterprise-Level Business Requirements
            1. 1.3.2.2.1. Preparation
            2. 1.3.2.2.2. Adventure Works Example: Preparation
            3. 1.3.2.2.3. Conduct Business and IT Interviews
            4. 1.3.2.2.4. Debrief and Document Interviews
            5. 1.3.2.2.5. Adventure Works Example: Interview Documentation
          3. 1.3.2.3. Data Auditing/Data Profiling
          4. 1.3.2.4. Creating the Program Requirements Findings Document
            1. 1.3.2.4.1. Synthesize Around Business Processes
            2. 1.3.2.4.2. Adventure Works Example: Enterprise Requirements Documentation
            3. 1.3.2.4.3. Building the Initial Data Warehouse Bus Matrix
            4. 1.3.2.4.4. Adventure Works Example: Bus Matrix
      4. 1.4. Prioritizing the Business Requirements
      5. 1.5. Revisiting the Project Planning
      6. 1.6. Gathering Project-Level Requirements
      7. 1.7. Summary
    2. 2. Designing the Business Process Dimensional Model
      1. 2.1. Dimensional Modeling Concepts and Terminology
        1. 2.1.1. Facts
          1. 2.1.1.1. Just the Facts
          2. 2.1.1.2. The Grain
        2. 2.1.2. Dimensions
        3. 2.1.3. Bringing Facts and Dimensions Together
        4. 2.1.4. The Bus Matrix, Conformed Dimensions, and Drill Across
      2. 2.2. Additional Design Concepts and Techniques
        1. 2.2.1. Surrogate Keys
        2. 2.2.2. Slowly Changing Dimensions
        3. 2.2.3. Dates
        4. 2.2.4. Degenerate Dimensions
        5. 2.2.5. Snowflaking
        6. 2.2.6. Many-to-Many or Multivalued Dimensions
          1. 2.2.6.1. The Many-to-Many Relationship Between a Fact and Dimension
          2. 2.2.6.2. Many-to-Many Between Dimensions
        7. 2.2.7. Hierarchies
          1. 2.2.7.1. Variable-Depth Hierarchies
          2. 2.2.7.2. Frequently Changing Hierarchies
        8. 2.2.8. Aggregate Dimensions
        9. 2.2.9. Junk Dimensions
        10. 2.2.10. The Three Fact Table Types
        11. 2.2.11. Aggregates
      3. 2.3. The Dimensional Modeling Process
        1. 2.3.1. Preparation
          1. 2.3.1.1. Identify Roles and Participants
          2. 2.3.1.2. Revisit the Requirements
          3. 2.3.1.3. Understand the Data Architecture Strategy
          4. 2.3.1.4. Set Up the Modeling Environment
          5. 2.3.1.5. Establish Naming Conventions
        2. 2.3.2. Data Profiling and Research
          1. 2.3.2.1. Data Profiling and Source System Exploration
        3. 2.3.3. Building Dimensional Models
          1. 2.3.3.1. High-Level Dimensional Model Design Session
            1. 2.3.3.1.1. Creating the High-Level Dimensional Model: The Four-step Modeling Process
            2. 2.3.3.1.2. The High-Level Graphical Model
          2. 2.3.3.2. Identifying Dimension Attributes and Fact Measures
        4. 2.3.4. Developing the Detailed Dimensional Model
        5. 2.3.5. Testing and Refining the Model
        6. 2.3.6. Reviewing and Validating the Model
      4. 2.4. Case Study: The Adventure Works Cycles Orders Dimensional Model
        1. 2.4.1. The Orders Fact Table
        2. 2.4.2. The Dimensions
          1. 2.4.2.1. Employee
          2. 2.4.2.2. Customer and Reseller
          3. 2.4.2.3. Currency
        3. 2.4.3. Identifying Dimension Attributes and Facts for the Orders Business Process
        4. 2.4.4. The Final Draft of the Initial Orders Model
        5. 2.4.5. Detailed Orders Dimensional Model Development
          1. 2.4.5.1. Identifying SCD Change Types
          2. 2.4.5.2. Reviewing the Issues
          3. 2.4.5.3. Identifying the Facts
        6. 2.4.6. Final Dimensional Model
      5. 2.5. Summary
    3. 3. The Toolset
      1. 3.1. The Microsoft DW/BI Toolset
      2. 3.2. Why Use the Microsoft Toolset?
      3. 3.3. Architecture of a Microsoft DW/BI System
        1. 3.3.1. Why Analysis Services?
        2. 3.3.2. Why a Relational Store?
        3. 3.3.3. ETL Is Not Optional
        4. 3.3.4. The Role of Master Data Services
        5. 3.3.5. Delivering BI Applications
      4. 3.4. Overview of the Microsoft Tools
        1. 3.4.1. Which Products Do You Need?
        2. 3.4.2. SQL Server Development and Management Tools
          1. 3.4.2.1. SQL Server Management Studio
            1. 3.4.2.1.1. Relational Database Management Operations
            2. 3.4.2.1.2. Analysis Services Management Operations
            3. 3.4.2.1.3. Reporting Services Management Operations
            4. 3.4.2.1.4. Integration Services Management Operations
          2. 3.4.2.2. Business Intelligence Development Studio (BIDS)
      5. 3.5. Summary
    4. 4. System Setup
      1. 4.1. System Sizing Considerations
        1. 4.1.1. Calculating Data Volumes
        2. 4.1.2. Determining Usage Complexity
          1. 4.1.2.1. Simple or Controlled Access
          2. 4.1.2.2. Moderate Complexity
          3. 4.1.2.3. Highly Demanding Use
        3. 4.1.3. Estimating Simultaneous Users
        4. 4.1.4. Assessing System Availability Requirements
        5. 4.1.5. How Big Will It Be?
      2. 4.2. System Configuration Considerations
        1. 4.2.1. Memory
        2. 4.2.2. Monolithic or Distributed?
        3. 4.2.3. Storage System Considerations
          1. 4.2.3.1. Balancing the Data Pipeline
          2. 4.2.3.2. Disk Performance
          3. 4.2.3.3. Fault Tolerance and RAID
          4. 4.2.3.4. Storage Area Networks
        4. 4.2.4. Processors
        5. 4.2.5. Setting Up for High Availability
      3. 4.3. Software Installation and Configuration
        1. 4.3.1. Development Environment Software Requirements
          1. 4.3.1.1. Development Database Server
          2. 4.3.1.2. Database Designer
          3. 4.3.1.3. Development Database Administrator
          4. 4.3.1.4. ETL System Developer
          5. 4.3.1.5. Report Designer
          6. 4.3.1.6. Reporting Portal Developer
          7. 4.3.1.7. Data Mining Model Developer
          8. 4.3.1.8. Analytic Application Developer
        2. 4.3.2. Test and Production Software Requirements
          1. 4.3.2.1. Database Server
          2. 4.3.2.2. Database Administrator
        3. 4.3.3. Operating Systems
        4. 4.3.4. SQL Server Relational Database Setup
          1. 4.3.4.1. Security Options During Installation
          2. 4.3.4.2. Files, Filegroups, and RAID
          3. 4.3.4.3. Database Recovery Model
          4. 4.3.4.4. Initial Database Size
        5. 4.3.5. Analysis Services Setup
          1. 4.3.5.1. Analysis Services File Locations and Storage Requirements
          2. 4.3.5.2. Analysis Services and Memory
        6. 4.3.6. Integration Services Setup
          1. 4.3.6.1. Integration Services File Locations and Storage Requirements
        7. 4.3.7. Reporting Services Setup
      4. 4.4. Summary
  8. 2. Building and Populating the Databases
    1. 5. Creating the Relational Data Warehouse
      1. 5.1. Getting Started
      2. 5.2. Complete the Physical Design
        1. 5.2.1. Surrogate Keys
        2. 5.2.2. String Columns
        3. 5.2.3. To Null, or Not to Null?
        4. 5.2.4. Housekeeping Columns
        5. 5.2.5. Table and Column Extended Properties
      3. 5.3. Define Storage and Create Constraints and Supporting Objects
        1. 5.3.1. Create Files and Filegroups
        2. 5.3.2. Data Compression
        3. 5.3.3. Entity and Referential Integrity Constraints
        4. 5.3.4. Initial Indexing and Database Statistics
          1. 5.3.4.1. Dimension Indexes
          2. 5.3.4.2. Fact Table Indexes
          3. 5.3.4.3. Statistics
        5. 5.3.5. Aggregate Tables
        6. 5.3.6. Create Table Views
        7. 5.3.7. Insert an Unknown Member Row
        8. 5.3.8. Example CREATE TABLE Statement
      4. 5.4. Partitioned Tables
        1. 5.4.1.
          1. 5.4.1.1. How Does Table Partitioning Work?
            1. 5.4.1.1.1. Create Files and Filegroups
            2. 5.4.1.1.2. Create the Partition Function
            3. 5.4.1.1.3. Create the Partition Scheme
            4. 5.4.1.1.4. Create the Partitioned Table
          2. 5.4.1.2. Managing Partitioned Tables
            1. 5.4.1.2.1. Adding a New Partition
            2. 5.4.1.2.2. Use Table Partitions for Fast Data Loads and Minimal Downtime
            3. 5.4.1.2.3. Using Mixed Grain Partitions
            4. 5.4.1.2.4. Dropping Old Data from a Partitioned Table
          3. 5.4.1.3. Using Partitioned Tables in the Data Warehouse Database
      5. 5.5. Finishing Up
        1. 5.5.1. Metadata Setup
      6. 5.6. Summary
    2. 6. Master Data Management
      1. 6.1. Managing Master Reference Data
        1. 6.1.1. Incomplete Attributes
        2. 6.1.2. Data Integration
        3. 6.1.3. Systems Integration
        4. 6.1.4. Master Data Management Systems and the Data Warehouse
      2. 6.2. Introducing SQL Server Master Data Services
        1. 6.2.1. Model Definition Features
          1. 6.2.1.1. User Interface: Defining the Master Data Model
          2. 6.2.1.2. Creating Database Structures
          3. 6.2.1.3. Security
          4. 6.2.1.4. Programmability of Model Definition
          5. 6.2.1.5. Full Versioning of Models
        2. 6.2.2. Data Management Features
          1. 6.2.2.1. User Interface: Exploring and Managing the Master Data
          2. 6.2.2.2. Importing and Updating Data
          3. 6.2.2.3. Exporting Data
          4. 6.2.2.4. Full Versioning of All Attributes
      3. 6.3. Creating a Simple Application
        1. 6.3.1. The Business Scenario
        2. 6.3.2. Keep It Simple
        3. 6.3.3. Create the MDS Model
        4. 6.3.4. Load the Subcategory Members
        5. 6.3.5. Polish the Model
        6. 6.3.6. Export to the Data Warehouse
      4. 6.4. Summary
    3. 7. Designing and Developing the ETL System
      1. 7.1. Round Up the Requirements
      2. 7.2. Develop the ETL Plan
      3. 7.3. Introducing SQL Server Integration Services
        1. 7.3.1. Control Flow and Data Flow
          1. 7.3.1.1. Data Flow
          2. 7.3.1.2. Error Flows
        2. 7.3.2. SSIS Package Architecture
      4. 7.4. The Major Subsystems of ETL
      5. 7.5. Extracting Data
        1. 7.5.1. Subsystem 1: Data Profiling
        2. 7.5.2. Subsystem 2: Change Data Capture System
        3. 7.5.3. Subsystem 3: Extract System
          1. 7.5.3.1. Loading Data
      6. 7.6. Cleaning and Conforming Data
        1. 7.6.1. Subsystem 4: Data Cleaning System
          1. 7.6.1.1. Cleaning Data in the Data Flow
          2. 7.6.1.2. Halting Package Execution
        2. 7.6.2. Subsystem 5: Error Event Schema
        3. 7.6.3. Subsystem 6: Audit Dimension Assembler
        4. 7.6.4. Subsystem 7: Deduplication System
        5. 7.6.5. Subsystem 8: Conforming System
      7. 7.7. Delivering Data for Presentation
        1. 7.7.1. Subsystem 9: Slowly Changing Dimension Manager
          1. 7.7.1.1. Standard Handling for Slowly Changing Dimensions
          2. 7.7.1.2. Custom Handling for Slowly Changing Dimensions
          3. 7.7.1.3. Alternatives to the Slowly Changing Dimension Transform
        2. 7.7.2. Subsystem 10: Surrogate Key Generator
        3. 7.7.3. Subsystem 11: Hierarchy Manager
        4. 7.7.4. Subsystem 12: Special Dimensions Manager
        5. 7.7.5. Subsystem 13: Fact Table Builders
          1. 7.7.5.1. Transaction Grain Fact Table Loader
          2. 7.7.5.2. Periodic Snapshot Fact Table Loader
          3. 7.7.5.3. Accumulating Snapshot Fact Table Loader
        6. 7.7.6. Subsystem 14: Surrogate Key Pipeline
          1. 7.7.6.1. Surrogate Key Pipeline Technique #1: Cascading Lookups
          2. 7.7.6.2. Surrogate Key Pipeline Technique #2: Database Joins
          3. 7.7.6.3. Which Technique Is Best?
        7. 7.7.7. Subsystem 15: Multi-Valued Dimension Bridge Table Builder
        8. 7.7.8. Subsystem 16: Late Arriving Data Handler
          1. 7.7.8.1. Late Arriving Dimension Members
          2. 7.7.8.2. Late Arriving Dimension Updates
          3. 7.7.8.3. Late Arriving Facts
        9. 7.7.9. Subsystem 17: Dimension Manager
        10. 7.7.10. Subsystem 18: Fact Provider System
        11. 7.7.11. Subsystem 19: Aggregate Builder
        12. 7.7.12. Subsystem 20: OLAP Cube Builder
        13. 7.7.13. Subsystem 21: Data Propagation Manager
      8. 7.8. Managing the ETL Environment
      9. 7.9. Summary
    4. 8. The Core Analysis Services OLAP Database
      1. 8.1. Overview of Analysis Services OLAP
        1. 8.1.1. Why Use Analysis Services?
        2. 8.1.2. Why Not Analysis Services?
      2. 8.2. Designing the OLAP Structure
        1. 8.2.1. Planning
          1. 8.2.1.1. Cube Content
          2. 8.2.1.2. Cube Granularity
          3. 8.2.1.3. Cube Usage
        2. 8.2.2. Getting Started
          1. 8.2.2.1. Setup
          2. 8.2.2.2. Create Relational Views
          3. 8.2.2.3. Populate the Data Warehouse Database
        3. 8.2.3. Create a Project and a Data Source View
        4. 8.2.4. Dimension Designs
          1. 8.2.4.1. Standard Dimensions
          2. 8.2.4.2. Variable Depth or Parent-Child Hierarchies
          3. 8.2.4.3. Multivalued or Many-to-Many Dimensions
          4. 8.2.4.4. Role-Playing Dimensions
        5. 8.2.5. Creating and Editing Dimensions
          1. 8.2.5.1. Editing Dimension Properties
          2. 8.2.5.2. Editing Attribute Properties
          3. 8.2.5.3. Time and Account Dimensions
          4. 8.2.5.4. Creating Hierarchies
          5. 8.2.5.5. Set Up Attribute Relationships
          6. 8.2.5.6. Browsing Dimension Data
        6. 8.2.6. Creating and Editing the Cube
          1. 8.2.6.1. Edit the Cube Structure
            1. 8.2.6.1.1. Edit Measure Groups and Measures
            2. 8.2.6.1.2. Edit Dimensions in the Cube
            3. 8.2.6.1.3. Cube Properties
          2. 8.2.6.2. Edit Dimension Usage
            1. 8.2.6.2.1. Measure Groups at Different Granularity
            2. 8.2.6.2.2. Complex Relationships Between Dimensions and Measure Groups
          3. 8.2.6.3. Build, Deploy, and Process the Project
          4. 8.2.6.4. Create Calculations
            1. 8.2.6.4.1. The Calculations Tab
            2. 8.2.6.4.2. Adding Business Intelligence
          5. 8.2.6.5. Define Key Performance Indicators
          6. 8.2.6.6. Create Actions
          7. 8.2.6.7. Partitions and Aggregations
          8. 8.2.6.8. Maintain Perspectives
          9. 8.2.6.9. Translations
      3. 8.3. Physical Design Considerations
        1. 8.3.1. Understanding Storage Modes
        2. 8.3.2. Developing the Partitioning Plan
        3. 8.3.3. Designing Performance Aggregations
        4. 8.3.4. Planning for Deployment
        5. 8.3.5. Processing the Full Cube
        6. 8.3.6. Developing the Incremental Processing Plan
          1. 8.3.6.1. Scheduled Processing
            1. 8.3.6.1.1. Full Reprocessing
            2. 8.3.6.1.2. Incremental Processing
            3. 8.3.6.1.3. Incremental Processing with Multiple Partitions
          2. 8.3.6.2. Planning for Updates to Dimensions
          3. 8.3.6.3. Planning for Fact Updates and Deletes
      4. 8.4. Summary
    5. 9. Design Requirements for Real-Time BI
      1. 9.1. Real-Time Triage
        1. 9.1.1. What Does Real-Time Mean?
        2. 9.1.2. Who Needs Real Time?
        3. 9.1.3. Real-Time Tradeoffs
      2. 9.2. Scenarios and Solutions
        1. 9.2.1. Executing Reports in Real Time
        2. 9.2.2. Serving Reports from a Cache
        3. 9.2.3. Creating an ODS with Mirrors and Snapshots
        4. 9.2.4. Creating an ODS with Replication
        5. 9.2.5. Building a BizTalk Application
        6. 9.2.6. Building a Real-Time Relational Partition
          1. 9.2.6.1. Querying Real-Time Data in the Relational Database
          2. 9.2.6.2. Using Analysis Services to Query Real-Time Data
      3. 9.3. Summary
  9. 3. Developing the BI Applications
    1. 10. Building BI Applications in Reporting Services
      1. 10.1. A Brief Overview of BI Applications
        1. 10.1.1. Types of BI Applications
        2. 10.1.2. The Value of Business Intelligence Applications
      2. 10.2. A High-Level Architecture for Reporting
        1. 10.2.1. Reviewing Business Requirements for Reporting
        2. 10.2.2. Examining the Reporting Services Architecture
        3. 10.2.3. Using Reporting Services as a Standard Reporting Tool
          1. 10.2.3.1. Creating Reports
          2. 10.2.3.2. Finding Reports
          3. 10.2.3.3. Viewing Reports
          4. 10.2.3.4. Receiving Results
          5. 10.2.3.5. Changing Reports
          6. 10.2.3.6. Solid, Reliable System
        4. 10.2.4. Reporting Services Assessment
      3. 10.3. The Reporting System Design and Development Process
        1. 10.3.1. Reporting System Design
          1. 10.3.1.1. Determining the Initial Report Set
          2. 10.3.1.2. Creating the Report Template
          3. 10.3.1.3. Creating Report Specifications and Documentation
            1. 10.3.1.3.1. Report Mock-Ups
            2. 10.3.1.3.2. User Interaction List
            3. 10.3.1.3.3. Detailed Documentation
          4. 10.3.1.4. Designing the Navigation Framework
          5. 10.3.1.5. Conducting the User Review
        2. 10.3.2. Reporting System Development
          1. 10.3.2.1. Prepare
          2. 10.3.2.2. Build
          3. 10.3.2.3. Test
          4. 10.3.2.4. Deploy
      4. 10.4. Building and Delivering Reports
        1. 10.4.1. Planning and Preparation
          1. 10.4.1.1. Setting Up the Development Environment
          2. 10.4.1.2. Creating Standard Templates
        2. 10.4.2. Creating Reports
          1. 10.4.2.1. Revisit the Report Specifications
          2. 10.4.2.2. The Report Creation Process
          3. 10.4.2.3. Creating Your First Standard Report
            1. 10.4.2.3.1. Creating the Data Source and Dataset Query
            2. 10.4.2.3.2. Design the Report Layout
            3. 10.4.2.3.3. Unit Test
            4. 10.4.2.3.4. Deploy to the Test Report Server and Test Some More
            5. 10.4.2.3.5. Deploy to Production
        3. 10.4.3. Reporting Operations
      5. 10.5. Ad Hoc Reporting Options
        1. 10.5.1. The Report Model
        2. 10.5.2. Shared Datasets
        3. 10.5.3. Report Parts
      6. 10.6. Summary
    2. 11. PowerPivot and Excel
      1. 11.1. Using Excel for Analysis and Reporting
      2. 11.2. The PowerPivot Architecture: Excel on Steroids
      3. 11.3. Creating and Using PowerPivot Databases
        1. 11.3.1. Getting Started
        2. 11.3.2. PowerPivot Table Design
          1. 11.3.2.1. Loading the Data
          2. 11.3.2.2. Creating the Relationships
        3. 11.3.3. Creating Analytics with PowerPivot
          1. 11.3.3.1. Creating a PowerPivot PivotTable
          2. 11.3.3.2. Adding New Measures to the PivotTable
          3. 11.3.3.3. The CALCULATE() Function
          4. 11.3.3.4. Adding a Computed Column to the PivotTable Database
        4. 11.3.4. Observations and Guidelines on PowerPivot for Excel
      4. 11.4. PowerPivot for SharePoint
        1. 11.4.1. The PowerPivot SharePoint User Experience
          1. 11.4.1.1. PowerPivot Publishing
          2. 11.4.1.2. PowerPivot Viewing
          3. 11.4.1.3. PowerPivot as a Data Source
        2. 11.4.2. Server-Level Resources
        3. 11.4.3. PowerPivot Monitoring and Management
          1. 11.4.3.1. PowerPivot Monitoring
          2. 11.4.3.2. PowerPivot Workbook Publishing Process
      5. 11.5. PowerPivot's Role in a Managed DW/BI Environment
      6. 11.6. Summary
    3. 12. The BI Portal and SharePoint
      1. 12.1. The BI Portal
      2. 12.2. Planning the BI Portal
        1. 12.2.1. Impact on Design
        2. 12.2.2. Business Process Categories
        3. 12.2.3. Additional Functions
        4. 12.2.4. Building the BI Portal
      3. 12.3. Using SharePoint as the BI Portal
        1. 12.3.1. Architecture and Concepts
          1. 12.3.1.1. SharePoint's Three-Tier Architecture and Topology
          2. 12.3.1.2. SharePoint Terminology
        2. 12.3.2. Setting Up SharePoint
          1. 12.3.2.1. The Installation Process
          2. 12.3.2.2. Plan SharePoint for BI
          3. 12.3.2.3. Installing the Test System
            1. 12.3.2.3.1. Set Up the Test Server
            2. 12.3.2.3.2. Install SharePoint Server 2010
            3. 12.3.2.3.3. Install PowerPivot for SharePoint
            4. 12.3.2.3.4. Verify the SharePoint Install
            5. 12.3.2.3.5. Install, Configure, and Verify Reporting Services
          4. 12.3.2.4. Completing the BI Portal
          5. 12.3.2.5. The Added Functionality of the BIPortal Site Template
          6. 12.3.2.6. Exploring SharePoint
      4. 12.4. Summary
    4. 13. Incorporating Data Mining
      1. 13.1. Defining Data Mining
        1. 13.1.1. Basic Data Mining Terminology
        2. 13.1.2. Business Uses of Data Mining
          1. 13.1.2.1. Classification
          2. 13.1.2.2. Estimation (Regression)
          3. 13.1.2.3. Prediction
          4. 13.1.2.4. Association or Affinity Grouping
          5. 13.1.2.5. Clustering (Segmentation)
          6. 13.1.2.6. Anomaly Detection
          7. 13.1.2.7. Description and Profiling
          8. 13.1.2.8. Business Task Summary
        3. 13.1.3. Roles and Responsibilities
      2. 13.2. SQL Server Data Mining Architecture Overview
        1. 13.2.1. The Data Mining Design Environment
        2. 13.2.2. Build, Deploy, and Process
        3. 13.2.3. Accessing the Mining Models
        4. 13.2.4. Integration Services and Data Mining
        5. 13.2.5. Additional Features
        6. 13.2.6. Architecture Summary
      3. 13.3. Microsoft Data Mining Algorithms
        1. 13.3.1. Decision Trees
        2. 13.3.2. Naïve Bayes
        3. 13.3.3. Clustering
        4. 13.3.4. Sequence Clustering
        5. 13.3.5. Time Series
        6. 13.3.6. Association
        7. 13.3.7. Neural Network
      4. 13.4. The Data Mining Process
        1. 13.4.1. The Business Phase
          1. 13.4.1.1. Identifying Business Opportunities
          2. 13.4.1.2. Understanding the Data
          3. 13.4.1.3. Describing the Data Mining Opportunity
        2. 13.4.2. The Data Mining Phase
          1. 13.4.2.1. Data Preparation
            1. 13.4.2.1.1. Cleaning and Transforming
            2. 13.4.2.1.2. Integrating External Variables
            3. 13.4.2.1.3. Building the Case Sets
          2. 13.4.2.2. Model Development
          3. 13.4.2.3. Model Validation (Evaluation)
            1. 13.4.2.3.1. Comparing Models
            2. 13.4.2.3.2. Business Review
        3. 13.4.3. The Operations Phase
          1. 13.4.3.1. Implementation
          2. 13.4.3.2. Assess Impact
          3. 13.4.3.3. Maintain the Model
        4. 13.4.4. Metadata
      5. 13.5. Data Mining Examples
        1. 13.5.1. Case Study: Categorizing Cities
          1. 13.5.1.1. Categorizing Cities: Business Opportunity
          2. 13.5.1.2. Categorizing Cities: Data Understanding
          3. 13.5.1.3. Categorizing Cities: Data Preparation
          4. 13.5.1.4. Categorizing Cities: Model Development
          5. 13.5.1.5. Categorizing Cities: Model Validation
          6. 13.5.1.6. Categorizing Cities: Implementation
          7. 13.5.1.7. Categorization Cities: Maintenance and Assessment
        2. 13.5.2. Case Study: Product Recommendations
          1. 13.5.2.1. Product Recommendations: The Business Phase
            1. 13.5.2.1.1. Product Recommendations: Business opportunities
            2. 13.5.2.1.2. Product Recommendations: Data Understanding
          2. 13.5.2.2. Product Recommendations: The Data Mining Phase
            1. 13.5.2.2.1. Product Recommendations: Data Preparation
            2. 13.5.2.2.2. Product Recommendations: Model Development
            3. 13.5.2.2.3. Product Recommendations: Model Validation
          3. 13.5.2.3. Product Recommendations: The Operations Phase
      6. 13.6. Summary
  10. 4. Deploying and Managing the DW/BI System
    1. 14. Designing and Implementing Security
      1. 14.1. Identifying the Security Manager
      2. 14.2. Securing the Hardware and Operating System
        1. 14.2.1. Securing the Operating System
        2. 14.2.2. Using Windows Integrated Security
      3. 14.3. Securing the Development Environment
      4. 14.4. Securing the Data
        1. 14.4.1. Providing Open Access for Internal Users
        2. 14.4.2. Itemizing Sensitive Data
        3. 14.4.3. Securing Various Types of Data Access
      5. 14.5. Securing the Components of the DW/BI System
        1. 14.5.1. Reporting Services Security
          1. 14.5.1.1. Administrative Roles for Reporting Services
          2. 14.5.1.2. User Roles for Reporting Services
          3. 14.5.1.3. Reporting Services in SharePoint Integrated Mode
        2. 14.5.2. Analysis Services Security
          1. 14.5.2.1. Administrative Roles for Analysis Services
          2. 14.5.2.2. User Roles for Analysis Services
            1. 14.5.2.2.1. Dimension Security
            2. 14.5.2.2.2. Cell Security
          3. 14.5.2.3. Dynamic Security
          4. 14.5.2.4. PowerPivot Security
        3. 14.5.3. Relational DW Security
          1. 14.5.3.1. Administrative Roles for the Relational Database
          2. 14.5.3.2. User Roles for the Relational Database
            1. 14.5.3.2.1. Reporting Account Permissions
            2. 14.5.3.2.2. Business User Roles
            3. 14.5.3.2.3. Row-Level or Filtering Security
            4. 14.5.3.2.4. Testing Relational Security
        4. 14.5.4. Integration Services Security
      6. 14.6. Usage Monitoring
      7. 14.7. Summary
    2. 15. Metadata Plan
      1. 15.1. Metadata Basics
        1. 15.1.1. The Purpose of Metadata
        2. 15.1.2. Metadata Categories
        3. 15.1.3. The Metadata Repository
      2. 15.2. Metadata Standards
      3. 15.3. SQL Server 2008 R2 Metadata
        1. 15.3.1. Cross-Tool Components
        2. 15.3.2. Relational Engine Metadata
        3. 15.3.3. Analysis Services
        4. 15.3.4. Integration Services
        5. 15.3.5. Reporting Services
        6. 15.3.6. Master Data Services
        7. 15.3.7. SharePoint
        8. 15.3.8. External Metadata Sources
          1. 15.3.8.1. System Monitor
          2. 15.3.8.2. Active Directory
        9. 15.3.9. Looking to the Future
      4. 15.4. A Practical Metadata Approach
        1. 15.4.1. Creating the Metadata Strategy
        2. 15.4.2. Business Metadata Reporting
          1. 15.4.2.1. Analysis Services as Primary Query Platform
          2. 15.4.2.2. Relational Engine Extended Properties
          3. 15.4.2.3. Business Metadata Schema
        3. 15.4.3. Process Metadata Reporting
        4. 15.4.4. Technical Metadata Reporting
        5. 15.4.5. Ongoing Metadata Management
      5. 15.5. Summary
    3. 16. Deployment
      1. 16.1. Setting Up the Environments
      2. 16.2. Testing
        1. 16.2.1. Development Testing
        2. 16.2.2. System Testing
        3. 16.2.3. Data Quality Assurance Testing
        4. 16.2.4. Performance Testing
          1. 16.2.4.1. Service Level Confirmation
          2. 16.2.4.2. Processing Performance: Getting Data In
          3. 16.2.4.3. Query Performance: Getting Data Out
        5. 16.2.5. Usability Testing
        6. 16.2.6. Testing Summary
      3. 16.3. Deploying to Production
        1. 16.3.1. Relational Database Deployment
        2. 16.3.2. Integration Services Package Deployment
        3. 16.3.3. Analysis Services Database Deployment
        4. 16.3.4. Reporting Services Report Deployment
        5. 16.3.5. Master Data Services Deployment
      4. 16.4. Data Warehouse and BI Documentation
        1. 16.4.1. Core Descriptions
          1. 16.4.1.1. Business Process Dimensional Model Descriptions
          2. 16.4.1.2. Table and Column Descriptions
          3. 16.4.1.3. Report Descriptions
        2. 16.4.2. Additional Documentation
      5. 16.5. User Training
      6. 16.6. User Support
      7. 16.7. Desktop Readiness and Configuration
      8. 16.8. Summary
    4. 17. Operations and Maintenance
      1. 17.1. Providing User Support
        1. 17.1.1. Maintaining the BI Portal
        2. 17.1.2. Extending the BI Applications
      2. 17.2. System Management
        1. 17.2.1. Governing the DW/BI System
          1. 17.2.1.1. Identifying and Terminating User Sessions
            1. 17.2.1.1.1. Relational Database
            2. 17.2.1.1.2. Analysis Services Database
            3. 17.2.1.1.3. Reporting Services
          2. 17.2.1.2. Resource Governance
            1. 17.2.1.2.1. Relational Database
            2. 17.2.1.2.2. Analysis Services Database
        2. 17.2.2. Performance Monitoring
          1. 17.2.2.1. Relational Database
          2. 17.2.2.2. Analysis Services Database
          3. 17.2.2.3. Reporting Services
          4. 17.2.2.4. Integration Services
          5. 17.2.2.5. PowerPivot
        3. 17.2.3. Usage Monitoring
        4. 17.2.4. Managing Disk Space
        5. 17.2.5. Service and Availability Management
        6. 17.2.6. Performance Tuning the DW/BI System
        7. 17.2.7. Backup and Recovery
          1. 17.2.7.1. Relational Databases
          2. 17.2.7.2. Integration Services
          3. 17.2.7.3. Analysis Services
          4. 17.2.7.4. Reporting Services
          5. 17.2.7.5. Recovery
        8. 17.2.8. Executing the ETL Packages
      3. 17.3. Summary
    5. 18. Present Imperatives and Future Outlook
      1. 18.1. Growing the DW/BI System
      2. 18.2. Lifecycle Review with Common Problems
        1. 18.2.1. Phase I — Requirements, Realities, Plans, and Designs
        2. 18.2.2. Phase II — Developing the Databases
        3. 18.2.3. Phase III — Developing the BI Applications and Portal Environment
        4. 18.2.4. Phase IV — Deploying and Managing the DW/BI System
        5. 18.2.5. Iteration and Growth
      3. 18.3. What We Like in the Microsoft BI Toolset
      4. 18.4. Future Directions: Room for Improvement
        1. 18.4.1. Query Tools
        2. 18.4.2. Metadata
        3. 18.4.3. Relational Database Engine
        4. 18.4.4. Analysis Services
        5. 18.4.5. Master Data Services
        6. 18.4.6. Integration
        7. 18.4.7. Customer Focus
      5. 18.5. Conclusion

Product information

  • Title: The Microsoft® Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft® Business Intelligence Toolset, Second Edition
  • Author(s): Joy Mundy, Warren Thornthwaite, Ralph Kimball
  • Release date: March 2011
  • Publisher(s): Wiley
  • ISBN: 9780470640388