Microsoft SQL Server High Availability

Cover......Page 1
Contents......Page 8
Acknowledgments......Page 17
About the Author......Page 18
Preface......Page 19
 What Is Business Intelligence?......Page 22
 Transaction Systems and the Search for Information......Page 23
  Why OLTP Reporting and Analysis Fails to Deliver......Page 24
  The Data Warehouse Design......Page 26
  Time and the Data Warehouse......Page 28
  Getting Data into the Data Warehouse......Page 29
 OLAP to the Rescue......Page 31
  Getting Information out of OLAP Databases......Page 32
  Why Is OLAP So Fast?......Page 33
 Dimensional Modeling Concepts......Page 35
  Hierarchies......Page 36
  Stars and Snowflakes......Page 37
  Choosing Between Star and Snowflake for a Dimension......Page 38
  Using Surrogate Keys......Page 39
  Designing a Dimensional Data Model......Page 40
  Making a List of Candidate Attributes and Dimensions......Page 41
  Grouping the Measures with the Same Grain into Fact Tables......Page 42
  Fitting the Source Data to the Model......Page 43
  A Business Value-Based Approach to BI Projects......Page 44
  Business Intelligence Project Pitfalls......Page 45
 Summary......Page 49
  Development and Management Tools......Page 50
 SQL Server Database Engine......Page 52
  Management......Page 53
  Security......Page 54
  Availability......Page 55
 Integration Services......Page 56
  Designing Packages......Page 58
  Deploying and Configuring Packages......Page 62
  Analysis Services Architecture......Page 63
  Development Environment......Page 65
  Managing and Securing Analysis Services......Page 66
  The Unified Dimensional Model......Page 67
  Support for Large and Mission-Critical BI Solutions......Page 68
  Reporting Architecture......Page 69
  Reporting Services Features......Page 72
  Managing and Securing Reports......Page 75
 Data Mining......Page 76
  Data Mining Architecture......Page 77
  Data Mining Features......Page 80
  Full-Text Search......Page 81
 Summary......Page 82
  Problem Statement......Page 84
  Business Requirements......Page 85
  Business Benefits......Page 86
  What Process Will We Be Focusing On?......Page 87
  What Level of Detail Do We Need?......Page 88
  What Are the Ways of Looking At the Information?......Page 89
  What Are We Measuring?......Page 94
 Technical Solution......Page 98
  Building Dimension Tables......Page 99
  Building Fact Tables......Page 104
  Securing the Data Warehouse......Page 108
  Deployment......Page 111
  Maintenance......Page 113
  Operations......Page 114
 Next Steps......Page 116
  Using the BI Development Studio to Generate the Data Warehouse......Page 117
 Summary......Page 118
Chapter 4 Building a Data Integration Process......Page 120
 Solution Overview......Page 121
  High-Level Architecture......Page 122
  Business Benefits......Page 123
  The Budget Data......Page 124
  Working with Extended Measures......Page 125
 Technical Solution......Page 126
  Data Sources and Destinations......Page 127
  Loading the Dimensions......Page 129
  Loading the Fact Table......Page 137
  Loading the Budget Information from Excel......Page 140
  Loading Multiple Sets of Data......Page 146
  Deployment......Page 149
  Deploying Packages to a New Server......Page 154
  Maintenance......Page 155
  Operations......Page 156
  Data Quality......Page 158
  Other Transformations......Page 159
  Summary......Page 160
  Problem Statement......Page 162
  Business Requirements......Page 163
  High-Level Architecture......Page 164
  How Do We Handle \"On-Time\" Shipments?......Page 166
  How Late Were These Shipments, Anyway?......Page 168
  Getting Started with Analysis Services......Page 169
  Building the Initial Cube......Page 172
  Setting Up the Dimensions......Page 178
  Modifying the Cube......Page 182
  Deployment......Page 190
  Maintenance......Page 193
  Operations......Page 194
  Using BI Development Studio in Online Mode......Page 197
 Summary......Page 198
  Problem Statement......Page 200
  Business Requirements......Page 201
  High-Level Architecture......Page 202
 Data Model......Page 205
  Who Does the Work?......Page 206
  What Are They Working On?......Page 207
  How Do We Measure What Work They Are Doing?......Page 208
  How Much Work Will They Be Doing in the Future?......Page 209
  Getting Started with Reporting Services......Page 210
  Accessing Reports......Page 214
  Giving Users What They Want......Page 216
  Presenting Information......Page 218
  Securing the Information......Page 223
  Accessing the Database......Page 225
  Subscribing to Reports......Page 226
 Managing the Solution......Page 229
  Deployment......Page 230
  Maintenance......Page 231
  Operations......Page 232
  Using Report Builder for End-User Reporting......Page 233
 Summary......Page 234
 Business Problem......Page 236
 Solution Overview......Page 237
  High-Level Architecture......Page 238
 Data Model......Page 239
  Merging and De-duplicating Customer Data......Page 241
  Dealing with Missing Dimension Members......Page 250
  Adding Row Counts for Auditing......Page 260
  Preventing Bad Data at the Source......Page 261
  Operations......Page 262
 Next Steps......Page 263
 Summary......Page 264
 Business Problem......Page 266
  Business Requirements......Page 267
  Business Benefits......Page 268
 Data Model......Page 269
  Managing Dimensions When History Doesn\'t Matter......Page 270
  Managing Dimensions to Preserve History......Page 271
  Updating Dimensions with Changing Attributes......Page 274
  Preserving Information for Dimensions with Historical Attributes......Page 277
  Detecting Changes in Source Dimension Data......Page 281
  Inserting Facts with Surrogate Key Lookups from Changing Dimensions......Page 282
  Analysis Services Dimension Changes......Page 284
 Managing the Solution......Page 287
  Operations......Page 288
  Dealing with Updated Facts......Page 290
 Summary......Page 291
 Business Problem......Page 292
  Business Requirements......Page 293
  High-Level Architecture......Page 294
 Data Model......Page 296
  How Good Are We at Handling Calls?......Page 297
  How Are Our Crime Reduction Initiatives Going?......Page 299
  How Are We Improving Professional Standards?......Page 300
  What Does the Public Think?......Page 301
  Analysis Services Database......Page 302
  Building the Reports......Page 311
  Building the Scorecard Portal......Page 316
  Deployment......Page 323
  Security......Page 324
  Maintenance......Page 326
 Next Steps......Page 327
  Supporting Multiple Languages with Analysis Services......Page 328
 Summary......Page 329
  Problem Statement......Page 330
  Business Requirements......Page 331
  High-Level Architecture......Page 332
 Data Model......Page 335
  How Often Are Users Visiting the Web Site?......Page 336
  Who Is Using the Web Site?......Page 338
  What Interesting Attributes Can We Track?......Page 339
  Adding Visit Information to the Data Warehouse......Page 340
  How We Will Be Using Data Mining......Page 342
  Approaching the Customer-Segmentation Problem......Page 343
  Getting Started with Data Mining......Page 344
  Analyzing with Data Mining Information......Page 350
  Creating a Model for Product Recommendations......Page 351
  Add Data Mining Intelligence into a Web Application......Page 355
  Deployment......Page 358
  Operations......Page 359
  Other Data Mining Possibilities......Page 360
 Summary......Page 361
 Business Problem......Page 364
  Problem Statement......Page 365
  Business Requirements......Page 366
  High-Level Architecture......Page 367
 Data Model......Page 370
  Partitioned Tables......Page 373
  Loading Large Volumes of Data......Page 379
  Partitioning Analysis Services Cubes......Page 383
  Aggregation Design......Page 387
  Large Dimension Support in SQL Server 2005......Page 388
  Managing Table Partitions......Page 389
  Managing Cube Partitions......Page 393
  Automating the Process......Page 396
  Scaling out Using Analysis Services Database Synchronization......Page 397
 Summary......Page 398
 Business Problem......Page 400
  High-Level Architecture......Page 401
  Business Benefits......Page 402
 Data Model......Page 403
 Technical Solution......Page 404
  Cube Design for Real Time......Page 405
  Real-Time ETL—Working with the Data Source......Page 414
  Operations......Page 415
  Loading Data Directly into Analysis Services Using Integration Services......Page 417
 Summary......Page 418
 A......Page 420
 C......Page 421
 D......Page 422
 E......Page 425
 I......Page 426
 M......Page 427
 P......Page 428
 R......Page 429
 S......Page 431
 T......Page 432
 W–Z......Page 433