Smart Business Intelligence Solutions with Microsoft SQL Server 2008

Table of Contents

Foreword; Acknowledgments; Lynn Langit; Davide Mauri; Sahil Malik; Introduction; Who This Book Is For; What This Book Is About; Prerelease Software; Hardware and Software Requirements; Find Additional Content Online; Support for This Book; Part I: Business Intelligence for Business Decision Makers and Architects; Chapter 1: Business Intelligence Basics; 1.1 Business Intelligence and Data Modeling; 1.2 OLTP and OLAP; 1.3 Common BI Terminology; 1.4 Core Components of a Microsoft BI Solution; 1.5 Optional Components of a Microsoft BI Solution; 1.6 Query Languages Used in BI Solutions; 1.7 Summary; Chapter 2: Visualizing Business Intelligence Results; 2.1 Matching Business Cases to BI Solutions; 2.2 Understanding Business Intelligence from a User’s Perspective; 2.3 Elements of a Complete BI Solution; 2.4 Common Business Challenges and BI Solutions; 2.5 Measuring the ROI of BI Solutions; 2.6 Summary; Chapter 3: Building Effective Business Intelligence Processes; 3.1 Software Development Life Cycle for BI Projects; 3.2 Skills Necessary for BI Projects; 3.3 Forming Your Team; 3.4 Summary; Chapter 4: Physical Architecture in Business Intelligence Solutions; 4.1 Planning for Physical Infrastructure Change; 4.2 Determining the Optimal Number and Placement of Servers; 4.3 Understanding Security Requirements; 4.4 Backup and Restore; 4.5 Auditing and Compliance; 4.6 Source Control; 4.7 Summary; Chapter 5: Logical OLAP Design Concepts for Architects; 5.1 Designing Basic OLAP Cubes; 5.2 Summary; Part II: Microsoft SQL Server 2008 Analysis Services for Developers; Chapter 6: Understanding SSAS in SSMS and SQL Server Profiler; 6.1 Core Tools in SQL Server Analysis Services; 6.2 Summary; Chapter 7: Designing OLAP Cubes Using BIDS; 7.1 Using BIDS; 7.2 Working in Solution Explorer; 7.3 Building OLAP Cubes in BIDS; 7.4 Understanding Dimensions; 7.5 Using Dimensions; 7.6 Building Your First OLAP Cube; 7.7 Summary; Chapter 8: Refining Cubes and Dimensions; 8.1 Refining Your First OLAP Cube; 8.2 Using Cube and Dimension Properties; 8.3 Advanced Cube and Dimension Properties; 8.4 Summary; Chapter 9: Processing Cubes and Dimensions; 9.1 Building, Processing, and Deploying OLAP Cubes; 9.2 Partitioning; 9.3 Implementing Aggregations; 9.4 Implementing Advanced Storage with MOLAP, HOLAP, or ROLAP; 9.5 Proactive Caching; 9.6 Cube and Dimension Processing Options; 9.7 Summary; Chapter 10: Introduction to MDX; 10.1 The Importance of MDX; 10.2 Writing Your First MDX Queries; 10.3 MDX Core Functions; 10.4 Filtering MDX Result Sets; 10.5 Calculated Members and Named Sets; 10.6 The TopCount Function; 10.7 Rank Function and Combinations; 10.8 Head and Tail Functions; 10.9 Hierarchical Functions in MDX; 10.10 Date Functions; 10.11 Summary; Chapter 11: Advanced MDX; 11.1 Querying Dimension Properties; 11.2 Looking at Date Dimensions and MDX Seasonality; 11.3 Creating Permanent Calculated Members; 11.4 Using IIf; 11.5 About Named Sets; 11.6 About Scripts; 11.7 Understanding SOLVE_ORDER; 11.8 Creating Key Performance Indicators; 11.9 Using MDX with SSRS and PerformancePoint Server; 11.10 Summary; Chapter 12: Understanding Data Mining Structures; 12.1 Reviewing Business Scenarios; 12.2 Working in the BIDS Data Mining Interface; 12.3 Data Mining Algorithms; 12.4 The Art of Data Mining; 12.5 Summary; Chapter 13: Implementing Data Mining Structures; 13.1 Implementing the CRISP-DM Life Cycle Model; 13.2 Building Data Mining Structures using BIDS; 13.3 Adding Data Mining Models Using BIDS; 13.4 Processing Mining Models; 13.5 Validating Mining Models; 13.6 Data Mining Prediction Queries; 13.7 Data Mining and Integration Services; 13.8 Data Mining Object Processing; 13.9 Data Mining Clients; 13.10 Summary; Part III: Microsoft SQL Server 2008 Integration Services for Developers; Chapter 14: Architectural Components of Microsoft SQL Server 2008 Integration Services; 14.1 Overview of Integration Services Architecture; 14.2 Integration Services Packages; 14.3 The Integration Services Object Model and Components; 14.4 The Integration Services Runtime; 14.5 The Integration Services Data Flow Engine; 14.6 Log Providers; 14.7 Deploying Integration Services Packages; 14.8 Summary; Chapter 15: Creating Microsoft SQL Server 2008 Integration Services Packages with Business Intelligence Development Studio; 15.1 Integration Services in Visual Studio 2008; 15.2 Connection Managers; 15.3 Control Flow; 15.4 Data Flow; 15.5 Variables; 15.6 Expressions; 15.7 Summary; Chapter 16: Advanced Features in Microsoft SQL Server 2008 Integration Servicessssss; 16.1 Error Handling in Integration Services; 16.2 Events, Logs, Debugging, and Transactions in SSIS; 16.3 Logging and Events; 16.4 Debugging Integration Services Packages; 16.5 Checkpoints and Transactions; 16.6 Best Practices for Designing Integration Services Packages; 16.7 Data Profiling; 16.8 Summary; Chapter 17: Microsoft SQL Server 2008 Integration Services Packages in Business Intelligence Solutions; 17.1 ETL for Business Intelligence; 17.2 Loading OLAP Cubes; 17.3 Moving to Star Schema Loading; 17.4 Updates; 17.5 ETL for Data Mining; 17.6 Summary; Chapter 18: Deploying and Managing Solutions in Microsoft SQL Server 2008 Integration Services; 18.1 Solution and Project Structures in Integration Services; 18.2 Source Code Control; 18.3 The Deployment Challenge; 18.4 SQL Server Agent and Integration Services; 18.5 The SSIS Service; 18.6 Summary; Chapter 19: Extending and Integrating SQL Server 2008 Integration Services; 19.1 Introduction to SSIS Scripting; 19.2 Visual Studio Tools for Applications; 19.3 The Script Task; 19.4 The Script Component; 19.5 Overview of Custom SSIS Task and Component Development; 19.6 Overview of SSIS Integration in Custom Applications; 19.7 Summary; Part IV: Microsoft SQL Server Reporting Services and Other Client Interfaces for Business Intelligence; Chapter 20: Creating Reports in SQL Server 2008 Reporting Services; 20.1 Understanding the Architecture of Reporting Services; 20.2 Installing and Configuring Reporting Services; 20.3 Creating Reports with BIDS; 20.4 Deploying Reports; 20.5 Summary; Chapter 21: Building Reports for SQL Server 2008 Reporting Services; 21.1 Using the Query Designers for Analysis Services; 21.2 Understanding Report Items; 21.3 Using Report Builder; 21.4 Summary; Chapter 22: Advanced SQL Server 2008 Reporting Services; 22.1 Adding Custom Code to SSRS Reports; 22.2 Viewing Reports in Word or Excel 2007; 22.3 URL Access; 22.4 Embedding Custom ReportViewer Controls; 22.5 About Report Parameters; 22.6 About Security Credentials; 22.7 About the SOAP API; 22.8 Deployment—Scalability and Security; 22.9 Administrative Scripting; 22.10 Summary; Chapter 23: Using Microsoft Excel 2007 as an OLAP Cube Client; 23.1 Using the Data Connection Wizard; 23.2 Working with the Import Data Dialog Box; 23.3 Understanding the PivotTable Interface; 23.4 Creating a Sample PivotTable; 23.5 Offline OLAP; 23.6 Excel OLAP Functions; 23.7 Extending Excel; 23.8 Summary; Chapter 24: Microsoft Office 2007 as a Data Mining Client; 24.1 Installing Data Mining Add-ins; 24.2 Data Mining Integration with Excel 2007; 24.3 Data Mining Integration in Visio 2007; 24.4 Client Visualization; 24.5 Data Mining in the Cloud; 24.6 Summary; Chapter 25: SQL Server Business Intelligence and Microsoft Office SharePoint Server 2007; 25.1 Excel Services; 25.2 SQL Server Reporting Services with Office SharePoint Server 2007; 25.3 PerformancePoint Server; 25.4 Summary; About the Authors; Lynn Langit; Davide Mauri; Sahil Malik; Kevin Goff; John Welch;