Practical Business Intelligence with SQL Server 2005

by ;
  • ISBN13:


  • ISBN10:


  • Edition: 1st
  • Format: Paperback
  • Copyright: 2006-08-28
  • Publisher: Addison-Wesley Professional
  • Purchase Benefits
  • Free Shipping On Orders Over $35!
    Your order must be $35 or more to qualify for free economy shipping. Bulk sales, PO's, Marketplace items, eBooks and apparel do not qualify for this offer.
  • Get Rewarded for Ordering Your Textbooks! Enroll Now
List Price: $64.99 Save up to $12.20
  • Buy New
    Add to Cart Free Shipping


Supplemental Materials

What is included with this book?

  • The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.
  • The eBook copy of this book is not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.


Design, Build, and Manage High-Value BI Solutions with SQL Server 2005 In this book, two of Microsoftrs"s leading consultants illustrate how to use SQL Server 2005 Business Intelligence (BI) technologies to solve real-world problems in markets ranging from retail and finance to healthcare. Drawing on extensive personal experience with Microsoftrs"s strategic customers, John C. Hancock and Roger Toren offer unprecedented insight into BI systems design and step-by-step best practices for implementation, deployment, and management. Hancock and Toren introduce practical BI concepts and terminology and provide a concise primer on the Microsoft BI platform. Next, they turn to the heart of the book-constructing solutions. Each chapter-length case study begins with the customerrs"s business goals, and then guides you through detailed data modeling. The case studies show how to avoid the pitfalls that derail many BI projects. Yours"ll translate each model into a working system and learn how to deploy it into production, maintenance, and efficient operation. Whether yours"re a decision-maker, architect, developer, or DBA, this book brings together all the knowledge yours"ll need to derive maximum business value from any BI project. bull; Leverage SQL Server 2005 databases, Integration Services, Analysis Services, and Reporting Services bull; Build data warehouses and extend them to support very large databases bull; Design effective Analysis Services databases bull; Ensure the superior data quality your BI system needs bull; Construct advanced enterprise scorecard applications bull; Use data mining to segment customers, cross-sell, and increase the value of each transaction bull; Design real-time BI applications bull; Get hands-on practice with SQL Server 2005rs"s BI toolset

Author Biography

John C. Hancock, Senior Consultant with Microsoft Consulting Services (MCS) in Toronto, Canada Roger Toren, Principal Consultant with MCS in Vancouver

Table of Contents

Acknowledgmentsp. xvi
About the Authorp. xvii
Prefacep. xviii
Introduction to Business Intelligencep. 11
What Is Business Intelligence?p. 1
Transaction Systems and the Search for Informationp. 2
Why OLTP Reporting and Analysis Fails to Deliverp. 3
Data Warehousesp. 5
The Data Warehouse Designp. 5
Time and the Data Warehousep. 7
Getting Data into the Data Warehousep. 8
OLAP to the Rescuep. 10
Loading Information into OLAP Databasesp. 11
Getting Information out of OLAP Databasesp. 11
Why Is OLAP So Fast?p. 12
Dimensional Modeling Conceptsp. 14
Hierarchiesp. 15
Stars and Snowflakesp. 16
Choosing Between Star and Snowflake for a Dimensionp. 17
Using Surrogate Keysp. 18
A Practical Approach to Dimensional Modelingp. 19
Designing a Dimensional Data Modelp. 19
Making a List of Candidate Attributes and Dimensionsp. 20
Making a List of Candidate Measuresp. 21
Grouping the Measures with the Same Grain into Fact Tablesp. 21
Fitting the Source Data to the Modelp. 22
Business Intelligence Projectsp. 23
A Business Value-Based Approach to BI Projectsp. 23
Business Intelligence Project Pitfallsp. 24
Summaryp. 28
Introduction to SQL Server 2005p. 29
SQL Server Componentsp. 29
Development and Management Toolsp. 29
Deploying Componentsp. 31
SQL Server Database Enginep. 31
Managementp. 32
Scheduled Execution of Processesp. 33
Securityp. 33
Availabilityp. 34
Scalabilityp. 35
Support for Very Large Databasesp. 35
Integration Servicesp. 35
Designing Packagesp. 37
Data Qualityp. 41
Deploying and Configuring Packagesp. 41
Analysis Servicesp. 42
Analysis Services Architecturep. 42
Development Environmentp. 44
Managing and Securing Analysis Servicesp. 44
The Unified Dimensional Modelp. 46
Support for Large and Mission-Critical BI Solutionsp. 47
Reporting Servicesp. 48
Reporting Architecturep. 48
Reporting Services Featuresp. 51
Managing and Securing Reportsp. 54
Data Miningp. 55
Data Mining Architecturep. 56
Data Mining Featuresp. 59
Other Technologiesp. 60
Full-Text Searchp. 60
Notification Servicesp. 61
Service Brokerp. 61
Summaryp. 61
Building a Data Warehousep. 63
Business Problemp. 63
Problem Statementp. 63
Solution Overviewp. 64
Business Requirementsp. 64
High-Level Architecturep. 65
Business Benefitsp. 65
Data Modelp. 66
What Process Will We Be Focusing On?p. 66
What Level of Detail Do We Need?p. 67
What Are the Ways of Looking At the Information?p. 68
What Are We Measuring?p. 73
Technical Solutionp. 77
Building Dimension Tablesp. 78
Building Fact Tablesp. 83
Securing the Data Warehousep. 87
Managing the Solutionp. 90
Deploymentp. 90
Maintenancep. 92
Operationsp. 93
Next Stepsp. 95
Extending the Manufacturing Solutionp. 96
Using the BI Development Studio to Generate the Data Warehousep. 96
Summaryp. 97
Building a Data Integration Processp. 99
Business Problemp. 100
Problem Statementp. 100
Solution Overviewp. 100
Business Requirementsp. 101
High-Level Architecturep. 101
Business Benefitsp. 102
Data Modelp. 103
The Budget Datap. 103
Working with Extended Measuresp. 104
Technical Solutionp. 105
Getting Started with Integration Servicesp. 106
Data Sources and Destinationsp. 106
Loading the Dimensionsp. 108
Loading the Fact Tablep. 116
Loading the Budget Information from Excelp. 119
Loading Multiple Sets of Datap. 125
Managing the Solutionp. 128
Protecting Your Source Codep. 128
Deploymentp. 128
Deploying Packages to a New Serverp. 133
Securityp. 134
Maintenancep. 134
Operationsp. 135
Next Stepsp. 137
Data Qualityp. 137
Scaling Your Solutionp. 138
Other Transformationsp. 138
Control Flow Tasksp. 139
Summaryp. 139
Building an Analysis Services Databasep. 141
Business Problemp. 141
Problem Statementp. 141
Solution Overviewp. 142
Business Requirementsp. 142
High-Level Architecturep. 143
Business Benefitsp. 145
Data Modelp. 145
How Do We Handle "On-Time" Shipments?p. 145
How Late Were These Shipments, Anyway?p. 147
What Doesn't Work Well in a Cube?p. 148
Technical Solutionp. 148
Getting Started with Analysis Servicesp. 148
Building the Initial Cubep. 151
Setting Up the Dimensionsp. 157
Modifying the Cubep. 161
Managing the Solutionp. 169
Deploymentp. 169
Maintenancep. 172
Operationsp. 173
Next Stepsp. 176
Extending the Manufacturing Solutionp. 176
Accessing the Informationp. 176
Using BI Development Studio in Online Modep. 176
Summaryp. 177
Reportingp. 179
Business Problemp. 179
Problem Statementp. 179
Solution Overviewp. 180
Business Requirementsp. 180
High-Level Architecturep. 181
Business Benefitsp. 184
Data Modelp. 184
Who Does the Work?p. 185
What Are They Working On?p. 186
How Do We Measure What Work They Are Doing?p. 187
How Much Work Will They Be Doing in the Future?p. 188
Technical Solutionp. 189
Getting Started with Reporting Servicesp. 189
Accessing Reportsp. 193
Giving Users What They Wantp. 195
Presenting Informationp. 197
Securing the Informationp. 202
Accessing the Databasep. 204
Subscribing to Reportsp. 205
Managing the Solutionp. 208
Deploymentp. 209
Maintenancep. 210
Operationsp. 211
Next Stepsp. 212
Adding Code to Reports Using .NETp. 212
Using Report Builder for End-User Reportingp. 212
Supporting Multiple Languages in a Reportp. 213
Summaryp. 213
Data Qualityp. 215
Business Problemp. 215
Problem Statementp. 216
Solution Overviewp. 216
Business Requirementsp. 217
High-Level Architecturep. 217
Business Benefitsp. 218
Data Modelp. 218
Technical Solutionp. 220
Merging and De-duplicating Customer Datap. 220
Dealing with Missing Dimension Membersp. 229
Adding Row Counts for Auditingp. 239
Preventing Bad Data at the Sourcep. 240
Managing the Solutionp. 241
Deploymentp. 241
Operationsp. 241
Next Stepsp. 242
Summaryp. 243
Managing Changing Datap. 245
Business Problemp. 245
Problem Statementp. 246
Solution Overviewp. 246
Business Requirementsp. 246
High-Level Architecturep. 247
Business Benefitsp. 247
Data Modelp. 248
Managing Dimensions When History Doesn't Matterp. 249
Managing Dimensions to Preserve Historyp. 250
Technical Solutionp. 253
Updating Dimensions with Changing Attributesp. 253
Preserving Information for Dimensions with Historical Attributesp. 256
Detecting Changes in Source Dimension Datap. 260
Inserting Facts with Surrogate Key Lookups from Changing Dimensionsp. 261
Dealing with Very Wide Dimension Tablesp. 263
Analysis Services Dimension Changesp. 263
Managing the Solutionp. 266
Deploymentp. 267
Operationsp. 267
Next Stepsp. 269
Dealing with Updated Factsp. 269
Historical Snapshots for Reportingp. 270
Summaryp. 270
Scorecardsp. 271
Business Problemp. 271
Problem Statementp. 272
Solution Overviewp. 272
Business Requirementsp. 272
High-Level Architecturep. 273
Business Benefitsp. 275
Data Modelp. 275
How Good Are We at Handling Calls?p. 276
How Are Our Crime Reduction Initiatives Going?p. 278
How Are We Improving Professional Standards?p. 279
What Does the Public Think?p. 280
Technical Solutionp. 281
Analysis Services Databasep. 281
Building the Reportsp. 290
Building the Scorecard Portalp. 295
Managing the Solutionp. 302
Deploymentp. 302
Securityp. 303
Maintenancep. 305
Operationsp. 306
Next Stepsp. 306
Microsoft Office Tools for Scorecardsp. 307
Supporting Multiple Languages with Analysis Servicesp. 307
Summaryp. 308
Data Miningp. 309
Business Problemp. 309
Problem Statementp. 309
Solution Overviewp. 310
Business Requirementsp. 310
High-Level Architecturep. 311
Business Benefitsp. 314
Data Modelp. 314
How Often Are Users Visiting the Web Site?p. 315
Who Is Using the Web Site?p. 317
What Interesting Attributes Can We Track?p. 318
Technical Solutionp. 319
Adding Visit Information to the Data Warehousep. 319
How We Will Be Using Data Miningp. 321
Approaching the Customer-Segmentation Problemp. 322
Getting Started with Data Miningp. 323
Analyzing with Data Mining Informationp. 329
Creating a Model for Product Recommendationsp. 330
Add Data Mining Intelligence into a Web Applicationp. 334
Managing the Solutionp. 337
Deploymentp. 337
Maintenancep. 338
Operationsp. 338
Next Stepsp. 339
Sequence Clustering to Build Smarter Web Sitesp. 339
Other Data Mining Possibilitiesp. 339
Using Data Mining in Integration Services to Improve Data Qualityp. 340
Summaryp. 340
Very Large Data Warehousesp. 343
Business Problemp. 343
Problem Statementp. 344
Solution Overviewp. 345
Business Requirementsp. 345
High-Level Architecturep. 346
Business Benefitsp. 349
Data Modelp. 349
Technical Solutionp. 352
Partitioned Tablesp. 352
Loading Large Volumes of Datap. 358
Partitioning Analysis Services Cubesp. 362
Aggregation Designp. 366
Large Dimension Support in SQL Server 2005p. 367
Managing the Solutionp. 368
Initial Loading of the Data Warehousep. 368
Managing Table Partitionsp. 368
Managing Cube Partitionsp. 372
Next Stepsp. 375
Automating the Processp. 375
Partitioned Viewsp. 376
Scaling out Using Analysis Services Database Synchronizationp. 376
Summaryp. 377
Real-Time Business Intelligencep. 379
Business Problemp. 379
Problem Statementp. 380
Solution Overviewp. 380
Business Requirementsp. 380
High-Level Architecturep. 380
Business Benefitsp. 381
Data Modelp. 382
Technical Solutionp. 383
Cube Design for Real Timep. 384
Real-Time ETL-Working with the Data Sourcep. 393
Managing the Solutionp. 394
Operationsp. 394
Next Stepsp. 396
Maintaining a Consistent View of the Source Datap. 396
Loading Data Directly into Analysis Services Using Integration Servicesp. 396
Notification Servicesp. 397
Summaryp. 397
Indexp. 399
Table of Contents provided by Ingram. All Rights Reserved.


Practical Business Intelligence with SQL Server 2005 Practical Business Intelligence with SQL Server 2005 Preface Each of the areas in the Microsoft Business Intelligence (BI) platform could take a whole book to describe in detail. Why then are we attempting to cover all aspects of BI solutions in one book? The answer is that people who design, build, and manage a BI solution need to have a good understanding of how all the BI components in SQL Server 2005 can work together, from relational databases to store the data, Integration Services to move and transform the data, Analysis Services to provide a platform to analyze and query the data, and Reporting Services to present the information to users. We really wanted to avoid just giving a superficial or "marketing" view of all the different aspects of the BI platform, so we have designed this book in a practical way. If you were starting out to build a BI solution for a business problem and had a clearly defined budget and deliverables, you would not necessarily learn every technical aspect of all the SQL Server components, just those areas you need to deliver business value. Books are available that cover specific technologies in depth, such as On Line Analytical Processing (OLAP) or data mining, and other books describe areas such as how to properly design data warehouses--this book combines the essentials of both areas to teach you how to design good solutions and describes the concrete steps and best practices to build a working solution in SQL Server 2005, without trying to cover every technology in depth. Structure of This Book Instead of structuring this book around the technology and trying hard to list and explain every feature in the platform, we pick a specific business issue for each chapter and show you how a complete solution could be built, touching on all the key technologies and design challenges along the way. We selected the business areas from various vertical industries that we have worked with, such as health care or financial services. Although all these vertical industries might not be applicable to your job, you can apply the lessons you learn to your own environment. In some cases, we have taken the liberty of simplifying the business problem to convey more clearly a specify point in a BI solution to a broader audience. It is not our intent to turn readers into industry experts. The authors have both worked as BI consultants for many years and have a fundamental belief that you cannot learn to build effective solutions from a technology reference book or a step-by-step guide to using technical features. Instead, we share our thoughts on design decisions at every point along the way to building a working solution and explain the tradeoffs and consequences in the real world. Each chapter focuses on some specific technology areas and builds on your knowledge from previous chapters, so we recommend that you read through them in sequence. The first chapter, "Introduction to Business Intelligence," introduces the terms and concepts behind BI, including data wareho

Rewards Program

Write a Review