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

Foundations of SQL Server 2005 Business Intelligence

Book Description

Just as every business needs to effectively employ business intelligence (BI) to stay competitive, every IT professional needs to master BI to stay employed in this fastest-growing segment of information technology. Foundations of SQL Server 2005 Business Intelligence is the quickest path to understanding BI, and it is essential reading for all who work with SQL Server 2005. It is written from a practical perspective, perfect for anyone who uses the tools in SQL Server 2005s extraordinarily rich BI product suite.

This book explains how best to use Analysis Services, SQL Server Integration Services, SQL Server Reporting Services, and SQL Server Data Mining. It also describes best practices for implementing end-to-end BI solutions in small, medium, and large business environments. And it provides important information about integrating BI with various client tools, including Excel, Business Scorecards, Proclarity, and SharePoint Portal Server. Developers, end users, and even managers will find this an enlightening guide to the power and promise of SQL Server 2005 BI.

Table of Contents

  1. About the Author
  2. About the Technical Reviewer
  3. Acknowledgments
  4. 1. What Is Business Intelligence?
    1. Just What Is BI?
    2. Defining BI Using Microsoft's Tools
      1. What Microsoft Products Are Involved?
      2. BI Languages
    3. Understanding BI from an End User's Perspective
      1. Demonstrating the Power of BI Using Excel 2003 Pivot Tables
    4. Understanding BI Through the Sample
    5. Understanding the Business Problems that BI Addresses
      1. Reasons to Switch to Microsoft's BI Tools
    6. Summary
  5. 2. OLAP Modeling
    1. Modeling OLAP Source SchemasStars
      1. Understanding the Star Schema
      2. Understanding a Dimension Table
      3. Why Create Star Schemas?
      4. Effectively Creating Star Schema Models Using Grain Statements
      5. Tools for Creating Your OLAP Model
    2. Modeling Source SchemasSnowflakes and Other Variations
      1. Understanding the Snowflake Schema
      2. Knowing When to Use Snowflakes
      3. Considering Other Possible Variations
      4. Choosing Whether to Use Views Against the Relational Data Sources
    3. Understanding Dimensional Modeling (UDM)
      1. Using the UDM
      2. The Slowly Changing Dimension (SCD)
      3. The Rapidly Changing Dimension (RCD)
      4. Writeback Dimension
    4. Understanding Fact (Measure) Modeling
      1. Calculated Measure vs. Derived Measure
    5. Other Types of Modeling
      1. Data Mining
      2. KPIs (Key Performance Indicators)
      3. Actions, Perspectives, Translations
      4. Source Control and Other Documentation Standards
    6. Summary
  6. 3. Introducing SSIS
    1. Understanding ETL
      1. Data Maps
      2. Staging Servers
      3. ETL Tools for BI/SSIS Packages
    2. Basic SSIS Packages Using BIDS
      1. Developing SSIS Packages
      2. Designing SSIS Packages
    3. Adding Transformations to the Data Flow
    4. Summary
  7. 4. Using SSAS
    1. Using BIDS to Build a Cube
      1. Building Your First Cube
    2. Refining Your Cube
      1. Reviewing Measures
      2. Reviewing Dimensions: Attributes
      3. Reviewing Dimensions: Hierarchies
      4. Reviewing Dimensions: Member Properties
    3. Summary
  8. 5. Intermediate OLAP Modeling
    1. Adding Key Performance Indicators (KPIs)
      1. Implementing KPIs in SSAS
      2. Considering Other KPI Issues
    2. Using Perspectives and Translations
      1. Perspectives
      2. Translations
      3. Localizing Measure Values
    3. Using Actions
      1. Other Types of Modeling
    4. Summary
  9. 6. Advanced OLAP Modeling
    1. Multiple Fact Tables in a Single Cube
      1. Considering Nulls
    2. Modeling Nonstar Dimensions
      1. Snowflake Dimensions
      2. Degenerate Dimensions
      3. Parent-Child Dimensions
      4. Many-to-Many Dimensions
      5. Role-Playing Dimensions
      6. Writeback Dimensions
    3. Modeling Changing Dimensions and More
      1. Error Handling for Dimension Attribute Loads
    4. Using the Business Intelligence Wizard
      1. What's Next?
    5. Summary
  10. 7. Cube Storage and Aggregation
    1. Using the Default Storage: MOLAP
      1. XMLA (XML for Analysis)
      2. Aggregations
      3. MOLAP as Default in SSAS
    2. Adding Aggregations
    3. Advanced Storage: MOLAP, HOLAP, or ROLAP
      1. Considering Other Types of Storage
    4. ROLAP Dimensions
      1. Huge Dimensions
    5. Summarizing OLAP Storage Options
    6. Using Proactive Caching
      1. Notification Settings for Proactive Caching
      2. Fine-Tuning Proactive Caching
    7. Deciding Among OLTP Partitioning, OLAP Partitioning, or Both
      1. Relational Table Partitioning in SQL Server 2005
      2. Other OLAP Partition Configurations
    8. Cube and Dimension Processing Options
      1. What's Next?
    9. Summary
  11. 8. Intermediate SSIS
    1. General ETL Package-Design Best Practices
    2. Creating the SSIS Package from Scratch
      1. Configuring Connections
      2. Using Data Source Views (DSVs)
    3. Reviewing the Included Samples Packages
    4. Adding Control Flow Tasks
      1. Container Tasks
      2. SQL Tasks
      3. File System Tasks
      4. Operating System Tasks
      5. Script Tasks
      6. Remote Tasks
      7. SSAS Tasks
      8. Precedence Constraints
      9. Using Expressions with Precedence Constraints
    5. Understanding Data Flow Transformations
      1. Understanding Data Sources and Destinations
    6. Adding Transformations to the Data Flow
    7. Adding Data Transformations
      1. Split Data Transformations
      2. Translate Data Transformations
      3. SSAS Data Transformations
      4. Slowly Changing Dimension Transformation
      5. Sample Data Transformations
      6. Run Command Data Transformations
      7. Enterprise Edition Only Data Transformations
    8. Using the Dynamic Package Configuration Wizard
      1. SSIS Expressions
    9. Summary
  12. 9. Advanced SSIS
    1. Understanding Package Execution
      1. Data Viewers
    2. Debugging SSIS Packages
    3. Logging Execution Results
      1. Error Handling
      2. Event Handlers
    4. Deploying the Package and Configuring Runtime Settings
      1. SSIS Package Deployment Options
      2. SSIS Package Execution Options
      3. SSIS Package Security
    5. Placing Checkpoints
    6. Using Transactions in SSIS Packages
    7. Summary
  13. 10. Introduction to MDX
    1. Understanding Basic MDX Query Syntax
      1. Writing Your First MDX Query
      2. Members, Tuples, and Sets
    2. Adding Calculated Members, Named Sets, and Script Commands
      1. Using Calculated Measures
      2. Named Sets
      3. Script Commands
    3. Understanding Common MDX Functions
    4. New or Updated MDX Functions
    5. Adding .NET Assemblies to Your SSAS Project
      1. Configuring Assemblies
    6. Summary
  14. 11. Introduction to Data Mining
    1. Defining SSAS Data Mining
      1. More Data Mining Concepts
      2. Architectural Considerations
    2. Reviewing Data Mining Structures
      1. Mining Structure Viewers
      2. Mining Accuracy Charts
      3. Mining Prediction Viewers
    3. Understanding the Nine Included Data Mining Algorithms
      1. Using the Mining Structure Wizard
      2. Content and Data Types
    4. Processing Mining Models
      1. SSIS and Data Mining
    5. Working with the DMX Language
      1. A Simple DMX Query
    6. Data Mining Clients
    7. Summary
  15. 12. Reporting Tools
    1. Using Excel 2003: Pivot Charts and More
      1. Limitations of Excel 2003 as an SSAS Client
    2. Using SQL Server Reporting Services (SSRS)
    3. Producing Reports with Report Builder
    4. Working with .NET 2.0 Report Viewer Controls
    5. Understanding SharePoint 2003 Web Parts
    6. Examining Business Scorecard Manager (BSM) 2005
    7. Considering ProClarity and Data Mining Clients
      1. ProClarity
      2. Data Mining Clients
    8. Summary
  16. 13. SSAS Administration
    1. Understanding Offline vs. Online Mode in BIDS
    2. Reviewing SSMS/SSAS Administration
      1. XML for Analysis (XMLA)
      2. SSAS Deployment Wizard
      3. Server Synchronization
    3. Thinking About Disaster Recovery
    4. Considering Security
      1. Connection Strings
      2. Security Roles
      3. Other Security Planning Issues
    5. Understanding Performance Tuning
    6. Applying Scalability
    7. Using High Availability Clustering
    8. Summary
  17. 14. Integration with Office 2007
    1. SQL Server 2005 SP2
    2. Exploring Excel 2007
      1. KPI Support
      2. Configuring Excel 2007 as a Data Mining Client
      3. Using Excel 2007 as a Data Mining Client
      4. Using the Excel 2007 Data Preparation Group
      5. Using the Excel 2007 Data Modeling Group
      6. Using the Excel 2007 Accuracy and Validation Group
      7. Additions to the Final Release
    3. Integrating Microsoft Office SharePoint Server 2007 (MOSS)
      1. Using Excel 2007 on the Web (Excel Services)
      2. MOSS Data Connection Libraries
      3. MOSS KPIs (Key Performance Indicators)
      4. Using the SSRS Report Center and Reporting Web Parts
      5. MOSS Business Data Catalog (BDC)
    4. Exploring Performance Point Server (PPS) 2007
    5. Summary
    6. Conclusion