Synopses & Reviews
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.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
Discover how to:
About the Author
Lynn Langit is a developer evangelist for the Microsoft MSDN® team. Prior to working at Microsoft, she founded and served as lead architect of a development firm that created BI solutions. She holds a number of Microsoft certifications, including MCITP, MCSD, MCDBA, and MCT.
Kevin S. Goff is a Microsoft MVP and a trainer and BI practice lead for a Microsoft Gold Certified Partner.
Davide Mauri, MCP, MCAD, MCDBA, MCT, is a Microsoft MVP who has worked with SQL Server since version 6.5.
Sahil Malik is a consultant and trainer who has been a Microsoft MVP and an INETA speaker for many years.
John Welch is a Microsoft MVP and the chief architect at a consulting firm specializing in BI solutions.
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 Users 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 DeploymentScalability 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;