Smart Business Intelligence Solutions with Microsoft® SQL Server® 2008

Smart Business Intelligence Solutions with Microsoft® SQL Server® 2008

by Kevin S. Goff Lynn Langit Davide Mauri, Sahil Malik, and John C. Welch
Released February 2009
Publisher(s): Microsoft Press
ISBN: 9780735625808

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

Get the end-to-end instruction you need to design, develop, and deploy more effective data integration, reporting, and analysis solutions using SQL Server 2008—whether you’re new to business intelligence (BI) programming or a seasoned pro. With real-world examples and insights from an expert team, you’ll master the concepts, tools, and techniques for building solutions that deliver intelligence—and business value—exactly where users want it.

Discover how to:

  • Manage the development life cycle and build a BI team

  • Dig into SQL Server Analysis Services, Integration Services, and Reporting Services

  • Navigate the Business Intelligence Development Studio (BIDS)

  • Write queries that rank, sort, and drill down on sales data

  • Develop extract, transform, and load (ETL) solutions

  • Add a source code control system

  • Help secure packages for deployment via encryption and credentials

  • Use MDX and DMX Query Designers to build reports based on OLAP cubes and data mining models

  • Create and implement custom objects using .NET code

  • View reports in Microsoft Office Excel and Office SharePoint Serverook

    •

    Table of contents

    1. Foreword
    2. Acknowledgments
      1. Lynn Langit
      2. Davide Mauri
      3. Sahil Malik
    3. Introduction
      1. Who This Book Is For
      2. What This Book Is About
        1. Part I, “Business Intelligence for Business Decision Makers and Architects”
        2. Part II, “Microsoft SQL Server 2008 Analysis Services for Developers”
        3. Part III, “Microsoft SQL Server 2008 Integration Services for Developers”
        4. Part IV, “Microsoft SQL Server Reporting Services and Other Client Interfaces for Business Intelligence”
      3. Prerelease Software
      4. Hardware and Software Requirements
      5. Find Additional Content Online
      6. Support for This Book
        1. Questions and Comments
    4. I. Business Intelligence for Business Decision Makers and Architects
      1. 1. Business Intelligence Basics
        1. Business Intelligence and Data Modeling
        2. OLTP and OLAP
          1. Online Transactional Processing
          2. Online Analytical Processing
        3. Common BI Terminology
          1. Data Warehouses
          2. Data Marts
          3. Cubes
          4. Decision Support Systems
          5. Data Mining Systems
          6. Extract, Transform, and Load Systems
          7. Report Processing Systems
          8. Key Performance Indicators
        4. Core Components of a Microsoft BI Solution
          1. SQL Server 2008 Analysis Services
            1. Data Mining with Analysis Services 2008
          2. SQL Server 2008 Reporting Services
          3. SQL Server 2008
          4. SQL Server 2008 Integration Services
        5. Optional Components of a Microsoft BI Solution
        6. Query Languages Used in BI Solutions
          1. MDX
          2. DMX
          3. XMLA
          4. RDL
        7. Summary
      2. 2. Visualizing Business Intelligence Results
        1. Matching Business Cases to BI Solutions
          1. Top 10 BI Scoping Questions
          2. Components of BI Solutions
        2. Understanding Business Intelligence from a User’s Perspective
          1. Demonstrating the Power of BI Using Excel 2007
            1. Building the First Sample—Using AdventureWorksDW2008
            2. Opening the Sample in BIDS
            3. Connecting to the Sample Cube Using Excel 2007
          2. Understanding Data Mining via the Excel Add-ins
          3. Viewing Data Mining Structures Using Excel 2007
            1. Building a Sample with Your Own Data
        3. Elements of a Complete BI Solution
          1. Reporting—Deciding Who Will Use the Solution
          2. ETL—Getting the Solution Implemented
          3. Data Mining—Don’t Leave It Out
        4. Common Business Challenges and BI Solutions
        5. Measuring the ROI of BI Solutions
        6. Summary
      3. 3. Building Effective Business Intelligence Processes
        1. Software Development Life Cycle for BI Projects
          1. Microsoft Solutions Framework
          2. Microsoft Solutions Framework for Agile Software Development
          3. Applying MSF to BI Projects
          4. Phases and Deliverables in the Microsoft Solutions Framework
            1. Envisioning
            2. Planning
            3. Building
            4. Stabilizing
            5. Deploying
        2. Skills Necessary for BI Projects
          1. Required Skills
            1. Building the Data Storage Containers
            2. Creating the User Interface
            3. Understanding Extract, Transform, and Load Processes
            4. Optimizing the Data Sources
          2. Optional Skills
            1. Building the Foundation
            2. Creating the User Interface
            3. Understanding Extract, Transform, and Load Processes
            4. Optimizing the Data Sources
        3. Forming Your Team
          1. Roles and Responsibilities Needed When Working with MSF
            1. Product Management
            2. Architecture
            3. Program Management
            4. Development
            5. Test
            6. User Experience
            7. Release Management
        4. Summary
      4. 4. Physical Architecture in Business Intelligence Solutions
        1. Planning for Physical Infrastructure Change
          1. Creating Accurate Baseline Surveys
          2. Assessing Current Service Level Agreements
        2. Determining the Optimal Number and Placement of Servers
          1. Considerations for Physical Servers
            1. Server Consolidation
          2. Considerations for Logical Servers and Services
        3. Understanding Security Requirements
          1. Security Requirements for BI Solutions
            1. Source Data: Access Using Least-Privileged Accounts
            2. Data in Transit: Security on the Wire
            3. Processing Layer: ETL
            4. SSAS Data
            5. On the User Client: Excel or SSRS
              1. Excel as an OLAP Cube Client
              2. Excel as a Data Mining Client
              3. SSRS as an OLAP Client
            6. Security Considerations for Custom Clients
        4. Backup and Restore
          1. Backing Up SSAS
          2. Backing Up SSIS
          3. Backing Up SSRS
        5. Auditing and Compliance
          1. Auditing Features in SQL Server 2008
        6. Source Control
        7. Summary
      5. 5. Logical OLAP Design Concepts for Architects
        1. Designing Basic OLAP Cubes
          1. Star Schemas
            1. Fact Tables
            2. Dimension Tables
          2. Denormalization
          3. Back to the Star
            1. Using Grain Statements
            2. Design Approaches
            3. Choosing Tools to Create Your OLAP Model
          4. Other Design Tips
            1. Using BIDS as a Designer
          5. Modeling Snowflake Dimensions
            1. Snowflake Schemas
            2. When Should You Use Snowflakes?
            3. What Other Cube Design Variations Are Possible?
            4. Why Not Just Use Views Against the Relational Data Sources?
          6. More About Dimensional Modeling
            1. Slowly Changing Dimensions
            2. Types of Slowly Changing Dimensions
            3. Rapidly Changing Dimensions
            4. Writeback
          7. Understanding Fact (Measure) Modeling
            1. Calculated vs. Derived Measures
          8. Other Considerations in BI Modeling
            1. Data Mining
            2. Key Performance Indicators (KPIs)
            3. Actions, Perspectives, and Translations
            4. Source Control and Other Documentation Standards
        2. Summary
    5. II. Microsoft SQL Server 2008 Analysis Services for Developers
      1. 6. Understanding SSAS in SSMS and SQL Server Profiler
        1. Core Tools in SQL Server Analysis Services
          1. Baseline Service Configuration
            1. Service Principal Names
          2. SSAS in SSMS
            1. How Do I View OLAP Objects?
            2. How Do I View OLAP Cubes?
            3. Viewing OLAP Cube Properties and Metadata
            4. How Do I View DM Structures?
          3. How Do You Query SSAS Objects?
            1. What Is SQL Server Profiler?
            2. Using SSAS Query Templates
          4. Using MDX Templates
          5. Using DMX Templates
          6. Using XMLA Templates
          7. Closing Thoughts on SSMS
        2. Summary
      2. 7. Designing OLAP Cubes Using BIDS
        1. Using BIDS
          1. Offline and Online Modes
        2. Working in Solution Explorer
          1. Data Sources in Analysis Services
          2. Data Source Views
          3. Roles in Analysis Services
          4. Using Compiled Assemblies with Analysis Services Objects
        3. Building OLAP Cubes in BIDS
          1. Examining the Sample Cube in Adventure Works
        4. Understanding Dimensions
          1. Attribute Hierarchies
          2. Attribute Relationships
          3. Translations
        5. Using Dimensions
          1. Measure Groups
          2. Beyond Star Dimensions
            1. Snowflake Dimension
            2. Fact Dimension
            3. Many-to-Many Dimension
        6. Building Your First OLAP Cube
          1. Selecting Measure Groups
          2. Adding Dimensions
        7. Summary
      3. 8. Refining Cubes and Dimensions
        1. Refining Your First OLAP Cube
          1. Translations and Perspectives
          2. Key Performance Indicators
          3. Actions
          4. Calculations (MDX Scripts or Calculated Members)
        2. Using Cube and Dimension Properties
          1. Time Intelligence
          2. SCOPE Keyword
          3. Account Intelligence and Unary Operator Definitions
          4. Other Wizard Options
          5. Currency Conversions
        3. Advanced Cube and Dimension Properties
        4. Summary
      4. 9. Processing Cubes and Dimensions
        1. Building, Processing, and Deploying OLAP Cubes
          1. Differentiating Data and Metadata
          2. Working in a Disconnected Environment
          3. Working in a Connected Environment
          4. Understanding Aggregations
        2. Partitioning
          1. Choosing Storage Modes: MOLAP, HOLAP, and ROLAP
            1. MOLAP
            2. HOLAP
            3. ROLAP
          2. OLTP Table Partitioning
          3. Other OLAP Partition Configurations
        3. Implementing Aggregations
          1. Aggregation Design Wizard
          2. Usage-Based Optimization Wizard
          3. SQL Server Profiler
          4. Aggregation Designer: Advanced View
        4. Implementing Advanced Storage with MOLAP, HOLAP, or ROLAP
        5. Proactive Caching
          1. Notification Settings for Proactive Caching
          2. Fine-Tuning Proactive Caching
          3. ROLAP Dimensions
          4. Linking
          5. Writeback
        6. Cube and Dimension Processing Options
        7. Summary
      5. 10. Introduction to MDX
        1. The Importance of MDX
        2. Writing Your First MDX Queries
          1. MDX Object Names
          2. Other Elements of MDX Syntax
        3. MDX Core Functions
        4. Filtering MDX Result Sets
        5. Calculated Members and Named Sets
          1. Creating Objects by Using Scripts
        6. The TopCount Function
        7. Rank Function and Combinations
        8. Head and Tail Functions
        9. Hierarchical Functions in MDX
        10. Date Functions
          1. Using Aggregation with Date Functions
          2. About Query Optimization
        11. Summary
      6. 11. Advanced MDX
        1. Querying Dimension Properties
        2. Looking at Date Dimensions and MDX Seasonality
        3. Creating Permanent Calculated Members
          1. Creating Permanent Calculated Members in BIDS
          2. Creating Calculated Members Using MDX Scripts
        4. Using IIf
        5. About Named Sets
        6. About Scripts
        7. Understanding SOLVE_ORDER
        8. Creating Key Performance Indicators
          1. Creating KPIs Programmatically
          2. Additional Tips on KPIs
        9. Using MDX with SSRS and PerformancePoint Server
          1. Using MDX with SSRS 2008
          2. Using MDX with PerformancePoint Server 2007
        10. Summary
      7. 12. Understanding Data Mining Structures
        1. Reviewing Business Scenarios
          1. Categories of Data Mining Algorithms
            1. Classification
            2. Clustering
            3. Association
            4. Forecasting and Regression
            5. Sequence Analysis and Prediction
            6. Deviation Analysis
        2. Working in the BIDS Data Mining Interface
          1. Understanding Data Types and Content Types
          2. Setting Advanced Data Properties
          3. Choosing a Data Mining Model
          4. Picking the Best Mining Model Viewer
          5. Mining Accuracy Charts and Prediction
        3. Data Mining Algorithms
          1. Microsoft Naïve Bayes
            1. Feature Selection
          2. Microsoft Decision Trees Algorithm
          3. Microsoft Linear Regression Algorithm
          4. Microsoft Time Series Algorithm
          5. Microsoft Clustering Algorithm
          6. Microsoft Sequence Clustering
          7. Microsoft Association Algorithm
          8. Microsoft Neural Network Algorithm
          9. Microsoft Logistic Regression
        4. The Art of Data Mining
        5. Summary
      8. 13. Implementing Data Mining Structures
        1. Implementing the CRISP-DM Life Cycle Model
        2. Building Data Mining Structures using BIDS
        3. Adding Data Mining Models Using BIDS
        4. Processing Mining Models
        5. Validating Mining Models
          1. Lift Charts
          2. Profit Charts
          3. Classification Matrix
          4. Cross Validation
        6. Data Mining Prediction Queries
          1. DMX Prediction Queries
          2. DMX Prediction Functions
        7. Data Mining and Integration Services
        8. Data Mining Object Processing
        9. Data Mining Clients
        10. Summary
    6. III. Microsoft SQL Server 2008 Integration Services for Developers
      1. 14. Architectural Components of Microsoft SQL Server 2008 Integration Services
        1. Overview of Integration Services Architecture
        2. Integration Services Packages
          1. Tools and Utilities for Developing, Deploying, and Executing Integration Services Packages
            1. SQL Server Management Studio
            2. Business Intelligence Development Studio
            3. DTEXEC and DTEXECUI
            4. DTUTIL
        3. The Integration Services Object Model and Components
          1. Control Flow
          2. Data Flow
          3. Variables
          4. Expressions
          5. Connection Managers
          6. Event Handlers and Error Handling
        4. The Integration Services Runtime
        5. The Integration Services Data Flow Engine
          1. Data Flow Buffers
            1. Data Flow Metadata
            2. Variable Width Columns
          2. Synchronous Data Flow Outputs
          3. Asynchronous Data Flow Outputs
        6. Log Providers
        7. Deploying Integration Services Packages
          1. Package Configurations
          2. Package Deployment Options
        8. Summary
      2. 15. Creating Microsoft SQL Server 2008 Integration Services Packages with Business Intelligence Development Studio
        1. Integration Services in Visual Studio 2008
          1. Creating New SSIS Projects with the Integration Services Project Template
          2. Viewing an SSIS Project in Solution Explorer
          3. Using the SSIS Package Designers
          4. Working with the SSIS Toolbox
          5. Choosing from the SSIS Menu
        2. Connection Managers
          1. Standard Database Connection Managers
          2. Other Types of Connection Managers
        3. Control Flow
          1. Control Flow Tasks
          2. Control Flow Containers
          3. Precedence Constraints
        4. Data Flow
          1. Data Flow Source Components
          2. Destination Components
          3. Transformation Components
          4. Integration Services Data Viewers
        5. Variables
          1. Variables Window
          2. Variable Properties
          3. System Variables
        6. Expressions
          1. Variables and Default Values Within a Package
        7. Summary
      3. 16. Advanced Features in Microsoft SQL Server 2008 Integration Services
        1. Error Handling in Integration Services
        2. Events, Logs, Debugging, and Transactions in SSIS
        3. Logging and Events
        4. Debugging Integration Services Packages
        5. Checkpoints and Transactions
          1. Configuring Package Transactions
        6. Best Practices for Designing Integration Services Packages
        7. Data Profiling
        8. Summary
      4. 17. Microsoft SQL Server 2008 Integration Services Packages in Business Intelligence Solutions
        1. ETL for Business Intelligence
        2. Loading OLAP Cubes
          1. Using Integration Services to Check Data Quality
            1. Assessing Data Quality with the Fuzzy Grouping Transformation
            2. Additional Approaches to Assessing Data Quality
          2. Transforming Source Data
          3. Using a Staging Server
          4. Data Lineage
        3. Moving to Star Schema Loading
          1. Loading Dimension Tables
          2. Loading Fact Tables
        4. Updates
          1. Fact Table Updates
          2. Dimension Table Updates
        5. ETL for Data Mining
          1. Initial Loading
          2. Model Training
          3. Data Mining Queries
        6. Summary
      5. 18. Deploying and Managing Solutions in Microsoft SQL Server 2008 Integration Services
        1. Solution and Project Structures in Integration Services
        2. Source Code Control
          1. Using Visual SourceSafe
        3. The Deployment Challenge
          1. Package Configurations
          2. Copy File Deployment
          3. BIDS Deployment
          4. Deployment with the Deployment Utility
        4. SQL Server Agent and Integration Services
          1. Introduction to SSIS Package Security
          2. Handling Sensitive Data and Proxy Execution Accounts
          3. Security: The Two Rules
        5. The SSIS Service
        6. Summary
      6. 19. Extending and Integrating SQL Server 2008 Integration Services
        1. Introduction to SSIS Scripting
        2. Visual Studio Tools for Applications
        3. The Script Task
          1. The Dts Object
          2. Debugging Script Tasks
        4. The Script Component
          1. The ComponentMetaData Property
          2. Source, Transformation, and Destination
            1. Source
            2. Synchronous and Asynchronous Transformation
            3. Destination
          3. Debugging Script Components
        5. Overview of Custom SSIS Task and Component Development
          1. Control Flow Tasks
          2. Data Flow Components
          3. Other Components
        6. Overview of SSIS Integration in Custom Applications
        7. Summary
    7. IV. Microsoft SQL Server Reporting Services and Other Client Interfaces for Business Intelligence
      1. 20. Creating Reports in SQL Server 2008 Reporting Services
        1. Understanding the Architecture of Reporting Services
        2. Installing and Configuring Reporting Services
          1. HTTP Listener
          2. Report Manager
          3. Report Server Web Service
          4. Authentication
          5. Background Processing (Job Manager)
        3. Creating Reports with BIDS
          1. Other Types of Reports
          2. Sample Reports
        4. Deploying Reports
        5. Summary
      2. 21. Building Reports for SQL Server 2008 Reporting Services
        1. Using the Query Designers for Analysis Services
          1. MDX Query Designer
          2. Setting Parameters in Your Query
          3. DMX Query Designer
          4. Working with the Report Designer in BIDS
        2. Understanding Report Items
          1. List and Rectangle Report Items
          2. Tablix Data Region
        3. Using Report Builder
        4. Summary
      3. 22. Advanced SQL Server 2008 Reporting Services
        1. Adding Custom Code to SSRS Reports
        2. Viewing Reports in Word or Excel 2007
        3. URL Access
        4. Embedding Custom ReportViewer Controls
        5. About Report Parameters
        6. About Security Credentials
        7. About the SOAP API
          1. What Happened to Report Models?
        8. Deployment—Scalability and Security
          1. Performance and Scalability
          2. Advanced Memory Management
          3. Scaling Out
        9. Administrative Scripting
          1. Using WMI
        10. Summary
      4. 23. Using Microsoft Excel 2007 as an OLAP Cube Client
        1. Using the Data Connection Wizard
        2. Working with the Import Data Dialog Box
        3. Understanding the PivotTable Interface
        4. Creating a Sample PivotTable
        5. Offline OLAP
        6. Excel OLAP Functions
        7. Extending Excel
        8. Summary
      5. 24. Microsoft Office 2007 as a Data Mining Client
        1. Installing Data Mining Add-ins
        2. Data Mining Integration with Excel 2007
          1. Using the Table Analysis Tools Group
          2. Using the Data Mining Tab in Excel 2007
            1. Management and Model Usage
            2. Data Preparation Group
            3. Data Modeling Group
            4. The Accuracy And Validation Group
        3. Data Mining Integration in Visio 2007
        4. Client Visualization
        5. Data Mining in the Cloud
        6. Summary
      6. 25. SQL Server Business Intelligence and Microsoft Office SharePoint Server 2007
        1. Excel Services
          1. Basic Architecture of Excel Services
          2. Immutability of Excel Sheets
          3. Introductory Sample Excel Services Worksheet
          4. Publishing Parameterized Excel Sheets
          5. Excel Services: The Web Services API
          6. A Real-World Excel Services Example
        2. SQL Server Reporting Services with Office SharePoint Server 2007
          1. Configuring SQL Server Reporting Services with Office SharePoint Server 2007
          2. Authoring and Deploying a Report
          3. Using the Report in Office SharePoint Server 2007: Native Mode
          4. Using the Report in Office SharePoint Server 2007: SharePoint Integrated Mode
          5. Using the Report Center Templates
        3. PerformancePoint Server
        4. Summary
    8. A. About the Authors
      1. Lynn Langit
      2. Davide Mauri
      3. Sahil Malik
      4. Kevin Goff
      5. John Welch
    9. Index
    10. About the Authors
    11. Copyright

    Product information

    • Title: Smart Business Intelligence Solutions with Microsoft® SQL Server® 2008
    • Author(s): Kevin S. Goff Lynn Langit Davide Mauri, Sahil Malik, and John C. Welch
    • Release date: February 2009
    • Publisher(s): Microsoft Press
    • ISBN: 9780735625808