You are previewing Foundations of SQL Server 2008 R2 Business Intelligence, Second Edition.
O'Reilly logo
Foundations of SQL Server 2008 R2 Business Intelligence, Second Edition

Book Description

Foundations of SQL Server 2008 R2 Business Intelligence introduces the exciting gamut of business intelligence tools included with SQL Server 2008. Microsoft has designed SQL Server 2008 to be more than just a database. It's a complete business intelligence (BI) platform. The database is at the core, and surrounding the database are tools for data mining, modeling, reporting, analyzing, charting, and integration with other, enterprise-level software packages.

SQL Server 2008 puts an incredible amount of BI functionality at your disposal. But how do you take advantage of it? That's what this book is all about. Authors Guy Fouché and Lynn Langit show how to implement end-to-end BI solutions using SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), and other tools in the Microsoft BI toolkit. You'll learn about all-new features such as PowerPivot and Report Builder 3.0. Also provided are clear examples of predictive analysis made possible through powerful data mining features in SQL Server.

If you're an analyst or developer working with SQL Server 2008 who is charged with delivering results that drive business success, you can't afford to be without this book; you can't afford to ignore the powerful BI suite that Microsoft has placed at your disposal.

  • Provides the "big picture" of Microsoft's BI tool suite

  • Covers PowerPivot and other game-changing technologies introduced alongside SQL Server 2008 Release 2

  • Gives a practical analysis of features based on real-world practices

What you'll learn

  • Apply dimensional- and fact-based modeling to create OLAP schemas

  • Optimize data extraction and transformation with SQL Server Integration Services

  • Develop OLAP and data-mining solutions using SQL Server Analysis Services

  • Create compelling reports using the new Report Builder 3.0 in SQL Server Reporting Services

  • Deliver self-service business-intelligence via Microsoft PowerPivot

  • Manage and maintain control over your business intelligence environment

Who this book is for

Foundations of SQL Server 2008 R2 Business Intelligence is for business intelligence analysts and developers wanting to employ the full stack of business intelligence tools that are part of SQL Server. The book also appeals to database administrators managing databases in support of business intelligence efforts.

Table of Contents

  1. Title Page
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. About the Authors
  6. About the Technical Reviewer
  7. Acknowledgments
  8. CHAPTER 1: What Is Business Intelligence?
    1. Just What Is Business Intelligence?
    2. Defining BI Using Microsoft's Tools
    3. Understanding BI from an End User's Perspective
    4. Understanding BI Through the Sample
    5. Understanding the Business Problems That BI Addresses
    6. Reasons to Switch to Microsoft's BI Tools
    7. Summary
  9. CHAPTER 2: OLAP Modeling Concepts
    1. Modeling OLAP Source Schemas—Stars
    2. Modeling Source Schemas—Snowflakes and Other Variations
    3. Understanding Unified Dimensional Modeling
    4. Understanding Fact (Measure) Modeling
    5. Other Types of Modeling
    6. Source Control and Other Documentation Standards
    7. Summary
  10. CHAPTER 3: Introducing OLAP Modeling with SSAS
    1. Using BIDS to Build a Cube
    2. Refining Your Cube
    3. Summary
  11. CHAPTER 4: Intermediate OLAP Modeling with SSAS
    1. Adding Key Performance Indicators (KPIs)
    2. Using Perspectives and Translations
    3. Using Actions
    4. Summary
  12. CHAPTER 5: Advanced OLAP Modeling with SSAS
    1. Multiple Fact Tables in a Single Cube
    2. Nulls
    3. Nonstar Dimensions
    4. Dimensions That Change
    5. Error Handling for Dimension Attribute Loads
    6. Using the Business Intelligence Wizard
    7. Summary
  13. CHAPTER 6: Cube Storage and Aggregation
    1. Using the Default Storage: MOLAP
    2. Adding Aggregations
    3. Using Advanced Storage
    4. Using Proactive Caching
    5. Deciding Between OLTP and OLAP Partitioning
    6. Choosing Cube and Dimension Processing Options
    7. Summary
  14. CHAPTER 7: Introducing SSIS
    1. Understanding ETL
    2. Building Basic SSIS Packages
    3. Adding Transformations to the Data Flow
    4. Summary
  15. CHAPTER 8: Intermediate SSIS
    1. Common ETL Package-Design Practices
    2. Creating an SSIS Package from Scratch
    3. Reviewing the Included Samples Packages
    4. Adding Control Flow Tasks
    5. Understanding Data Flow Transformations
    6. Adding Data Transformations
    7. Using the Dynamic Package Configuration Wizard
    8. Assigning SSIS Expressions
    9. Summary
  16. CHAPTER 9: Advanced SSIS
    1. Understanding Package Execution
    2. Deploying SSIS Packages
    3. Placing Checkpoints
    4. Using Transactions in SSIS Packages
    5. Data Profiling
    6. Summary
  17. CHAPTER 10: Reporting Tools
    1. Using Excel Pivot Tables and Pivot Charts
    2. Using SQL Server Reporting Services
    3. Building Your First SSRS Report
    4. Producing Reports with Report Builder
    5. Summary
  18. CHAPTER 11: Data Mining with Excel
    1. Exploring Excel 2010
    2. Using Excel for Data Mining
    3. Summary
  19. CHAPTER 12: Introducing PowerPivot
    1. The PowerPivot for Excel GUI
    2. Using PowerPivot with Adventure Works
    3. Summary
  20. CHAPTER 13: Introduction to MDX
    1. MDX Query Syntax
    2. Calculated Members, Named Sets, and Script Commands
    3. Common MDX Functions
    4. Summary
  21. CHAPTER 14: Introduction to Data Mining
    1. Defining SSAS Data Mining
    2. Reviewing DataMining Structures
    3. Understanding and Using the Included DataMining Algorithms
    4. Processing Mining Models
    5. Working with the DMX Language
    6. Summary
    1. Creating A HIERARCHYID Table
    2. Adding Data to the Table
    3. Displaying Hierarchical Data in SSMS
  23. Index