Microsoft Data Warehouse Toolkit

Joy Mundy is a member of the Kimball Group and has been focusing on data warehousing and business intelligence since the early 1990s. Warren Thornthwaite has been building decision support and data warehousing systems since 1980 and is a member of the Kimball Group.Ralph Kimball, PhD, is known worldwide as an innovator, writer, educator, speaker, and consultant in the field of data warehousing. He is the founder of the Kimball Group (www.kimballgroup.com), which provides data warehouse consulting and education.

• Foreword xxviiIntroduction xxixPart 1 Requirements, Realities, and Architecture 1Chapter 1 Defining Business Requirements 3The Most Important Determinant of Long-Term Success 5Adventure Works Cycles Introduction 6Uncovering Business Value 6Obtaining Sponsorship 7Defining Enterprise-Level Business Requirements 8Prioritizing the Business Requirements 22Revisiting the Project Planning 25Gathering Project-Level Requirements 26Summary 28Chapter 2 Designing the Business Process Dimensional Model 29Dimensional Modeling Concepts and Terminology 30Facts 31Dimensions 33Bringing Facts and Dimensions Together 34The Bus Matrix, Conformed Dimensions, and Drill Across 36Additional Design Concepts and Techniques 38Surrogate Keys 38Slowly Changing Dimensions 39Dates 42Degenerate Dimensions 43Snowflaking 43Many-to-Many or Multivalued Dimensions 44Hierarchies 47Aggregate Dimensions 49Junk Dimensions 51The Three Fact Table Types 52Aggregates 53The Dimensional Modeling Process 54Preparation 55Data Profiling and Research 60Building Dimensional Models 63Developing the Detailed Dimensional Model 66Testing and Refining the Model 68Reviewing and Validating the Model 68Case Study: The Adventure Works Cycles Orders Dimensional Model 69The Orders Fact Table 69The Dimensions 69Identifying Dimension Attributes and Facts for the Orders Business Process 72The Final Draft of the Initial Orders Model 74Detailed Orders Dimensional Model Development 75Final Dimensional Model 77Summary 77Chapter 3 The Toolset 79The Microsoft DW/BI Toolset 80Why Use the Microsoft Toolset? 82Architecture of a Microsoft DW/BI System 83Why Analysis Services? 84Why a Relational Store? 86ETL Is Not Optional 86The Role of Master Data Services 88Delivering BI Applications 88Overview of the Microsoft Tools 89Which Products Do You Need? 90SQL Server Development and Management Tools 92Summary 97Chapter 4 System Setup 99System Sizing Considerations 100Calculating Data Volumes 101Determining Usage Complexity 102Estimating Simultaneous Users 104Assessing System Availability Requirements 105How Big Will It Be? 105System Configuration Considerations 105Memory 106Monolithic or Distributed? 106Storage System Considerations 110Processors 113Setting Up for High Availability 114Software Installation and Configuration 115Development Environment Software Requirements 116Test and Production Software Requirements 120Operating Systems 122SQL Server Relational Database Setup 122Analysis Services Setup 126Integration Services Setup 129Reporting Services Setup 130Summary 131Part 2 Building and Populating the Databases 133Chapter 5 Creating the Relational Data Warehouse 135Getting Started 136Complete the Physical Design 137Surrogate Keys 138String Columns 138To Null, or Not to Null? 140Housekeeping Columns 140Table and Column Extended Properties 142Define Storage and Create Constraints and Supporting Objects 142Create Files and Filegroups 142Data Compression 144Entity and Referential Integrity Constraints 145Initial Indexing and Database Statistics 147Aggregate Tables 150Create Table Views 151Insert an Unknown Member Row 152Example CREATE TABLE Statement 152Partitioned Tables 153Finishing Up 163Staging Tables 163Metadata Setup 163Summary 164Chapter 6 Master Data Management 165Managing Master Reference Data 166Incomplete Attributes 167Data Integration 168Systems Integration 170Master Data Management Systems and the Data Warehouse 171Introducing SQL Server Master Data Services 171Model Definition Features 172Data Management Features 174User Interface: Exploring and Managing the Master Data 174Importing and Updating Data 176Exporting Data 177Full Versioning of All Attributes 179Creating a Simple Application 179Summary 186Chapter 7 Designing and Developing the ETL System 187Round Up the Requirements 188Develop the ETL Plan 191Introducing SQL Server Integration Services 192Control Flow and Data Flow 194SSIS Package Architecture 197The Major Subsystems of ETL 198Extracting Data 199Subsystem 1: Data Profiling 199Subsystem 2: Change Data Capture System 200Subsystem 3: Extract System 202Cleaning and Conforming Data 206Subsystem 4: Data Cleaning System 206Subsystem 5: Error Event Schema 214Subsystem 6: Audit Dimension Assembler 215Subsystem 7: Deduplication System 216Subsystem 8: Conforming System 217Delivering Data for Presentation 218Subsystem 9: Slowly Changing Dimension Manager 218Subsystem 10: Surrogate Key Generator 223Subsystem 11: Hierarchy Manager 223Subsystem 12: Special Dimensions Manager 224Subsystem 13: Fact Table Builders 225Subsystem 14: Surrogate Key Pipeline 229Subsystem 15: Multi-Valued Dimension Bridge Table Builder 235Subsystem 16: Late Arriving Data Handler 235Subsystem 17: Dimension Manager 238Subsystem 18: Fact Provider System 238Subsystem 19: Aggregate Builder 239Subsystem 20: OLAP Cube Builder 239Subsystem 21: Data Propagation Manager 240Managing the ETL Environment 240Summary 243Chapter 8 The Core Analysis Services OLAP Database 245Overview of Analysis Services OLAP 247Why Use Analysis Services? 247Why Not Analysis Services? 249Designing the OLAP Structure 250Planning 251Getting Started 253Create a Project and a Data Source View 255Dimension Designs 257Creating and Editing Dimensions 261Creating and Editing the Cube 274Physical Design Considerations 291Understanding Storage Modes 293Developing the Partitioning Plan 294Designing Performance Aggregations 296Planning for Deployment 298Processing the Full Cube 299Developing the Incremental Processing Plan 299Summary 304Chapter 9 Design Requirements for Real-Time BI 305Real-Time Triage 306What Does Real-Time Mean? 306Who Needs Real Time? 307Real-Time Tradeoffs 308Scenarios and Solutions 311Executing Reports in Real Time 313Serving Reports from a Cache 313Creating an ODS with Mirrors and Snapshots 314Creating an ODS with Replication 314Building a BizTalk Application 315Building a Real-Time Relational Partition 315Querying Real-Time Data in the Relational Database 317Using Analysis Services to Query Real-Time Data 318Summary 319Part 3 Developing the BI Applications 321Chapter 10 Building BI Applications in Reporting Services 323A Brief Overview of BI Applications 324Types of BI Applications 325The Value of Business Intelligence Applications 326A High-Level Architecture for Reporting 328Reviewing Business Requirements for Reporting 328Examining the Reporting Services Architecture 330Using Reporting Services as a Standard Reporting Tool 332Reporting Services Assessment 339The Reporting System Design and Development Process 340Reporting System Design 341Reporting System Development 348Building and Delivering Reports 351Planning and Preparation 351Creating Reports 354Reporting Operations 368Ad Hoc Reporting Options 369The Report Model 370Shared Datasets 371Report Parts 371Summary 372Chapter 11 PowerPivot and Excel 375Using Excel for Analysis and Reporting 376The PowerPivot Architecture: Excel on Steroids 378Creating and Using PowerPivot Databases 380Getting Started 381PowerPivot Table Design 381Creating Analytics with PowerPivot 385Observations and Guidelines on PowerPivot for Excel 392PowerPivot for SharePoint 394The PowerPivot SharePoint User Experience 394Server-Level Resources 397PowerPivot Monitoring and Management 397PowerPivot’s Role in a Managed DW/BI Environment 400Summary 401Chapter 12 The BI Portal and SharePoint 403The BI Portal 404Planning the BI Portal 405Impact on Design 406Business Process Categories 407Additional Functions 408Building the BI Portal 409Using SharePoint as the BI Portal 411Architecture and Concepts 412Setting Up SharePoint 417Summary 426Chapter 13 Incorporating Data Mining 429Defining Data Mining 430Basic Data Mining Terminology 432Business Uses of Data Mining 433Roles and Responsibilities 440SQL Server Data Mining Architecture Overview 440The Data Mining Design Environment 442Build, Deploy, and Process 442Accessing the Mining Models 443Integration Services and Data Mining 443Additional Features 444Architecture Summary 445Microsoft Data Mining Algorithms 445Decision Trees 446Naïve Bayes 447Clustering 448Sequence Clustering 448Time Series 449Association 449Neural Network 449The Data Mining Process 450The Business Phase 451The Data Mining Phase 453The Operations Phase 460Metadata 462Data Mining Examples 463Case Study: Categorizing Cities 463Case Study: Product Recommendations 472Summary 488Part 4 Deploying and Managing the DW/BI System 491Chapter 14 Designing and Implementing Security 493Identifying the Security Manager 494Securing the Hardware and Operating System 495Securing the Operating System 495Using Windows Integrated Security 496Securing the Development Environment 497Securing the Data 498Providing Open Access for Internal Users 498Itemizing Sensitive Data 500Securing Various Types of Data Access 500Securing the Components of the DW/BI System 502Reporting Services Security 502Analysis Services Security 505Relational DW Security 514Integration Services Security 520Usage Monitoring 521Summary 521Chapter 15 Metadata Plan 523Metadata Basics 524The Purpose of Metadata 524Metadata Categories 525The Metadata Repository 526Metadata Standards 526SQL Server 2008 R2 Metadata 527Cross-Tool Components 528Relational Engine Metadata 532Analysis Services 532Integration Services 533Reporting Services 533Master Data Services 534SharePoint 534External Metadata Sources 534Looking to the Future 535A Practical Metadata Approach 535Creating the Metadata Strategy 536Business Metadata Reporting 538  Process Metadata Reporting 541Technical Metadata Reporting 542Ongoing Metadata Management 543Summary 543Chapter 16 Deployment 545Setting Up the Environments 546Testing 550Development Testing 551System Testing 555Data Quality Assurance Testing 557Performance Testing 559Usability Testing 562Testing Summary 563Deploying to Production 564Relational Database Deployment 565Integration Services Package Deployment 567Analysis Services Database Deployment 568Reporting Services Report Deployment 571Master Data Services Deployment 572Data Warehouse and BI Documentation 573Core Descriptions 573Additional Documentation 575User Training 576User Support 579Desktop Readiness and Configuration 580Summary 581Chapter 17 Operations and Maintenance 583Providing User Support 584Maintaining the BI Portal 585Extending the BI Applications 586System Management 587Governing the DW/BI System 588Performance Monitoring 593Usage Monitoring 600Managing Disk Space 602Service and Availability Management 603Performance Tuning the DW/BI System 604Backup and Recovery 606Executing the ETL Packages 611Summary 611Chapter 18 Present Imperatives and Future Outlook 613Growing the DW/BI System 613Lifecycle Review with Common Problems 615Phase I — ​Requirements, Realities, Plans, and Designs 616Phase II — ​Developing the Databases 616Phase III — ​Developing the BI Applications and Portal Environment 617Phase IV — ​Deploying and Managing the DW/BI System 618Iteration and Growth 618What We Like in the Microsoft BI Toolset 619Future Directions: Room for Improvement 620Conclusion 623Index 625