Business Intelligence Development 107

1 ContentsContents at a Glance vContents viiAcknowledgments xvIntroduction xviiWho Is This Book For? ..xviiHow Is This Book Organized? ..xviiPre-Release Software ..xviiiSupport for This Book ..xixWe Want to Hear from You ..xixDatabase Administration 1C H A P T E R 1 3 SQL server 2008 R2 Editions and Enhancements 3 SQL server 2008 R2 Enhancements for DBAs ..3 Application and Multi- server Administration Enhancements 4 Additional SQL server 2008 R2 Enhancements for DBAs 8 Advantages of Using Windows server 2008 R2 ..10 SQL server 2008 R2 Editions ..11 Premium Editions 12 Core Editions 12 Specialized Editions 13 Hardware and Software Requirements.

2 14 Installation, Upgrade, and Migration Strategies ..16 The In-Place Upgrade 16 Side-by-Side Migration 18C H A P T E R 2 2 1 Multi- server Administration 21 The SQL server Utility ..21 SQL server Utility Key Concepts 23 UCP Prerequisites 25 UCP Sizing and Maximum Capacity Specifications 25 Creating a UCP ..26 Creating a UCP by Using SSMS 26 Creating a UCP by Using Windows PowerShell 28 UCP Post-Installation Steps 29 Enrolling SQL server Instances ..29 Managed Instance Enrollment Prerequisites 30 Enrolling SQL server Instances by Using SSMS 30 Enrolling SQL server Instances by Using Windows PowerShell 32 The Managed Instances Dashboard 32 Managing Utility Administration Settings.

3 33 Connecting to a UCP 33 The Policy Tab 34 The Security Tab 37 The Data Warehouse Tab 39C H A P T E R 3 4 1 Data-Tier Applications 41 Introduction to Data-Tier Applications ..41 The Data-Tier Application Life Cycle 42 Common Uses for Data-Tier Applications 43 Supported SQL server Objects 44 Visual Studio 2010 and Data-Tier Application Projects ..45 Launching a Data-Tier Application Project Template in Visual Studio 2010 45 Importing an Existing Data-Tier Application Project into Visual Studio 2010 47 Extracting a Data-Tier Application with SQL server Management Studio 49 Installing a New DAC Instance with the Deploy Data-Tier Application Wizard.

4 52 Registering a Data-Tier Application ..55 Deleting a Data-Tier Application ..56 Upgrading a Data-Tier Application ..59C H A P T E R 4 6 3 High Availability and Virtualization Enhancements 63 Enhancements to High Availability with Windows server 2008 R2 ..63 Failover Clustering with Windows server 2008 R2 ..64 Traditional Failover Clustering 65 Guest Failover Clustering 67 Enhancements to the Validate A Configuration Wizard 68 The Windows server 2008 R2 Best Practices Analyzer 71 SQL server 2008 R2 Virtualization and Hyper-V ..72 Live Migration Support Through CSV 72 Windows server 2008 R2 Hyper-V System Requirements 73 Practical Uses for Hyper-V and SQL server 2008 R2 74 Implementing Live Migration for SQL server 2008 R2.

5 75 Enabling CSV 76 Creating a SQL server VM with Hyper-V 76 Configuring a SQL server VM for Live Migration 79 Initiating a Live Migration of a SQL server VM 83C H A P T E R 5 8 5 Consolidation and Monitoring 85 SQL server Consolidation Strategies ..85 Consolidating Databases and Instances 86 Consolidating SQL server Through Virtualization 87 Using the SQL server Utility for Consolidation and Monitoring ..89 Using the SQL server Utility Dashboard ..90 Using the Managed Instances Viewpoint ..95 The Managed Instances List View Columns 96 The Managed Instances Detail Tabs 97 Using the Data-Tier Application Viewpoint.

6 100 The Data-Tier Application List View 102 The Data-Tier Application Tabs 102 Business Intelligence Development 107C H A P T E R 6 1 0 9 Scalable Data Warehousing 109 Parallel Data Warehouse Architecture ..109 Data Warehouse Appliances 109 Processing Architecture 110 The Multi-Rack System 110 Hub-and-Spoke Architecture 115 Data Management ..115 Shared Nothing Architecture 115 Data Types 120 Query Processing 121 Data Load Processing 121 Monitoring and Management ..122 Business Intelligence Integration ..123 Integration Services 123 Reporting Services 123 Analysis Services and PowerPivot 123C H A P T E R 7 1 2 5 Master Data Services 125 Master Data Management.

7 125 Master Data Challenges 125 Key Features of Master Data Services 126 Master Data Services Components ..127 Master Data Services Configuration Manager 128 The Master Data Services Database 128 Master Data Manager 128 Data Stewardship ..129 Model Objects 129 Master Data Maintenance 131 Business Rules 132 Transaction Logging 134 Integration ..135 Importing Master Data 135 Exporting Master Data 136 Administration ..137 Versions 137 Security 138 Model Deployment 142 Programmability ..142 The Class Library 142 Master Data Services Web Service 143 Matching Functions 143C H A P T E R 8 1 4 5 Complex Event Processing with StreamInsight 145 Complex Event Processing.

8 145 Complex Event Processing Applications 145 StreamInsight Highlights 146 StreamInsight Architecture ..146 Data Structures 147 The CEP server 147 Deployment Models 149 Application Development ..150 Event Types 150 Adapters 151 Query Templates 154 Queries 155 Query Template Binding 162 The Query Object 163 The Management Interface ..163 Diagnostic Views 163 Windows PowerShell Diagnostics 164C H A P T E R 9 1 6 5 Reporting Services Enhancements 165 New Data Sources ..165 Expression Language Improvements ..165 Combining Data from More Than One Dataset 166 Aggregation 168 Conditional Rendering Expressions 169 Page Numbering 170 Read/Write Report Variable 170 Layout Control.

9 171 Pagination Properties 172 Data Synchronization 173 Text Box Orientation 174 Data Visualization ..175 Data Bars 175 Sparklines 176 Indicators 176 Maps 177 Reusability ..178 Shared Datasets 179 Cache Refresh 179 Report Parts 180 Atom Data Feed 182 Report Builder 3 .0 ..183 Edit Sessions 183 The Report Part Gallery 183 Report Access and Management ..184 Report Manager Improvements 184 Report Viewer Improvements 186 Improved Browser Support 186 RDL Sandboxing 186 SharePoint Integration ..187 Improved Installation and Configuration 187RS Utility Scripting 187 SharePoint Lists as Data Sources 187 SharePoint Unified Logging Service 188C H A P T E R 1 0 1 8 9 Self-Service Analysis with PowerPivot 189 PowerPivot for Excel.

10 190 The PowerPivot Add-in for Excel 190 Data Sources 191 Data Preparation 193 PowerPivot Reports 196 Data Analysis Expressions 199 PowerPivot for SharePoint ..201 Architecture 201 Content Management 204 Data Refresh 205 Linked Documents 205 The PowerPivot Web Service 205 The PowerPivot Management Dashboard ..206 About the Authors 215 PUBLISHED BYMicrosoft PressA Division of Microsoft CorporationOne Microsoft WayRedmond, Washington 98052-6399 Copyright 2010 by Microsoft CorporationAll rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the of Congress Control Number: 2010925075 Printed and bound in the United States of 2 3 4 5 6 7 8 9 WCT 5 4 3 2 1 0 Distributed in Canada by Fenn and Company CIP catalogue record for this book is available from the British Press books are available through booksellers and distributors worldwide.