book

The Microsoft® Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft® Business Intelligence Toolset, Second Edition

Name: The Microsoft® Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft® Business Intelligence Toolset, Second Edition
ISBN: 9780470640388

by Joy Mundy, Warren Thornthwaite, Ralph Kimball

March 2011

Beginner to intermediate

1588 pages

18h 57m

English

Wiley

Read now

Unlock full access

Copyright
About the Authors
Credits
Acknowledgments
Foreword
Introduction
The Data Warehouse and Business Intelligence SystemThe Kimball GroupWhy We Wrote This BookWho Should Read This BookThe Kimball LifecycleLifecycle Tracks and Task AreasKey Terminology and the Microsoft ToolsetRoles and ResponsibilitiesHow This Book Is OrganizedPart 1: Requirements, Realities, and ArchitectureChapter 1: Defining Business RequirementsChapter 2: Designing the Business Process Dimensional ModelChapter 3: The ToolsetChapter 4: System SetupPart 2: Building and Populating the DatabasesChapter 5: Creating the Relational Data WarehouseChapter 6: Master Data ManagementChapter 7: Designing and Developing the ETL SystemChapter 8: The Core Analysis Services OLAP DatabaseChapter 9: Design Requirements for Real-Time BIPart 3: Developing the BI ApplicationsChapter 10: Building BI Applications in Reporting ServicesChapter 11: PowerPivot and ExcelChapter 12: The BI Portal and SharePointChapter 13: Incorporating Data MiningPart 4: Deploying and Managing the DW/BI SystemChapter 14: Designing and Implementing SecurityChapter 15: Metadata PlanChapter 16: DeploymentChapter 17: Operations and MaintenanceChapter 18: Present Imperatives and Future OutlookAdditional InformationOn the Website
1. Requirements, Realities, and Architecture
1. Defining Business Requirements: Building the foundation.
1.1. The Most Important Determinant of Long-Term Success1.2. Adventure Works Cycles Introduction1.3. Uncovering Business Value1.3.1. Obtaining Sponsorship1.3.2. Defining Enterprise-Level Business Requirements1.3.2.1. Establishing Initial Enterprise Requirements Project Scope1.3.2.2. Gathering and Documenting Enterprise-Level Business Requirements1.3.2.2.1. Preparation1.3.2.2.2. Adventure Works Example: Preparation1.3.2.2.3. Conduct Business and IT Interviews1.3.2.2.4. Debrief and Document Interviews1.3.2.2.5. Adventure Works Example: Interview Documentation1.3.2.3. Data Auditing/Data Profiling1.3.2.4. Creating the Program Requirements Findings Document1.3.2.4.1. Synthesize Around Business Processes1.3.2.4.2. Adventure Works Example: Enterprise Requirements Documentation1.3.2.4.3. Building the Initial Data Warehouse Bus Matrix1.3.2.4.4. Adventure Works Example: Bus Matrix1.4. Prioritizing the Business Requirements1.5. Revisiting the Project Planning1.6. Gathering Project-Level Requirements1.7. Summary
2. Designing the Business Process Dimensional Model
2.1. Dimensional Modeling Concepts and Terminology2.1.1. Facts2.1.1.1. Just the Facts2.1.1.2. The Grain2.1.2. Dimensions2.1.3. Bringing Facts and Dimensions Together2.1.4. The Bus Matrix, Conformed Dimensions, and Drill Across2.2. Additional Design Concepts and Techniques2.2.1. Surrogate Keys2.2.2. Slowly Changing Dimensions2.2.3. Dates2.2.4. Degenerate Dimensions2.2.5. Snowflaking2.2.6. Many-to-Many or Multivalued Dimensions2.2.6.1. The Many-to-Many Relationship Between a Fact and Dimension2.2.6.2. Many-to-Many Between Dimensions2.2.7. Hierarchies2.2.7.1. Variable-Depth Hierarchies2.2.7.2. Frequently Changing Hierarchies2.2.8. Aggregate Dimensions2.2.9. Junk Dimensions2.2.10. The Three Fact Table Types2.2.11. Aggregates2.3. The Dimensional Modeling Process2.3.1. Preparation2.3.1.1. Identify Roles and Participants2.3.1.2. Revisit the Requirements2.3.1.3. Understand the Data Architecture Strategy2.3.1.4. Set Up the Modeling Environment2.3.1.5. Establish Naming Conventions2.3.2. Data Profiling and Research2.3.2.1. Data Profiling and Source System Exploration2.3.3. Building Dimensional Models2.3.3.1. High-Level Dimensional Model Design Session2.3.3.1.1. Creating the High-Level Dimensional Model: The Four-step Modeling Process2.3.3.1.2. The High-Level Graphical Model2.3.3.2. Identifying Dimension Attributes and Fact Measures2.3.4. Developing the Detailed Dimensional Model2.3.5. Testing and Refining the Model2.3.6. Reviewing and Validating the Model2.4. Case Study: The Adventure Works Cycles Orders Dimensional Model2.4.1. The Orders Fact Table2.4.2. The Dimensions2.4.2.1. Employee2.4.2.2. Customer and Reseller2.4.2.3. Currency2.4.3. Identifying Dimension Attributes and Facts for the Orders Business Process2.4.4. The Final Draft of the Initial Orders Model2.4.5. Detailed Orders Dimensional Model Development2.4.5.1. Identifying SCD Change Types2.4.5.2. Reviewing the Issues2.4.5.3. Identifying the Facts2.4.6. Final Dimensional Model2.5. Summary
3. The Toolset
3.1. The Microsoft DW/BI Toolset3.2. Why Use the Microsoft Toolset?3.3. Architecture of a Microsoft DW/BI System3.3.1. Why Analysis Services?3.3.2. Why a Relational Store?3.3.3. ETL Is Not Optional3.3.4. The Role of Master Data Services3.3.5. Delivering BI Applications3.4. Overview of the Microsoft Tools3.4.1. Which Products Do You Need?3.4.2. SQL Server Development and Management Tools3.4.2.1. SQL Server Management Studio3.4.2.1.1. Relational Database Management Operations3.4.2.1.2. Analysis Services Management Operations3.4.2.1.3. Reporting Services Management Operations3.4.2.1.4. Integration Services Management Operations3.4.2.2. Business Intelligence Development Studio (BIDS)3.5. Summary

4. System Setup
4.1. System Sizing Considerations4.1.1. Calculating Data Volumes4.1.2. Determining Usage Complexity4.1.2.1. Simple or Controlled Access4.1.2.2. Moderate Complexity4.1.2.3. Highly Demanding Use4.1.3. Estimating Simultaneous Users4.1.4. Assessing System Availability Requirements4.1.5. How Big Will It Be?4.2. System Configuration Considerations4.2.1. Memory4.2.2. Monolithic or Distributed?4.2.3. Storage System Considerations4.2.3.1. Balancing the Data Pipeline4.2.3.2. Disk Performance4.2.3.3. Fault Tolerance and RAID4.2.3.4. Storage Area Networks4.2.4. Processors4.2.5. Setting Up for High Availability4.3. Software Installation and Configuration4.3.1. Development Environment Software Requirements4.3.1.1. Development Database Server4.3.1.2. Database Designer4.3.1.3. Development Database Administrator4.3.1.4. ETL System Developer4.3.1.5. Report Designer4.3.1.6. Reporting Portal Developer4.3.1.7. Data Mining Model Developer4.3.1.8. Analytic Application Developer4.3.2. Test and Production Software Requirements4.3.2.1. Database Server4.3.2.2. Database Administrator4.3.3. Operating Systems4.3.4. SQL Server Relational Database Setup4.3.4.1. Security Options During Installation4.3.4.2. Files, Filegroups, and RAID4.3.4.3. Database Recovery Model4.3.4.4. Initial Database Size4.3.5. Analysis Services Setup4.3.5.1. Analysis Services File Locations and Storage Requirements4.3.5.2. Analysis Services and Memory4.3.6. Integration Services Setup4.3.6.1. Integration Services File Locations and Storage Requirements4.3.7. Reporting Services Setup4.4. Summary
2. Building and Populating the Databases
5. Creating the Relational Data Warehouse
5.1. Getting Started5.2. Complete the Physical Design5.2.1. Surrogate Keys5.2.2. String Columns5.2.3. To Null, or Not to Null?5.2.4. Housekeeping Columns5.2.5. Table and Column Extended Properties5.3. Define Storage and Create Constraints and Supporting Objects5.3.1. Create Files and Filegroups5.3.2. Data Compression5.3.3. Entity and Referential Integrity Constraints5.3.4. Initial Indexing and Database Statistics5.3.4.1. Dimension Indexes5.3.4.2. Fact Table Indexes5.3.4.3. Statistics5.3.5. Aggregate Tables5.3.6. Create Table Views5.3.7. Insert an Unknown Member Row5.3.8. Example CREATE TABLE Statement5.4. Partitioned Tables5.4.1.5.4.1.1. How Does Table Partitioning Work?5.4.1.1.1. Create Files and Filegroups5.4.1.1.2. Create the Partition Function5.4.1.1.3. Create the Partition Scheme5.4.1.1.4. Create the Partitioned Table5.4.1.2. Managing Partitioned Tables5.4.1.2.1. Adding a New Partition5.4.1.2.2. Use Table Partitions for Fast Data Loads and Minimal Downtime5.4.1.2.3. Using Mixed Grain Partitions5.4.1.2.4. Dropping Old Data from a Partitioned Table5.4.1.3. Using Partitioned Tables in the Data Warehouse Database5.5. Finishing Up5.5.1. Metadata Setup5.6. Summary
6. Master Data Management
6.1. Managing Master Reference Data6.1.1. Incomplete Attributes6.1.2. Data Integration6.1.3. Systems Integration6.1.4. Master Data Management Systems and the Data Warehouse6.2. Introducing SQL Server Master Data Services6.2.1. Model Definition Features6.2.1.1. User Interface: Defining the Master Data Model6.2.1.2. Creating Database Structures6.2.1.3. Security6.2.1.4. Programmability of Model Definition6.2.1.5. Full Versioning of Models6.2.2. Data Management Features6.2.2.1. User Interface: Exploring and Managing the Master Data6.2.2.2. Importing and Updating Data6.2.2.3. Exporting Data6.2.2.4. Full Versioning of All Attributes6.3. Creating a Simple Application6.3.1. The Business Scenario6.3.2. Keep It Simple6.3.3. Create the MDS Model6.3.4. Load the Subcategory Members6.3.5. Polish the Model6.3.6. Export to the Data Warehouse6.4. Summary
7. Designing and Developing the ETL System
7.1. Round Up the Requirements7.2. Develop the ETL Plan7.3. Introducing SQL Server Integration Services7.3.1. Control Flow and Data Flow7.3.1.1. Data Flow7.3.1.2. Error Flows7.3.2. SSIS Package Architecture7.4. The Major Subsystems of ETL7.5. Extracting Data7.5.1. Subsystem 1: Data Profiling7.5.2. Subsystem 2: Change Data Capture System7.5.3. Subsystem 3: Extract System7.5.3.1. Loading Data7.6. Cleaning and Conforming Data7.6.1. Subsystem 4: Data Cleaning System7.6.1.1. Cleaning Data in the Data Flow7.6.1.2. Halting Package Execution7.6.2. Subsystem 5: Error Event Schema7.6.3. Subsystem 6: Audit Dimension Assembler7.6.4. Subsystem 7: Deduplication System7.6.5. Subsystem 8: Conforming System7.7. Delivering Data for Presentation7.7.1. Subsystem 9: Slowly Changing Dimension Manager7.7.1.1. Standard Handling for Slowly Changing Dimensions7.7.1.2. Custom Handling for Slowly Changing Dimensions7.7.1.3. Alternatives to the Slowly Changing Dimension Transform7.7.2. Subsystem 10: Surrogate Key Generator7.7.3. Subsystem 11: Hierarchy Manager7.7.4. Subsystem 12: Special Dimensions Manager7.7.5. Subsystem 13: Fact Table Builders7.7.5.1. Transaction Grain Fact Table Loader7.7.5.2. Periodic Snapshot Fact Table Loader7.7.5.3. Accumulating Snapshot Fact Table Loader7.7.6. Subsystem 14: Surrogate Key Pipeline7.7.6.1. Surrogate Key Pipeline Technique #1: Cascading Lookups7.7.6.2. Surrogate Key Pipeline Technique #2: Database Joins7.7.6.3. Which Technique Is Best?7.7.7. Subsystem 15: Multi-Valued Dimension Bridge Table Builder7.7.8. Subsystem 16: Late Arriving Data Handler7.7.8.1. Late Arriving Dimension Members7.7.8.2. Late Arriving Dimension Updates7.7.8.3. Late Arriving Facts7.7.9. Subsystem 17: Dimension Manager7.7.10. Subsystem 18: Fact Provider System7.7.11. Subsystem 19: Aggregate Builder7.7.12. Subsystem 20: OLAP Cube Builder7.7.13. Subsystem 21: Data Propagation Manager7.8. Managing the ETL Environment7.9. Summary
8. The Core Analysis Services OLAP Database
8.1. Overview of Analysis Services OLAP8.1.1. Why Use Analysis Services?8.1.2. Why Not Analysis Services?8.2. Designing the OLAP Structure8.2.1. Planning8.2.1.1. Cube Content8.2.1.2. Cube Granularity8.2.1.3. Cube Usage8.2.2. Getting Started8.2.2.1. Setup8.2.2.2. Create Relational Views8.2.2.3. Populate the Data Warehouse Database8.2.3. Create a Project and a Data Source View8.2.4. Dimension Designs8.2.4.1. Standard Dimensions8.2.4.2. Variable Depth or Parent-Child Hierarchies8.2.4.3. Multivalued or Many-to-Many Dimensions8.2.4.4. Role-Playing Dimensions8.2.5. Creating and Editing Dimensions8.2.5.1. Editing Dimension Properties8.2.5.2. Editing Attribute Properties8.2.5.3. Time and Account Dimensions8.2.5.4. Creating Hierarchies8.2.5.5. Set Up Attribute Relationships8.2.5.6. Browsing Dimension Data8.2.6. Creating and Editing the Cube8.2.6.1. Edit the Cube Structure8.2.6.1.1. Edit Measure Groups and Measures8.2.6.1.2. Edit Dimensions in the Cube8.2.6.1.3. Cube Properties8.2.6.2. Edit Dimension Usage8.2.6.2.1. Measure Groups at Different Granularity8.2.6.2.2. Complex Relationships Between Dimensions and Measure Groups8.2.6.3. Build, Deploy, and Process the Project8.2.6.4. Create Calculations8.2.6.4.1. The Calculations Tab8.2.6.4.2. Adding Business Intelligence8.2.6.5. Define Key Performance Indicators8.2.6.6. Create Actions8.2.6.7. Partitions and Aggregations8.2.6.8. Maintain Perspectives8.2.6.9. Translations8.3. Physical Design Considerations8.3.1. Understanding Storage Modes8.3.2. Developing the Partitioning Plan8.3.3. Designing Performance Aggregations8.3.4. Planning for Deployment8.3.5. Processing the Full Cube8.3.6. Developing the Incremental Processing Plan8.3.6.1. Scheduled Processing8.3.6.1.1. Full Reprocessing8.3.6.1.2. Incremental Processing8.3.6.1.3. Incremental Processing with Multiple Partitions8.3.6.2. Planning for Updates to Dimensions8.3.6.3. Planning for Fact Updates and Deletes8.4. Summary
9. Design Requirements for Real-Time BI
9.1. Real-Time Triage9.1.1. What Does Real-Time Mean?9.1.2. Who Needs Real Time?9.1.3. Real-Time Tradeoffs9.2. Scenarios and Solutions9.2.1. Executing Reports in Real Time9.2.2. Serving Reports from a Cache9.2.3. Creating an ODS with Mirrors and Snapshots9.2.4. Creating an ODS with Replication9.2.5. Building a BizTalk Application9.2.6. Building a Real-Time Relational Partition9.2.6.1. Querying Real-Time Data in the Relational Database9.2.6.2. Using Analysis Services to Query Real-Time Data9.3. Summary
3. Developing the BI Applications
10. Building BI Applications in Reporting Services
10.1. A Brief Overview of BI Applications10.1.1. Types of BI Applications10.1.2. The Value of Business Intelligence Applications10.2. A High-Level Architecture for Reporting10.2.1. Reviewing Business Requirements for Reporting10.2.2. Examining the Reporting Services Architecture10.2.3. Using Reporting Services as a Standard Reporting Tool10.2.3.1. Creating Reports10.2.3.2. Finding Reports10.2.3.3. Viewing Reports10.2.3.4. Receiving Results10.2.3.5. Changing Reports10.2.3.6. Solid, Reliable System10.2.4. Reporting Services Assessment10.3. The Reporting System Design and Development Process10.3.1. Reporting System Design10.3.1.1. Determining the Initial Report Set10.3.1.2. Creating the Report Template10.3.1.3. Creating Report Specifications and Documentation10.3.1.3.1. Report Mock-Ups10.3.1.3.2. User Interaction List10.3.1.3.3. Detailed Documentation10.3.1.4. Designing the Navigation Framework10.3.1.5. Conducting the User Review10.3.2. Reporting System Development10.3.2.1. Prepare10.3.2.2. Build10.3.2.3. Test10.3.2.4. Deploy10.4. Building and Delivering Reports10.4.1. Planning and Preparation10.4.1.1. Setting Up the Development Environment10.4.1.2. Creating Standard Templates10.4.2. Creating Reports10.4.2.1. Revisit the Report Specifications10.4.2.2. The Report Creation Process10.4.2.3. Creating Your First Standard Report10.4.2.3.1. Creating the Data Source and Dataset Query10.4.2.3.2. Design the Report Layout10.4.2.3.3. Unit Test10.4.2.3.4. Deploy to the Test Report Server and Test Some More10.4.2.3.5. Deploy to Production10.4.3. Reporting Operations10.5. Ad Hoc Reporting Options10.5.1. The Report Model10.5.2. Shared Datasets10.5.3. Report Parts10.6. Summary
11. PowerPivot and Excel
11.1. Using Excel for Analysis and Reporting11.2. The PowerPivot Architecture: Excel on Steroids11.3. Creating and Using PowerPivot Databases11.3.1. Getting Started11.3.2. PowerPivot Table Design11.3.2.1. Loading the Data11.3.2.2. Creating the Relationships11.3.3. Creating Analytics with PowerPivot11.3.3.1. Creating a PowerPivot PivotTable11.3.3.2. Adding New Measures to the PivotTable11.3.3.3. The CALCULATE() Function11.3.3.4. Adding a Computed Column to the PivotTable Database11.3.4. Observations and Guidelines on PowerPivot for Excel11.4. PowerPivot for SharePoint11.4.1. The PowerPivot SharePoint User Experience11.4.1.1. PowerPivot Publishing11.4.1.2. PowerPivot Viewing11.4.1.3. PowerPivot as a Data Source11.4.2. Server-Level Resources11.4.3. PowerPivot Monitoring and Management11.4.3.1. PowerPivot Monitoring11.4.3.2. PowerPivot Workbook Publishing Process11.5. PowerPivot's Role in a Managed DW/BI Environment11.6. Summary
12. The BI Portal and SharePoint
12.1. The BI Portal12.2. Planning the BI Portal12.2.1. Impact on Design12.2.2. Business Process Categories12.2.3. Additional Functions12.2.4. Building the BI Portal12.3. Using SharePoint as the BI Portal12.3.1. Architecture and Concepts12.3.1.1. SharePoint's Three-Tier Architecture and Topology12.3.1.2. SharePoint Terminology12.3.2. Setting Up SharePoint12.3.2.1. The Installation Process12.3.2.2. Plan SharePoint for BI12.3.2.3. Installing the Test System12.3.2.3.1. Set Up the Test Server12.3.2.3.2. Install SharePoint Server 201012.3.2.3.3. Install PowerPivot for SharePoint12.3.2.3.4. Verify the SharePoint Install12.3.2.3.5. Install, Configure, and Verify Reporting Services12.3.2.4. Completing the BI Portal12.3.2.5. The Added Functionality of the BIPortal Site Template12.3.2.6. Exploring SharePoint12.4. Summary
13. Incorporating Data Mining
13.1. Defining Data Mining13.1.1. Basic Data Mining Terminology13.1.2. Business Uses of Data Mining13.1.2.1. Classification13.1.2.2. Estimation (Regression)13.1.2.3. Prediction13.1.2.4. Association or Affinity Grouping13.1.2.5. Clustering (Segmentation)13.1.2.6. Anomaly Detection13.1.2.7. Description and Profiling13.1.2.8. Business Task Summary13.1.3. Roles and Responsibilities13.2. SQL Server Data Mining Architecture Overview13.2.1. The Data Mining Design Environment13.2.2. Build, Deploy, and Process13.2.3. Accessing the Mining Models13.2.4. Integration Services and Data Mining13.2.5. Additional Features13.2.6. Architecture Summary13.3. Microsoft Data Mining Algorithms13.3.1. Decision Trees13.3.2. Naïve Bayes13.3.3. Clustering13.3.4. Sequence Clustering13.3.5. Time Series13.3.6. Association13.3.7. Neural Network13.4. The Data Mining Process13.4.1. The Business Phase13.4.1.1. Identifying Business Opportunities13.4.1.2. Understanding the Data13.4.1.3. Describing the Data Mining Opportunity13.4.2. The Data Mining Phase13.4.2.1. Data Preparation13.4.2.1.1. Cleaning and Transforming13.4.2.1.2. Integrating External Variables13.4.2.1.3. Building the Case Sets13.4.2.2. Model Development13.4.2.3. Model Validation (Evaluation)13.4.2.3.1. Comparing Models13.4.2.3.2. Business Review13.4.3. The Operations Phase13.4.3.1. Implementation13.4.3.2. Assess Impact13.4.3.3. Maintain the Model13.4.4. Metadata13.5. Data Mining Examples13.5.1. Case Study: Categorizing Cities13.5.1.1. Categorizing Cities: Business Opportunity13.5.1.2. Categorizing Cities: Data Understanding13.5.1.3. Categorizing Cities: Data Preparation13.5.1.4. Categorizing Cities: Model Development13.5.1.5. Categorizing Cities: Model Validation13.5.1.6. Categorizing Cities: Implementation13.5.1.7. Categorization Cities: Maintenance and Assessment13.5.2. Case Study: Product Recommendations13.5.2.1. Product Recommendations: The Business Phase13.5.2.1.1. Product Recommendations: Business opportunities13.5.2.1.2. Product Recommendations: Data Understanding13.5.2.2. Product Recommendations: The Data Mining Phase13.5.2.2.1. Product Recommendations: Data Preparation13.5.2.2.2. Product Recommendations: Model Development13.5.2.2.3. Product Recommendations: Model Validation13.5.2.3. Product Recommendations: The Operations Phase13.6. Summary
4. Deploying and Managing the DW/BI System
14. Designing and Implementing Security
14.1. Identifying the Security Manager14.2. Securing the Hardware and Operating System14.2.1. Securing the Operating System14.2.2. Using Windows Integrated Security14.3. Securing the Development Environment14.4. Securing the Data14.4.1. Providing Open Access for Internal Users14.4.2. Itemizing Sensitive Data14.4.3. Securing Various Types of Data Access14.5. Securing the Components of the DW/BI System14.5.1. Reporting Services Security14.5.1.1. Administrative Roles for Reporting Services14.5.1.2. User Roles for Reporting Services14.5.1.3. Reporting Services in SharePoint Integrated Mode14.5.2. Analysis Services Security14.5.2.1. Administrative Roles for Analysis Services14.5.2.2. User Roles for Analysis Services14.5.2.2.1. Dimension Security14.5.2.2.2. Cell Security14.5.2.3. Dynamic Security14.5.2.4. PowerPivot Security14.5.3. Relational DW Security14.5.3.1. Administrative Roles for the Relational Database14.5.3.2. User Roles for the Relational Database14.5.3.2.1. Reporting Account Permissions14.5.3.2.2. Business User Roles14.5.3.2.3. Row-Level or Filtering Security14.5.3.2.4. Testing Relational Security14.5.4. Integration Services Security14.6. Usage Monitoring14.7. Summary
15. Metadata Plan
15.1. Metadata Basics15.1.1. The Purpose of Metadata15.1.2. Metadata Categories15.1.3. The Metadata Repository15.2. Metadata Standards15.3. SQL Server 2008 R2 Metadata15.3.1. Cross-Tool Components15.3.2. Relational Engine Metadata15.3.3. Analysis Services15.3.4. Integration Services15.3.5. Reporting Services15.3.6. Master Data Services15.3.7. SharePoint15.3.8. External Metadata Sources15.3.8.1. System Monitor15.3.8.2. Active Directory15.3.9. Looking to the Future15.4. A Practical Metadata Approach15.4.1. Creating the Metadata Strategy15.4.2. Business Metadata Reporting15.4.2.1. Analysis Services as Primary Query Platform15.4.2.2. Relational Engine Extended Properties15.4.2.3. Business Metadata Schema15.4.3. Process Metadata Reporting15.4.4. Technical Metadata Reporting15.4.5. Ongoing Metadata Management15.5. Summary
16. Deployment
16.1. Setting Up the Environments16.2. Testing16.2.1. Development Testing16.2.2. System Testing16.2.3. Data Quality Assurance Testing16.2.4. Performance Testing16.2.4.1. Service Level Confirmation16.2.4.2. Processing Performance: Getting Data In16.2.4.3. Query Performance: Getting Data Out16.2.5. Usability Testing16.2.6. Testing Summary16.3. Deploying to Production16.3.1. Relational Database Deployment16.3.2. Integration Services Package Deployment16.3.3. Analysis Services Database Deployment16.3.4. Reporting Services Report Deployment16.3.5. Master Data Services Deployment16.4. Data Warehouse and BI Documentation16.4.1. Core Descriptions16.4.1.1. Business Process Dimensional Model Descriptions16.4.1.2. Table and Column Descriptions16.4.1.3. Report Descriptions16.4.2. Additional Documentation16.5. User Training16.6. User Support16.7. Desktop Readiness and Configuration16.8. Summary
17. Operations and Maintenance
17.1. Providing User Support17.1.1. Maintaining the BI Portal17.1.2. Extending the BI Applications17.2. System Management17.2.1. Governing the DW/BI System17.2.1.1. Identifying and Terminating User Sessions17.2.1.1.1. Relational Database17.2.1.1.2. Analysis Services Database17.2.1.1.3. Reporting Services17.2.1.2. Resource Governance17.2.1.2.1. Relational Database17.2.1.2.2. Analysis Services Database17.2.2. Performance Monitoring17.2.2.1. Relational Database17.2.2.2. Analysis Services Database17.2.2.3. Reporting Services17.2.2.4. Integration Services17.2.2.5. PowerPivot17.2.3. Usage Monitoring17.2.4. Managing Disk Space17.2.5. Service and Availability Management17.2.6. Performance Tuning the DW/BI System17.2.7. Backup and Recovery17.2.7.1. Relational Databases17.2.7.2. Integration Services17.2.7.3. Analysis Services17.2.7.4. Reporting Services17.2.7.5. Recovery17.2.8. Executing the ETL Packages17.3. Summary
18. Present Imperatives and Future Outlook
18.1. Growing the DW/BI System18.2. Lifecycle Review with Common Problems18.2.1. Phase I — Requirements, Realities, Plans, and Designs18.2.2. Phase II — Developing the Databases18.2.3. Phase III — Developing the BI Applications and Portal Environment18.2.4. Phase IV — Deploying and Managing the DW/BI System18.2.5. Iteration and Growth18.3. What We Like in the Microsoft BI Toolset18.4. Future Directions: Room for Improvement18.4.1. Query Tools18.4.2. Metadata18.4.3. Relational Database Engine18.4.4. Analysis Services18.4.5. Master Data Services18.4.6. Integration18.4.7. Customer Focus18.5. Conclusion

Overview

Best practices and invaluable advice from world-renowned data warehouse experts

In this book, leading data warehouse experts from the Kimball Group share best practices for using the upcoming "Business Intelligence release" of SQL Server, referred to as SQL Server 2008 R2. In this new edition, the authors explain how SQL Server 2008 R2 provides a collection of powerful new tools that extend the power of its BI toolset to Excel and SharePoint users and they show how to use SQL Server to build a successful data warehouse that supports the business intelligence requirements that are common to most organizations. Covering the complete suite of data warehousing and BI tools that are part of SQL Server 2008 R2, as well as Microsoft Office, the authors walk you through a full project lifecycle, including design, development, deployment and maintenance.

Features more than 50 percent new and revised material that covers the rich new feature set of the SQL Server 2008 R2 release, as well as the Office 2010 release
Includes brand new content that focuses on PowerPivot for Excel and SharePoint, Master Data Services, and discusses updated capabilities of SQL Server Analysis, Integration, and Reporting Services
Shares detailed case examples that clearly illustrate how to best apply the techniques described in the book
The accompanying Web site contains all code samples as well as the sample database used throughout the case studies

The Microsoft Data Warehouse Toolkit, Second Edition provides you with the knowledge of how and when to use BI tools such as Analysis Services and Integration Services to accomplish your most essential data warehousing tasks.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

Publisher Resources

ISBN: 9780470640388Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills